Implementing DAO for Database Interaction

DAO: A boundary object that encapsulates persistence operations so the rest of the application does not need to know query, connection, or storage-specific details.

In Java, a DAO is useful when it gives the codebase a clear persistence boundary. It is less useful when it is only another layer that forwards every call to an ORM with no added design value.

Start With A Narrow Interface

Good DAOs expose operations in the language of the application, not in the language of SQL tables:

1public interface CustomerDao {
2    Optional<CustomerRecord> findById(CustomerId id);
3    List<CustomerRecord> findActiveByRegion(String region);
4    void save(CustomerRecord customer);
5}

That interface says what the application needs from persistence without exposing connection handling, query strings, or framework mechanics.

A Simple JDBC-Based Implementation

 1public final class JdbcCustomerDao implements CustomerDao {
 2    private final DataSource dataSource;
 3
 4    public JdbcCustomerDao(DataSource dataSource) {
 5        this.dataSource = dataSource;
 6    }
 7
 8    @Override
 9    public Optional<CustomerRecord> findById(CustomerId id) {
10        String sql = """
11            select id, name, region, active
12            from customer
13            where id = ?
14            """;
15
16        try (Connection connection = dataSource.getConnection();
17             PreparedStatement statement = connection.prepareStatement(sql)) {
18            statement.setString(1, id.value());
19            try (ResultSet rs = statement.executeQuery()) {
20                if (!rs.next()) {
21                    return Optional.empty();
22                }
23                return Optional.of(
24                    new CustomerRecord(
25                        new CustomerId(rs.getString("id")),
26                        rs.getString("name"),
27                        rs.getString("region"),
28                        rs.getBoolean("active")
29                    )
30                );
31            }
32        } catch (SQLException e) {
33            throw new CustomerPersistenceException("Failed to load customer " + id, e);
34        }
35    }
36}

The point is not that JDBC is always the best option. The point is that the DAO owns persistence mechanics, exception translation, and row mapping.

What The Boundary Should Isolate

The DAO should isolate:

• query construction
• connection or session use
• result mapping
• storage-specific exception handling

The visual below shows the intended boundary:

    flowchart LR
	    Service["Application service"] --> DAO["CustomerDao"]
	    DAO --> SQL["SQL or ORM query"]
	    SQL --> Store["Database"]

What Should Not Leak Through

A DAO loses value when it leaks too much of the underlying persistence model:

• raw ResultSet objects
• storage-specific exceptions everywhere
• ad hoc transactions in application services
• entity-manager or session APIs exposed directly to higher layers

If those details still dominate service code, the DAO is not really acting as a boundary.

When DAO Helps Most

DAO is a strong fit when:

• the system has meaningful query logic
• data access needs explicit review and testing
• storage technology may change in implementation details even if not in kind
• the team wants application services to stay persistence-agnostic

Design Review Questions

When reviewing a Java DAO, ask:

• Does the interface express application needs rather than table mechanics?
• Are SQL, session, or mapper details contained in the DAO?
• Is exception translation handled consistently?
• Does the DAO add clarity beyond what the framework already gives?

DAO is strongest when it creates a real persistence boundary. It is weak when it exists only to preserve an old pattern checklist.