Handling SQLException “properly” in JDBC is mostly about (1) not leaking resources, (2) preserving diagnostic detail, (3) rolling back safely, and (4) translating errors into something meaningful at your app boundary.

1) Always close JDBC resources (use try-with-resources)

This eliminates most error-handling bugs (leaks and double-closes), and it also handles exceptions thrown during close() by attaching them as suppressed exceptions.

package org.kodejava.jdbc;

import javax.sql.DataSource;
import java.sql.*;

public final class JdbcExample {
    public static void runQuery(DataSource ds, long id) {
        String sql = "select name from users where id = ?";

        try (Connection con = ds.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {

            ps.setLong(1, id);

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    String name = rs.getString(1);
                    // use name...
                }
            }

        } catch (SQLException e) {
            throw toDataAccessException("Failed running query: " + sql, e);
        }
    }

    static RuntimeException toDataAccessException(String message, SQLException e) {
        // Keep the SQLException as the cause so details are not lost.
        return new RuntimeException(message + " (SQLState=" + e.getSQLState() + ", code=" + e.getErrorCode() + ")", e);
    }
}

Key points

• Prefer PreparedStatement over Statement (safety + plan reuse).
• Wrap/translate once, near your data-access boundary, but keep e as the cause.

2) Log/inspect the full JDBC error chain (and suppressed exceptions)

JDBC drivers can chain multiple exceptions (e.g., one per batch item), and try-with-resources can add suppressed exceptions from close().

package org.kodejava.jdbc;

import java.sql.SQLException;

public final class SqlDiagnostics {
    public static String describe(SQLException e) {
        StringBuilder sb = new StringBuilder();
        for (Throwable t = e; t != null; t = (t instanceof SQLException se) ? se.getNextException() : null) {
            if (t instanceof SQLException se) {
                sb.append("SQLException: message=").append(se.getMessage())
                        .append(", SQLState=").append(se.getSQLState())
                        .append(", code=").append(se.getErrorCode())
                        .append('\n');
            } else {
                sb.append("Throwable: ").append(t).append('\n');
            }

            for (Throwable sup : t.getSuppressed()) {
                sb.append("  suppressed: ").append(sup).append('\n');
            }
        }
        return sb.toString();
    }
}

When this matters

• Batch updates (BatchUpdateException)
• Failures during resource cleanup (network drop during close())

3) Handle transactions: commit/rollback with a safe rollback path

If you manually manage transactions (setAutoCommit(false)), your exception handling must:

1. rollback on failure,
2. not mask the original exception if rollback also fails,
3. restore state if you’re reusing connections (pools usually reset, but don’t rely on it blindly).

package org.kodejava.jdbc;

import java.sql.*;

public final class JdbcTxExample {
    public static void transfer(Connection con, long fromId, long toId, long amount) throws SQLException {
        boolean oldAutoCommit = con.getAutoCommit();
        con.setAutoCommit(false);

        try (PreparedStatement debit = con.prepareStatement("update acct set bal = bal - ? where id = ?");
             PreparedStatement credit = con.prepareStatement("update acct set bal = bal + ? where id = ?")) {

            debit.setLong(1, amount);
            debit.setLong(2, fromId);
            debit.executeUpdate();

            credit.setLong(1, amount);
            credit.setLong(2, toId);
            credit.executeUpdate();

            con.commit();

        } catch (SQLException e) {
            try {
                con.rollback();
            } catch (SQLException rb) {
                e.addSuppressed(rb); // keep original, attach rollback failure for debugging
            }
            throw e; // or translate here
        } finally {
            try {
                con.setAutoCommit(oldAutoCommit);
            } catch (SQLException ac) {
                // typically log; don't hide earlier failure
            }
        }
    }
}

4) Don’t swallow exceptions; translate them at the right layer

Common strategy:

• DAO/repository layer: catch SQLException, add context (operation + key parameters), then rethrow as:
  • a checked app exception (if you want callers to handle), or
  • a runtime “data access” exception (common in service-oriented apps).
• Service/controller boundary: map to user-safe messages (avoid exposing SQL text / internals).

Avoid:

• catch (SQLException e) {} (silences failures)
• Throwing a new exception without e as cause (loses SQLState/vendor code)

5) Use SQLState / vendor codes for decisions (sparingly)

If you need conditional handling (e.g., unique constraint violation), prefer SQLState classes when possible:

• 23*** integrity constraint violation (many DBs)
• 40*** transaction rollback / serialization failure (often retryable)

But keep it minimal: drivers/databases vary.

6) Be careful with retries

Only retry when you can justify it:

• transient network issues
• deadlocks / serialization failures (often safe to retry the whole transaction)

Never retry blindly on all SQLExceptions.

---

Quick checklist

• try-with-resources for Connection/Statement/ResultSet
• Keep SQLException as the cause; don’t lose SQLState / error code
• Walk getNextException() and check suppressed exceptions
• In manual transactions: rollback in catch, attach rollback failures via addSuppressed
• Translate exceptions at the repository boundary; expose safe messages at the edge