Skip to content
Tutorials Center
My learning

Topics

Lesson 109 of 119 ยท Projects

Project: Library Database with JDBC

Build a complete library app in Java with JDBC and H2 - schema and constraints, a DAO layer, transactional loans, join-based reports, a console UI and JUnit tests.

Individual lessons teach one tool at a time. Real applications need all of them at once, and most of the interesting decisions happen where the pieces meet: which rule belongs in the database and which in Java, where a transaction starts and ends, how a report query stays readable. In this project you will build a small but complete library management app on top of JDBC and an H2 database, and you will make those decisions yourself.

The app manages authors, books, members and loans. Members borrow books under a few realistic rules, return them (late returns cost a fee), and librarians get reports: the catalog with free copies, overdue loans, popular books and member activity. Everything runs in your browser's editor against a real in-memory H2 database. You will build it in five stages, and every stage is a complete program that includes all the code from the stages before it, because each run starts with an empty database.

You should know the material from CRUD with JDBC, Transactions with JDBC and The DAO and Repository Patterns. This project puts those lessons together, so it links back to them instead of repeating them.

What we are building

Before writing code, write down what the app must do. These are the requirements:

  • The library has books (title, author, publication year, number of copies) and members (name, email, active or not).
  • An active member can borrow a book if a copy is free. A loan lasts 14 days.
  • A member may hold at most 3 books at once, and may not borrow anything while they have an overdue book.
  • Returning a book closes the loan. A late return costs 0.25 per day, capped at 5.00.
  • A member can borrow several books at once, and that checkout is all or nothing.
  • Librarians need four reports: the catalog with free copies, overdue loans, the most borrowed books, and member activity.

The code is organized in layers, from the bottom up:

Layer Classes Knows about
Schema SQL text blocks Tables, keys and constraints
Database helper Database Connections, statements, transactions
DAOs BookDao, MemberDao, LoanDao, ReportDao SQL and row mapping
Service LoanService Business rules, dates and fees
User interface LibraryConsole Commands and output formatting

Each layer only calls the one below it. The console never writes SQL, and the DAOs never decide whether a member is allowed to borrow. That separation is what makes the rules testable in stage 5.

Stage 1: the schema

The schema is the foundation, so it deserves the most thought. Four tables are enough: author, book, member and loan. A loan is a row that links one book to one member for a period of time. It has a returned_on date that stays NULL while the book is out, so "open loans" are simply the rows where returned_on IS NULL.

Notice what is not in the schema: there is no available column on book. You could store a counter and update it on every loan and return, but then the counter and the loan rows could disagree after a bug or a crash. Instead, free copies are computed: copies minus the number of open loans. Storing each fact once is the core idea of database normalization, and it removes a whole class of bugs.

The schema also protects itself. The database rejects data that breaks the rules it can check, no matter which program sends it:

  • NOT NULL for required columns, UNIQUE for emails and for one title per author.
  • Foreign keys so a loan can't point to a book or member that doesn't exist.
  • CHECK constraints so copies can't go negative and a due date can't come before the borrow date.
  • Every constraint has a name, so error messages say COPIES_NOT_NEGATIVE instead of CONSTRAINT_1F.
  • Two indexes on loan for the questions the app asks most: "open loans of this member" and "open loans of this book".

The program creates the schema, loads sample data, counts the rows, and then tries four inserts that each break a rule.

Java Stage 1: schema, constraints and sample data

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

All four bad inserts fail with SQLState 23xxx, the standard class for integrity constraint violations (23513 is a check constraint, 23506 a foreign key, 23505 a unique key). Code can rely on those codes across databases, while the message text differs between H2, PostgreSQL and MySQL.

The sample loans are dated around March 2026 because the app will use a fixed "today" of March 16, 2026. That keeps every run's output identical. Some loans are already returned, Ana's and Chen's open loans are overdue, and Ben's loan is due exactly today, which is not overdue yet.

A few more schema decisions worth naming:

  • The year column is published_year, not year, because YEAR is a reserved word in H2.
  • DATE (not TIMESTAMP) is right for borrow and due dates: a library cares about days, not the second a book was handed over. Java maps it to LocalDate.
  • Constraints are a safety net, not the user interface. The Java code will check the rules first and produce friendly messages; the constraints catch the bugs that slip through.

Stage 2: the database helper and the DAOs

