PostgreSQL のサンプル DB dvdrental をベースに Spring Boot + Thymeleaf で DVD レンタル管理アプリを作った話

DVD レンタル管理アプリを作るにあたって、最初に決めたのは UI ではなく「どのデータを土台にするか」でした。

今回はゼロからテーブル設計を起こすのではなく、PostgreSQL のサンプルデータベースとして知られている dvdrental をベースにして、そこへ管理画面を載せていく形で進めました。

つまり、Java / Spring Boot / Thymeleaf / PostgreSQL を使って、dvdrental を管理画面アプリへどう落とし込んだかをまとめた記事です。

今回ベースにしたサンプル DB は、次のページから確認できます。

PostgreSQL Sample Database

この記事では、DVD レンタル管理アプリ本体をどんな考え方で組み立てたのかをまとめます。

PostgreSQL のサンプルデータベース dvdrental を使った Spring Boot 管理画面、Thymeleaf 管理画面、DVD レンタル管理システム、dvdrental の ER 図を使った画面設計、という観点で情報を探している人にもつながるように、実装の流れと画面設計の考え方をまとめています。

この記事で分かること

・PostgreSQL のサンプル DB dvdrental を業務アプリの土台にした理由 ・Spring Boot + Thymeleaf で管理画面アプリを組み立てた流れ ・ER 図をもとに顧客、店舗、スタッフ、在庫、レンタル、支払いをどう画面へ落としたか ・JPA と SQL をどう使い分けたか ・検索条件保持や確認画面など、業務アプリとして使いやすくする工夫

今回作成した主な機能は次のとおりです。

・ログイン ・ダッシュボード ・顧客管理 ・店舗管理 ・スタッフ管理 ・在庫管理 ・支払い管理 ・レンタル管理 ・売上レポート

PostgreSQL のサンプル DB dvdrental を土台にした理由

このアプリで一番最初に押さえたのは、PostgreSQL のサンプル DB を土台にすることです。

dvdrental には、店舗、スタッフ、顧客、作品、在庫、貸出、支払いといった DVD レンタル業務に必要なテーブルが最初からそろっています。

これがかなり大きくて、最初から次のような題材を持った状態で開発を始められました。

・顧客一覧やスタッフ一覧のような検索画面 ・顧客詳細や支払い詳細のような参照画面 ・貸出や支払いを題材にした集計 ・店舗、在庫、顧客のような CRUD 系の画面

ゼロから業務テーブルを考えるより、まず既存のスキーマとデータを理解して、その上に画面を作っていく方が進めやすいと感じました。

まずはサンプル DB をそのまま使える形にした

このリポジトリでは、ローカルの PostgreSQL 起動時に docker/postgres/init/01-dvdrental-full.sql を読み込むようにしています。

さらに docker/postgres/init/02-convert-currency-to-jpy.sql も適用して、支払い金額を円換算した状態で画面確認できるようにしました。

dvdrental をどう日本語化したか、CSV と SQL をどう使い分けたか、円換算した full dataset をどう作ったかは、こちらの記事にまとめています。

PostgreSQL のサンプル DB dvdrental を日本語化した方法 SQL と CSV を併用して管理画面向けデータを作る

dvdrental の ER 図と主要テーブルの関係を整理した

画面を作り始める前に、まずは今回の管理画面で中心になるテーブル同士の関係をざっくり整理しました。

dvdrental にはテーブルが多くありますが、最初から全部を細かく追うと逆に頭の中で散らばりやすいです。そこで、まずは顧客、スタッフ、店舗、作品、在庫、レンタル、支払いという、画面に直結する主要テーブルから押さえるようにしました。

今回の管理画面で特に中心になった関係を簡略化すると、次のようになります。

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

	STORE {
		int store_id PK
		int manager_staff_id FK
		int address_id FK
	}

	STAFF {
		int staff_id PK
		int store_id FK
		int address_id FK
		string username
		boolean active
	}

	CUSTOMER {
		int customer_id PK
		int store_id FK
		int address_id FK
		boolean activebool
	}

	FILM {
		int film_id PK
		int language_id FK
		string title
		decimal rental_rate
	}

	INVENTORY {
		int inventory_id PK
		int film_id FK
		int store_id FK
	}

	RENTAL {
		int rental_id PK
		int inventory_id FK
		int customer_id FK
		int staff_id FK
		datetime rental_date
		datetime return_date
	}

	PAYMENT {
		int payment_id PK
		int customer_id FK
		int staff_id FK
		int rental_id FK
		decimal amount
		datetime payment_date
	}

この図を先に押さえておくと、画面ごとの役割も整理しやすくなります。たとえば顧客管理は customer を中心に store や payment を見る画面、レンタル管理は rental を中心に inventory、film、customer をたどる画面、支払い管理は payment を中心に customer、staff、rental を見る画面、というように考えやすくなります。

