Building the Data Layer
The data layer is the seam between your application's business logic and whatever persistence mechanism you choose — a relational database today, maybe a document store tomorrow. Getting this layer right means the rest of the application never has to care about SQL, JDBC boilerplate, or connection management. In this lesson you will design and implement a repository/DAO layer that is clean, testable, and genuinely decoupled.
Repository vs DAO: Choosing the Right Pattern
Both patterns abstract persistence, but they sit at different levels of the domain model:
- DAO (Data Access Object) — table-centric. One DAO per database table, methods like
findById, save, deleteById. It knows about rows and columns.
- Repository — domain-centric. One repository per aggregate root in your domain (e.g.
OrderRepository, not OrderLineItemDAO). It speaks in domain objects, not SQL results.
For our capstone — a task-management application — the domain objects map closely to tables, so the distinction is small. We will use the repository name and mindset: the caller asks for a Task, not a result set.
Defining Repository Interfaces
Always program to an interface. The interface lives in a domain or port package; the JDBC implementation lives in an infrastructure or persistence package. This boundary lets you swap SQLite for PostgreSQL — or swap the whole layer for an in-memory fake during tests — without touching business logic.
// domain/repository/TaskRepository.java
package com.example.taskapp.domain.repository;
import com.example.taskapp.domain.model.Task;
import java.util.List;
import java.util.Optional;
public interface TaskRepository {
Task save(Task task); // insert or update
Optional<Task> findById(long id);
List<Task> findAll();
List<Task> findByStatus(String status);
void deleteById(long id);
}
Optional, not null. Return Optional<Task> from finder methods instead of returning null. Callers must handle the absent case explicitly, eliminating silent NullPointerExceptions at the service layer.
JDBC Infrastructure: the Connection Source
Rather than opening a new connection on every call (expensive) or passing a Connection around (fragile), use a tiny DataSource wrapper. For a self-contained capstone, SQLite with a simple pool is enough; the pattern scales directly to HikariCP + PostgreSQL in production.
// infrastructure/persistence/Database.java
package com.example.taskapp.infrastructure.persistence;
import org.sqlite.SQLiteDataSource;
import javax.sql.DataSource;
public final class Database {
private static final DataSource DATA_SOURCE;
static {
SQLiteDataSource ds = new SQLiteDataSource();
ds.setUrl("jdbc:sqlite:taskapp.db");
DATA_SOURCE = ds;
}
private Database() {}
public static DataSource get() {
return DATA_SOURCE;
}
}
Replace the static singleton with dependency injection in production. Pass a DataSource into each repository constructor instead of calling Database.get() inside repository methods. That makes every repository independently testable with an in-memory H2 or a Testcontainers database.
Schema Initialisation
Keep the DDL inside the application, not in a separate admin script someone might forget to run. A SchemaInitializer runs at startup, creating tables if they do not already exist.
// infrastructure/persistence/SchemaInitializer.java
package com.example.taskapp.infrastructure.persistence;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public final class SchemaInitializer {
public static void run() {
String ddl = """
CREATE TABLE IF NOT EXISTS tasks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
description TEXT,
status TEXT NOT NULL DEFAULT 'TODO',
created_at TEXT NOT NULL,
updated_at TEXT NOT NULL
);
""";
try (Connection conn = Database.get().getConnection();
Statement stmt = conn.createStatement()) {
stmt.execute(ddl);
} catch (SQLException e) {
throw new RuntimeException("Schema initialisation failed", e);
}
}
private SchemaInitializer() {}
}
Implementing the Repository
The JDBC implementation translates between ResultSet rows and domain objects. Two private helpers — a mapper and a PreparedStatement builder — keep the public methods readable.
// infrastructure/persistence/JdbcTaskRepository.java
package com.example.taskapp.infrastructure.persistence;
import com.example.taskapp.domain.model.Task;
import com.example.taskapp.domain.repository.TaskRepository;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
public class JdbcTaskRepository implements TaskRepository {
private static final String INSERT =
"INSERT INTO tasks (title, description, status, created_at, updated_at) VALUES (?, ?, ?, ?, ?)";
private static final String UPDATE =
"UPDATE tasks SET title=?, description=?, status=?, updated_at=? WHERE id=?";
private static final String FIND_BY_ID = "SELECT * FROM tasks WHERE id = ?";
private static final String FIND_ALL = "SELECT * FROM tasks ORDER BY created_at DESC";
private static final String FIND_STATUS = "SELECT * FROM tasks WHERE status = ? ORDER BY created_at DESC";
private static final String DELETE = "DELETE FROM tasks WHERE id = ?";
@Override
public Task save(Task task) {
if (task.getId() == 0) {
return insert(task);
} else {
return update(task);
}
}
private Task insert(Task task) {
String now = LocalDateTime.now().toString();
try (Connection conn = Database.get().getConnection();
PreparedStatement ps = conn.prepareStatement(INSERT, Statement.RETURN_GENERATED_KEYS)) {
ps.setString(1, task.getTitle());
ps.setString(2, task.getDescription());
ps.setString(3, task.getStatus());
ps.setString(4, now);
ps.setString(5, now);
ps.executeUpdate();
try (ResultSet keys = ps.getGeneratedKeys()) {
if (keys.next()) {
return task.withId(keys.getLong(1));
}
}
throw new RuntimeException("Insert did not return a generated key");
} catch (SQLException e) {
throw new DataAccessException("insert task", e);
}
}
private Task update(Task task) {
String now = LocalDateTime.now().toString();
try (Connection conn = Database.get().getConnection();
PreparedStatement ps = conn.prepareStatement(UPDATE)) {
ps.setString(1, task.getTitle());
ps.setString(2, task.getDescription());
ps.setString(3, task.getStatus());
ps.setString(4, now);
ps.setLong(5, task.getId());
ps.executeUpdate();
return task;
} catch (SQLException e) {
throw new DataAccessException("update task", e);
}
}
@Override
public Optional<Task> findById(long id) {
try (Connection conn = Database.get().getConnection();
PreparedStatement ps = conn.prepareStatement(FIND_BY_ID)) {
ps.setLong(1, id);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
return Optional.of(map(rs));
}
}
return Optional.empty();
} catch (SQLException e) {
throw new DataAccessException("findById " + id, e);
}
}
@Override
public List<Task> findAll() {
try (Connection conn = Database.get().getConnection();
PreparedStatement ps = conn.prepareStatement(FIND_ALL);
ResultSet rs = ps.executeQuery()) {
List<Task> result = new ArrayList<>();
while (rs.next()) result.add(map(rs));
return result;
} catch (SQLException e) {
throw new DataAccessException("findAll", e);
}
}
@Override
public List<Task> findByStatus(String status) {
try (Connection conn = Database.get().getConnection();
PreparedStatement ps = conn.prepareStatement(FIND_STATUS)) {
ps.setString(1, status);
try (ResultSet rs = ps.executeQuery()) {
List<Task> result = new ArrayList<>();
while (rs.next()) result.add(map(rs));
return result;
}
} catch (SQLException e) {
throw new DataAccessException("findByStatus " + status, e);
}
}
@Override
public void deleteById(long id) {
try (Connection conn = Database.get().getConnection();
PreparedStatement ps = conn.prepareStatement(DELETE)) {
ps.setLong(1, id);
ps.executeUpdate();
} catch (SQLException e) {
throw new DataAccessException("deleteById " + id, e);
}
}
// ---------- private helpers ----------
private Task map(ResultSet rs) throws SQLException {
return new Task(
rs.getLong("id"),
rs.getString("title"),
rs.getString("description"),
rs.getString("status"),
LocalDateTime.parse(rs.getString("created_at")),
LocalDateTime.parse(rs.getString("updated_at"))
);
}
}
A Domain Exception for Data Errors
Wrapping SQLException in a checked exception forces callers to handle database failures explicitly — which is exactly the wrong contract at the business-logic layer. Instead, convert SQLException to an unchecked runtime exception that communicates intent without leaking infrastructure details.
// infrastructure/persistence/DataAccessException.java
package com.example.taskapp.infrastructure.persistence;
public class DataAccessException extends RuntimeException {
public DataAccessException(String operation, Throwable cause) {
super("Data access failed during: " + operation, cause);
}
}
Never let SQLException propagate above the data layer. SQLException exposes JDBC internals — error codes, SQL states, vendor-specific messages — that have no meaning to a service or controller class. Wrap it every time, log it at the repository boundary, and rethrow your own abstraction.
Wiring It All Together
In Main, call SchemaInitializer.run() once before creating any repository. Inject the concrete repository into the service via the interface type:
// Main.java (excerpt)
SchemaInitializer.run();
TaskRepository repository = new JdbcTaskRepository();
TaskService service = new TaskService(repository);
// ... hand service to the CLI or HTTP layer
Because TaskService depends only on the TaskRepository interface, you can hand it a hand-crafted stub or a Mockito mock in unit tests, with no database involved at all.
Summary
A well-designed data layer hides every JDBC detail behind a domain-facing interface. The concrete repository handles connection acquisition, PreparedStatement management, row mapping, and exception translation. The rest of the application — services, CLI handlers, future HTTP controllers — sees only domain objects and Optional results. In the next lesson you will build the service layer that orchestrates these repositories to implement real business rules.