JDBC & the DAO Pattern

Handling SQL Exceptions & Resources

18 min Lesson 7 of 13

Handling SQL Exceptions & Resources

Two of the most common sources of bugs in JDBC code are unclosed resources and swallowed exceptions. A Connection, PreparedStatement, or ResultSet that is never closed leaks file descriptors and database-side cursors, degrading or crashing the application under load. An exception that is caught and silently discarded turns a clear error into a mystery. This lesson covers the right patterns — and the real trade-offs — so your DAO layer is robust in production.

What SQLException Carries

java.sql.SQLException is a checked exception that wraps three distinct pieces of diagnostic information beyond the message string:

SQL State — a five-character code defined by the SQL standard (X/Open or ISO). The first two characters identify the class of error: 08 = connection failure, 22 = data exception, 23 = integrity constraint violation, 42 = syntax error or access violation.
Error Code — a vendor-specific integer (e.g. MySQL 1062 = duplicate entry, PostgreSQL 0 if unknown). Useful when you need database-specific recovery.
Chained exceptions — getNextException() returns additional SQLExceptions that the driver chained to the first. Always iterate the chain when logging.

catch (SQLException ex) {
    // Walk the full exception chain — drivers often chain the root cause
    for (SQLException e = ex; e != null; e = e.getNextException()) {
        logger.error("SQLState={} errorCode={} message={}",
                e.getSQLState(), e.getErrorCode(), e.getMessage());
    }
}

SQLState is more portable than error codes. If you need to detect a duplicate-key violation across MySQL and PostgreSQL, check for SQLState 23000 or 23505 rather than vendor error codes, which differ between databases.

Subclasses of SQLException Worth Knowing

Since Java 6, JDBC defines several typed subclasses that let you catch specific categories without inspecting SQLState strings:

SQLTransientException — the operation may succeed if retried (e.g. SQLTransientConnectionException for a transient network glitch).
SQLNonTransientException — retrying without fixing the root cause will not help (e.g. bad SQL syntax, constraint violation).
SQLIntegrityConstraintViolationException — subclass of SQLNonTransientException; thrown on unique, foreign-key, or check constraint violations.
SQLTimeoutException — the query exceeded the statement timeout set via setQueryTimeout().
BatchUpdateException — thrown when one statement in a executeBatch() call fails; getUpdateCounts() reveals which rows succeeded.

try {
    userDao.insert(user);
} catch (SQLIntegrityConstraintViolationException ex) {
    // Duplicate email — this is a business-logic error, not a bug
    throw new DuplicateEmailException("Email already registered: " + user.getEmail(), ex);
} catch (SQLTransientConnectionException ex) {
    // Network blip — safe to retry after a brief pause
    retryPolicy.execute(() -> userDao.insert(user));
} catch (SQLException ex) {
    // Everything else — escalate
    throw new DataAccessException("Unexpected database error", ex);
}

Try-With-Resources: The Only Acceptable Pattern

Before Java 7, JDBC code required nested finally blocks to guarantee that every resource was closed even when an exception was thrown mid-operation. That code was notoriously verbose and easy to get wrong — a common mistake was to close only the ResultSet but forget the Statement. Try-with-resources eliminates that problem entirely.

Any object whose class implements java.lang.AutoCloseable can appear in the resource list of a try statement. Connection, Statement, PreparedStatement, and ResultSet all implement AutoCloseable. Resources are closed in reverse declaration order — ResultSet before PreparedStatement before Connection — regardless of whether an exception is thrown.

// All three resources are closed automatically, in reverse order
try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement(
             "SELECT id, email FROM users WHERE active = ?");
     ) {

    ps.setBoolean(1, true);

    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getLong("id") + " " + rs.getString("email"));
        }
    }   // rs closed here

}   // ps and conn closed here

Open ResultSet in its own nested try block. ResultSet is created after the statement is prepared and parameters are bound, so it cannot share the outer resource list without awkward null-initialisation. A nested try (ResultSet rs = ps.executeQuery()) is the cleanest solution and makes the lifecycle of each object crystal-clear.

Suppressed Exceptions

