Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 119 ยท Databases with JDBC

The DAO and Repository Patterns

Separate persistence from business logic in Java. Build a JDBC BookDao over H2, add a row-mapping helper, swap in a fake for tests, and share transactions.

After a few lessons of JDBC, you have probably noticed how much of the code is ceremony: get a connection, prepare a statement, set parameters, loop over the result set, copy columns into objects, close everything, handle SQLException. If that code sits inside your business logic, a simple rule like "a member may borrow at most three books" ends up buried between rs.getString("title") calls, and you can't test the rule without a database.

The Data Access Object (DAO) pattern moves all of that into one class per kind of data. The rest of the application talks to an interface such as BookDao with methods like findById and insert, and never sees SQL. The closely related Repository pattern does the same in the language of the business domain. In this lesson you will build a complete JDBC DAO over H2, shrink its boilerplate with a small mapping helper, test business logic with a fake DAO, and share one transaction across several DAOs, which is exactly the problem frameworks like Spring solve for you.

The problem: SQL mixed with rules

Here is the kind of method that grows in every project before someone introduces a DAO (read-only):

Java
public void borrow(long memberId, long bookId) throws SQLException {
    try (Connection c = DriverManager.getConnection(URL)) {
        var count = c.prepareStatement("SELECT COUNT(*) FROM loan WHERE member_id = ? AND returned IS NULL");
        count.setLong(1, memberId);
        var rs = count.executeQuery();
        rs.next();
        if (rs.getInt(1) >= 3) {
            throw new IllegalStateException("Limit reached");
        }
        var insert = c.prepareStatement("INSERT INTO loan (member_id, book_id) VALUES (?, ?)");
        // ... and it goes on
    }
}

It works, but it mixes three jobs: the business rule (the limit of three), SQL, and resource handling. The URL is hard-coded, the statements are never closed, and testing the limit requires a database with the right tables and rows.

Anatomy of a DAO

A DAO has two parts:

  1. An interface that speaks in domain objects: Optional<Book> findById(long id), not ResultSet.
  2. An implementation that knows the SQL and the JDBC details, for example JdbcBookDao.

Good DAO methods follow a few conventions:

  • Return Optional<T> for "zero or one" lookups and a List<T> (never null) for "zero or more".
  • Take and return immutable domain types. Records are ideal (see Records).
  • Get connections from a DataSource passed to the constructor, never a hard-coded URL (see Connection Pooling and DataSource).
  • Keep each row-to-object conversion in one private mapping method.
  • Translate SQLException (checked and JDBC-specific) into an unchecked exception of your own, so callers aren't forced to know they are talking to JDBC.

Here is a full DAO for books, plus a little program that uses it.

Java A complete JdbcBookDao over H2

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 table is created: it only speaks about books. It doesn't know about connections, statements or column names. If the table is renamed or you move from H2 to PostgreSQL, only JdbcBookDao changes.

The column is published_year in SQL but year in the record. That is deliberate: YEAR is a reserved word in H2 2.x, and CREATE TABLE book (..., year INT) fails with a syntax error. Column names and field names don't have to match; the mapping method is the one place that connects them.

A small mapping helper

JdbcBookDao repeats the same 6โ€“8 lines in every method. The standard cure, used by Spring's JdbcTemplate and many lightweight libraries, is a helper that does the ceremony once and asks you only for the parts that change: the SQL, the parameters and a row mapper that turns the current row into an object.

Java A JdbcTemplate-style helper

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.

Every query still uses a PreparedStatement with bound parameters, so the helper keeps you safe from SQL injection. setObject lets the driver pick the SQL type from the Java type, which works well for strings, numbers, LocalDate and BigDecimal.

Interfaces make business logic testable

The biggest payoff of a DAO interface is that business code can depend on the interface, and tests can hand it a fake: a simple in-memory implementation. The rule "at most three open loans" can then be tested in microseconds, without any database.

The example below has a LoanService with that rule, a LoanDao interface, and an in-memory fake. JUnit tests (run with the launcher pattern from Unit Testing with JUnit 6) check the rule through the fake.

Java Testing a service with a fake DAO

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 three tests pass. Try breaking the rule, for example change >= to > in borrow, and run again: fourthLoanIsRejected fails and tells you why. The real JdbcLoanDao still needs its own tests against a database, but those test only SQL, not business rules. That split is the subject of Integration Testing, and hand-written fakes versus mocking libraries are compared in Test Doubles and Mockito.

Transactions that span several DAOs

There is one catch in the DAOs above: every method gets its own connection. Borrowing a book might need loanDao.insert(...) and bookDao.decrementCopies(...) to succeed or fail together, and a transaction only exists on a single connection (see Transactions with JDBC).

