JDBC · Lesson 12

JDBC Best Practices and DAO Pattern

Organizing JDBC Code

The DAO pattern places database access behind a focused interface. A service works with domain objects and repository methods instead of SQL strings, connection details, and cursor mechanics. This boundary makes persistence code easier to test and replace.

A Small DAO Contract

Define methods around business data rather than exposing JDBC types to every caller. The implementation owns statement creation and row mapping.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

record Learner(long id, String name) {}

public class LearnerDao {
    public Learner findById(Connection connection, long learnerId) throws Exception {
        String sql = "SELECT id, name FROM learners WHERE id = ?";
        try (PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setLong(1, learnerId);
            try (ResultSet results = statement.executeQuery()) {
                if (!results.next()) return null;
                return new Learner(results.getLong("id"), results.getString("name"));
            }
        }
    }
}

DAO Responsibilities

Common Mistake and Practices

Common Mistake: Creating one generic DAO that accepts arbitrary SQL from every caller. It removes the boundary's meaning and makes authorization, performance, and testing much harder to reason about.

• Use parameterized SQL everywhere.
• Keep DAOs cohesive and explicit.
• Return domain types or focused projections.
• Keep transaction decisions at the use-case boundary.
• Test queries against a representative database.

Real-World Use Case

A learner service calls LearnerDao.findById and never knows whether the data comes from PostgreSQL, a read replica, or a test database. The DAO keeps schema details in one controlled place.