Building a DVD Rental Admin App with Spring Boot + Thymeleaf Based on the PostgreSQL dvdrental Sample DB

When building the DVD rental admin app, the first decision wasn’t the UI — it was “what data to build on.”

Rather than designing tables from scratch, I proceeded by using the PostgreSQL sample database dvdrental as the foundation and building an admin interface on top of it.

This is an article summarizing how I mapped dvdrental into an admin application using Java / Spring Boot / Thymeleaf / PostgreSQL.

The sample DB used as the basis can be found at the following page:

PostgreSQL Sample Database

This article summarizes the thinking behind how the DVD rental admin app itself was assembled.

What You’ll Learn from This Article

• Why the PostgreSQL sample DB dvdrental was used as the foundation for a business app
• The flow of building an admin app with Spring Boot + Thymeleaf
• How customers, stores, staff, inventory, rentals, and payments were mapped to screens based on the ER diagram
• How JPA and SQL were used differently
• Techniques for making it usable as a business app, such as retaining search conditions and confirmation screens

The main features created this time are as follows:

• Login
• Dashboard
• Customer management
• Store management
• Staff management
• Inventory management
• Payment management
• Rental management
• Sales report

Why the PostgreSQL dvdrental Sample DB Was Used as Foundation

The very first thing established in this app was using the PostgreSQL sample DB as the foundation.

dvdrental comes pre-loaded with tables needed for DVD rental operations: stores, staff, customers, films, inventory, rentals, and payments.

This was quite significant — development could begin with the following subject matter already in place:

• Search screens like customer lists and staff lists
• Reference screens like customer details and payment details
• Aggregations based on rentals and payments
• CRUD screens for stores, inventory, and customers

It felt easier to first understand the existing schema and data, then build screens on top, rather than designing business tables from scratch.

First, Made the Sample DB Usable

In this repository, docker/postgres/init/01-dvdrental-full.sql is loaded when starting the local PostgreSQL.

Additionally, docker/postgres/init/02-convert-currency-to-jpy.sql is also applied to enable screen verification with payment amounts converted to JPY.

Organized the dvdrental ER Diagram and Key Table Relationships

Before starting to build screens, I first roughly organized the relationships between tables central to this admin screen.

dvdrental has many tables, but trying to trace all of them in detail from the start tends to scatter thinking. So I first focused on the key tables directly connected to screens: customers, staff, stores, films, inventory, rentals, and payments.

Simplified, the key relationships central to this admin screen look like this:

