JDBC & the DAO Pattern

Transactions in Web Apps

18 min Lesson 6 of 13

Transactions in Web Apps

A database transaction is a sequence of SQL statements that the database engine treats as a single, indivisible unit of work. Either every statement in the group succeeds and is permanently recorded — a commit — or none of them take effect and the database is restored to the state it was in before the first statement ran — a rollback. This guarantee, codified in the ACID properties, is what separates a reliable web application from one that leaves data in an inconsistent state under error conditions.

Why Every Non-Trivial Write Operation Needs a Transaction

Consider an e-commerce order placement: you must insert a row into orders, insert several rows into order_items, and decrement stock in products. If the stock update fails after the order was already written, your database contains a phantom order with no inventory deducted. Without a transaction, partial writes produce corrupt business data that is often invisible until an audit.

ACID in one paragraph: Atomicity — all or nothing. Consistency — constraints (foreign keys, check constraints) are satisfied both before and after. Isolation — concurrent transactions do not see each other's uncommitted changes (at the default isolation level). Durability — once committed, data survives crashes. JDBC gives you atomicity and durability directly; isolation is configurable per connection.

JDBC Default: Auto-Commit Mode

By default, every JDBC Connection starts in auto-commit mode: each statement is its own micro-transaction, committed immediately after execution. This is fine for reads, but it means multi-step writes have no protection at all. The first thing you do when you need a real transaction is turn auto-commit off:

connection.setAutoCommit(false); // begin a transaction

From that point, statements accumulate in an open transaction until you explicitly call commit() or rollback(). Calling close() on a connection that still has an open, uncommitted transaction will cause the driver to roll back automatically — but relying on that is a bug, not a feature.

The Canonical Transaction Pattern in a Servlet / Service Method

The gold-standard structure for a transactional operation using plain JDBC looks like this:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.sql.DataSource;

public class OrderService {

    private final DataSource dataSource;

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

    public void placeOrder(int customerId, int productId, int quantity, double price)
            throws SQLException {

        try (Connection conn = dataSource.getConnection()) {

            conn.setAutoCommit(false);   // 1. open transaction

            try {
                // 2. insert the order header
                long orderId;
                try (PreparedStatement ps = conn.prepareStatement(
                        "INSERT INTO orders (customer_id, total) VALUES (?, ?)",
                        PreparedStatement.RETURN_GENERATED_KEYS)) {
                    ps.setInt(1, customerId);
                    ps.setDouble(2, price * quantity);
                    ps.executeUpdate();
                    try (var keys = ps.getGeneratedKeys()) {
                        keys.next();
                        orderId = keys.getLong(1);
                    }
                }

                // 3. insert the line item
                try (PreparedStatement ps = conn.prepareStatement(
                        "INSERT INTO order_items (order_id, product_id, qty, unit_price) VALUES (?, ?, ?, ?)")) {
                    ps.setLong(1, orderId);
                    ps.setInt(2, productId);
                    ps.setInt(3, quantity);
                    ps.setDouble(4, price);
                    ps.executeUpdate();
                }

                // 4. decrement stock
                try (PreparedStatement ps = conn.prepareStatement(
                        "UPDATE products SET stock = stock - ? WHERE id = ? AND stock >= ?")) {
                    ps.setInt(1, quantity);
                    ps.setInt(2, productId);
                    ps.setInt(3, quantity);
                    int rows = ps.executeUpdate();
                    if (rows == 0) {
                        throw new IllegalStateException("Insufficient stock for product " + productId);
                    }
                }

                conn.commit();   // 5. all succeeded — persist
            } catch (Exception e) {
                conn.rollback(); // 6. something failed — undo everything
                throw e;
            }
        }
        // conn.close() returns the connection to the pool
    }
}

The structure is deliberate: setAutoCommit(false) is called first, then every statement runs, and a catch at the same level as the try block calls rollback() before re-throwing. The outer try-with-resources closes the connection regardless of success or failure.

Always re-throw after rollback. Swallowing the exception after a rollback hides errors from callers and from logging infrastructure. Catch, roll back, then let the exception propagate so the servlet or controller can return a proper error response.

Treating a Servlet Request as a Unit of Work

In a web application, the natural boundary for a transaction is usually a single HTTP request. The pattern is: open a connection, begin a transaction, do all the database work the request requires, commit if everything succeeded, roll back if anything threw, then return the connection to the pool. This is sometimes called the Unit of Work pattern.

