Using SQLExceptionTranslator

SQLExceptionTranslator is an interface that must be implemented by classes capable of converting between SQLExceptions and native org.springframework.dao.DataAccessException, which do not depend on the data access strategy. Implementations can be typed (for example, using SQLState codes for JDBC) or proprietary (for example, using Oracle error codes) for greater precision.

SQLErrorCodeSQLExceptionTranslator is the default implementation of SQLExceptionTranslator. This implementation uses specific vendor codes. It is more precise than the SQLState implementation. The error code conversion is based on codes stored in a JavaBean type class called SQLErrorCodes. This class is created and populated by a SQLErrorCodesFactory, which (as the name suggests) is a factory for creating SQLErrorCodes based on the contents of the configuration file sql-error-codes.xml. This file is populated with manufacturer codes and is based on the DatabaseProductName taken from the DatabaseMetaData. The codes used are for the actual database you are using.

SQLErrorCodeSQLExceptionTranslator applies negotiation rules in the following sequence:

1. Any custom transformation implemented by a subclass. Typically a specific SQLErrorCodeSQLExceptionTranslator is used, so this rule does not apply. It only applies if you actually provided an implementation of the subclass.

2. Any custom implementation of the SQLExceptionTranslator interface, which is exposed as a customSqlExceptionTranslator property of the SQLErrorCodes class.

3. The list of instances of the CustomSQLErrorCodesTranslation class (provided for the customTranslations property of the SQLErrorCodes class) is searched for a match.

4. Error code mapping is applied.

5. A return converter (translator) is used. SQLExceptionSubclassTranslator is the default translator. If this translation is not available, the next transform to return is SQLStateSQLExceptionTranslator.

You can extend SQLErrorCodeSQLExceptionTranslator, as shown in the following example:

public class CustomSQLErrorCodesTranslator extends SQLErrorCodeSQLExceptionTranslator {
    protected DataAccessException customTranslate(String task, String sql, SQLException sqlEx) {
        if (sqlEx.getErrorCode() == -12345) {
            return new DeadlockLoserDataAccessException(task, sqlEx);
        }
        return null;
    }
}

class CustomSQLErrorCodesTranslator : SQLErrorCodeSQLExceptionTranslator() {
    override fun customTranslate(task: String, sql: String?, sqlEx: SQLException): DataAccessException? {
        if (sqlEx.errorCode == -12345) {
            return DeadlockLoserDataAccessException(task, sqlEx)
        }
        return null
    }
}

The previous example converts a specific error code (-12345) and leaves other errors to the standard converter implementation. To use this custom translator, you need to pass it to the JdbcTemplate via the setExceptionTranslator method, and then use this JdbcTemplate for any data access handling where this a converter is required. The following example shows how this custom converter can be used:

private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
    // create a JdbcTemplate and set the data source
    this.jdbcTemplate = new JdbcTemplate();
    this.jdbcTemplate.setDataSource(dataSource);
    // create a custom transformer and set the data source to search for the default transform
    CustomSQLErrorCodesTranslator tr = new CustomSQLErrorCodesTranslator();
    tr.setDataSource(dataSource);
    this.jdbcTemplate.setExceptionTranslator(tr);
}
public void updateShippingCharge(long orderId, long pct) {
    // use the prepared JdbcTemplate for this update
    this.jdbcTemplate.update("update orders" +
        " set shipping_charge = shipping_charge * ? / 100" +
        " where id = ?", pct, orderId);
}

// create a JdbcTemplate template and set the data source
private val jdbcTemplate = JdbcTemplate(dataSource).apply {
    // create a custom transformer and set the data source to search for the default transform
    exceptionTranslator = CustomSQLErrorCodesTranslator().apply {
        this.dataSource = dataSource
    }
}
fun updateShippingCharge(orderId: Long, pct: Long) {
    // use the prepared JdbcTemplate for this update
    this.jdbcTemplate!!.update("update orders" +
            " set shipping_charge = shipping_charge * ? / 100" +
            " where id = ?", pct, orderId)
}

The custom resolver is passed a data source to search for error codes in sql-error-codes.xml.