The Repository Pattern

Put each table's SQL behind an interface in domain terms, translate SQLException at the edge, and keep transactions in the service layer above.

Syntax

public interface ProductRepository {
    Optional<Product> findById(long id);
    List<Product> findByCategory(String category, int limit);
    Product create(NewProduct product);
}

A repository hides how a kind of object is stored behind an interface written in the language of the domain: findById, findByCustomer, create. Handlers and services call the interface; only the implementation knows about SQL, connections and result sets. In a web service that gives you one place per table where the SQL lives, handlers you can read without scrolling past JDBC code, and the option of an in-memory implementation in unit tests.

Shape of the interface

Return records from your domain, never a ResultSet or a Connection.
findById returns Optional; the caller decides whether missing means 404.
Name methods for what the application needs. findOpenOrdersOlderThan(Duration) lets the database filter with an index; findAll() followed by a filter in Java loads the whole table.
Put paging in the signature (a limit and an offset, or a cursor) once a table can grow. An unbounded findAll is a production incident waiting for enough data.

Exceptions

SQLException is checked, and letting it out of the interface ties every caller to JDBC. Catch it in the implementation and translate it. A unique violation (SQLState 23505 in PostgreSQL) becomes a domain exception such as DuplicateSkuException, which the web layer turns into 409 Conflict. Anything else is wrapped in an unchecked exception with the original as its cause, so the stack trace still shows the real failure. Check the SQLState rather than the exception class: drivers are not required to throw specific subclasses such as SQLIntegrityConstraintViolationException, and the PostgreSQL driver does not. Spring does the same translation into its DataAccessException hierarchy.

Transactions belong above

A repository method is usually too small to be a transaction boundary. Placing an order touches stock and orders, two repositories, and both must commit together. Let the service layer own the transaction: either it opens the connection and passes it to repository methods, or a framework such as Spring binds the connection to the current thread and @Transactional on the service method covers every repository call inside it.

Testing

An in-memory implementation makes service tests fast, but it cannot tell you whether your SQL is right. Test the JDBC implementation itself against a real database of the same kind and version, for example with Testcontainers. An embedded database such as H2 accepts some SQL that PostgreSQL rejects and the other way round, so tests can pass on it while production fails.

Resist building one generic repository for every table on day one. A small interface per aggregate, holding only the queries the application runs, is easier to read and to optimise.

Example

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import javax.sql.DataSource;

// The interface speaks the domain: no SQLException, ResultSet or Connection.
interface ProductRepository {
    Optional<Product> findById(long id);
    List<Product> findByCategory(String category, int limit);
    Product create(NewProduct product);
}

record Product(long id, String sku, String name, String category, BigDecimal price) {}
record NewProduct(String sku, String name, String category, BigDecimal price) {}

class DuplicateSkuException extends RuntimeException {
    DuplicateSkuException(String sku, Throwable cause) {
        super("SKU already exists: " + sku, cause);
    }
}

class DataAccessFailure extends RuntimeException {
    DataAccessFailure(String message, Throwable cause) {
        super(message, cause);
    }
}

public class JdbcProductRepository implements ProductRepository {
    private static final String COLUMNS = "id, sku, name, category, price";
    private static final String UNIQUE_VIOLATION = "23505";   // PostgreSQL SQLState

    private final DataSource dataSource;

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

