The DAO Pattern
Every non-trivial application eventually faces the same design question: where does the code that talks to the database belong? Without a deliberate answer, SQL strings creep into servlets, service classes, and even view helpers — a condition commonly called persistence logic bleed. The Data Access Object (DAO) pattern is the industry's standard cure. It hides every detail of data retrieval and storage behind a focused interface, leaving the rest of the application free to work with plain Java objects and ignorant of SQL entirely.
What Problem DAOs Actually Solve
Imagine a servlet that lists products. Without a DAO, it would hold a Connection, build SQL strings, iterate a ResultSet, and construct domain objects — all inline. That servlet is now impossible to unit test without a running database, impossible to switch from MySQL to PostgreSQL without touching business logic, and impossible to read quickly by anyone new to the code.
A DAO draws a hard boundary: on one side, what the application needs ("give me all products in category X"); on the other side, how that happens in the database. Everything on the second side is the DAO's private concern.
The Interface — Programming to an Abstraction
Start by defining an interface that expresses the operations the application needs, using only domain types:
package com.example.dao;
import com.example.model.Product;
import java.util.List;
import java.util.Optional;
public interface ProductDao {
List<Product> findAll();
Optional<Product> findById(int id);
List<Product> findByCategory(String category);
void save(Product product); // INSERT or UPDATE
void delete(int id);
}
Notice what is absent: no Connection, no SQLException, no SQL keyword. Callers depend solely on this contract. Swapping the backing store — MySQL today, an in-memory fake for tests tomorrow — requires no change anywhere except which implementation is injected.
Why use Optional for single-object lookups? Returning null from findById forces every caller to add a null check or risk a NullPointerException. Optional<Product> makes the possibility of absence explicit in the type, so the compiler can enforce handling.
The Domain Model
The objects the DAO works with are simple Plain Old Java Objects (POJOs) — sometimes called entities or domain objects. They carry state but contain no database logic:
package com.example.model;
public class Product {
private int id;
private String name;
private String category;
private double price;
private int stock;
// canonical constructor
public Product(int id, String name, String category, double price, int stock) {
this.id = id;
this.name = name;
this.category = category;
this.price = price;
this.stock = stock;
}
// zero-arg constructor for the "insert without ID" case
public Product() {}
// getters and setters omitted for brevity
public int getId() { return id; }
public String getName() { return name; }
public String getCategory() { return category; }
public double getPrice() { return price; }
public int getStock() { return stock; }
public void setId(int id) { this.id = id; }
public void setName(String name) { this.name = name; }
public void setCategory(String cat) { this.category = cat; }
public void setPrice(double price) { this.price = price; }
public void setStock(int stock) { this.stock = stock; }
}
The JDBC Implementation
The concrete class implements the interface and is the only place in the entire codebase that knows about SQL:
package com.example.dao;
import com.example.model.Product;
import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
public class JdbcProductDao implements ProductDao {
private final DataSource dataSource;
// DataSource injected — no DriverManager, no hardcoded URL
public JdbcProductDao(DataSource dataSource) {
this.dataSource = dataSource;
}
@Override
public List<Product> findAll() {
String sql = "SELECT id, name, category, price, stock FROM products ORDER BY name";
List<Product> results = new ArrayList<>();
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
results.add(mapRow(rs));
}
} catch (SQLException e) {
throw new DataAccessException("findAll failed", e);
}
return results;
}
@Override
public Optional<Product> findById(int id) {
String sql = "SELECT id, name, category, price, stock FROM products WHERE id = ?";
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, id);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? Optional.of(mapRow(rs)) : Optional.empty();
}
} catch (SQLException e) {
throw new DataAccessException("findById failed for id=" + id, e);
}
}
@Override
public List<Product> findByCategory(String category) {
String sql = "SELECT id, name, category, price, stock FROM products WHERE category = ?";
List<Product> results = new ArrayList<>();
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, category);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) results.add(mapRow(rs));
}
} catch (SQLException e) {
throw new DataAccessException("findByCategory failed", e);
}
return results;
}
@Override
public void save(Product product) {
if (product.getId() == 0) {
insert(product);
} else {
update(product);
}
}
private void insert(Product p) {
String sql = "INSERT INTO products (name, category, price, stock) VALUES (?, ?, ?, ?)";
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
ps.setString(1, p.getName());
ps.setString(2, p.getCategory());
ps.setDouble(3, p.getPrice());
ps.setInt(4, p.getStock());
ps.executeUpdate();
try (ResultSet keys = ps.getGeneratedKeys()) {
if (keys.next()) p.setId(keys.getInt(1));
}
} catch (SQLException e) {
throw new DataAccessException("insert failed", e);
}
}
private void update(Product p) {
String sql = "UPDATE products SET name=?, category=?, price=?, stock=? WHERE id=?";
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setString(1, p.getName());
ps.setString(2, p.getCategory());
ps.setDouble(3, p.getPrice());
ps.setInt(4, p.getStock());
ps.setInt(5, p.getId());
ps.executeUpdate();
} catch (SQLException e) {
throw new DataAccessException("update failed", e);
}
}
@Override
public void delete(int id) {
String sql = "DELETE FROM products WHERE id = ?";
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, id);
ps.executeUpdate();
} catch (SQLException e) {
throw new DataAccessException("delete failed", e);
}
}
// private row-mapper — the only place that knows column names
private Product mapRow(ResultSet rs) throws SQLException {
return new Product(
rs.getInt("id"),
rs.getString("name"),
rs.getString("category"),
rs.getDouble("price"),
rs.getInt("stock")
);
}
}
The Custom Runtime Exception
Because SQLException is a checked exception, it would leak from every DAO method into callers that have no business knowing about JDBC. The standard solution is to wrap it in a runtime exception and rethrow:
package com.example.dao;
public class DataAccessException extends RuntimeException {
public DataAccessException(String message, Throwable cause) {
super(message, cause);
}
}
Callers catch DataAccessException only at the boundary where they can do something useful — typically a servlet's doGet / doPost that maps it to a 500 error page.
Keep the private mapRow method. If the column name in the database ever changes, you fix it in exactly one place. Inlining rs.getString("name") in each query method guarantees you will miss at least one occurrence.
Wiring It Together in a Servlet
The servlet becomes a thin coordinator: it validates input, calls the DAO, and delegates rendering to a JSP. It contains zero SQL:
@WebServlet("/products")
public class ProductServlet extends HttpServlet {
private ProductDao productDao;
@Override
public void init() {
// DataSource looked up from JNDI (configured in the app server)
DataSource ds = (DataSource) new InitialContext()
.lookup("java:comp/env/jdbc/shopDB");
productDao = new JdbcProductDao(ds);
}
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
String cat = req.getParameter("category");
List<Product> products = (cat != null && !cat.isBlank())
? productDao.findByCategory(cat)
: productDao.findAll();
req.setAttribute("products", products);
req.getRequestDispatcher("/WEB-INF/views/products.jsp").forward(req, resp);
}
}
Testing the DAO in Isolation
Because the DAO interface accepts a DataSource, tests can inject an in-memory H2 database without touching production code:
// In a JUnit 5 test — no Mockito, no mocking, just a real H2 in-memory DB
class JdbcProductDaoTest {
private static HikariDataSource ds;
private ProductDao dao;
@BeforeAll
static void setupPool() {
HikariConfig cfg = new HikariConfig();
cfg.setJdbcUrl("jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1");
cfg.setUsername("sa");
cfg.setPassword("");
ds = new HikariDataSource(cfg);
}
@BeforeEach
void setupSchema() throws Exception {
try (Connection c = ds.getConnection(); Statement s = c.createStatement()) {
s.execute("DROP TABLE IF EXISTS products");
s.execute("""
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
category VARCHAR(50),
price DOUBLE,
stock INT
)""");
}
dao = new JdbcProductDao(ds);
}
@Test
void saveAndFindById() {
Product p = new Product();
p.setName("Widget"); p.setCategory("tools"); p.setPrice(9.99); p.setStock(50);
dao.save(p);
assertTrue(p.getId() > 0, "generated key should be set after insert");
Optional<Product> found = dao.findById(p.getId());
assertTrue(found.isPresent());
assertEquals("Widget", found.get().getName());
}
}
Do not test DAO logic through a mock ResultSet. Mocking ResultSet verifies that your code calls the right methods in the right order — not that it returns the right data. Use a real (lightweight) database like H2 so the test is meaningful.
Key Trade-offs and Alternatives
- DAO vs Repository: "Repository" (from Domain-Driven Design) is conceptually richer — it acts like an in-memory collection of domain objects. In practice, for plain JDBC the terms are often used interchangeably. The key distinction matters more when using JPA/Hibernate.
- One DAO per table vs per aggregate: For simple schemas, one DAO per table is fine. When domain objects span multiple tables (e.g.,
Order with OrderItem rows), a single OrderDao managing both is cleaner than two separate DAOs that must coordinate.
- Generic base DAO: A
BaseDao<T, ID> superclass can hold shared CRUD logic, reducing boilerplate when you have many entities. Introduce it once you have three or more concrete DAOs with repetitive code.
Summary
The DAO pattern isolates all JDBC code behind a typed interface. The interface expresses domain intent; the implementation handles SQL. This separation makes individual components testable in isolation, allows the backing store to be swapped without touching business logic, and keeps servlets and service classes readable. Everything in the coming lessons — transactions, exception handling, the service layer — builds on this foundation.