Resource management & transactions

Closing JDBC resources

JDBC objects — Connection, PreparedStatement, and ResultSet — all implement AutoCloseable. Always open them in a try-with-resources block:

try (Connection conn = DriverManager.getConnection(url, user, pass);
     PreparedStatement ps = conn.prepareStatement(sql);
     ResultSet rs = ps.executeQuery()) {

    while (rs.next()) {
        System.out.println(rs.getString(1));
    }
} // rs, ps, conn closed automatically in REVERSE order

Resources are closed in reverse declaration order: ResultSet first, then PreparedStatement, then Connection. This mirrors how you'd close them manually — always close inner objects before outer ones. Leaving a Connection open leaks a database connection from the pool, which eventually causes Too many connections errors.

Transactions

By default, JDBC runs in auto-commit mode — each SQL statement is its own transaction. To group statements atomically:

try (Connection conn = DriverManager.getConnection(url, user, pass)) {
    conn.setAutoCommit(false);           // begin manual transaction
    try {
        ps1.executeUpdate();             // debit account A
        ps2.executeUpdate();             // credit account B
        conn.commit();                   // both succeed — persist
    } catch (SQLException e) {
        conn.rollback();                 // either fails — undo all
        throw e;
    }
}

setAutoCommit(false) begins a transaction. commit() persists all changes since the last commit. rollback() undoes them. Always call rollback() in the catch block — if commit() was never reached, the database holds locks until the connection closes, causing contention.