Building a CRUD App with JDBC

Executive Summary

This CRUD app with JDBC is a book catalog. It has a Book record (isbn, title, author, year) and a books table in H2, created at startup with a text-block schema. A BookRepository’s five public methods then cover CRUD. First, save uses MERGE for create-or-update, and findById and findAll handle reads. Next, updateStock is a conditional update whose count matters, and delete’s count is the not-found signal. Every method borrows from a small HikariCP pool inside try-with-resources. It binds parameters only through PreparedStatement placeholders, per the fundamentals article, and maps rows to records inside the loop. The one multi-statement operation is checkout, which inserts a loan row and decrements availability. It runs the transaction article’s try-rollback pattern, so a failure between statements leaves nothing behind.

The JUnit test tier creates a fresh in-memory database per test class with a tiny pool. Then @BeforeEach resets the schema. The tests verify the round trip, the not-found Optional, the update count, and the transactional checkout. Finally, the checklist verifies each behavior. The extensions point toward extracting a repository interface and the migration tooling that production schema work uses.

Designing the CRUD App with JDBC: Three Files and a Table

It is small enough to hold in your head, but shaped exactly like a production service’s data layer:

  Main.java                    starts the pool, runs a demo, closes
     |
  BookRepository.java          ALL the SQL lives here, and nowhere else
     |   borrows from
  HikariDataSource             the pool, created once, closed at shutdown
     v
  H2: table BOOKS              rows in, records out

  BookTest.java (src/test)     the same repository, over a fresh in-memory H2

One layering rule makes this maintainable. The domain type, Book, knows nothing about SQL, and the repository knows nothing about business rules. Main wires them, while the tests exercise the repository exactly as production does, through the same public methods.

The Domain and the Schema

In short, there are two declarations, one per side of the mapping:

// the domain type: a record, immutable, no SQL anywhere near it
public record Book(String isbn, String title, String author, int year, int available) {}

// the schema, created at startup via text blocks:
public class Schema {
    public static final String BOOKS = """
        CREATE TABLE books (
            isbn      VARCHAR(20) PRIMARY KEY,
            title     VARCHAR(200) NOT NULL,
            author    VARCHAR(100) NOT NULL,
            year      INT          NOT NULL,
            available INT          NOT NULL DEFAULT 0
        )
        """;
    public static final String LOANS = """
        CREATE TABLE loans (
            id        BIGINT AUTO_INCREMENT PRIMARY KEY,
            isbn      VARCHAR(20)  NOT NULL,
            member_id VARCHAR(50)  NOT NULL,
            loaned_at TIMESTAMP    NOT NULL
        )
        """;
}

H2 in file mode, jdbc:h2:file:./data/catalog, keeps data between runs. By contrast, mem mode, jdbc:h2:mem:catalog;DB_CLOSE_DELAY=-1, lives only as long as the JVM. That is exactly what the tests want, and the H2 documentation covers both modes. In production, a migration tool owns the schema rather than a startup string, and the checklist returns to that point. Still, the startup DDL keeps this article’s program self-contained.

The Repository: Create and Read

The repository takes the pool and does its borrowing per method, the borrow-at-the-operation rule:

public class BookRepository {
    private final DataSource pool;

    public BookRepository(DataSource pool) {
        this.pool = pool;                       // injected, not constructed: mockable,
    }                                            // poolable, and testable by design

    // CREATE (or update): MERGE = upsert, keyed on the primary key
    public void save(Book book) throws SQLException {
        try (var conn = pool.getConnection();
             var ps = conn.prepareStatement("""
                 MERGE INTO books KEY(isbn) VALUES (?, ?, ?, ?, ?)
                 """)) {
            ps.setString(1, book.isbn());
            ps.setString(2, book.title());
            ps.setString(3, book.author());
            ps.setInt(4, book.year());
            ps.setInt(5, book.available());
            ps.executeUpdate();
        }
    }

