Repository pattern in Java

Problem

Queries scattered over the page and the checkout

The shop gives 10% off to a customer with three orders or more. The customer's page shows whether there is a discount, and the checkout applies it when paying. Each one writes its own query to the database and its own rule:

// The customer's page
static void showDiscount(Connection conn, String customer) throws SQLException {
    try (PreparedStatement st = conn.prepareStatement("SELECT COUNT(*) FROM orders WHERE customer = ?")) {
        st.setString(1, customer);
        try (ResultSet rs = st.executeQuery()) {
            rs.next();
            int count = rs.getInt(1);
            String text = count > 3 ? "10% off" : "no discount";
            System.out.println(customer + ": " + text);
        }
    }
}

// The checkout
static double checkout(Connection conn, String customer, double total) throws SQLException {
    try (PreparedStatement st = conn.prepareStatement("SELECT id FROM orders WHERE customer = ?")) {
        st.setString(1, customer);
        int count = 0;
        try (ResultSet rs = st.executeQuery()) {
            while (rs.next()) {
                count++;
            }
        }
        return count >= 3 ? total * 0.9 : total;
    }
}

try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
    setup(conn);
    showDiscount(conn, "Maria");
    System.out.printf("Maria pays €%.2f%n", checkout(conn, "Maria", 50.00));
}

Maria: no discount
Maria pays €45.00

Maria has three orders. Her page tells her "no discount", because there the rule is count > 3. The checkout gives her the discount, because there it is count >= 3. Two places, two versions of one rule, and nobody notices until a customer complains.

There is a second problem. The rule is glued to the query. To check whether >= 3 is right, you need a database with a table and rows in it. The orders table and its rows are filled by the setup function, as in the lessons about the database in a program. It is hidden here so it doesn't get in the way.

The idea

Repository: the data behind an interface

The queries go to one place: a repository. It has a few methods named in the language of the shop, not of the database: findById, findByCustomer, save. Whoever needs orders calls them and doesn't know whether there is SQL, a file or a dictionary in memory behind them.

This is the Repository pattern. Around it the code is arranged in layers, and each layer talks only to the layer below it:

the controller (the page, the checkout) gets the request and shows the answer;
the service holds the rule: "three orders or more give 10%";
the repository gives and keeps data. There are no rules in it;
the data: memory, SQLite or another database.

The service gets the repository from outside, in its constructor. This is Dependency Injection from the lesson Singleton and Dependency Injection. That is why any repository with the same methods can sit under it.

See it

The contract and the repository in memory

The order and the contract of the repository: three methods, and not a single line of SQL.

record Order(int id, String customer, double total) {}

interface OrderRepository {
    Order findById(int id);
    List<Order> findByCustomer(String customer);
    void save(Order order);
}

The first repository keeps the orders in memory, in a dictionary from the number to the order. save with the same number replaces the old order.

class InMemoryOrderRepository implements OrderRepository {
    private final Map<Integer, Order> orders = new LinkedHashMap<>();

    public Order findById(int id) {
        return orders.get(id);
    }

    public List<Order> findByCustomer(String customer) {
        List<Order> result = new ArrayList<>();
        for (Order order : orders.values()) {
            if (order.customer().equals(customer)) {
                result.add(order);
            }
        }
        return result;
    }

    public void save(Order order) {
        orders.put(order.id(), order);
    }
}

One function fills any repository with the shop's orders. It only knows the contract, so it works with memory and with the database:

static void fill(OrderRepository orders) {
    orders.save(new Order(1, "Maria", 80.00));
    orders.save(new Order(2, "Ivan", 25.50));
    orders.save(new Order(3, "Maria", 40.00));
    orders.save(new Order(4, "Maria", 12.90));
}

See it

The service doesn't know where the data comes from

The rule is in the service, and the controller only shows the answer. Neither of them writes SQL:

class LoyaltyService {
    private final OrderRepository orders;

    LoyaltyService(OrderRepository orders) {
        this.orders = orders;
    }

    int discountFor(String customer) {
        return orders.findByCustomer(customer).size() >= 3 ? 10 : 0;
    }
}

class OrderController {
    private final LoyaltyService loyalty;

    OrderController(LoyaltyService loyalty) {
        this.loyalty = loyalty;
    }

    void show(String customer) {
        int discount = loyalty.discountFor(customer);
        String text = discount > 0 ? "" + discount + "% off" : "no discount";
        System.out.println(customer + ": " + text);
    }
}

The same service and the same controller, first on top of memory, then on top of SQLite:

OrderRepository memory = new InMemoryOrderRepository();
fill(memory);
new OrderController(new LoyaltyService(memory)).show("Maria");

try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
    OrderRepository database = new SqliteOrderRepository(conn);
    fill(database);
    new OrderController(new LoyaltyService(database)).show("Maria");
}