Try-with-resources handles a subtle edge case that the old finally pattern got wrong: if the body of the try throws an exception and close() also throws, Java attaches the close exception as a suppressed exception on the primary one rather than replacing it. This preserves the original error — the thing you actually need to diagnose — while still recording the close failure.

catch (SQLException ex) {
    logger.error("Primary exception: {}", ex.getMessage());
    // Log any exceptions that occurred during resource cleanup
    for (Throwable suppressed : ex.getSuppressed()) {
        logger.warn("Suppressed during close: {}", suppressed.getMessage());
    }
    throw new DataAccessException("Query failed", ex);
}

The Pre-Java-7 Anti-Pattern (and Why It Failed)

For historical context, here is the fragile pattern try-with-resources replaced. Notice how a single forgotten null-check or a thrown exception inside finally can mask the original error or leave a resource open:

Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
    conn = dataSource.getConnection();
    ps   = conn.prepareStatement("SELECT ...");
    rs   = ps.executeQuery();
    // process rs ...
} catch (SQLException ex) {
    throw new DataAccessException(ex);
} finally {
    // If rs.close() throws, ps.close() is never reached — BUG
    if (rs   != null) try { rs.close();   } catch (SQLException ignored) {}
    if (ps   != null) try { ps.close();   } catch (SQLException ignored) {}
    if (conn != null) try { conn.close(); } catch (SQLException ignored) {}
}

Never silence exceptions in a catch block with an empty body or a bare ignored variable. At minimum, log them at WARN level. Silenced exceptions make production incidents dramatically harder to diagnose and may indicate serious problems like a pool leak or a corrupted transaction.

Wrapping SQLException in a Runtime Exception

Because SQLException is checked, it leaks database details into every layer of your application if you let it propagate unchecked. The standard DAO pattern wraps it in a custom unchecked exception before it crosses the DAO boundary:

// Custom unchecked wrapper — lives in your persistence package
public class DataAccessException extends RuntimeException {
    public DataAccessException(String message, Throwable cause) {
        super(message, cause);
    }
}

// Inside a DAO method
public User findById(long id) {
    String sql = "SELECT id, email, name FROM users WHERE id = ?";
    try (Connection conn = dataSource.getConnection();
         PreparedStatement ps = conn.prepareStatement(sql)) {

        ps.setLong(1, id);
        try (ResultSet rs = ps.executeQuery()) {
            if (rs.next()) {
                return mapRow(rs);
            }
            return null;
        }
    } catch (SQLException ex) {
        throw new DataAccessException("findById(" + id + ") failed", ex);
    }
}

The service layer catches DataAccessException (or lets it propagate to the servlet/controller) without depending on java.sql at all. This keeps your database technology choice hidden behind the DAO interface — a key goal of the pattern.

Setting Statement and Query Timeouts

A runaway query can hold a pool connection for minutes, starving other requests. Always set a queryTimeout on any Statement that executes user-driven logic:

try (Connection conn = dataSource.getConnection();
     PreparedStatement ps = conn.prepareStatement(
             "SELECT * FROM reports WHERE created_by = ?")) {

    ps.setQueryTimeout(5); // 5 seconds — throws SQLTimeoutException if exceeded
    ps.setLong(1, userId);

    try (ResultSet rs = ps.executeQuery()) {
        // process ...
    }
} catch (SQLTimeoutException ex) {
    logger.warn("Query timed out for user {}", userId);
    throw new ServiceException("Report query took too long. Please try a smaller date range.");
} catch (SQLException ex) {
    throw new DataAccessException("Report query failed", ex);
}

Summary

Robust JDBC resource management rests on three habits: always use try-with-resources for every Connection, Statement, and ResultSet; always log the full exception chain including SQLState, error code, and suppressed exceptions; and always wrap SQLException in a domain-specific unchecked exception before it escapes the DAO layer. Apply typed subclasses (SQLIntegrityConstraintViolationException, SQLTransientConnectionException) to handle specific failure modes gracefully without parsing strings. In the next lesson you will add a service layer on top of the DAO that enforces business rules and transactional boundaries.