JDBC & Databases

Project: A DAO Layer

15 min Lesson 10 of 13

Project: A DAO Layer

Every production application eventually needs a clean boundary between business logic and the database. The Data Access Object (DAO) pattern draws that boundary: a DAO class owns all SQL for one table, exposing plain Java methods to the rest of the application. Your service layer never writes a PreparedStatement again — it calls userDao.findById(id) and works with a User object.

In this project lesson you will build a complete, production-quality DAO layer for a users table, starting from the interface contract, through the JDBC implementation, and finishing with a thin service that uses it.

Why a DAO?

Separation of concerns — SQL lives in one place; business logic stays SQL-free.
Testability — the service layer depends on the interface, so you can swap in an in-memory fake for unit tests without touching a real database.
Maintainability — a schema change (rename a column, add a field) is confined to one class.
Readability — userDao.findByEmail(email) tells a reader exactly what is happening, no SQL scanning required.

DAO vs Repository: both patterns do the same job. DAO is the classic Java EE term (one DAO per table). Repository is the Domain-Driven Design term (one repository per aggregate, may span tables). In a plain JDBC project the names are interchangeable — what matters is the principle.

Step 1 — The Domain Object

A domain object (sometimes called an entity or model) is a plain Java class whose fields mirror the table columns. Use a Java 17 record for immutable snapshots, or a regular class with getters when you need mutability.

// User.java
public record User(long id, String name, String email, String role) {

    // Convenience factory — build a new User without an id yet
    public static User of(String name, String email, String role) {
        return new User(0, name, email, role);
    }
}

Step 2 — The DAO Interface

Define the contract first. The rest of the application depends only on this interface, never on the concrete JDBC class.

import java.util.List;
import java.util.Optional;

// UserDao.java
public interface UserDao {

    User save(User user);               // INSERT; returns the saved user with its generated id
    Optional<User> findById(long id);  // SELECT by PK
    Optional<User> findByEmail(String email);
    List<User> findAll();
    List<User> findByRole(String role);
    boolean update(User user);          // UPDATE; returns true if a row was changed
    boolean deleteById(long id);        // DELETE; returns true if a row was removed
}

Return Optional for single-row lookups that may produce no result. Returning null forces every caller to null-check; Optional makes the possibility of absence explicit in the type.

Step 3 — The JDBC Implementation

The implementation class holds a DataSource injected through the constructor. It opens a fresh connection per method (the pool handles recycling), executes SQL, and maps the ResultSet through a private helper.

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

// JdbcUserDao.java
public class JdbcUserDao implements UserDao {

    private final DataSource dataSource;

    public JdbcUserDao(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    // ---- private helper -------------------------------------------------

    private User mapRow(ResultSet rs) throws SQLException {
        return new User(
            rs.getLong("id"),
            rs.getString("name"),
            rs.getString("email"),
            rs.getString("role")
        );
    }

    // ---- INSERT ---------------------------------------------------------

    @Override
    public User save(User user) {
        String sql = "INSERT INTO users (name, email, role) VALUES (?, ?, ?)";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {

            ps.setString(1, user.name());
            ps.setString(2, user.email());
            ps.setString(3, user.role());
            ps.executeUpdate();

            try (ResultSet keys = ps.getGeneratedKeys()) {
                if (keys.next()) {
                    return new User(keys.getLong(1), user.name(), user.email(), user.role());
                }
            }
            throw new SQLException("INSERT did not return a generated key");

        } catch (SQLException e) {
            throw new DataAccessException("save failed", e);
        }
    }

    // ---- SELECT by PK ---------------------------------------------------

    @Override
    public Optional<User> findById(long id) {
        String sql = "SELECT id, name, email, role FROM users WHERE id = ?";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setLong(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? Optional.of(mapRow(rs)) : Optional.empty();
            }

        } catch (SQLException e) {
            throw new DataAccessException("findById failed", e);
        }
    }

    // ---- SELECT by email ------------------------------------------------