Maria: 10% off
Maria: 10% off

class LoyaltyService {
    private final OrderRepository orders;

    LoyaltyService(OrderRepository orders) {
        this.orders = orders;
    }

    int discountFor(String customer) {
        return orders.findByCustomer(customer).size() >= 3 ? 10 : 0;
    }
}

class OrderController {
    private final LoyaltyService loyalty;

    OrderController(LoyaltyService loyalty) {
        this.loyalty = loyalty;
    }

    void show(String customer) {
        int discount = loyalty.discountFor(customer);
        String text = discount > 0 ? "" + discount + "% off" : "no discount";
        System.out.println(customer + ": " + text);
    }
}

OrderRepository memory = new InMemoryOrderRepository();
fill(memory);
new OrderController(new LoyaltyService(memory)).show("Maria");

try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
    OrderRepository database = new SqliteOrderRepository(conn);
    fill(database);
    new OrderController(new LoyaltyService(database)).show("Maria");
}

Step by step, in words

A repository in memory, filled with the orders. The service gets it without knowing what it is.
The controller gets the request for Maria. It doesn't ask the database: it calls the service.
The service needs the orders of the customer and asks the repository for them.
findByCustomer on OrderRepository. The repository in memory answers: it walks through its dictionary.
Three orders of Maria come up. The rule is here and only here: 3 ≥ 3, so 10%.
The controller only shows the answer: “Maria: 10% off”.
The swap: SQLite takes the place of memory. It is the same OrderRepository, so the service and the controller are not touched.
The same call. Only the object under the service is different.
findByCustomer now sends SELECT … WHERE customer = ? to the orders table.
Three rows again, 10% again. The service never noticed that the source changed.
The same line, this time from a database.
Controller → service → repository → data. Only the bottom layer changed, and the rule stayed in one place.

Only the bottom layer changed. The service and the controller are the same classes, without a single changed line, and Maria now gets the discount both on the page and at the checkout, because there is one rule.

See it

The real database behind the same interface

Here is the repository in SQLite. Every method is one query with parameters (?), never text glued into the SQL. Why that matters is in the lesson SQL injection and parameters.

class SqliteOrderRepository implements OrderRepository {
    private final Connection conn;

    SqliteOrderRepository(Connection conn) throws SQLException {
        this.conn = conn;
        try (Statement st = conn.createStatement()) {
            st.executeUpdate("CREATE TABLE IF NOT EXISTS orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, total REAL NOT NULL)");
        }
    }

    public Order findById(int id) {
        try (PreparedStatement st = conn.prepareStatement("SELECT id, customer, total FROM orders WHERE id = ?")) {
            st.setInt(1, id);
            try (ResultSet rs = st.executeQuery()) {
                return rs.next() ? read(rs) : null;
            }
        } catch (SQLException e) {
            throw new IllegalStateException(e);
        }
    }

    public List<Order> findByCustomer(String customer) {
        try (PreparedStatement st = conn.prepareStatement("SELECT id, customer, total FROM orders WHERE customer = ? ORDER BY id")) {
            st.setString(1, customer);
            List<Order> result = new ArrayList<>();
            try (ResultSet rs = st.executeQuery()) {
                while (rs.next()) {
                    result.add(read(rs));
                }
            }
            return result;
        } catch (SQLException e) {
            throw new IllegalStateException(e);
        }
    }

    public void save(Order order) {
        try (PreparedStatement st = conn.prepareStatement(
                "INSERT INTO orders (id, customer, total) VALUES (?, ?, ?) ON CONFLICT (id) DO UPDATE SET customer = excluded.customer, total = excluded.total")) {
            st.setInt(1, order.id());
            st.setString(2, order.customer());
            st.setDouble(3, order.total());
            st.executeUpdate();
        } catch (SQLException e) {
            throw new IllegalStateException(e);
        }
    }

    private static Order read(ResultSet rs) throws SQLException {
        return new Order(rs.getInt("id"), rs.getString("customer"), rs.getDouble("total"));
    }
}

The interface methods don't declare SQLException, so the database error is wrapped in an IllegalStateException. That way the service doesn't depend on java.sql, and the error still reaches whoever can show it.

This is where the SQL for orders lives. Here you can fix a slow query, add an index or swap SQLite for PostgreSQL, and the service doesn't notice. If the repository becomes an ORM, watch out for a query per row: the lesson ORM and the N+1 problem shows what it looks like.

See it

The rule without a database

Since the service accepts any repository, the rule is checked with the repository in memory: no file, no server, in milliseconds.

InMemoryOrderRepository orders = new InMemoryOrderRepository();
LoyaltyService loyalty = new LoyaltyService(orders);
orders.save(new Order(1, "Maria", 80.00));
orders.save(new Order(2, "Maria", 40.00));
System.out.println("2 orders: " + loyalty.discountFor("Maria") + "%");
orders.save(new Order(3, "Maria", 12.90));
System.out.println("3 orders: " + loyalty.discountFor("Maria") + "%");

