Provide access to all counters in case of BatchUpdateException with multiple batches

[asollberger]

Affects: 3.0.0.M1 to 5.2.0.RELEASE

Goal

I'm trying to use the jdbcTemplate.batchUpdate and would like to know the exact failed statement.

Issues

1. Trying to catch the BatchUpdateException which contains the counters does not work because the BatchUpdateException gets removed from the exception stack.
2. Even when the BatchUpdateException gets returned it only contains the counters of the current batch. The successful batch counts need to be communicated back to the caller as well.

Code to reproduce the problem

I use this table to quickly get a unique constraint exception

create table batch_test (
    id number primary key
);

Configuration

Spring: 5.1.3.RELEASE
Database: Oracle 18c

Code trying to catch the BatchUpdateException

final List<Integer> primaryKeys = Arrays.asList(1, 2, 3, 4, 5, 6, 2, 7, 8, 9);
try {
    return jdbcTemplate.batchUpdate(
            "insert into batch_test values (?)",
            primaryKeys,
            4,
            (ps, primaryKey) -> {
                ps.setInt(1, primaryKey);
            }
    );
} catch (final DataAccessException e) {
    final Throwable cause = e.getCause();
    if (cause instanceof BatchUpdateException) {
        final BatchUpdateException batchUpdateException = (BatchUpdateException) cause;
        final long[] updateCounts = batchUpdateException.getLargeUpdateCounts();
        // log the exact one that failed
        for (int index = 0; index < updateCounts.length; index++) {
            if (updateCounts[index] == Statement.EXECUTE_FAILED) {
                logger.debug("The insert of element " + index + " of the array failed");
            }
        }
        if (updateCounts.length < primaryKeys.size()) {
            logger.debug("The insert of elements " + (updateCounts.length - 1)
                    + " to " + (primaryKeys.size() - 1) + " of the array failed");
        }
    }
    throw e;
}

Possible solutions

The problem I'm having happens here:
https://github.com/spring-projects/spring-framework/blame/master/spring-jdbc/src/main/java/org/springframework/jdbc/support/SQLErrorCodeSQLExceptionTranslator.java#L179

The original stack sqlEx gets overridden with the next exception and thus the information of the BatchUpdateException gets lost.

I understand the intent to translate the cause into an exception that is a little more meaningful, but IMHO the original BatchUpdateException should not be trashed.

Wherever a new exception is created the original exception should be used for the stack. So instead of:

DataAccessException dae = customTranslate(task, sql, sqlEx);
DataAccessException customDex = customTranslator.translate(task, sql, sqlEx);
DataAccessException customException = createCustomException(task, sql, sqlEx, customTranslation.getExceptionClass());
return new BadSqlGrammarException(task, (sql != null ? sql : ""), sqlEx);

the original exception should be used:

DataAccessException dae = customTranslate(task, sql, ex);
DataAccessException customDex = customTranslator.translate(task, sql, ex);
DataAccessException customException = createCustomException(task, sql, ex, customTranslation.getExceptionClass());
return new BadSqlGrammarException(task, (sql != null ? sql : ""), ex);

The second issue is probably here:
https://github.com/spring-projects/spring-framework/blob/master/spring-jdbc/src/main/java/org/springframework/jdbc/core/JdbcTemplate.java#L1059

rowsAffected needs to be communicated back to the caller to pinpoint the problematic update

Workaround

My current solution consist of writing my own Translator, overriding the doTranslate method and setting the translator when I create the jdbcTemplate Bean

public class BatchUpdateExceptionTranslator extends SQLErrorCodeSQLExceptionTranslator {
    @Override
    @Nullable
    protected DataAccessException doTranslate(String task, @Nullable String sql, SQLException ex) {
        // exchanging sqlEx with ex whenever creating a new Exception
    }
}

@Configuration
public class MyConfiguration {
    @Bean
    public JdbcTemplate jdbcTemplate(
            final DataSource dataSource
    ) {
        final JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.setExceptionTranslator(new BatchUpdateExceptionTranslator());
        return jdbcTemplate;
    }
}

The second issue cannot be fixed as easily. Either avoid the use of the batched update or set the batch size very high to not run into that problem

[lafual]

If anyone is coming here for the work around (I am using spring-jdbc-5.3.29.jar, oracle8-19.3.0.0.jar, Oracle 19, JDK 8) and has the "second issue" (batch size < collection size), then the above sample needs to be adapted.

I am trying to insert ~40,000 records and Oracle is throwing ORA-00001: unique constraint (..) violated. In order to get the failed row I need to set "batch size = collection.size". The return results array only contains successes, so the bad row (I assume) is the new row, which means that collection.get(update.length) should be reported.

[marschall]

I don't know if everything in this issue is still current.

Trying to catch the BatchUpdateException which contains the counters does not work because the BatchUpdateException gets removed from the exception stack

This no longer seems to be the case. BatchUpdateException gets passed as a cause.

Even when the BatchUpdateException gets returned it only contains the counters of the current batch. The successful batch counts need to be communicated back to the caller as well.

I have three proposals how this could be solved:

1. Add an additional exception (eg. BatchUpdateDetailsException) in the stack that wraps BatchUpdateException
2. Add a suppressed exception to BatchUpdateException, eg. BatchUpdateDetailsException
3. Add a next exception to BatchUpdateException, eg. BatchUpdateDetailsException

[snicoll]

Playing a bit with the example above, we throw a DuplicateKeyException which sounds like the right call, having the BatchUpdateException as the nested cause. The problem is this method that lets your run several batches won't convey where it is indeed.

I think a try/catch here could be useful:

spring-framework/spring-jdbc/src/main/java/org/springframework/jdbc/core/JdbcTemplate.java

Line 1119 in 94e2bef

rowsAffected.add(ps.executeBatch());

We could then throw an exception that contains something built with rowsAffected. To limit disturbance, extending from BatchUpdateException is an idea but I am not aware of framework extending an SQL exception. Wrapping it does not look great as it would break backward compatiblity.

I've moved the sample above to a test case, see snicoll@b90368b

WDYT @jhoeller?

[jhoeller]

Maybe we could replace the original BatchUpdateException with an instance of our own, constructing it with aggregated update counts? Would not even have to be a custom subclass of BatchUpdateException if it does not need to expose additional state.

[snicoll]

@asollberger, @marschall, and anyone else interested, I've pushed a proposal for this see snicoll@bda23b4. Although I am not a huge fan of extending BatchSqlUpdateException that's what we should be doing to keep backward compatibility.

We'd like some feedback before moving on.