JAVA-250 · Lesson 6 of 12 · 23 min
The data access object pattern
▶ Video coming soon A video version of this lesson is on the way.
By Friday, October 22, Hamza's experiments had left SQL in eleven places in StockStore's code: in the counter screen, the parts-room screen, the reorder report and three helper classes. Ingrid asked him to change the name of one column, reorder_at, to reorder_level, and to count how many files that touched. It was seven. "Every one of those is a place a typo can hide," she said, "and none of them can be tested without a database." This lesson introduces the data access object (DAO) pattern, which puts all the SQL for one kind of data behind one interface, and shows how it makes database code easier to change, easier to test and easier to keep correct.
One interface per kind of data
A data access object is an object whose only job is to move one kind of data between the program and the database. Its methods speak the language of the program, such as find a part by code or list the parts below their reorder level, and hide the SQL that does it. The rest of the program depends only on the DAO's interface, never on JDBC. That gives three benefits at once. SQL lives in one class per table, so a renamed column is one edit. The code that uses the data can be tested with an in-memory implementation of the interface, with no database at all. And the database can change, from SQLite to PostgreSQL, by writing a second implementation rather than editing the whole program.
Hamza's PartDao started with four methods. Each returns Java types, an Optional for a lookup that may find nothing and a List for queries, and none of them mentions a Connection or an SQLException. The example below defines the interface, an in-memory implementation backed by a TreeMap and a ReorderService that uses only the interface. It works out how many of each low part to order, rounding up to whole packs, the rule Hamza had written test-first in the testing course.
import java.util.*;
public class Main {
record Part(String code, int quantity, int reorderAt, int packSize) {}
interface PartDao {
Optional<Part> find(String code);
List<Part> belowReorderLevel();
void save(Part part);
boolean adjust(String code, int change); // false if the change would go below zero
}
static class InMemoryPartDao implements PartDao {
private final Map<String, Part> parts = new TreeMap<>();
public Optional<Part> find(String code) { return Optional.ofNullable(parts.get(code)); }
public List<Part> belowReorderLevel() {
return parts.values().stream().filter(p -> p.quantity() < p.reorderAt()).toList();
}
public void save(Part part) { parts.put(part.code(), part); }
public boolean adjust(String code, int change) {
Part p = parts.get(code);
if (p == null || p.quantity() + change < 0) return false;
parts.put(code, new Part(code, p.quantity() + change, p.reorderAt(), p.packSize()));
return true;
}
}
static class ReorderService {
private final PartDao dao;
ReorderService(PartDao dao) { this.dao = dao; }
Map<String, Integer> orderList() {
Map<String, Integer> order = new TreeMap<>();
for (Part p : dao.belowReorderLevel()) {
int wanted = p.reorderAt() * 2 - p.quantity(); // bring stock up to twice the reorder level
int packs = (wanted + p.packSize() - 1) / p.packSize(); // round up to whole packs
order.put(p.code(), packs * p.packSize());
}
return order;
}
}
public static void main(String[] args) {
PartDao dao = new InMemoryPartDao();
dao.save(new Part("BELL", 3, 4, 6));
dao.save(new Part("CHAIN-11", 1, 3, 1));
dao.save(new Part("PAD-ROAD", 3, 5, 4));
dao.save(new Part("GRIPS", 9, 4, 2));
System.out.println("Sell 2 chains: " + dao.adjust("CHAIN-11", -2));
System.out.println("Sell 1 chain: " + dao.adjust("CHAIN-11", -1));
System.out.println("Order: " + new ReorderService(dao).orderList());
}
}Sell 2 chains: false
Sell 1 chain: true
Order: {BELL=6, CHAIN-11=6, PAD-ROAD=8}What this code does
ReorderService never sees a map or a database; it calls only the PartDao methods, so either implementation can be passed in. Selling two chains is refused because there is only one, and selling one leaves none. Bells need 8 − 3 = 5, rounded up to one pack of 6, chains need 6, and road pads need 7, rounded up to two packs of 4.
The JDBC implementation
The production implementation of the same interface holds a DataSource and turns each method into one SQL statement. All the JDBC rules from lessons 2 to 5 live here and nowhere else: prepared statements, try-with-resources, a mapping method for the table and the atomic conditional update for stock changes. When Ingrid's column rename comes, this class and the migration of lesson 11 are the only files that change.
public final class JdbcPartDao implements PartDao {
private static final String COLUMNS = "code, quantity, reorder_at, pack_size";
private final DataSource ds;
public JdbcPartDao(DataSource ds) { this.ds = ds; }
private static Part map(ResultSet rs) throws SQLException {
return new Part(rs.getString("code"), rs.getInt("quantity"),
rs.getInt("reorder_at"), rs.getInt("pack_size"));
}
@Override public Optional<Part> find(String code) {
String sql = "SELECT " + COLUMNS + " FROM part WHERE code = ?";
try (Connection c = ds.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, code);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? Optional.of(map(rs)) : Optional.empty();
}
} catch (SQLException e) {
throw DataAccess.translate("find part " + code, e);
}
}
@Override public List<Part> belowReorderLevel() {
String sql = "SELECT " + COLUMNS + " FROM part WHERE quantity < reorder_at ORDER BY code";
try (Connection c = ds.getConnection(); PreparedStatement ps = c.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
List<Part> parts = new ArrayList<>();
while (rs.next()) parts.add(map(rs));
return parts;
} catch (SQLException e) {
throw DataAccess.translate("list low parts", e);
}
}
@Override public boolean adjust(String code, int change) {
String sql = "UPDATE part SET quantity = quantity + ? WHERE code = ? AND quantity + ? >= 0";
try (Connection c = ds.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
ps.setInt(1, change);
ps.setString(2, code);
ps.setInt(3, change);
return ps.executeUpdate() == 1;
} catch (SQLException e) {
throw DataAccess.translate("adjust " + code, e);
}
}
// save(Part) uses INSERT ... ON CONFLICT (code) DO UPDATE, shown in lesson 12
}What this code does
Each method borrows a connection from the DataSource, which in production is the pool of lesson 5, and gives it back at the end of the try block. The map method is the single place where columns become a Part, and adjust uses the atomic conditional update from lesson 4. SQLException never leaves the class; translate turns it into the unchecked exceptions described below.
SELECT code, quantity, reorder_at, pack_size
FROM part
WHERE quantity < reorder_at
ORDER BY code;What this code does
The query belowReorderLevel sends, run here on the sample data. It returns exactly the rows the in-memory implementation returned in the example above.
| code | quantity | reorder_at | pack_size |
|---|---|---|---|
| BELL | 3 | 4 | 6 |
| CHAIN-11 | 1 | 3 | 1 |
| PAD-ROAD | 3 | 5 | 4 |
Translating exceptions
SQLException is checked, so if DAO methods declared it, every caller, every caller's caller and every lambda in between would have to declare or catch it, even though almost none of them can do anything about a database failure. The usual solution, used by Spring and most other frameworks, is to translate it at the edge of the data layer into an unchecked exception, keeping the original as the cause. A few cases that callers can handle get their own subclass: a duplicate key, which the counter screen can explain to the user, and a transient failure such as a lost connection, which can be retried.
import java.sql.*;
public class Main {
static class DataAccessException extends RuntimeException {
DataAccessException(String msg, Throwable cause) { super(msg, cause); }
}
static class DuplicateKeyException extends DataAccessException {
DuplicateKeyException(String msg, Throwable cause) { super(msg, cause); }
}
static class TransientDataAccessException extends DataAccessException {
TransientDataAccessException(String msg, Throwable cause) { super(msg, cause); }
}
static DataAccessException translate(String task, SQLException e) {
String state = e.getSQLState() == null ? "" : e.getSQLState();
String msg = task + " failed: " + e.getMessage();
if (state.equals("23505") || e.getMessage().contains("UNIQUE constraint failed")) return new DuplicateKeyException(msg, e);
if (state.startsWith("08") || state.equals("40001") || e instanceof SQLTransientException) return new TransientDataAccessException(msg, e);
return new DataAccessException(msg, e);
}
public static void main(String[] args) {
SQLException[] failures = {
new SQLException("ERROR: duplicate key value violates unique constraint \"part_pkey\"", "23505"),
new SQLException("[SQLITE_CONSTRAINT_PRIMARYKEY] UNIQUE constraint failed: part.code"),
new SQLTransientConnectionException("connection reset", "08006"),
new SQLException("no such column: reorder_level", "42703")};
for (SQLException e : failures) {
DataAccessException d = translate("save part BELL", e);
System.out.println(d.getClass().getSimpleName() + " <- " + d.getCause().getClass().getSimpleName()
+ " [" + (e.getSQLState() == null ? "no state" : e.getSQLState()) + "]");
}
}
}DuplicateKeyException <- SQLException [23505] DuplicateKeyException <- SQLException [no state] TransientDataAccessException <- SQLTransientConnectionException [08006] DataAccessException <- SQLException [42703]
What this code does
The first two failures are the same problem reported by two databases: PostgreSQL gives the standard SQLState 23505, while the SQLite message names the constraint, so translate checks both. A connection reset becomes a transient exception that a caller may retry, and anything else becomes the general DataAccessException. Each new exception keeps the original as its cause, so no detail is lost from the log.
Where should transactions go when the DAO hides connections? Not inside each DAO method, because one business operation, such as receiving a delivery, uses several DAOs and must commit them together. The common answer is a small transaction helper in the service layer that opens one connection, starts a transaction, passes the connection to the DAOs, and commits or rolls back. Frameworks such as Spring do this with an annotation, @Transactional; in plain Java it is a method that takes a lambda.
public final class Tx {
private final DataSource ds;
public Tx(DataSource ds) { this.ds = ds; }
public <T> T inTransaction(SqlWork<T> work) {
try (Connection c = ds.getConnection()) {
c.setAutoCommit(false);
try {
T result = work.run(c);
c.commit();
return result;
} catch (SQLException | RuntimeException e) {
c.rollback();
throw e;
} finally {
c.setAutoCommit(true);
}
} catch (SQLException e) {
throw DataAccess.translate("transaction", e);
}
}
@FunctionalInterface
public interface SqlWork<T> { T run(Connection c) throws SQLException; }
}
// receiving a delivery: both DAOs use the same connection, so they share one transaction
// tx.inTransaction(c -> { parts.adjust(c, "TUBE-700x25", 20); log.record(c, "TUBE-700x25", 20, "RECEIVED"); return null; });What this code does
The transaction pattern from lesson 4 is written once, here, instead of in every service method. Work is a lambda that receives the connection, so the DAOs it calls all run inside the same transaction. Because SqlWork is allowed to throw SQLException, the lambda body can call JDBC methods directly.
Testing a service without a database
The biggest practical payoff of the pattern is testing. Hamza's testing course had taught him to test business rules quickly and in isolation, and a DAO interface is exactly the seam that makes that possible: give the service an in-memory DAO, or a small hand-written fake that records what it was asked, and the test runs in milliseconds. The JDBC implementation is then tested separately, against a real database, in a few integration tests that check each SQL statement once.
import java.util.*;
public class Main {
record Part(String code, int quantity, int reorderAt, int packSize) {}
interface PartDao { Optional<Part> find(String code); boolean adjust(String code, int change); }
static class SaleService {
private final PartDao dao;
SaleService(PartDao dao) { this.dao = dao; }
String sell(String code, int qty) {
if (qty <= 0) return "rejected: quantity must be positive";
if (dao.find(code).isEmpty()) return "rejected: unknown part " + code;
return dao.adjust(code, -qty) ? "sold " + qty + " " + code : "rejected: not enough " + code;
}
}
// a fake DAO for tests: fixed data, and a record of every call
static class FakePartDao implements PartDao {
final List<String> calls = new ArrayList<>();
final Map<String, Integer> stock = new TreeMap<>(Map.of("BELL", 3));
public Optional<Part> find(String code) {
calls.add("find " + code);
return stock.containsKey(code) ? Optional.of(new Part(code, stock.get(code), 4, 6)) : Optional.empty();
}
public boolean adjust(String code, int change) {
calls.add("adjust " + code + " " + change);
if (stock.get(code) + change < 0) return false;
stock.merge(code, change, Integer::sum);
return true;
}
}
static void check(String name, String actual, String expected) {
System.out.println((actual.equals(expected) ? "PASS " : "FAIL ") + name + ": " + actual);
}
public static void main(String[] args) {
FakePartDao dao = new FakePartDao();
SaleService service = new SaleService(dao);
check("normal sale", service.sell("BELL", 2), "sold 2 BELL");
check("too many", service.sell("BELL", 5), "rejected: not enough BELL");
check("unknown part", service.sell("HORN", 1), "rejected: unknown part HORN");
check("zero quantity", service.sell("BELL", 0), "rejected: quantity must be positive");
System.out.println("DAO calls: " + dao.calls);
System.out.println("Bells left: " + dao.stock.get("BELL"));
}
}PASS normal sale: sold 2 BELL PASS too many: rejected: not enough BELL PASS unknown part: rejected: unknown part HORN PASS zero quantity: rejected: quantity must be positive DAO calls: [find BELL, adjust BELL -2, find BELL, adjust BELL -5, find HORN] Bells left: 1
What this code does
The fake DAO keeps three bells in a map and writes down every method call, so the test can check both the results and how the service used the data layer. The four checks cover a normal sale, a sale larger than the stock, an unknown part and an invalid quantity. The call list shows that the zero-quantity sale never reached the DAO at all, exactly as the rule intends.
DAOs, repositories and libraries
You will meet the word repository for nearly the same idea. Strictly, a DAO is organized around tables and data operations, while a repository, from domain-driven design, behaves like an in-memory collection of domain objects with methods such as findById and save, hiding even the fact that a database exists. Spring Data's repositories, which the web service course uses, generate the implementation from an interface. Many projects use the two words interchangeably, and both serve the same purpose: one place for persistence code, behind an interface. A generic repository interface parameterized by the entity and key types is a common starting point.
import java.util.*;
import java.util.function.Function;
public class Main {
interface Repository<T, ID> {
Optional<T> findById(ID id);
List<T> findAll();
T save(T item);
boolean deleteById(ID id);
}
static class MapRepository<T, ID extends Comparable<ID>> implements Repository<T, ID> {
private final Map<ID, T> items = new TreeMap<>();
private final Function<T, ID> idOf;
MapRepository(Function<T, ID> idOf) { this.idOf = idOf; }
public Optional<T> findById(ID id) { return Optional.ofNullable(items.get(id)); }
public List<T> findAll() { return List.copyOf(items.values()); }
public T save(T item) { items.put(idOf.apply(item), item); return item; }
public boolean deleteById(ID id) { return items.remove(id) != null; }
}
record Bike(String tag, String kind) {}
record Supplier(int id, String name) {}
public static void main(String[] args) {
Repository<Bike, String> bikes = new MapRepository<>(Bike::tag);
bikes.save(new Bike("SG-0012-TA", "TANDEM"));
bikes.save(new Bike("SG-0007-AR", "CARGO"));
bikes.save(new Bike("SG-0003-CI", "CITY"));
Repository<Supplier, Integer> suppliers = new MapRepository<>(Supplier::id);
suppliers.save(new Supplier(2, "Larchmont Cycle Supply"));
suppliers.save(new Supplier(1, "Velotek"));
System.out.println(bikes.findAll());
System.out.println(bikes.findById("SG-0007-AR").map(Bike::kind).orElse("none"));
System.out.println("Retired SG-0003-CI: " + bikes.deleteById("SG-0003-CI") + ", bikes left: " + bikes.findAll().size());
System.out.println(suppliers.findById(1).orElseThrow().name());
}
}[Bike[tag=SG-0003-CI, kind=CITY], Bike[tag=SG-0007-AR, kind=CARGO], Bike[tag=SG-0012-TA, kind=TANDEM]] CARGO Retired SG-0003-CI: true, bikes left: 2 Velotek
What this code does
One generic class serves both bikes, keyed by a String tag, and suppliers, keyed by an Integer id, because the key is extracted by a function passed to the constructor. The TreeMap keeps findAll in key order. The interface is the same shape a database-backed repository would have, so tests and production can share it.
Plain JDBC DAOs are a perfectly professional choice for a program of StockStore's size. When the boilerplate grows, libraries help without hiding SQL: Spring's JdbcClient and JdbcTemplate handle connections, statements and exception translation, Jdbi maps rows to objects declaratively, and jOOQ generates Java classes from the schema so that queries are checked by the compiler. Lessons 7 to 10 cover the other path, JPA, which generates most of the SQL for you.
Try it yourself
Add a query to the in-memory DAO
Add a method onShelf(String shelf) to InMemoryPartDao that returns the parts on that shelf, sorted by code (the map already keeps them in code order).
The program prints the parts on shelf C-03 as [BELL, GRIPS, PUMP-FLOOR].
import java.util.*;
public class Main {
record Part(String code, String shelf, int quantity) {}
static class InMemoryPartDao {
private final Map<String, Part> parts = new TreeMap<>();
void save(Part p) { parts.put(p.code(), p); }
List<String> onShelf(String shelf) {
return List.of();
}
}
public static void main(String[] args) {
InMemoryPartDao dao = new InMemoryPartDao();
dao.save(new Part("PUMP-FLOOR", "C-03", 2));
dao.save(new Part("BELL", "C-03", 3));
dao.save(new Part("CHAIN-11", "B-14", 1));
dao.save(new Part("GRIPS", "C-03", 9));
System.out.println(dao.onShelf("C-03"));
}
}Try it yourself
Translate an SQL state
Write translate so it returns DuplicateKeyException for SQLState 23505, ForeignKeyException for 23503, TransientDataAccessException for any state starting with 08, and DataAccessException for anything else.
The program prints one name for each state.
import java.util.*;
public class Main {
static String translate(String state) {
return "DataAccessException";
}
public static void main(String[] args) {
for (String s : List.of("23505", "23503", "08006", "42703", "08001")) {
System.out.println(s + " -> " + translate(s));
}
}
}Common mistakes
MistakeWriting SQL directly in screens and reports.
InsteadPut all SQL for a table in one DAO class behind an interface.
MistakeDeclaring throws SQLException on every service method.
InsteadTranslate SQLException into unchecked exceptions at the edge of the data layer, keeping the cause.
MistakeCommitting inside each DAO method.
InsteadManage transactions in the service layer, so several DAOs can share one.
MistakeTesting business rules only against a real database.
InsteadTest services with an in-memory or fake DAO, and test the SQL in a few integration tests.
MistakeLosing the original SQLException when translating.
InsteadPass it as the cause of the new exception.
Summary and key terms
- A data access object hides the SQL for one kind of data behind an interface whose methods use program types such as Optional and List.
- The rest of the program depends only on the interface, so SQL lives in one place, services can be tested with in-memory or fake implementations, and the database can change by adding an implementation.
- The JDBC implementation contains all the JDBC rules: a DataSource, prepared statements, try-with-resources, one mapping method per table and atomic updates.
- SQLException is translated at the edge into unchecked exceptions, with subclasses for cases callers can handle, such as duplicate keys and transient failures.
- Transactions belong in the service layer, often in a helper that runs a lambda inside one connection.
- Repositories are a closely related idea from domain-driven design, and libraries such as JdbcClient, Jdbi and jOOQ reduce the boilerplate.
- Data access object
- An object that moves one kind of data between the program and the database behind an interface.
- DAO interface
- The methods a DAO offers, written in program terms with no JDBC types.
- In-memory implementation
- A version of a DAO that keeps data in collections, used in tests.
- Fake
- A simple working implementation of an interface used in tests, often recording its calls.
- Exception translation
- Turning a low-level exception such as SQLException into an exception the program understands.
- Unchecked exception
- An exception that does not have to be declared or caught.
- Service layer
- The code that carries out business operations, using DAOs and managing transactions.
- Integration test
- A test that checks code together with a real external system such as a database.
- Repository
- A collection-like interface for domain objects that hides persistence.
- Transaction helper
- A method that runs work inside one connection and transaction, then commits or rolls back.
Practice problems
Work each problem on paper first, then reveal the answer to check.
How many files changed when the column was renamed before the DAO, and after?
Show answer
Seven before; afterwards only the DAO and the migration.
What should a lookup method return when nothing matches?
Show answer
An empty Optional.
Why translate SQLException into an unchecked exception?
Show answer
So callers that cannot handle it are not forced to declare it, while the cause is kept.
Where should a delivery's transaction be managed?
Show answer
In the service layer, around both DAO calls.
How can ReorderService be tested without a database?
Show answer
By giving it an in-memory or fake PartDao.
Lesson quiz · 5 questions
Score 60% or more to complete this lesson.
Create a free account to save your progress, take quizzes and earn a certificate.