    // READ one: Optional tells the not-found story without exceptions
    public Optional<Book> findById(String isbn) throws SQLException {
        try (var conn = pool.getConnection();
             var ps = conn.prepareStatement(
                     "SELECT isbn, title, author, year, available FROM books WHERE isbn = ?")) {
            ps.setString(1, isbn);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? Optional.of(map(rs)) : Optional.empty();
            }
        }
    }

    // READ many: explicit columns, mapped to records inside the loop
    public List<Book> findAll() throws SQLException {
        try (var conn = pool.getConnection();
             var ps = conn.prepareStatement(
                     "SELECT isbn, title, author, year, available FROM books ORDER BY title");
             ResultSet rs = ps.executeQuery()) {
            var books = new ArrayList<Book>();
            while (rs.next()) {
                books.add(map(rs));
            }
            return List.copyOf(books);
        }
    }

    // the row mapper: one place translates rows to records
    private Book map(ResultSet rs) throws SQLException {
        return new Book(rs.getString("isbn"), rs.getString("title"),
                        rs.getString("author"), rs.getInt("year"), rs.getInt("available"));
    }
}

Two decisions especially deserve emphasis. First, Optional as the findById return type makes not-found a value, not an exception. That matches how callers treat a missing book: as ordinary. Second, the single map method is the one line that would change if a column moved. All five reads share it, and the tutorial’s separation of SQL from mapping holds throughout the class.

Update and Delete: Counts Are Information

// UPDATE with a guard: only checkout when a copy is available
public boolean checkout(String isbn) throws SQLException {
    try (var conn = pool.getConnection();
         var ps = conn.prepareStatement(
                 "UPDATE books SET available = available - 1 WHERE isbn = ? AND available > 0")) {
        ps.setString(1, isbn);
        return ps.executeUpdate() == 1;     // the count IS the business answer:
    }                                        // 1 = checked out, 0 = gone or empty
}

// DELETE: the count is the verdict
public boolean delete(String isbn) throws SQLException {
    try (var conn = pool.getConnection();
         var ps = conn.prepareStatement("DELETE FROM books WHERE isbn = ?")) {
        ps.setString(1, isbn);
        return ps.executeUpdate() == 1;     // 1 = deleted, 0 = was never there
    }
}

Read the checkout guard carefully, because this pattern removes a whole class of race. The WHERE available > 0 condition makes the database the atomic arbiter. As a result, two simultaneous checkouts cannot both succeed at availability zero, and no Java-side lock is required. The boolean return follows from the count. In other words, executeUpdate’s number is not noise to ignore; it is the method’s answer. The fundamentals article’s rule earns its keep in every conditional update you will ever write.

The Transactional Operation: checkout With a Loan Record

A real checkout is two statements: decrement the book and insert the loan. The pooling article’s try-rollback pattern makes them atomic:

public void checkoutWithLoan(String isbn, String memberId) throws SQLException {
    try (var conn = pool.getConnection()) {
        conn.setAutoCommit(false);                       // one transaction begins
        try (var book = conn.prepareStatement(
                     "UPDATE books SET available = available - 1 WHERE isbn = ? AND available > 0");
             var loan = conn.prepareStatement(
                     "INSERT INTO loans(isbn, member_id, loaned_at) VALUES (?, ?, CURRENT_TIMESTAMP)")) {

            book.setString(1, isbn);
            int taken = book.executeUpdate();

            if (taken == 0) {                            // guard INSIDE the transaction
                conn.rollback();                         // nothing happened, honestly
                throw new BookUnavailableException(isbn);
            }

            loan.setString(1, isbn);
            loan.setString(2, memberId);
            loan.executeUpdate();

            conn.commit();                               // both statements: permanent
        } catch (SQLException e) {
            conn.rollback();                            // any failure: both vanish
            throw new CheckoutFailedException(isbn, e);
        }
    }
}

This is the pooling article’s transfer in the catalog’s vocabulary. The guard’s failure rolls back before anything is visible. Likewise, the loan insert’s failure rolls back the decrement, and the commit is the single instant where the operation becomes true. The exceptions carry context, the isbn and the cause, per the custom-exceptions discipline. Meanwhile, resetting the connection’s autocommit on return is the pool’s business, and HikariCP does it.

Testing the Repository Over H2

The test tier runs the real repository against a fresh in-memory database, with no mocks, because the database is the thing being tested. The good tests article’s pyramid places this in the integration band:

class BookRepositoryTest {
    private BookRepository repository;

    @BeforeEach
    void freshDatabase() throws Exception {
        var config = new HikariConfig();
        config.setJdbcUrl("jdbc:h2:mem:test;DB_CLOSE_DELAY=-1");
        config.setMaximumPoolSize(2);                    // tiny: tests need no more
        var pool = new HikariDataSource(config);

        try (var conn = pool.getConnection();
             var stmt = conn.createStatement()) {
            stmt.execute(Schema.BOOKS);                  // fresh schema per test class
        }
        repository = new BookRepository(pool);
    }

    @Test
    void saveAndFindRoundTrips() throws Exception {
        var book = new Book("978-0134685991", "Effective Java", "Bloch", 2018, 3);

        repository.save(book);

        assertEquals(book, repository.findById("978-0134685991").orElseThrow());
    }

    @Test
    void missingBookIsEmptyNotException() throws Exception {
        assertTrue(repository.findById("000-0000000000").isEmpty());
    }

    @Test
    void checkoutOfLastCopySucceedsThenFails() throws Exception {
        repository.save(new Book("978-0134685991", "Effective Java", "Bloch", 2018, 1));

        assertTrue(repository.checkout("978-0134685991"));
        assertFalse(repository.checkout("978-0134685991"));   // guard: no negative stock
    }

    @Test
    void deleteRemovesAndReports() throws Exception {
        repository.save(new Book("978-0134685991", "Effective Java", "Bloch", 2018, 1));

        assertTrue(repository.delete("978-0134685991"));
        assertFalse(repository.delete("978-0134685991"));     // second delete: nothing there
    }
}

Per the JUnit guide, each test builds its own fixture through @BeforeEach. The assertions cover the happy path and both not-found stories. In addition, the checkout test exercises the database’s atomic guard, which a mock could never verify. In other words, this is the integration tier doing its one job: testing the seam, the SQL, for real.

How Real Systems Do This

This CRUD app with JDBC, with five methods, one pool, one row mapper, and one transactional operation, is the data layer of most Java services in miniature. Indeed, plenty run this shape at scale, with migrations instead of startup DDL and PostgreSQL instead of H2. Production adds the conventions the checklist flags. First, the schema lives under a migration tool like Flyway or Liquibase, so every environment applies the same ordered changes. Second, the pool is named and monitored. Third, the repository’s interface is extracted so services depend on the contract, which is exactly the next article’s formal subject.

The lesson I would attach to this exact build comes from a schema-drift bug I caused early.

Two services shared a database, and each carried its own copy of the CREATE TABLE statement in its startup code. That is the shape this article uses for a self-contained program. The two copies drifted silently over three months, until one added a column the other’s INSERT did not know about. The failure then surfaced as a midnight error. It took hours to trace to a mismatch nobody had listed anywhere. The fix was a single-sourced schema: one migration chain that both services consumed. It also taught me the rule this article passes to you. Startup DDL is for self-contained programs and tests. However, the moment a second consumer exists, the schema needs one owner and a history. Data outlives code, so schema without history is data with amnesia.

Your Build Checklist

1. The schema creates both tables at startup. Rerunning the program against file-mode H2 does not fail on the second run, because it guards with CREATE TABLE IF NOT EXISTS.
2. save then findById round-trips a Book exactly, record equality and all five columns.
3. findAll returns an empty list, not an error, on an empty table, and the books arrive ordered by title.
4. checkout returns true once and false at zero availability, and availability never goes negative, even under two rapid calls.
5. checkoutWithLoan inserts the loan row and decrements the book in one transaction: break the insert deliberately and confirm the decrement rolled back.
6. delete returns true then false, and findById after the successful delete is empty.
7. The test class runs green in mvn test, in seconds, with no external database running.
8. Stretch: extract a BookRepository interface and add a search by title prefix. Then point the schema at Flyway migrations in a migrations folder, as production does.

When NOT to Use This