つまり、ER 図は単に DB 設計を確認するためだけではなく、「どの画面がどのテーブル群をまとめて見せるか」を決めるための土台として使いました。

Spring Boot + Thymeleaf でどんな管理画面アプリを作ったか

アプリ本体は Spring Boot + Thymeleaf を使ったサーバー描画型の管理画面です。

言い換えると、Java と PostgreSQL を使った業務向け Web 管理画面を、Spring Boot と Thymeleaf でどう組み立てたかを具体的に追える構成にしています。

主な技術スタックは次のとおりです。

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

SPA に寄せるのではなく、検索、詳細、登録、更新、確認を順番に積み上げる形にしました。業務画面としてはこの方が素直に作りやすく、状態保持も考えやすいです。

まずはログイン画面を用意し、そこから各機能へ入れるようにしています。

ログイン後はダッシュボードを入口にして、貸出や売上の状況を見られるようにしました。

Spring Boot + Thymeleaf の管理画面を業務単位で切った

dvdrental のスキーマを見ながら、まずは画面の役割を業務単位で分けました。

今回用意した主な画面は次のとおりです。

・ログイン ・ダッシュボード ・顧客管理 ・店舗管理 ・スタッフ管理 ・在庫管理 ・支払い管理 ・レンタル管理 ・売上レポート

テンプレートは functions/customers.html や functions/payments.html のように機能単位で分けています。

Java 側の構成も feature/customer、feature/store、feature/staff のように切っていて、Controller、Service、Form を機能ごとにまとめました。画面数が増えても追いやすい構成にしたかったからです。

それぞれの機能では、扱う情報をかなりはっきり分けています。

ダッシュボード

ダッシュボードは、管理画面全体の入口として、各機能が何を扱うのかを見渡せるようにしました。単なるリンク集ではなく、スタッフ、在庫、レンタル、顧客、支払い、店舗、レポートがそれぞれ何を見る画面なのかを最初に把握できるようにしています。 なぜこれを作ったかというと、業務画面は機能が増えるほど入口が分かりづらくなるからです。最初に全体像を見せる画面があるだけで、利用者が迷いにくくなります。

顧客管理

顧客管理では、顧客の基本情報だけでなく、所属店舗、稼働状態、貸出残数、累計支払いまで一緒に見られるようにしています。業務画面としては、名前とメールだけ見えても足りないので、顧客単位で状況をまとめて追えるようにしました。 顧客管理を作った理由は、問い合わせ対応や貸出状況確認の起点になりやすいからです。顧客単位でまとまって見えないと、その都度複数画面を行き来することになります。

実際の画面は次のような形です。

登録画面と登録確認画面は次のようにしています。

スタッフ管理

スタッフ管理では、所属店舗、稼働状態、担当件数、回収額まで確認できます。スタッフ情報を単なるマスタ管理で終わらせず、業務上どれだけ動いているかも見える形に寄せています。 スタッフ管理を入れたのは、アカウント管理だけでなく、店舗運営の実務担当を追えるようにしたかったからです。誰がどの店舗に属し、どれだけ対応しているかが見えると、管理画面として意味が出ます。

スタッフ管理は、担当状況が一覧で見えることを重視しました。

登録画面と登録確認画面は次のような形です。

店舗管理

店舗管理では、責任者、所在地、在庫数、顧客数、売上をまとめて見られるようにしました。店舗単位で状況を把握したい場面が多いので、店ごとの規模感が分かる情報を一画面に集めています。 店舗管理が必要だったのは、スタッフ、顧客、在庫、売上が最終的に店舗単位へ集約されるからです。店ごとに状態を俯瞰できる画面があると、全体把握がかなりしやすくなります。

店舗ごとの情報を一画面で比べられるようにすると、全体の把握がかなりしやすくなります。

登録画面と登録確認画面は次のような形です。

在庫管理

在庫管理では、作品、カテゴリ、説明、言語、店舗在庫を横断して確認できます。さらに CSV 出力と CSV 登録・削除も扱えるようにしていて、単なる参照画面ではなくデータ操作もできるようにしました。 在庫管理を作った理由は、作品情報を見るだけでは実務に足りず、「どの店舗に、いま何があるか」まで見えないと使いにくいからです。CSV を扱えるようにしたのも、まとめて在庫を調整したい場面を想定したためです。

在庫管理は、作品情報と店舗在庫を横断して見えることを最優先にしました。

支払い管理

支払い管理では、顧客別、店舗別の支払い履歴に加えて、平均単価や総額も追えるようにしています。payment テーブルをそのまま見るのではなく、管理画面として意味のあるまとまりで見せることを意識しました。 支払い管理を入れたのは、顧客対応と売上確認の両方に関わるからです。金額情報は一覧で追えないと確認コストが高いので、履歴と集計を同じ流れで見られるようにしました。