erDiagram
	STORE ||--o{ STAFF : has
	STORE ||--o{ CUSTOMER : has
	STORE ||--o{ INVENTORY : stocks
	STAFF ||--o{ RENTAL : handles
	STAFF ||--o{ PAYMENT : handles
	CUSTOMER ||--o{ RENTAL : rents
	CUSTOMER ||--o{ PAYMENT : pays
	FILM ||--o{ INVENTORY : stocked_as
	INVENTORY ||--o{ RENTAL : rented_as
	RENTAL ||--o{ PAYMENT : billed_by

Having this diagram in mind first makes the role of each screen easier to organize. For example: customer management is a screen centered on customer that looks at store and payment; rental management is a screen centered on rental that traverses inventory, film, and customer; payment management is a screen centered on payment that looks at customer, staff, and rental.

What Kind of Admin App Was Built with Spring Boot + Thymeleaf

The app itself is a server-rendered admin screen using Spring Boot + Thymeleaf.

The main tech stack is as follows:

• Java 21
• Spring Boot 4
• Thymeleaf
• Spring Security
• Spring Data JPA
• NamedParameterJdbcTemplate
• PostgreSQL
• Flyway
• Docker

Rather than leaning toward SPA, I built by stacking search, details, registration, updates, and confirmation in sequence. This approach is easier to build for business screens, and state management is easier to think about.

First, a login screen was prepared as the entry point to each function.

After login, a dashboard serves as the entry point for viewing rental and sales status.

Split the Spring Boot + Thymeleaf Admin Screen by Business Unit

Looking at the dvdrental schema, I first divided the screen roles by business unit.

The main screens prepared this time are:

• Login
• Dashboard
• Customer management
• Store management
• Staff management
• Inventory management
• Payment management
• Rental management
• Sales report

Templates are divided by function like functions/customers.html and functions/payments.html.

The Java structure is also divided like feature/customer, feature/store, feature/staff, with Controller, Service, and Form grouped per function.

Dashboard

The dashboard serves as the entry point for the entire admin screen, providing an overview of what each function handles. Rather than just a list of links, it gives an initial understanding of what staff, inventory, rentals, customers, payments, stores, and reports are each about.

Customer Management

Customer management allows viewing not just basic customer information, but also affiliated store, active status, remaining rental count, and cumulative payments together. As a business screen, seeing just names and emails isn’t enough, so it was designed to track customer status in one place.

Staff Management

Staff management allows confirming affiliated store, active status, assignment count, and recovery amounts. Rather than just master data management, it’s oriented toward showing how much staff are actually working operationally.

Store Management

Store management allows viewing managers, locations, inventory counts, customer counts, and sales together. Since there are many situations where you want to grasp store-unit status, information showing the scale of each store is gathered on one screen.

Inventory Management

Inventory management allows cross-checking films, categories, descriptions, languages, and store inventory. Additionally, CSV export and CSV registration/deletion are available, making it not just a reference screen but enabling data operations.

Payment Management

Payment management allows tracking not just payment history by customer and store, but also average unit price and total amounts. Rather than just displaying the payment table as-is, the focus was on presenting it in meaningful groups for an admin screen.

Rental Management

Rental management allows switching between in-rental, returned, and overdue states while checking rental history by customer and film. Overdue count and total billed amount are also visible, so a lot of status can be understood from just the list screen.

Sales Report

The sales report allows confirming sales by category and KPIs by store. It’s positioned as a way to look back on the whole with numbers at the end.

How the Sample DB Was Transformed into App Form

Since the existing tables are used directly in the app, I first straightforwardly mapped the table structure to Java entities.

For example, for the customer table, a Customer entity was created with mappings for customer_id, store_id, first_name, last_name, email, address_id, activebool, etc.

The mindset at this stage was to not immediately create complex domain models. It’s more stable to first get to a state where the existing schema can be correctly read, then add screen-specific DTOs and Forms on top.

JPA and SQL Were Considered Separately

As implementation progressed, it became clear that registration/update processing and list/aggregation processing have different suitable writing styles.

Therefore, this app roughly uses them as follows:

• Registration, updates, and reference foundation: JPA
• Dashboards and aggregations: Write SQL directly

Flows like form input, validation, confirmation screen, and saving are easier to assemble with JPA-based approach, while screens that want to show aggregated results from the start, like dashboards and reports, have clearer intent when SQL is written directly.

Search Condition Retention on the Server Side

Search condition retention for customer management is implemented using Spring MVC’s @SessionAttributes. In business screens, if conditions disappear when returning from a detail view, it becomes quite difficult to use, so this was set up from the start with the premise of server-side retention.

@Controller
@SessionAttributes("customerSearchForm")
public class CustomerController {

	@GetMapping("/functions/customers")
	public String customers(Model model, @ModelAttribute("customerSearchForm") CustomerSearchForm form) {
		return renderCustomers(form, model);
	}

	@ModelAttribute("customerSearchForm")
	public CustomerSearchForm customerSearchForm() {
		return new CustomerSearchForm();
	}

	@PostMapping("/functions/customers")
	public String customers(@ModelAttribute("customerSearchForm") CustomerSearchForm form, Model model) {
		return renderCustomers(form, model);
	}
}

Dashboard Aggregation with SQL

For the dashboard, the policy was to retrieve everything with SQL from the start. Open rentals, overdue count, inventory count, active customers, and last 30 days sales are metrics you want to see at a glance at the admin screen entry point, so they’re retrieved in one aggregation.

Map<String, Object> summary = jdbcTemplate.queryForMap(
		"""
		select
			count(*) filter (where return_date is null) as open_rentals,
			count(*) filter (
				where return_date is null
				  and rental_date < current_timestamp - (interval '1 day' * 3)
			) as overdue_rentals,
			(select count(*) from inventory) as inventory_count,
			(select count(*) from customer where activebool) as active_customers,
			(select coalesce(sum(amount), 0) from payment where payment_date >= current_date - interval '30 day') as monthly_sales
		from rental
		""",
		new MapSqlParameterSource());

Login Also Used staff from the Sample DB

Login uses Spring Security.

However, rather than placing fixed in-memory users, it’s configured to login using data from the staff table.

That is, the staff information in the sample DB is treated directly as users entering the admin screen.

Things to Be Careful About as a Business Screen

In this app, more than polishing appearances, the focus was on making it operable without stress as a business screen.

Points particularly focused on:

• Conditions remain even after going from a list to details and back
• Input values remain even after going from an update screen to a confirmation screen and back
• Username is retained on login failure
• Sidebar state is retained after screen transitions

Server-rendered admin screens have usability directly connected to these small details.

Inventory Can Also Be Operated via CSV

Making CSV handling available in inventory management also reflects operational considerations. Since touching items one by one from the screen takes time, inventory search results can be output as CSV, and batch registration and deletion are also available.

public byte[] exportCsv(InventorySearchForm form) {
    MapSqlParameterSource params = new MapSqlParameterSource()
	    .addValue("titleEnabled", StringUtils.hasText(form.getTitleKeyword()))
	    .addValue("titleKeyword", toSqlLike(form.getTitleKeyword()))
	    .addValue("storeEnabled", form.getStoreId() != null)
	    .addValue("storeId", form.getStoreId() != null ? form.getStoreId() : -1);

    List<InventoryCsvRow> rows = jdbcTemplate.query(
	    """
	    select i.inventory_id, i.film_id, f.title, i.store_id
	    from inventory i
	    join film f on f.film_id = i.film_id
	    where (:titleEnabled = false or lower(f.title) like :titleKeyword)
	      and (:storeEnabled = false or i.store_id = :storeId)
	    """,
	    params,
	    (rs, rowNum) -> new InventoryCsvRow(
		    rs.getInt("inventory_id"),
		    rs.getShort("film_id"),
		    rs.getString("title"),
		    "",
		    "",
		    rs.getShort("store_id")));
    // CSV assembly processing
}

What Was Good About Building on a Sample DB

In practice, the good thing was that since the data relationships already exist from the start, there’s no shortage of subject matter when adding screens.

For example, it can be naturally expanded in the following flow:

1. Create customer list
2. Create customer details
3. Create customer update
4. Expand to stores, staff, inventory
5. Add list-type items like payments, rentals, reports
6. Finally add overall sense with dashboard

Compared to imaginary business from scratch, being pulled along by the existing schema while adding screens has good compatibility for both learning and implementation practice.

Summary

For building the DVD rental admin app, the very first thing established was using the PostgreSQL sample DB as the foundation.

Using dvdrental means customers, stores, staff, inventory, rentals, and payments are ready from the start, making it easy to expand into screen design, authentication, lists, details, updates, and aggregations.

It’s easier to first understand the existing schema and build an admin screen on top of it, rather than designing business data from scratch.

The configuration and thinking when placing it on AWS is summarized in the following article:

Configuration, Operations, and Security Considerations for Deploying a Spring Boot + Thymeleaf Admin App to AWS ECS/Fargate