Next comes the persistence layer. Writing raw JDBC in every DAO method repeats the same ten lines, so the project starts with a small Database helper in the style of the one from The DAO and Repository Patterns: query, queryOne, update, insert (returns the generated key) and script, plus a RowMapper lambda for turning a row into an object. Every SQL call goes through a PreparedStatement with bound parameters, so there is no way to build a query by concatenating user input (see PreparedStatement and SQL Injection).

The helper also turns every checked SQLException into an unchecked DataAccessException that includes the failed SQL. Business code can't do anything useful with a raw SQLException, and forcing every method to declare it would leak JDBC into all layers.

On top of the helper sit three DAOs. Each owns its SQL and one mapping lambda. The domain types are records, and Book already contains the author's name, not just author_id, because every screen that shows a book also shows its author. A join in the DAO is cheaper than a second query per book.

One method deserves attention: BookDao.search. It uses LIKE with a pattern built from user input. Parameters prevent injection, but they do not stop % and _ in the search text from acting as wildcards. Searching for 50% would match everything that contains "50". The escapeLike helper escapes them with !, and the SQL declares ESCAPE '!'.

Java Stage 2: a Database helper and three DAOs

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Look at main: after the schema is created, it doesn't contain a single line of JDBC. That is the point of the DAO layer. Some details to notice in the output and the code:

  • markReturned only updates open loans (AND returned_on IS NULL) and reports whether a row changed. The second call returns false, so a book can't be "returned" twice. Putting the condition in the UPDATE is safer than reading the row first and checking in Java, because there is no gap between the check and the write.
  • getObject("published_year", Integer.class) returns null for a missing year. getInt would return 0, a year that never happened.
  • queryOne refuses two rows. If a query that should match one row matches two, that is a bug, and failing loudly beats silently picking the first.
  • The URL ends with DB_CLOSE_DELAY=-1. Without it, H2 deletes an in-memory database when its last connection closes, and the helper closes its connection after every call.

The Database helper opens a new connection for every call. With H2's JdbcDataSource that is cheap. In production you would pass a pooled DataSource such as HikariCP instead, and the code would not change at all (see Connection Pooling and DataSource).

Stage 3: transactions and business rules

Now for the heart of the app: LoanService. Borrowing a book is not one SQL statement. The service has to load the member, check that the membership is active, check for overdue books, count the member's open loans, count the book's open loans against its copies, and only then insert the loan. If two librarians lend the last copy of a book at the same moment, both checks could pass before either insert happens, and the library would lend a copy it doesn't have.

Two tools fix this:

  1. A transaction around the whole operation. All the reads and the insert run on one connection with autocommit off, and an exception anywhere rolls everything back. The Database helper gains an inTransaction method that binds the connection to the current thread with a ThreadLocal, exactly the technique from The DAO and Repository Patterns. DAO calls made inside the transaction automatically use its connection; calls outside it still get a short-lived connection of their own.
  2. A row lock on the book. BookDao.lockStock reads the copies with SELECT ... FOR UPDATE. A second transaction that wants the same book waits at that line until the first one commits, and then sees the new loan when it counts. Two borrowers of the last copy are now handled one after the other.

inTransaction also handles nesting: if a transaction is already running on this thread, it simply joins it. That is what makes borrowAll work. It opens one transaction and calls borrow for each book, and each borrow joins the outer transaction instead of committing on its own. If the third book fails, the first two loans roll back with it. Spring calls this behavior propagation REQUIRED, and it is the default for @Transactional.

Two more decisions in the service:

  • Dates come from an injected Clock. The service never calls LocalDate.now() directly; it calls LocalDate.now(clock). Production code passes Clock.systemDefaultZone(), and the demo and the tests pass a fixed clock. Without this, a test for "returned 3 days late" would need to wait 17 days.
  • Money is a BigDecimal. 0.25 * 20 is exact with BigDecimal, and min(MAX_FEE) applies the cap. Never use double for money; see Numbers and Math for why binary floating point and cents don't mix.

Rule violations throw a LibraryException with a message that a librarian can read. It is a different type from DataAccessException, so the user interface can show the first kind and treat the second kind as a bug.

Java Stage 3: a transactional LoanService

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Read the output from top to bottom and match each line to a rule. Ben gets the only copy of The Glass Orchard. Chen is refused first because of his overdue loan. After he returns it (20 days late: 20 ร— 0.25 = 5.00, which is also the cap), he is refused again, now because Ben has the only copy. Returning the same loan twice is refused. Ana is blocked by her overdue loan, Dara by her inactive membership, and Ana's own return is 10 days late (2.50).