支払い管理は、履歴と金額感がすぐ伝わるように一覧性を強めています。

レンタル管理

レンタル管理では、貸出中、返却済み、延滞の状態を切り替えながら、顧客別、作品別のレンタル履歴を確認できるようにしました。延滞件数や請求総額も見えるので、一覧画面だけでもかなり状況が分かります。 レンタル管理を重視したのは、このアプリの中心業務だからです。貸出と返却の状態が分からないと、在庫管理も支払い管理もつながらないので、ここは最初から一覧性を強く意識しました。

レンタル管理は、状態切り替えと履歴確認をひと続きで扱える形にしています。

売上レポート

売上レポートでは、カテゴリ別売上と店舗別 KPI を確認できるようにしています。カテゴリごとの売上、レンタル件数、平均単価に加えて、店舗側の運営状況も見られるようにして、最後に全体を数字で振り返れる位置づけにしました。 売上レポートを入れたのは、日々の操作画面だけでは全体傾向が見えないからです。管理画面として終わらせず、あとから数字で振り返れるところまで持っていきたかったので、分析寄りの画面も用意しました。

売上レポートは、一覧業務の最後に数字で振り返るための画面として置いています。

サンプル DB をどうアプリの形に変えたか

既存のテーブルをそのままアプリに使うので、まずはテーブル構造を素直に Java のエンティティへ落とし込みました。

たとえば customer テーブルに対しては Customer エンティティを作り、customer_id、store_id、first_name、last_name、email、address_id、activebool などを対応づけています。

この段階で意識したのは、いきなり複雑なドメインモデルにしないことでした。まずは既存スキーマを正しく読める状態にして、その上で画面都合の DTO や Form を足していく方が崩れにくいです。

サンプル DB ベースで作る場合は、テーブル設計を変えることよりも、今ある構造をどう見せるか、どう編集させるかの方が重要だと思います。

JPA と SQL は分けて考えた

実装していくと、登録更新系の処理と、一覧集計系の処理では向いている書き方が違いました。

そのため、このアプリではざっくり次のように使い分けています。

・登録、更新、参照の土台は JPA ・ダッシュボードや集計は SQL を直接書く

フォーム入力、バリデーション、確認画面、保存のような流れは JPA ベースの方が組み立てやすく、逆にダッシュボードやレポートのように最初から集計結果を見せたい画面は SQL を直接書いた方が意図がはっきりします。

検索条件保持はサーバー側で持つ

実際に、顧客管理の検索条件保持は Spring MVC の @SessionAttributes を使って実装しています。業務画面では、詳細へ行って戻ったら条件が消えるだけでかなり使いにくくなるので、ここは最初からサーバー側で保持する前提にしました。

このコードでやっていることは単純で、customerSearchForm という検索フォームをセッションへ載せて、一覧表示と検索実行のどちらでも同じオブジェクトを使い回すことです。@ModelAttribute で初期値を用意しているので、最初のアクセス時は空の検索条件で入り、その後は POST 後も詳細画面から戻った後も入力値が残ります。

業務画面では、検索し直すたびに条件を毎回入れ直すだけでかなりストレスになります。この部分は派手な処理ではありませんが、一覧系画面の使い勝手を支える土台として入れています。

@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);
	}
}

ダッシュボードの集計は SQL でまとめる

ダッシュボード側は逆に、最初から SQL でまとめて取る方針にしました。貸出中件数、延滞件数、在庫数、有効顧客数、直近 30 日売上は、管理画面の入口でぱっと見たい指標だったので、1 回で集約して取得する形にしています。

ここは JPA のエンティティを何段もたどるより、最終的に画面へ出したい数字を SQL でそのまま定義した方が読みやすいと判断しました。count(*) filter (...) やサブクエリを使って、カード表示に必要な指標だけをひとまとめで返しています。

Java 側では queryForMap で 1 行だけ受け取り、その値をダッシュボード用の表示モデルへ詰め替えます。つまりこの層の役割は、DB の集計結果をそのまま UI 用のメトリクスへ変換することです。

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());

ログインもサンプル DB の staff を使った

ログインは Spring Security を使っています。

ただし、固定のメモリユーザーを置くのではなく、staff テーブルのデータを使ってログインする形にしました。

つまり、サンプル DB にあるスタッフ情報を、そのまま管理画面へ入るユーザーとして扱っています。

UserDetailsService 実装では username と有効フラグでスタッフを読み込み、認証後は管理画面へ遷移させる構成です。

このやり方だと、認証だけが別世界のダミーデータになることがなく、画面に出てくるスタッフ情報とログインユーザーのつながりを自然に持たせられます。

ログイン失敗時のストレスも減らす

ログイン設定は、ログイン画面だけを公開し、それ以外は認証必須にする素直な構成です。ログイン失敗時にユーザー名をセッションへ残しているのも、入力し直しのストレスを減らしたかったからです。

