Skip to content
Ameen Abozaid
Englishالعربية
PublishedBEG-055-L06

Demo: building the BookRepository with JDBC

  • Lesson 6 of 7
  • 10 min read
  • Beginner
On This Lesson 11 topics

Lesson Overview

Time to put the pieces together. In this lesson you build the data-access class for the lab: a BookRepository interface and a JdbcBookRepository that implements it with save, findById, findByTitle, and delete. A class like this, which hides all SQL for one kind of thing behind plain Java methods, is often called a repository or a DAO (data access object).

You will also make one planned mistake on purpose: forgetting to close resources. You will see what it does to the database, and then fix it. Finally you will test the repository against a separate test database.

This lesson uses everything so far: the Db helper (lesson 2), mapping and Optional (lesson 3), parameters only (lesson 4), and JUnit tests from Maven (BEG-054). It prepares the lab, BEG-055-LAB1.

Learning Objectives

By the end of this lesson you will be able to:

  • Define a repository interface that hides JDBC from the rest of the program.
  • Implement save with INSERT and read the generated id back.
  • Implement findById, findByTitle, and delete with parameters only.
  • Show what happens when resources are not closed, and fix it with try-with-resources.
  • Write integration tests against a separate test database.

Written Explanation

The interface: what, not how

java
public interface BookRepository {
    Book save(Book book) throws SQLException;
    Optional<Book> findById(int id) throws SQLException;
    List<Book> findByTitle(String title) throws SQLException;
    boolean delete(int id) throws SQLException;
}

The rest of the application depends on this interface only. It does not know about Connection or SQL. You met the idea in BEG-024: programming to an interface means you can later replace the JDBC class with another implementation (or a fake in a test) without touching the callers. findById returns Optional<Book> because a book may not exist; delete returns a boolean saying whether a row was removed.

SQLException is checked, so the interface declares it. (Many teams later wrap it in their own exception so that callers do not depend on JDBC; for now we keep it simple.)

Where the connection comes from

For this lesson each method opens its own connection with Db.open() and closes it at the end. That is simple and correct, though slow if called thousands of times. Connection pools, which reuse connections, are a later topic (JNR-025 builds on this module). The important part is that every method closes what it opens.

save: INSERT and the generated key

The books table has id SERIAL PRIMARY KEY, so the database invents the id. We pass Statement.RETURN_GENERATED_KEYS so JDBC lets us read it back:

java
String sql = "INSERT INTO books (title, isbn, copies) VALUES (?, ?, ?)";
try (Connection conn = Db.open();
     PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
    ps.setString(1, book.title());
    ps.setString(2, book.isbn());
    ps.setInt(3, book.copies());
    ps.executeUpdate();
    try (ResultSet keys = ps.getGeneratedKeys()) {
        keys.next();
        return new Book(keys.getInt("id"), book.title(), book.isbn(), book.copies());
    }
}

The caller passes new Book(0, "Clean Code", "111", 2); the 0 just means "no id yet". save returns a new Book with the real id. With PostgreSQL's driver the generated-keys result can be read by column name id; other drivers may differ, which is one reason to keep this code inside the repository.

The read and delete methods

findById, findByTitle, and delete follow lesson 3's shapes. The whole implementation:

java
public class JdbcBookRepository implements BookRepository {
    private static final String COLUMNS = "id, title, isbn, copies";

    private static Book map(ResultSet rs) throws SQLException {
        return new Book(rs.getInt("id"), rs.getString("title"),
                rs.getString("isbn"), rs.getInt("copies"));
    }

    @Override
    public Optional<Book> findById(int id) throws SQLException {
        String sql = "SELECT " + COLUMNS + " FROM books WHERE id = ?";
        try (Connection conn = Db.open();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? Optional.of(map(rs)) : Optional.empty();
            }
        }
    }

    @Override
    public List<Book> findByTitle(String title) throws SQLException {
        String sql = "SELECT " + COLUMNS + " FROM books WHERE title = ? ORDER BY id";
        List<Book> books = new ArrayList<>();
        try (Connection conn = Db.open();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, title);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    books.add(map(rs));
                }
            }
        }
        return books;
    }

    @Override
    public boolean delete(int id) throws SQLException {
        String sql = "DELETE FROM books WHERE id = ?";
        try (Connection conn = Db.open();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            return ps.executeUpdate() == 1;
        }
    }
    // save(...) as above
}

Note the one concatenation: "SELECT " + COLUMNS + .... COLUMNS is a constant written by us, not user input, and the values still go through ?. That is the allowed kind of concatenation from lesson 4.

A test database, never your real one

An integration test talks to a real database. The tests below delete every row in books before each test, so they must run against a separate database. We created library_test with the same tables as library and pointed DB_URL at it when running tests:

text
DB_URL=jdbc:postgresql://localhost:5432/library_test

(The port and credentials in your setup may differ; ours used a scratch server.) This also shows why the connection comes from configuration: the same code runs against different databases without a change.

