Revisit DataAccessException translation API

[ttddyy]

I am implementing transaction retry mechanism to our applications and using DataAccessException mapping facility to translate some db failures to transient exception.
Since DataAccessException mapping is one of the early days feature of spring-framework, I felt some of the APIs are bit outdated.

Here is list of things I encountered:

SQLErrorCodeSQLExceptionTranslator requires subclassing to perform custom mapping logic.

To add custom mappings to existing logic, SQLErrorCodeSQLExceptionTranslator#customTranslate need to be overridden by subclass.

I made a delegation subclass, then injected custom translators.

public class CustomDelegatingSQLErrorCodeSQLExceptionTranslator extends SQLErrorCodeSQLExceptionTranslator {

	@Nullable
	private SQLExceptionTranslator delegate;

	// constructors

	@Override
	protected DataAccessException customTranslate(String task, String sql, SQLException sqlEx) {
		if (this.delegate == null) {
			return null;
		}
		return this.delegate.translate(task, sql, sqlEx);
	}
}

I think composition style API is nicer to have than sub-classing.

Does not have a way to append/update some of the error codes on SQLErrorCodes (not just entire override)

Error code provided by org/springframework/jdbc/support/sql-error-codes.xml(SQL_ERROR_CODE_DEFAULT_PATH) can be overridden by sql-error-codes.xml(SQL_ERROR_CODE_OVERRIDE_PATH).
However, the override is done by bean(DB type). So, it cannot partially update error codes on a specific bean. For example, adding some error codes to transient exceptions to PostgreSQL.

To workaround repeating entire DB error code mapping, I created a SQLErrorCodesUpdater which uses the nature that the SQLErrorCodes is a mutable java bean.

@AllArgsConstructor
@Getter
public class CustomSQLErrorCodes {

	/**
	 * name has to match with db vendor name from
	 * "DataSourceMetaData#getDatabaseProductName" which also matches to the bean id in
	 * "org/springframework/jdbc/support/sql-error-codes.xml"
	 */
	private String databaseName;

	/**
	 * Which exception to map
	 */
	private DataAccessExceptionType dataAccessExceptionType;

	/**
	 * Whether replace or add to the default sql error code
	 */
	private boolean add;

	/**
	 * SQL error code to add or replace
	 */
	private String[] codes;

}

public enum DataAccessExceptionType {
	BAD_SQL_GRAMMAR("badSqlGrammarCodes"),
	INVALID_RESULT_SET_ACCESS("invalidResultSetAccessCodes"),
	DUPLICATE_KEY("duplicateKeyCodes"),
  ... 

	private String propertyName;
  ...
}

public class SQLErrorCodesUpdater {

	public void update(CustomSQLErrorCodes customSQLErrorCodes) {
		update(customSQLErrorCodes.getDatabaseName(), customSQLErrorCodes.getDataAccessExceptionType(),
				customSQLErrorCodes.isAdd(), customSQLErrorCodes.getCodes());
	}

	private void update(String databaseName, DataAccessExceptionType exceptionType, boolean add, String[] values) {
		SQLErrorCodesFactory factory = SQLErrorCodesFactory.getInstance();
		SQLErrorCodes sec = factory.getErrorCodes(databaseName);

		String propertyName = exceptionType.getPropertyName();

		BeanWrapper beanWrapper = new BeanWrapperImpl(sec);
		Set<String> newCodes = new HashSet<>(Arrays.asList(values));
		if (add) {
			// since codes fields in SQLErrorCodes have initially empty String[] assigned
			// codes will never be null
			String[] codes = (String[]) beanWrapper.getPropertyValue(propertyName);
			newCodes.addAll(Arrays.asList(codes));
		}
		beanWrapper.setPropertyValue(propertyName, newCodes.toArray(new String[0]));

	}

Then, run them at runtime

@Bean
public InitializingBean updateSQLErrorCodes(ObjectProvider<CustomSQLErrorCodes> customSQLErrorCodesProvider) {
  SQLErrorCodesUpdater updater = new SQLErrorCodesUpdater();
  return () -> {
    for (CustomSQLErrorCodes customSQLErrorCodes : customSQLErrorCodesProvider) {
      updater.update(customSQLErrorCodes);
    }
  };
}

Alternatively, CustomSQLErrorCodesTranslation can be used to provide custom mappings, but it is mapped to SQLErrorCodes instance which is database vendor specific. I can create a custom error code map, but assigning it requires to retrieve db vendor type.

CustomSQLErrorCodesTranslation translation = new CustomSQLErrorCodesTranslation();
translation.setExceptionClass(TransientDataAccessResourceException.class);
translation.setErrorCodes("99999");

// custom error map by DB vendor type
Map<String, List<CustomSQLErrorCodesTranslation>> map = new HashMap<>();
map.put("H2", Arrays.asList(translation));

// here needs to retrieve db vendor name
String dbName = null;
try {
  dbName = JdbcUtils.extractDatabaseMetaData(dataSource, "getDatabaseProductName");
}
catch (MetaDataAccessException ex) {
  // TODO: log or re-throw
}
List<CustomSQLErrorCodesTranslation> customTranslations = map.getOrDefault(dbName, Collections.emptyList());

SQLErrorCodes sqlErrorCodes = SQLErrorCodesFactory.getInstance().getErrorCodes(dataSource);
sqlErrorCodes.setCustomTranslations(customTranslations.toArray(new CustomSQLErrorCodesTranslation[0]));

Still it's a bit complicated to just apply custom error code mappings.

SQLErrorCodes does not know the db vendor name, I need to retrieve it from datasource. (SQLErrorCodes#getDatabaseProductName() returns one from bean property and different from bean id(db vendor name), which is a bit confusing as well.)

An instance of SQLErrorCodeSQLExceptionTranslator is specific to single DB type

SQLErrorCodeSQLExceptionTranslator when setDataSource, setDatabaseProductName, setSqlErrorCodes, or corresponding constructors are called, then sqlErrorCodes is populated for the specific DB vendor type.
This makes the bean dependent to specific vendor type. It is fine but not simple to find this dependency until digging the code.

Probably adding documentation would be helpful.

PersistenceExceptionTranslationInterceptor#detectPersistenceExceptionTranslators does not consider ordering

When multiple PersistenceExceptionTranslator are available, there is no way to specify ordering.
I don't find any workaround to guarantee the ordering of each translator unless spring code is updated to consider the ordering.