この設定で重要なのは、認可ルール、ログイン処理 URL、失敗時処理を Spring Security の設定へまとめていることです。/login と認証処理用 URL だけを公開し、通常画面はすべて認証必須にしています。

また、loginFailureHandler では認証失敗時に入力済みユーザー名をセッションへ退避しています。パスワードは保持せず、ユーザー名だけを戻す形にしているので、使い勝手と安全性のバランスを取りやすいです。

http.authorizeHttpRequests(auth -> auth
	.requestMatchers("/login", LOGIN_PROCESSING_PATH).permitAll()
	.requestMatchers("/css/**").permitAll()
	.anyRequest().authenticated()
)
.formLogin(form -> form
	.loginPage("/login")
	.loginProcessingUrl(LOGIN_PROCESSING_PATH)
	.failureHandler(loginFailureHandler())
	.successHandler(loginSuccessHandler())
	.permitAll()
);

@Bean
public AuthenticationFailureHandler loginFailureHandler() {
	return (request, response, exception) -> {
		request.getSession(true).setAttribute(LOGIN_USERNAME_SESSION_KEY, request.getParameter("username"));
		response.sendRedirect(request.getContextPath() + "/login?error");
	};
}

業務画面として気をつけたこと

このアプリでは、見た目を整えること以上に、業務画面としてストレスなく操作できることを重視しました。

特に意識したのは次の点です。

・検索後に一覧から詳細へ行って戻っても条件が残ること ・更新画面から確認画面へ進み、戻っても入力値が残ること ・ログイン失敗時にユーザー名を保持すること ・サイドバー状態を画面遷移後も保持すること

サーバー描画の管理画面は、こういう細かいところが使い勝手に直結します。単に画面が出るだけでなく、業務中の操作が途中で切れないことを意識して作りました。

在庫は CSV でも操作できるようにした

在庫管理で CSV を扱えるようにしたのも、業務側の都合を反映した部分です。画面から 1 件ずつ触るだけだと時間がかかるので、在庫ありの検索結果を CSV で出し、そのまま一括登録や削除もできるようにしました。

このエクスポート処理では、まず画面の検索条件を MapSqlParameterSource へ詰めて SQL に渡しています。キーワード未入力や店舗未選択でも同じ SQL を使えるように、titleEnabled や storeEnabled のフラグで条件の有効無効を切り替えているのがポイントです。

取得した結果はそのまま CSV 文字列へ変換するのではなく、一度 InventoryCsvRow へマッピングしています。これで SQL の取得結果と CSV 出力列の対応が明確になり、あとで項目を増減させるときも追いやすくなります。

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 組み立て処理
}

サンプル DB ベースで作ってよかったこと

実際にやってみてよかったのは、最初からデータの関係があるので、画面を増やすときに題材に困らないことでした。

たとえば次の流れで自然に広げていけます。

1. 顧客一覧を作る
2. 顧客詳細を作る
3. 顧客更新を作る
4. 店舗、スタッフ、在庫にも広げる
5. 支払い、レンタル、レポートのような一覧系を足す
6. 最後にダッシュボードで全体感を出す

ゼロベースの架空業務よりも、既存スキーマに引っ張られながら画面を増やせるので、学習用にも実装練習用にもかなり相性が良いと思います。

これから作るならこの順がやりやすい

PostgreSQL のサンプル DB を題材に管理画面を作るなら、最初から全部を作ろうとしない方が進めやすいです。

自分なら次の順で進めます。

1. まず DB を起動して中身を見る
2. 顧客、スタッフ、店舗など分かりやすいテーブルから一覧画面を作る
3. 詳細、更新、確認画面へ広げる
4. その後で支払い、レンタル、レポート、ダッシュボードを足す
5. 最後に認証や画面全体の使い勝手を整える

最初に「PostgreSQL のサンプル DB を土台にする」と決めてしまうと、何を作るかで迷いにくくなります。

まとめ

DVD レンタル管理アプリ本体を作るうえで、一番最初に押さえたのは PostgreSQL のサンプル DB をベースにすることでした。

dvdrental を使うことで、顧客、店舗、スタッフ、在庫、貸出、支払いといった題材が最初からそろっていて、画面設計、認証、一覧、詳細、更新、集計へと広げやすくなります。

ゼロから業務データを考えるより、まず既存スキーマを理解して、その上に管理画面を作る方が進めやすいです。

PostgreSQL のサンプル DB を題材に、Spring Boot + Thymeleaf で業務アプリを作ってみたい人の参考になればうれしいです。

AWS へ載せたときの構成や考え方は、こちらの記事にまとめています。

Spring Boot + Thymeleaf の管理画面アプリを AWS ECS/Fargate に載せるにあたって、構成・運用・セキュリティをどう考えたか