There are two common solutions:

  • Pass the connection in. DAO methods take a Connection parameter, and the service opens it, starts the transaction and commits. Simple and explicit, but JDBC leaks into every method signature.
  • Bind the connection to the current thread. A small transaction manager opens a connection, stores it in a ThreadLocal, and DAOs ask the manager for "the current connection". This is exactly how Spring's @Transactional works behind the scenes.

Here is the second approach in plain Java. The DAOs call tx.connection(). Inside tx.inTransaction(...) that returns the shared connection; the DAO code doesn't need to know.

Java One transaction across two 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.

The second member's loan row was inserted, then takeCopy found no copies and threw, and the rollback removed the loan row again. The database ends with zero copies and exactly one loan. In Spring, the TransactionManager class, the ThreadLocal and the inTransaction call all disappear behind one annotation, @Transactional, but the mechanism is the same. Because the connection is bound to a thread, work handed to another thread (an executor, a parallel stream) does not join the transaction, which surprises people in Spring too.

DAO versus Repository

The two names are often used interchangeably, and in many codebases the difference is only the suffix. There is a real distinction in intent, though:

DAO Repository
Thinks in tables and rows domain objects and collections
Typical methods insert, update, findByAuthorId save, findById, findOverdueLoans()
Scope often one DAO per table one repository per aggregate (e.g. an Order with its lines)
Origin J2EE core patterns Domain-Driven Design (Eric Evans)

A repository pretends to be an in-memory collection of domain objects: you save an Order and it figures out which rows in orders and order_line to write. It may use one or more DAOs underneath. A DAO is a thinner, more technical layer. Both hide SQL from business code, which is what matters most.

You will meet repositories everywhere in Spring. With Spring Data JPA you only declare the interface, and Spring generates the implementation, including queries derived from method names (read-only; covered in Spring Data JPA):

Java
public interface BookRepository extends JpaRepository<Book, Long> {
    List<Book> findByAuthorOrderByTitleAsc(String author);
    Optional<Book> findByTitleIgnoreCase(String title);
}

Common mistakes

  • Returning JDBC types. A DAO that returns a ResultSet forces the caller to manage resources and know column names, and the result set is closed the moment the connection goes back to the pool.
  • The N+1 query trap. Calling authorDao.findById(book.authorId()) inside a loop over 200 books runs 201 queries. Add a DAO method that joins, or loads all authors for a set of IDs in one query. The problem gets its own lesson in Fetching Strategies and the N+1 Problem.
  • The over-generic DAO. A GenericDao<T, ID> with reflection-based SQL saves a little typing and costs a lot of clarity. Write specific methods for the queries you actually need.
  • Business rules in the DAO. "Reject the fourth loan" belongs in the service. The DAO only answers questions and stores data.
  • Swallowing exceptions. Logging an SQLException and returning Optional.empty() turns a broken database into "not found". Wrap and rethrow.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does LoanService depend on the LoanDao interface instead of JdbcLoanDao?

    Depending on the interface lets you test business rules with a fake and change persistence technology without touching the service.
  2. 2. What should a DAO's findById return when no row matches?

    Optional makes "might not exist" visible in the signature and forces the caller to handle it. Exceptions are for failures, not for a normal miss.
  3. 3. In the two-DAO example, the second borrow inserts a loan row and then takeCopy throws. What is in the loan table afterwards?

    Both DAO calls used the same thread-bound connection, so the rollback undid the second loan row. The first borrow had already committed.
  4. 4. A page lists 50 books with their author names by calling authorDao.findById for each book. How many queries run?

    One query loads the books and then one query per book loads its author, the classic N+1 problem. A join or a batched lookup needs one or two.

Summary

  • A DAO hides all JDBC code behind an interface that speaks in domain objects; business code never sees SQL, connections or result sets.
  • Implement DAOs with a DataSource, PreparedStatement, one private row-mapping method, Optional/List return types and an unchecked exception that wraps SQLException.
  • A small helper with query, queryOne, update and a RowMapper lambda removes most of the boilerplate, like Spring's JdbcTemplate.
  • Depending on the interface lets you test business rules with fast in-memory fakes.
  • Transactions spanning several DAOs need one shared connection: pass it in, or bind it to the thread, which is what @Transactional does.
  • A Repository is the domain-oriented cousin of the DAO: one per aggregate, collection-like methods. Spring Data generates repositories from interfaces.

Next: JSON with Jackson starts the Data Formats chapter by turning these Java objects into JSON and back.