    @Override
    public Optional<Product> findById(long id) {
        String sql = "SELECT " + COLUMNS + " FROM products 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(map(rs)) : Optional.empty();
            }
        } catch (SQLException e) {
            throw new DataAccessFailure("findById failed for product " + id, e);
        }
    }

    @Override
    public List<Product> findByCategory(String category, int limit) {
        String sql = "SELECT " + COLUMNS + " FROM products WHERE category = ? ORDER BY name, id LIMIT ?";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, category);
            ps.setInt(2, limit);
            try (ResultSet rs = ps.executeQuery()) {
                List<Product> out = new ArrayList<>();
                while (rs.next()) {
                    out.add(map(rs));
                }
                return out;
            }
        } catch (SQLException e) {
            throw new DataAccessFailure("findByCategory failed", e);
        }
    }

    @Override
    public Product create(NewProduct p) {
        String sql = "INSERT INTO products (sku, name, category, price) VALUES (?, ?, ?, ?)";
        try (Connection conn = dataSource.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql, new String[] {"id"})) {
            ps.setString(1, p.sku());
            ps.setString(2, p.name());
            ps.setString(3, p.category());
            ps.setBigDecimal(4, p.price());
            ps.executeUpdate();
            try (ResultSet keys = ps.getGeneratedKeys()) {
                keys.next();
                return new Product(keys.getLong(1), p.sku(), p.name(), p.category(), p.price());
            }
        } catch (SQLException e) {
            if (UNIQUE_VIOLATION.equals(e.getSQLState())) {
                throw new DuplicateSkuException(p.sku(), e);   // the API answers 409
            }
            throw new DataAccessFailure("create failed for SKU " + p.sku(), e);
        }
    }

    private static Product map(ResultSet rs) throws SQLException {
        return new Product(rs.getLong("id"), rs.getString("sku"), rs.getString("name"),
                rs.getString("category"), rs.getBigDecimal("price"));
    }
}

When to use it

Keep every SQL statement for the products table in one class that the product endpoints call through an interface.
Turn a unique-constraint violation on SKU into a 409 Conflict response instead of a generic 500.
Unit test checkout rules with an in-memory repository and test the SQL separately against a real database.

More examples

An in-memory implementation behind the same interface

The fake reproduces the behaviour the service depends on, including the duplicate-SKU rule the real table enforces with a unique index. The status codes show how domain results and exceptions map to HTTP answers without the service touching SQL.

import java.math.BigDecimal;
import java.util.Map;
import java.util.Optional;
import java.util.TreeMap;

record Product(long id, String sku, String name, BigDecimal price) {}

class DuplicateSkuException extends RuntimeException {
    DuplicateSkuException(String sku) {
        super("SKU already exists: " + sku);
    }
}

interface ProductRepository {
    Optional<Product> findById(long id);
    Product create(String sku, String name, BigDecimal price);
}

// Used by fast unit tests of the service; the JDBC version is tested against a real database.
class InMemoryProductRepository implements ProductRepository {
    private final Map<Long, Product> rows = new TreeMap<>();
    private long nextId = 1;

    public Optional<Product> findById(long id) {
        return Optional.ofNullable(rows.get(id));
    }

    public Product create(String sku, String name, BigDecimal price) {
        if (rows.values().stream().anyMatch(p -> p.sku().equals(sku))) {
            throw new DuplicateSkuException(sku);
        }
        Product p = new Product(nextId++, sku, name, price);
        rows.put(p.id(), p);
        return p;
    }
}

// The service only knows the interface, so it cannot tell which implementation it has.
class ProductService {
    private final ProductRepository products;

    ProductService(ProductRepository products) {
        this.products = products;
    }

    String get(long id) {
        return products.findById(id).map(p -> "200 " + p).orElse("404 no product " + id);
    }

    String create(String sku, String name, String price) {
        try {
            return "201 " + products.create(sku, name, new BigDecimal(price));
        } catch (DuplicateSkuException e) {
            return "409 " + e.getMessage();
        }
    }
}

void main() {
    ProductService service = new ProductService(new InMemoryProductRepository());
    IO.println(service.create("LAMP-1", "Desk lamp", "24.90"));
    IO.println(service.create("LAMP-1", "Floor lamp", "79.00"));
    IO.println(service.get(1));
    IO.println(service.get(2));
}

// Output:
// 201 Product[id=1, sku=LAMP-1, name=Desk lamp, price=24.90]
// 409 SKU already exists: LAMP-1
// 200 Product[id=1, sku=LAMP-1, name=Desk lamp, price=24.90]
// 404 no product 2

The JDBC implementation with Spring's JdbcClient

Spring removes the connection handling and translates SQLException into unchecked DataAccessException subclasses such as DuplicateKeyException. The interface, and every caller of it, stays exactly the same as with plain JDBC.

