Spring JDBC automatically translates SQLExceptions into Spring's unchecked DataAccessException hierarchy. This provides consistent exception handling across different database vendors and allows easier error handling without catching checked exceptions.
Interface for SQLException translation strategy.
public interface SQLExceptionTranslator {
DataAccessException translate(String task, String sql, SQLException ex);
}Translator using database-specific error codes.
public class SQLErrorCodeSQLExceptionTranslator extends AbstractFallbackSQLExceptionTranslator {
public SQLErrorCodeSQLExceptionTranslator() { }
public SQLErrorCodeSQLExceptionTranslator(DataSource dataSource) { }
public SQLErrorCodeSQLExceptionTranslator(String dbName) { }
public void setDataSource(DataSource dataSource) { }
public void setDatabaseProductName(String dbName) { }
public void setSqlErrorCodes(SQLErrorCodes sec) { }
public DataAccessException translate(String task, String sql, SQLException ex) { }
}Translator using SQL state codes.
public class SQLStateSQLExceptionTranslator implements SQLExceptionTranslator {
public SQLStateSQLExceptionTranslator() { }
public DataAccessException translate(String task, String sql, SQLException ex) { }
}Translator using JDBC 4 exception subclasses.
public class SQLExceptionSubclassTranslator implements SQLExceptionTranslator {
public SQLExceptionSubclassTranslator() { }
public DataAccessException translate(String task, String sql, SQLException ex) { }
}Configuration for error code translation.
public class SQLErrorCodes {
public void setBadSqlGrammarCodes(String... badSqlGrammarCodes) { }
public String[] getBadSqlGrammarCodes() { }
public void setInvalidResultSetAccessCodes(String... invalidResultSetAccessCodes) { }
public void setDuplicateKeyCodes(String... duplicateKeyCodes) { }
public void setDataIntegrityViolationCodes(String... dataIntegrityViolationCodes) { }
public void setDataAccessResourceFailureCodes(String... dataAccessResourceFailureCodes) { }
public void setCannotAcquireLockCodes(String... cannotAcquireLockCodes) { }
public void setDeadlockLoserCodes(String... deadlockLoserCodes) { }
public void setCannotSerializeTransactionCodes(String... cannotSerializeTransactionCodes) { }
public void setDatabaseProductName(String databaseProductName) { }
public String getDatabaseProductName() { }
public void setCustomTranslations(CustomSQLErrorCodesTranslation... customTranslations) { }
}Main DataAccessException types from spring-tx:
// Root exception
public abstract class DataAccessException extends RuntimeException { }
// SQL-specific exceptions
public class BadSqlGrammarException extends DataAccessException { }
public class DataIntegrityViolationException extends DataAccessException { }
public class DuplicateKeyException extends DataIntegrityViolationException { }
public class DataAccessResourceFailureException extends DataAccessException { }
public class CannotGetJdbcConnectionException extends DataAccessResourceFailureException { }
public class InvalidResultSetAccessException extends DataAccessException { }
public class UncategorizedSQLException extends DataAccessException { }
public class TransientDataAccessResourceException extends DataAccessException { }
public class DeadlockLoserDataAccessException extends TransientDataAccessResourceException { }import org.springframework.dao.DuplicateKeyException;
import org.springframework.dao.DataAccessException;
try {
jdbcTemplate.update(
"INSERT INTO users (email, name) VALUES (?, ?)",
"john@example.com",
"John Doe"
);
} catch (DuplicateKeyException e) {
// Handle duplicate key
System.err.println("Email already exists");
} catch (DataAccessException e) {
// Handle other database errors
System.err.println("Database error: " + e.getMessage());
}import org.springframework.jdbc.support.SQLExceptionTranslator;
public class CustomExceptionTranslator implements SQLExceptionTranslator {
@Override
public DataAccessException translate(String task, String sql, SQLException ex) {
// Custom logic based on error code or message
if (ex.getErrorCode() == 1062) { // MySQL duplicate key
return new DuplicateKeyException("Duplicate entry", ex);
}
return null; // Fall back to default
}
}
// Configure custom translator
JdbcTemplate template = new JdbcTemplate(dataSource);
template.setExceptionTranslator(new CustomExceptionTranslator());SQLErrorCodes errorCodes = new SQLErrorCodes();
errorCodes.setDatabaseProductName("MyDatabase");
errorCodes.setBadSqlGrammarCodes("42000", "42001");
errorCodes.setDuplicateKeyCodes("23505");
errorCodes.setDataIntegrityViolationCodes("23000", "23001");
SQLErrorCodeSQLExceptionTranslator translator =
new SQLErrorCodeSQLExceptionTranslator();
translator.setSqlErrorCodes(errorCodes);
jdbcTemplate.setExceptionTranslator(translator);import org.springframework.jdbc.support.CustomSQLErrorCodesTranslation;
CustomSQLErrorCodesTranslation customTranslation =
new CustomSQLErrorCodesTranslation();
customTranslation.setErrorCodes("ORA-12345", "ORA-12346");
customTranslation.setExceptionClass(MyCustomException.class);
SQLErrorCodes errorCodes = new SQLErrorCodes();
errorCodes.setCustomTranslations(customTranslation);
SQLErrorCodeSQLExceptionTranslator translator =
new SQLErrorCodeSQLExceptionTranslator();
translator.setSqlErrorCodes(errorCodes);try {
jdbcTemplate.update("UPDATE users SET balance = balance - ? WHERE id = ?", amount, userId);
} catch (DuplicateKeyException e) {
// Specific: duplicate key constraint
logger.error("Duplicate key", e);
} catch (DataIntegrityViolationException e) {
// More general: any constraint violation
logger.error("Constraint violation", e);
} catch (CannotGetJdbcConnectionException e) {
// Connection problems
logger.error("Cannot connect to database", e);
} catch (BadSqlGrammarException e) {
// SQL syntax errors
logger.error("Invalid SQL", e);
} catch (DataAccessException e) {
// Catch-all for database errors
logger.error("Database error", e);
}try {
jdbcTemplate.query("SELECT * FROM invalid_table", rs -> {});
} catch (DataAccessException e) {
// Get cause SQLException
SQLException sqlEx = (SQLException) e.getCause();
if (sqlEx != null) {
System.out.println("SQL State: " + sqlEx.getSQLState());
System.out.println("Error Code: " + sqlEx.getErrorCode());
System.out.println("Message: " + sqlEx.getMessage());
}
}Spring Boot automatically loads error codes from sql-error-codes.xml:
<!-- Custom sql-error-codes.xml -->
<bean id="MyDatabase" class="org.springframework.jdbc.support.SQLErrorCodes">
<property name="databaseProductName" value="MyDatabase"/>
<property name="badSqlGrammarCodes">
<value>42000,42001,42S01,42S02</value>
</property>
<property name="duplicateKeyCodes">
<value>23505</value>
</property>
<property name="dataIntegrityViolationCodes">
<value>23000,23001</value>
</property>
</bean>