The checkout of books 1, 5 and 2 is the interesting part. The first two borrow calls succeeded and inserted rows, then book 2 failed because Ben has the only copy. Because all three ran in one transaction, the rollback removed the two new rows, and Ana's count of open loans is still 0. The next checkout works, and after it Ana has reached the limit of three books.

The ThreadLocal transaction belongs to one thread. If borrowAll handed each book to an executor or a parallel stream, the other threads would see no transaction and would commit on their own connections, silently breaking the all-or-nothing promise. The same rule applies to Spring's @Transactional.

Stage 4: reports and the console app

The data is in place and the rules are enforced. Now the librarians need answers. Reports are read-only queries that combine tables, so they get their own ReportDao that returns small, purpose-built records (CatalogLine, OverdueLine, and so on). Those records are shaped for display, not for storage, which is the idea behind DTOs and projections you will meet again in Querying with JPQL and the Criteria API.

Each report query teaches a SQL lesson:

  • Catalog with free copies uses LEFT JOIN loan l ON l.book_id = b.id AND l.returned_on IS NULL. The open-loan condition must be in the ON clause. If you move it to WHERE, books with no open loans are filtered out, because their l.returned_on is NULL from the outer join, not from a real loan. It's one of the most common SQL mistakes.
  • Overdue loans joins three tables and computes "days late" in Java with ChronoUnit.DAYS.between. Date arithmetic functions differ between databases (DATEDIFF in H2 and MySQL, subtraction in PostgreSQL); computing it in Java keeps the SQL portable.
  • Popular books uses COUNT(l.id) rather than COUNT(*). With a LEFT JOIN, a book that was never borrowed still produces one row with a NULL loan id, so COUNT(*) would say 1 and COUNT(l.id) correctly says 0. FETCH FIRST ? ROWS ONLY is the standard SQL way to limit rows.
  • Member activity uses a conditional count, COUNT(CASE WHEN ... THEN 1 END), to compute open and total loans in one pass.

The console is the last layer. It reads commands line by line, calls the service or a report, and prints the result. It catches LibraryException and prints a friendly "Sorry" message, but it doesn't catch DataAccessException: a broken database should crash loudly, not look like a refused loan. The input box is preset with a session of commands; edit it and run again to try your own.

Java Stage 4: reports and the console app

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

main is now just wiring: it creates the data source, the DAOs, the service and the console, and passes each object what it needs through its constructor. Nothing creates its own dependencies. This hand-written wiring is exactly what a dependency injection container automates, as you saw in Dependency Injection and Inversion of Control.

The console prints the preset commands back (> borrow 2 6) because text from the Input box isn't echoed like typed input in a terminal. The console also receives its PrintStream instead of using System.out directly, so a test could pass a PrintStream over a ByteArrayOutputStream and check the printed text.

Stage 5: automated tests

The demo runs prove that the app works today. Tests prove that it keeps working after the next change. The most valuable tests here are the ones for LoanService, because that is where the rules live. They run against a real H2 database, not a fake, so they check the SQL and the constraints together with the Java logic. That makes them integration tests in the sense of Integration Testing, but they are still fast: each one creates its own in-memory database in a few milliseconds.

The test class follows three rules that keep tests reliable:

  • Every test gets a fresh database with a unique name (library-test-1, library-test-2, ...), created in @BeforeEach. No test can see another test's rows, so the order of tests never matters.
  • Every test creates exactly the data it needs with small helper methods (book("Tide Tables", 1)), instead of sharing the big sample data set. When a test fails, everything it depends on is visible in the test itself.
  • Time is controlled. The service gets a fixed clock. To test a late return, the test creates a second service whose clock is 20 days later and uses it on the same database.

The last test is the important one for stage 3's row lock: eight members try to borrow the single copy of a book at the same time on eight virtual threads. Exactly one may succeed. Without FOR UPDATE, several threads could pass the "copies left" check before any of them inserted its loan.

Java Stage 5: JUnit tests for the loan rules

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

All nine tests pass. Now break something on purpose to see the tests do their job:

  • Remove FOR UPDATE from lockStock and run a few times. The concurrency test may now report more than one success.
  • Remove the outer db.inTransaction from borrowAll, so each borrow commits on its own. checkoutIsAllOrNothing fails with the message "the two successful loans must be rolled back".
  • Change .min(MAX_FEE) to .max(MAX_FEE), and lateReturnsPayPerDayUpToTheCap catches it.