    @Override
    public Optional<User> findByEmail(String email) {
        String sql = "SELECT id, name, email, role FROM users WHERE email = ?";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, email);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? Optional.of(mapRow(rs)) : Optional.empty();
            }

        } catch (SQLException e) {
            throw new DataAccessException("findByEmail failed", e);
        }
    }

    // ---- SELECT all -----------------------------------------------------

    @Override
    public List<User> findAll() {
        String sql = "SELECT id, name, email, role FROM users ORDER BY id";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            List<User> users = new ArrayList<>();
            while (rs.next()) {
                users.add(mapRow(rs));
            }
            return users;

        } catch (SQLException e) {
            throw new DataAccessException("findAll failed", e);
        }
    }

    // ---- SELECT by role -------------------------------------------------

    @Override
    public List<User> findByRole(String role) {
        String sql = "SELECT id, name, email, role FROM users WHERE role = ?";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, role);
            try (ResultSet rs = ps.executeQuery()) {
                List<User> users = new ArrayList<>();
                while (rs.next()) {
                    users.add(mapRow(rs));
                }
                return users;
            }

        } catch (SQLException e) {
            throw new DataAccessException("findByRole failed", e);
        }
    }

    // ---- UPDATE ---------------------------------------------------------

    @Override
    public boolean update(User user) {
        String sql = "UPDATE users SET name = ?, email = ?, role = ? WHERE id = ?";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, user.name());
            ps.setString(2, user.email());
            ps.setString(3, user.role());
            ps.setLong(4, user.id());
            return ps.executeUpdate() > 0;

        } catch (SQLException e) {
            throw new DataAccessException("update failed", e);
        }
    }

    // ---- DELETE ---------------------------------------------------------

    @Override
    public boolean deleteById(long id) {
        String sql = "DELETE FROM users WHERE id = ?";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setLong(1, id);
            return ps.executeUpdate() > 0;

        } catch (SQLException e) {
            throw new DataAccessException("deleteById failed", e);
        }
    }
}

Step 4 — The DataAccessException Wrapper

Callers should not have to declare throws SQLException everywhere. Wrap the checked exception in a runtime exception so it propagates naturally.

// DataAccessException.java
public class DataAccessException extends RuntimeException {
    public DataAccessException(String message, Throwable cause) {
        super(message, cause);
    }
}

Step 5 — A Service That Uses the DAO

The service holds business rules. It depends only on UserDao (the interface), not on JdbcUserDao directly.

// UserService.java
public class UserService {

    private final UserDao userDao;

    public UserService(UserDao userDao) {
        this.userDao = userDao;
    }

    public User registerUser(String name, String email, String role) {
        userDao.findByEmail(email).ifPresent(existing -> {
            throw new IllegalStateException("Email already registered: " + email);
        });
        return userDao.save(User.of(name, email, role));
    }

    public User getOrThrow(long id) {
        return userDao.findById(id)
                .orElseThrow(() -> new IllegalArgumentException("No user with id " + id));
    }

    public List<User> admins() {
        return userDao.findByRole("ADMIN");
    }
}

Constructor injection makes the dependency explicit and keeps the class easy to test. To unit-test UserService, pass a simple in-memory UserDao implementation — no database required.

Step 6 — Wiring It Together

In a real application a DI framework (Spring, Guice) handles wiring. In a standalone project, wire manually in main:

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class Main {
    public static void main(String[] args) {

        // 1. Connection pool (HikariCP)
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
        config.setUsername("root");
        config.setPassword("secret");
        config.setMaximumPoolSize(10);
        var dataSource = new HikariDataSource(config);

        // 2. Wire layers
        UserDao userDao = new JdbcUserDao(dataSource);
        UserService service = new UserService(userDao);

        // 3. Use the service
        User alice = service.registerUser("Alice", "alice@example.com", "ADMIN");
        System.out.println("Saved: " + alice);

        service.admins().forEach(System.out::println);

        dataSource.close();
    }
}

Key Design Trade-offs

One connection per method call — simple and correct with a pool, but a multi-step operation that needs atomicity should pass a Connection explicitly (or use a TransactionManager abstraction).
No query builder — raw SQL strings are fragile to rename refactors. A small project can live with this; larger projects benefit from jOOQ or QueryDSL for type-safe SQL.
mapRow is not reused across DAOs — for many tables, a RowMapper<T> functional interface keeps mapping logic composable and testable in isolation.

Never put SQL inside a service or controller. Once SQL leaks out of the DAO layer, it multiplies: you end up with the same query in three places, and a schema change requires hunting every copy.

Summary

A DAO layer consists of: a domain record/class that models one row, a DAO interface that declares every database operation, a JDBC implementation that owns all SQL and maps result sets, a runtime exception wrapper to avoid checked-exception noise, and a service that depends only on the interface. This architecture is the foundation of every Java persistence layer — whether you eventually replace the JDBC implementation with JPA, jOOQ, or MyBatis, the interface and service code stays unchanged.