import java.math.BigDecimal;
import java.util.Optional;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.jdbc.core.simple.JdbcClient;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

record Product(long id, String sku, String name, BigDecimal price) {}

class DuplicateSkuException extends RuntimeException {
    DuplicateSkuException(String sku) {
        super("SKU already exists: " + sku);
    }
}

interface ProductRepository {
    Optional<Product> findById(long id);
    Product create(String sku, String name, BigDecimal price);
}

@Repository
class JdbcClientProductRepository implements ProductRepository {
    private final JdbcClient jdbc;

    JdbcClientProductRepository(JdbcClient jdbc) {
        this.jdbc = jdbc;
    }

    @Override
    public Optional<Product> findById(long id) {
        return jdbc.sql("SELECT id, sku, name, price FROM products WHERE id = ?")
                .param(id)
                .query(Product.class)
                .optional();
    }

    @Override
    public Product create(String sku, String name, BigDecimal price) {
        KeyHolder keys = new GeneratedKeyHolder();
        try {
            jdbc.sql("INSERT INTO products (sku, name, price) VALUES (?, ?, ?)")
                .params(sku, name, price)
                .update(keys, "id");
        } catch (DuplicateKeyException e) {          // Spring already translated the SQLState
            throw new DuplicateSkuException(sku);
        }
        return new Product(keys.getKeyAs(Long.class), sku, name, price);
    }
}

Unit testing the service with JUnit

These tests run in milliseconds and need no database or container. Pair them with a smaller set of integration tests that run JdbcProductRepository against a real PostgreSQL instance.

import java.math.BigDecimal;
import java.util.Map;
import java.util.Optional;
import java.util.TreeMap;

import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertTrue;

import org.junit.jupiter.api.Test;

class ProductServiceTest {
    private final ProductService service = new ProductService(new InMemoryProductRepository());

    @Test
    void rejectsADuplicateSku() {
        service.create("LAMP-1", "Desk lamp", "24.90");

        String second = service.create("LAMP-1", "Floor lamp", "79.00");

        assertTrue(second.startsWith("409"));
    }

    @Test
    void reportsAMissingProduct() {
        assertEquals("404 no product 99", service.get(99));
    }
}

// The types under test, as in the in-memory example above.
record Product(long id, String sku, String name, BigDecimal price) {}

class DuplicateSkuException extends RuntimeException {
    DuplicateSkuException(String sku) {
        super("SKU already exists: " + sku);
    }
}

interface ProductRepository {
    Optional<Product> findById(long id);
    Product create(String sku, String name, BigDecimal price);
}

// Used by fast unit tests of the service; the JDBC version is tested against a real database.
class InMemoryProductRepository implements ProductRepository {
    private final Map<Long, Product> rows = new TreeMap<>();
    private long nextId = 1;

    public Optional<Product> findById(long id) {
        return Optional.ofNullable(rows.get(id));
    }

    public Product create(String sku, String name, BigDecimal price) {
        if (rows.values().stream().anyMatch(p -> p.sku().equals(sku))) {
            throw new DuplicateSkuException(sku);
        }
        Product p = new Product(nextId++, sku, name, price);
        rows.put(p.id(), p);
        return p;
    }
}

// The service only knows the interface, so it cannot tell which implementation it has.
class ProductService {
    private final ProductRepository products;

    ProductService(ProductRepository products) {
        this.products = products;
    }

    String get(long id) {
        return products.findById(id).map(p -> "200 " + p).orElse("404 no product " + id);
    }

    String create(String sku, String name, String price) {
        try {
            return "201 " + products.create(sku, name, new BigDecimal(price));
        } catch (DuplicateSkuException e) {
            return "409 " + e.getMessage();
        }
    }
}

Discussion

Be the first to comment on this lesson.

Shape of the interface

Exceptions

Transactions belong above

Testing

Example

When to use it

More examples

An in-memory implementation behind the same interface

The JDBC implementation with Spring's JdbcClient

Unit testing the service with JUnit

Related

Discussion