The test for the first-come-first-served rule depends on the database honoring FOR UPDATE row locks. H2, PostgreSQL, MySQL (InnoDB) and Oracle all do. An alternative design that doesn't need locks is optimistic locking with a version column, covered in Entity Lifecycle and Transactions.

Design decisions in review

Step back and look at the choices this project made, because you will face the same ones in every data-driven application:

Decision Choice here Why
Free copies Computed from open loans One source of truth; no counter to drift
Data integrity Constraints in the schema and checks in Java Constraints catch bugs, Java gives friendly messages
SQL location Only in DAOs Rules and UI stay readable and testable
Errors LibraryException vs DataAccessException Rule violations are shown, infrastructure failures crash loudly
Transactions Service methods, thread-bound connection, nested calls join One borrow or one checkout is one unit of work
Concurrency SELECT ... FOR UPDATE on the book row Two borrowers of the last copy are serialized
Time Injected Clock Deterministic demos and tests
Money BigDecimal Exact cents
Wiring Constructors in main No hidden globals; easy to swap parts in tests

If you later move this app to JPA or Spring, almost every row of this table survives. Spring's @Transactional replaces inTransaction, Spring Data replaces the DAOs, and the service code barely changes. That is the payoff of clean layers.

Ideas for extending the project

Pick one or two and build them on top of the stage 4 program:

  • Reservations. Let a member reserve a book whose copies are all out. When a copy is returned, the oldest reservation gets it first. You'll need a reservation table and a rule in borrow that checks the queue.
  • Renewals. Add renew <loan>: extend the due date by 14 days, at most twice, and never when someone has reserved the book. Add a renewals column with a CHECK (renewals <= 2).
  • Fees as data. Store fees in a fee table when a book comes back late, add pay <member>, and block borrowing while unpaid fees exceed 10.00.
  • Adding books and members from the console, with validation (non-blank title, valid email) and the unique constraints mapped to friendly messages by checking SQLState 23505.
  • Paging. books 2 shows the second page of 3 books using ORDER BY title OFFSET ? ROWS FETCH NEXT ? ROWS ONLY.
  • Batch import. Read a CSV of new books from a text block and insert them with addBatch inside one transaction (see Batch Updates, Metadata and Large Objects).
  • Console tests. Feed the console a BufferedReader over a string and capture its output, then assert on the text.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does the book table have no "available copies" column?

    A stored counter would have to be updated on every loan and return, and any bug or crash could leave it disagreeing with the loan rows. Computing it avoids that.
  2. 2. In the catalog report, what happens if you move l.returned_on IS NULL from the ON clause of the LEFT JOIN to the WHERE clause?

    WHERE filters after the join. A book with only returned loans produces rows whose returned_on is a real date, and those rows are filtered out, so the book vanishes. In the ON clause, the condition only limits which loans join.
  3. 3. borrowAll(ana, [1, 5, 2]) inserts loans for books 1 and 5, then book 2 fails. How many new loans does Ana have afterwards?

    Each borrow call joins the outer transaction started by borrowAll, so the exception rolls back both inserted rows.
  4. 4. What does SELECT title, copies FROM book WHERE id = ? FOR UPDATE add to the borrow operation?

    The second transaction waits at the locked row until the first commits, then counts open loans again and sees the new loan.
  5. 5. Why does LoanService call LocalDate.now(clock) instead of LocalDate.now()?

    Injecting a Clock makes time a dependency. Production passes the system clock; tests pass fixed clocks for any date they need.

Summary

  • Start with the schema: normalized tables, named constraints, foreign keys, CHECKs and indexes for the queries you run most. Compute derived values like free copies instead of storing them.
  • A small Database helper with query, queryOne, update and insert removes JDBC boilerplate and wraps SQLException in an unchecked exception.
  • DAOs own all SQL and mapping; escape % and _ in LIKE patterns even when using parameters.
  • Business rules live in a service. Each operation runs in one transaction; nested calls join it, so multi-step operations are all or nothing.
  • SELECT ... FOR UPDATE serializes concurrent changes to the same row; an injected Clock and BigDecimal make dates and money testable and exact.
  • Reports use joins, LEFT JOIN conditions in ON, COUNT(column) and conditional counts, returning display-shaped records.
  • Tests run against a fresh in-memory database per test, create their own data, and include a real concurrency check.

Next: Project: A REST API from Scratch puts an HTTP interface on the same layered design and builds a JSON task API with HttpServer, Jackson and H2.