Examples

Example 1: the planned mistake, leaking resources (simple)

Here is the version a rushed beginner writes. It looks fine and passes a quick manual check:

java
static int leaky() throws SQLException {
    Connection conn = Db.open();
    PreparedStatement ps = conn.prepareStatement(
            "SELECT id, title, isbn, copies FROM books WHERE title = ?");
    ps.setString(1, "x");
    ResultSet rs = ps.executeQuery();
    int n = 0;
    while (rs.next()) n++;
    return n;   // nothing is closed
}

To see the damage, we called it 20 times and then counted the sessions of our user in pg_stat_activity (a PostgreSQL view of current connections) using a separate, correctly closed connection:

text
sessions before: 1
sessions after 20 leaky calls: 21
sessions after 20 repository calls (closed): 1

(The "1" is the counting connection itself.) Twenty leaky calls left twenty sessions open on the server, while twenty calls to the repository left none. The same server has a limit: show max_connections returned 100 on ours, and a program that leaks steadily will eventually be refused. The fix is already in the repository above: every resource is declared in a try-with-resources header, so statement and result set (and here the connection too) are closed in reverse order, even when an exception occurs.

Example 2: integration tests (practical)

java
class JdbcBookRepositoryTest {
    private final BookRepository repo = new JdbcBookRepository();

    @BeforeEach
    void cleanTable() throws SQLException {
        try (Connection c = Db.open(); Statement st = c.createStatement()) {
            st.executeUpdate("DELETE FROM loans");
            st.executeUpdate("DELETE FROM books");
        }
    }

    @Test
    void saveThenFindById() throws SQLException {
        Book saved = repo.save(new Book(0, "Clean Code", "111", 2));
        assertTrue(saved.id() > 0);
        assertEquals(saved, repo.findById(saved.id()).orElseThrow());
    }

    @Test
    void findByIdReturnsEmptyWhenMissing() throws SQLException {
        assertTrue(repo.findById(-1).isEmpty());
    }

    @Test
    void findByTitleReturnsMatchesOnly() throws SQLException {
        repo.save(new Book(0, "Clean Code", "111", 2));
        repo.save(new Book(0, "Effective Java", "222", 1));
        List<Book> found = repo.findByTitle("Clean Code");
        assertEquals(1, found.size());
        assertEquals("111", found.get(0).isbn());
    }

    @Test
    void deleteRemovesTheBook() throws SQLException {
        Book saved = repo.save(new Book(0, "Clean Code", "111", 2));
        assertTrue(repo.delete(saved.id()));
        assertFalse(repo.delete(saved.id()));
        assertTrue(repo.findById(saved.id()).isEmpty());
    }
}

(Statement with a fixed string is fine here: there is no user input.) Running mvn test with the environment variables set to the test database, the version with these four tests plus one more from Example 3 reported:

text
[INFO] Tests run: 5, Failures: 0, Errors: 0, Skipped: 0
[INFO] BUILD SUCCESS

The test needs the JUnit dependency (org.junit.jupiter:junit-jupiter, test scope) from BEG-054; the tests use JUnit 5.12.2 and Maven Surefire 3.5.4 in our run. Check current versions yourself.

Example 3: the injection test (realistic)

A good habit is to keep the attack from lesson 4 as a permanent regression test:

java
@Test
void injectionStringFindsNothing() throws SQLException {
    repo.save(new Book(0, "Clean Code", "111", 2));
    assertTrue(repo.findByTitle("x' OR '1'='1").isEmpty());
}

If a future change ever concatenates the title into the SQL, this test fails and the build tells you before an attacker does. In our run it passed, as one of the five tests above.

Backend Insight

The repository pattern is the standard way backends isolate the database. Services call bookRepository.findById(5); only the repository knows about SQL. When you reach Spring Data, you will write only the interface and the framework generates the implementation, but the interface you design here is the same shape, and the SQL you now understand is what it generates.

Integration tests against a real database catch problems unit tests cannot: a wrong column name, a missing constraint, a typo in the SQL. Keep them in a separate database so a test run can never delete real data, and make each test start from a known state (as @BeforeEach does).

Common Mistakes

Mistake 1: Opening a connection and not closing it

The leak in Example 1. Every Connection, PreparedStatement, and ResultSet goes in a try-with-resources header.

Mistake 2: Running delete-everything tests on the real database

Point DB_URL at library_test for tests. Double-check the name before you run.

Mistake 3: Returning null from findById

Use Optional. Callers cannot forget the "not found" case.

Mistake 4: Letting SQL leak out of the repository

If controllers or services build SQL, you lose the single place to review for injection and to change the schema.

Mistake 5: Ignoring the generated key

Returning the input Book with id still 0 from save makes later calls like findById(book.id()) fail. Read getGeneratedKeys() and return the new object.

Exercises

Level A: Foundation

  1. Why does BookRepository not mention Connection or SQL?
  2. What does delete return, and how does it know?
  3. Which line of Example 1 should have been inside a try-with-resources header, and why?