2 orders: 0%
3 orders: 10%

Two orders are not enough, the third gives 10%. That is exactly the border where the page and the checkout disagreed. This is also what a test of the rule looks like: you fill the repository in memory and check what the service says.

Try it

Predict the output

Order number 1 is saved twice, with a different total. What does the program print?

OrderRepository orders = new InMemoryOrderRepository();
orders.save(new Order(1, "Maria", 80.00));
orders.save(new Order(1, "Maria", 50.00));
System.out.println(orders.findByCustomer("Maria").size());
System.out.printf("€%.2f%n", orders.findById(1).total());

Answer

2 / €80.00

2 / €50.00

1 / €80.00

1 / €50.00

Try it

Find the bug

Someone decides that searching by customer is slow and writes a repository that keeps the orders by customer right away:

class FastOrderRepository implements OrderRepository {
    private final Map<String, List<Order>> byCustomer = new HashMap<>();

    public Order findById(int id) {
        for (List<Order> orders : byCustomer.values()) {
            for (Order order : orders) {
                if (order.id() == id) {
                    return order;
                }
            }
        }
        return null;
    }

    public List<Order> findByCustomer(String customer) {
        return byCustomer.getOrDefault(customer, new ArrayList<>());
    }

    public void save(Order order) {
        byCustomer.values().forEach(orders -> orders.removeIf(o -> o.id() == order.id()));
        byCustomer.computeIfAbsent(order.customer(), c -> new ArrayList<>()).add(order);
    }
}

A new method appears in the service: "will there be a discount if they buy this one too?". Then the page asks how many orders Ivan has:

class LoyaltyService {
    private final OrderRepository orders;

    LoyaltyService(OrderRepository orders) {
        this.orders = orders;
    }

    int discountFor(String customer) {
        return orders.findByCustomer(customer).size() >= 3 ? 10 : 0;
    }

    int discountIfBuys(String customer, Order cart) {
        List<Order> orders = this.orders.findByCustomer(customer);
        orders.add(cart);
        return orders.size() >= 3 ? 10 : 0;
    }
}

OrderRepository orders = new FastOrderRepository();
fill(orders);
LoyaltyService loyalty = new LoyaltyService(orders);
System.out.println("If Ivan buys one more: " + loyalty.discountIfBuys("Ivan", new Order(5, "Ivan", 30.00)) + "%");
System.out.println("Ivan's orders: " + orders.findByCustomer("Ivan").size());

If Ivan buys one more: 0%
Ivan's orders: 2

Ivan has one order, and the repository says two. Nobody called save. On which line of the repository is the bug?

Pick the line with the bug.

The idea

When you don't need it

Repository adds an interface and at least one class. It isn't worth it when:

the program is a small script with one query. The layers will be longer than the work itself;
you use an ORM that already gives a repository (Spring Data). Your own interface that only forwards to it line by line is one more file with nothing new;
the repository grows a method for every page: findByCustomerAndStatusOrderByTotal and twenty more like it. Then the queries are scattered again, just with longer names.

It pays off when the rules must be checked without a database, when the source can change (a database, a file, another service) or when the same queries are used from many places.

Exercise

Deleting from the repository

Solve the task in your editor.

Delete. Add delete(id) to the contract and to both repositories. In SQLite it is DELETE FROM orders WHERE id = ?. Show Maria's discount, delete order 4 and show it again, once with memory and once with the database.

Maria: 10% off
Maria: no discount
Maria: 10% off
Maria: no discount

More tasks

A rule by total. The shop changes the rule: 10% after €150 spent or more. Which class do you touch? Check it with the repository in memory, without a database.
A third repository. Write a repository that keeps the orders in a file, one per line. The service and the controller must stay unchanged.
A copy when searching. Fix the repository from "Find the bug" and add a check: after discountIfBuys Ivan still has 1 order.

Test

Check yourself

1. Where does the rule "3 orders or more give 10% off" live?In the controller, which shows the answerIn the service, which gets the repository from outsideIn the repository, next to the query

2. Why can the rule be checked without a database?It can’t: the rule always needs the real databaseBecause SQLite is fastBecause the service gets the repository as an interface, and the repository in memory is enough

3. The repository returns the list it keeps inside, and the caller adds to it. What happens?The added order shows up in the repository, without anyone calling saveNothing: the list is a copyThe program doesn’t compile

4. The project uses an ORM that already gives a repository for every table. Is it worth adding your own interface with the same methods on top?Yes, there is always room for one more layerNo, ORMs forbid interfacesUsually not: an interface that only forwards to the ORM adds code without adding anything

Repository

Cookies