How to catch a specific exceptions in JDBC?

Question

How to catch a specific exceptions in JDBC? Examples: primary key exception or foreign key exception.

Answer 1

SQLException contains some database-specific info related to the exception. From the doc:

Each SQLException provides several kinds of information:

1) a string describing the error. This is used as the Java Exception message, available via the method getMesage.

2) a "SQLstate" string, which follows either the XOPEN SQLstate conventions or the SQL 99 conventions. The values of the SQLState string are described in the appropriate spec. The DatabaseMetaData method getSQLStateType can be used to discover whether the driver returns the XOPEN type or the SQL 99 type.

3) an integer error code that is specific to each vendor. Normally this will be the actual error code returned by the underlying database.

4) a chain to a next Exception. This can be used to provide additional error information.

Answer 2

Brian's right, a SQLException will be thrown for just about ANY JDBC problem. This is partially why JDBC is so annoying. The Spring library JDBC helpers provide an exception translator to look at the SQLCode, SQLState, etc., and throw the appropriate DataAccessException. There are many of these exception classes, and they give you a better idea of what went wrong, with names such as DataIntegrityViolationException, DataSourceLookupFailureException, PermissionDeniedDataAccessException, and others.

Answer 3

The best and DB-independent way to handle SQLException more specifically is to determine the SQL state code which can be obtained by SQLException#getSQLState(). The SQLState is a 5-char code, of which the first two are common among all DB's and the last three might differ depending on the DB and/or the specific condition. Here's an extract from the spec:

• 02: no data
• 08: connection exception
• 07: dynamic SQL error
• 0A: feature not supported
• 21: cardinality violation
• 22: data exception
• 23: integrity constraint violation
• 24: invalid cursor state
• 25: invalid transaction state
• 26: invalid SQL statement name
• 28: invalid authorization specification
• 2B: dependent privilege descriptors still exist
• 2C: invalid character set name
• 2D: invalid transaction termination
• 2E: invalid connection name
• 33: invalid SQL descriptor name
• 34: invalid cursor name
• 35: invalid condition number
• 3D: invalid catalog name
• 3C: ambiguous cursor name
• 3F: invalid schema name

So to determine whether the SQL Exception is caused by a constraint violation, you can just do the following in a (fictive) SQLUtil class:

public static boolean isConstraintViolation(SQLException e) {
    return e.getSQLState().startsWith("23");
}