Practical JDBC: Prepared Statements, Transactions and Rollback - Yenra

Run a contained database exercise

Use JDK 25 and Maven 3.9.x. The download pins H2 2.3.232 and the build plugins for this educational example. H2 runs inside the Java process with an in-memory database, so the exercise needs no database server or real credentials and leaves no persistent stock records. The first Maven run downloads dependencies. For real applications, select maintained versions appropriate to your database and patch policy.

Download the complete JDBC Maven project and README (ZIP)

cd project
mvn -q compile exec:java

Expect the following application output; Maven or the JDK may also print diagnostic messages:

Committed: bin1=70, bin2=50
Rolled back: Destination bin missing
After failure: bin1=70, bin2=50, total=120

The H2 connection reference describes jdbc:h2:mem: URLs. The empty password and sa name belong only to this isolated in-memory demonstration. A deployed service should obtain a scoped database identity through its approved configuration and secret mechanism.

Follow the JDBC resource lifetime

The driver implements the database-specific protocol. A Connection represents a database session, a PreparedStatement carries SQL with bound values, and a ResultSet exposes query rows. Nest try-with-resources blocks so the result set closes before its statement and the statement before the connection. The java.sql package documentation identifies these roles.

import java.sql.*;

public class JdbcDemo {
    static void move(Connection c, int from, int to, int units) throws SQLException {
        if (units <= 0) throw new IllegalArgumentException("Positive units required");
        if (from == to) throw new IllegalArgumentException("Use different bins");
        try (PreparedStatement debit = c.prepareStatement(
                "UPDATE bins SET units=units-? WHERE id=? AND units>=?");
             PreparedStatement credit = c.prepareStatement(
                "UPDATE bins SET units=units+? WHERE id=?")) {
            debit.setInt(1, units); debit.setInt(2, from); debit.setInt(3, units);
            if (debit.executeUpdate() != 1) throw new SQLException("Insufficient stock or missing source");
            credit.setInt(1, units); credit.setInt(2, to);
            if (credit.executeUpdate() != 1) throw new SQLException("Destination bin missing");
        }
    }
    static int units(Connection c, int id) throws SQLException {
        try (PreparedStatement q = c.prepareStatement("SELECT units FROM bins WHERE id=?")) {
            q.setInt(1, id);
            try (ResultSet rows = q.executeQuery()) {
                if (!rows.next()) throw new SQLException("Bin missing");
                return rows.getInt("units");
            }
        }
    }
    static void transaction(Connection c, int from, int to, int amount) throws SQLException {
        // This small application owns the connection and transaction boundary.
        c.setAutoCommit(false);
        try {
            move(c, from, to, amount);
            c.commit();
        } catch (SQLException | RuntimeException failure) {
            try { c.rollback(); } catch (SQLException rollback) { failure.addSuppressed(rollback); }
            throw failure;
        }
    }
    public static void main(String[] args) throws SQLException {
        try (Connection c = DriverManager.getConnection("jdbc:h2:mem:stock", "sa", "")) {
            try (Statement s = c.createStatement()) {
                s.executeUpdate("CREATE TABLE bins(id INTEGER PRIMARY KEY, units INTEGER NOT NULL CHECK(units>=0))");
                s.executeUpdate("INSERT INTO bins VALUES(1,100),(2,20)");
            }
            transaction(c, 1, 2, 30);
            System.out.printf("Committed: bin1=%d, bin2=%d%n", units(c,1), units(c,2));
            try {
                transaction(c, 1, 99, 10);
                throw new AssertionError("Missing destination should fail");
            } catch (SQLException expected) {
                System.out.println("Rolled back: " + expected.getMessage());
            }
            int a=units(c,1), b=units(c,2);
            System.out.printf("After failure: bin1=%d, bin2=%d, total=%d%n", a,b,a+b);
            if (a != 70 || b != 50) throw new AssertionError("Rollback failed");
            c.rollback(); // End the final read transaction before closing.
        }
    }
}

The program creates its table and seed data before disabling auto-commit for the transfer. Keeping schema setup outside the demonstration transaction avoids confusing database-specific DDL behavior with ordinary row updates. The connection stays open throughout the exercise, retaining the in-memory database until the example is done.

Bind values and check affected rows

The debit statement subtracts units only from a matching bin with enough stock. Its three question marks are filled in order, starting at index 1. Bound values remain separate from the SQL structure. Use placeholders for data values; choose table names, sort directions and other SQL structure through a fixed allowlist when those must vary.

The debit and credit each require exactly one affected row. That check catches a missing bin or insufficient stock at the point where it matters. A successful API call with an update count of zero is a valid database result that still fails this business operation. The PreparedStatement contract describes parameter binding and update counts.

The read query advances the result set with next() before retrieving a column. Here, units is declared NOT NULL. When reading nullable numeric columns in other schemas, account for SQL NULL explicitly: primitive getters can require a subsequent wasNull() check, or use an appropriate nullable object mapping.

Make success and rollback observable

A fresh JDBC connection normally starts in auto-commit mode, where each completed statement is its own transaction. This example calls setAutoCommit(false) before the two updates, then calls commit() after both succeed. On failure, it attempts rollback() and preserves a rollback exception as a suppressed exception on the original failure. These operations are defined by the Connection API.

The helper owns a connection dedicated to this small application. In pooled or framework-managed code, establish who owns the transaction before calling commit, rollback or changing auto-commit. Return a borrowed connection according to the pool's contract, and use the framework's transaction mechanism where it owns the session. Blindly copying this helper into a managed transaction can split the intended unit of work.

A connection failure during commit can leave the client uncertain about the result. For consequential operations, use a durable operation identifier and a reconciliation or idempotency design before retrying. The demonstration covers a known local statement failure, not every distributed failure mode.

Diagnose failures using their layer

On a narrow screen, scroll the table horizontally. Keyboard users can focus the table and use the arrow keys.

Preserve exception type, SQLState and vendor code in controlled diagnostics. Redact credentials and sensitive bound values. When a query is slow, inspect its execution plan, row count and indexes before changing Java collections or allocating more threads. The application diagnosis guide explains how to relate database waits to the rest of a request.

Extend the exercise by attempting a transfer larger than the source stock, then assert that both balances remain unchanged. For concurrent transfers, test on the actual target database with its isolation level and driver; an in-memory H2 success establishes the local example's behavior, not production equivalence.