Level B: Application

  1. Build the interface and the JdbcBookRepository in your project and run the five tests against a test database. Copy the Maven summary line.
  2. Add Optional<Book> findByIsbn(String isbn) with a test for an existing and a missing ISBN.
  3. Add List<Book> findAll() ordered by id and a test that saves two books and checks the order.

Level C: Challenge

  1. Write a test that saves two books with the same ISBN and asserts that the second save throws an SQLException whose getSQLState() is 23505. What does that code tell you, and which table rule caused it?
  2. JdbcBookRepository opens one connection per method, so two repository calls cannot share a transaction. Describe a design change (not full code) that lets a caller run save for two books in one transaction, using what you learned in lesson 5.
Show solutions

A1. The interface describes what the application can do with books. How it is stored (JDBC, another database, an in-memory fake) is the implementation's business, so callers stay independent and testable.

A2. It returns true when executeUpdate() reports exactly 1 changed row, and false (0 rows) when no book had that id. The test above deletes twice and expects true, then false.

A3. Connection conn = Db.open();, the PreparedStatement, and the ResultSet all should be: without it, an exception or an early return skips any close calls (here there were none at all). Declaring all three in try headers closes them automatically.

B1. Expected: Tests run: 5, Failures: 0, Errors: 0, Skipped: 0 and BUILD SUCCESS, provided the test database has the tables.

B2. Same shape as findById with WHERE isbn = ? and ps.setString(1, isbn). Test: save one book, assert findByIsbn("111") is present with the right title, and findByIsbn("nope") is empty.

B3. SELECT id, title, isbn, copies FROM books ORDER BY id with a while (rs.next()) loop. Test: save "A" then "B", assert titles are ["A", "B"] in that order.

C1. Our run printed 23505 ERROR: duplicate key value violates unique constraint "books_isbn_key". 23505 is PostgreSQL's code for a unique violation; the UNIQUE constraint on isbn rejected the second row. A test: SQLException e = assertThrows(SQLException.class, () -> repo.save(second)); assertEquals("23505", e.getSQLState());.

C2. Let the caller supply the connection: add overloads such as save(Connection conn, Book book) (or give the repository a connection in its constructor for the length of one unit of work). A service method then opens one connection, calls setAutoCommit(false), calls save(conn, b1) and save(conn, b2), commits, rolls back on failure, and restores auto-commit, as in lesson 5. Frameworks automate this later.

Mini Challenge

Add a fifth method boolean updateCopies(int id, int copies) to the interface and implementation, using UPDATE books SET copies = ? WHERE id = ?. Write two tests: one that changes the copies of a saved book and reads it back, and one that tries to set -1 and expects an SQLException (the CHECK constraint from BEG-051). Then run the whole suite.

Check your work: no concatenated values, all resources in try headers, and every test starts from an empty table.

Summary & Next Lesson

Key takeaways

  • A repository interface hides JDBC; the implementation holds every SQL string for books.
  • save uses RETURN_GENERATED_KEYS to read the new id and returns a new Book.
  • Lookups return Optional or a list; delete returns whether a row was removed.
  • Unclosed resources leave sessions open on the server (20 leaky calls left 20 sessions in our run); try-with-resources prevents it.
  • Run integration tests against a separate test database and keep an injection test as a regression guard.

Next lesson

The next lesson, "Demo: SQL injection, then the PreparedStatement fix", repeats the attack and defence on the repository in a step-by-step demonstration, including the tempting but wrong "just remove the quotes" patch.

Self-check

  1. Why does the application depend on the interface and not on the JDBC class?
  2. What does RETURN_GENERATED_KEYS allow?
  3. What did 20 leaky calls do in our run?
  4. Why must tests run against a separate database?
  5. What is 23505?
Show answers
  1. So the storage can change or be faked in tests without changing the callers.
  2. Reading the database-generated id after an INSERT with getGeneratedKeys().
  3. They left 20 sessions open on the server.
  4. The tests delete all rows, which would destroy real data.
  5. PostgreSQL's SQLState for a unique constraint violation.

References

  • Oracle, PreparedStatement (Java SE 21 API). It supports 1-based parameters and the return value of executeUpdate.
  • Oracle, JDBC Basics. It supports the overview of connections, statements, and result sets, and the try-with-resources style.
  • All results shown (session counts, Tests run: 5, the 23505 message, max_connections of 100) come from real runs on PostgreSQL 18 with driver 42.7.8, JUnit 5.12.2, and Maven Surefire 3.5.4. I did not verify getGeneratedKeys or RETURN_GENERATED_KEYS behaviour in a documentation page; it was verified only by running it with this driver, and the lesson says other drivers may differ. The pg_stat_activity view was used as observed, not cross-checked in the PostgreSQL manual.

Practice

Apply this lesson with the module's lab, exercises and quiz.

Lab and exercises Quiz

Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.

Continue to next lesson

Type to search levels, phases, modules and lessons.