• Do not let Connection or PreparedStatement escape the repository: callers see Book and Optional, and SQL is an implementation the repository owns.
• Do not build SQL strings in service code. The day business logic concatenates a WHERE clause, the injection article’s review question applies. The repository’s ? bindings are the answer.
• Do not use startup DDL once a second service touches the schema. Use migrations, one owner, and ordered history, or else the drift story above arrives on your schedule too.
• Do not mock the repository in its own tests. The H2 tier tests the real SQL, whereas mocking it verifies only your imagination.
• Do not grow the repository beyond its one job: business rules belong above it, and the pattern article draws the boundary formally.

Common Mistakes

• Ignoring executeUpdate counts in update and delete: the not-found case becomes indistinguishable from success, and callers act on fictions.
• Exceptions for not-found: a missing book is a value, Optional, and the exceptions stay for genuine failures, per the custom-exceptions article.
• Sharing one borrowed connection across repository methods. Connections are per-operation, so shared state recreates the concurrency course’s races on the database side.
• Testing only the happy path. Repository bugs live in the empty-table, second-delete, and zero-availability cases, so this article’s tests cover all three.
• Inline schema in a multi-consumer database: the drift story, single-source the schema or inherit its amnesia.
• Forgetting rollback in the transactional path. Without rollback, the guard’s throw leaves the transaction’s fate to the pool’s reset policy. Of course, “usually fine” is not a data guarantee.
• Returning a live ArrayList from findAll. List.copyOf is one line, and because the output is immutable, no caller can corrupt the repository’s answers.

Key Takeaways

• A CRUD app with JDBC assembles cleanly from the last two articles. It needs a record per row, a repository that owns all SQL, a pool borrowed per operation, and counts as business answers.
• Optional makes not-found a value, and update and delete return their executeUpdate verdicts as booleans.
• Guards in the WHERE clause make the database the atomic arbiter: available > 0 prevents double-checkout without a Java lock.
• The transactional operation is the try-rollback pattern verbatim: setAutoCommit(false), guard, work, commit, rollback in the catch.
• H2 in-memory gives the integration tier real SQL at test speed: no mocks, fresh schema per fixture, seconds per run.
• The layering is the design. Domain records know no SQL, the repository knows no business rules, and tests exercise the real seam.
• Schema needs one owner the moment a second consumer appears: migrations over startup DDL, history over amnesia.

FAQ

What is CRUD in Java?

Create, read, update, delete: the four operations a data-backed service performs. This article maps them to save, findById and findAll, conditional updates, and delete. All of them go through a repository that owns the SQL and returns domain records and Optional values.

How do I write a JDBC repository?

Inject a DataSource, and borrow a connection per method inside try-with-resources. Then bind every parameter through PreparedStatement placeholders and map rows to records in one mapper method. Also treat executeUpdate counts as business answers. Multi-statement operations add the try-rollback transaction pattern.

How do I handle not-found in JDBC?

Return Optional from single-row reads, and return booleans or counts from updates and deletes. After all, a missing row is a value, not an exception. Reserve exceptions for genuine failures, carrying context and the cause per the custom-exceptions discipline.

How do I test JDBC code?

Run the real repository against an in-memory database like H2. Use a fresh schema per fixture and a tiny pool. Then test the round trip, both not-found stories, and the conditional guards. The database is the seam under test, so mocking it verifies nothing.

What database should a Java CRUD app use?

Start with H2 for examples, tests, and small tools, zero installation and one dependency. PostgreSQL is the standard production target this same code runs against unchanged, because the repository speaks JDBC to both identically.

Conclusion

You have built the layer every production service stands on. It has a repository with honest CRUD, an atomic business operation, and real integration tests. Moreover, its shape scales to PostgreSQL and migrations without redesign. As a result, the data layer of the course is now working software, not just a set of APIs.

The next article introduces the mapping layer that sits on top of JDBC: JPA and Hibernate. There, the object-relational mismatch gets automated, and entities replace hand-written row mappers. Similarly, the EntityManager replaces your prepared statements, and the article states the honest trade-offs of that automation plainly.

Five methods, one pool, one transaction, tests that run in seconds. Build a CRUD app with JDBC once by hand, and every ORM you meet will make sense.

Last updated on 29 September 2026.