A clean way to implement this without scattering setAutoCommit / commit / rollback calls throughout your DAO layer is to push the transaction boundary into a helper method that accepts a lambda:

import java.sql.Connection;
import java.sql.SQLException;
import java.util.function.Consumer;
import javax.sql.DataSource;

public final class Tx {

    private Tx() {}

    @FunctionalInterface
    public interface TxWork<T> {
        T execute(Connection conn) throws SQLException;
    }

    public static <T> T run(DataSource ds, TxWork<T> work) throws SQLException {
        try (Connection conn = ds.getConnection()) {
            conn.setAutoCommit(false);
            try {
                T result = work.execute(conn);
                conn.commit();
                return result;
            } catch (Exception e) {
                conn.rollback();
                throw e;
            }
        }
    }
}

Callers pass a lambda that receives the already-open, already-transactional connection. The rollback logic lives in exactly one place:

// In a servlet doPost():
long orderId = Tx.run(dataSource, conn -> {
    return orderDao.insert(conn, customerId, cart);
    // itemDao.insertAll and productDao.decrementStock could also go here
});

Savepoints — Partial Rollbacks

Sometimes you want to undo only part of a transaction — for example, to retry a failed sub-operation without losing earlier successful writes. JDBC supports savepoints:

conn.setAutoCommit(false);

// first operation — always required
insertAuditLog(conn, "order attempt");

java.sql.Savepoint sp = conn.setSavepoint("before_payment");
try {
    chargePaymentGateway(conn, amount);
} catch (SQLException e) {
    conn.rollback(sp);          // undo only the payment step
    insertAuditLog(conn, "payment failed, retrying");
    chargeWithFallbackMethod(conn, amount);
}

conn.commit();

Not every database or driver supports savepoints equally well; verify your target DB's documentation before relying on them in critical paths.

Isolation Levels — A Brief Map

JDBC lets you set the isolation level per connection with conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ). The four standard levels from weakest to strongest are:

READ_UNCOMMITTED — can read another transaction's uncommitted (dirty) data. Almost never appropriate.
READ_COMMITTED — reads only committed data; the default in PostgreSQL and SQL Server. Prevents dirty reads.
REPEATABLE_READ — re-reading a row in the same transaction always yields the same result; the MySQL/InnoDB default. Prevents non-repeatable reads.
SERIALIZABLE — full serial execution semantics; prevents phantom reads but can be slow under concurrency.

Changing isolation levels in a pooled environment. When you borrow a connection from a pool, its isolation level is whatever was last set on it. If you change the isolation level for one request, reset it before returning the connection to the pool, or every subsequent borrower inherits your change. HikariCP lets you specify a global default via cfg.setTransactionIsolation("TRANSACTION_READ_COMMITTED") — set it once there rather than per-connection.

Propagating Connections Through the DAO Layer

A common beginner mistake is to have each DAO method call dataSource.getConnection() independently. When you do that, every DAO call executes in its own separate connection — and therefore in its own separate transaction. Calls across DAOs can never be atomic. The solution is to pass the connection into DAO methods from the service layer, where the transaction is opened:

// WRONG — each DAO opens its own connection; no shared transaction
public void transfer(int from, int to, double amount) throws SQLException {
    accountDao.debit(from, amount);   // opens conn A, commits, closes
    accountDao.credit(to, amount);    // opens conn B, commits, closes
    // if credit() throws, debit() is already committed — money is lost
}

// CORRECT — one connection, one transaction
public void transfer(int from, int to, double amount) throws SQLException {
    Tx.run(dataSource, conn -> {
        accountDao.debit(conn, from, amount);
        accountDao.credit(conn, to, amount);
        return null;
    });
}

Summary

Every multi-step database write in a web application belongs inside a transaction. The JDBC pattern is: call setAutoCommit(false), execute your statements, call commit() on success, and rollback() in the catch block before re-throwing. Encapsulate this in a reusable utility (a Tx.run() helper or, later, a Spring @Transactional annotation) so the mechanics live in one place. Pass the open connection into your DAO methods rather than having them open their own — that is the only way multiple DAO calls can participate in the same transaction. In the next lesson you will look at how to handle SQLException and resource leaks gracefully across the entire JDBC call stack.