Spring JdbcTemplate batchUpdate handling exceptions

5
votes

Currently our code uses batchUpdate method of JdbcTemplate to do batch Insertion.

My question is in case of any exception in one of the update how to handle it (suppose just by adding the log) and continue with the next update sql statements?

Also how batchUpdate() method fo JdbcTemplate handles the exceptions?

Snippet here.

    /**
     * Saves the list of <code>Item</code> objects to the database in a batch mode
     * 
     * @param objects
     *    list of objects to save in a batch mode
     */
    public void save(final List<Item> listOfItems) {

        for (List<Debit> list : listOfItems) {
            getJdbcTemplate().batchUpdate(insertItem, new ItemBatchPreparedStatementSetter(list));
        }
    }
2
springjdbcjdbctemplatespring-jdbcbatch-updates
Handing errors in batch updates is not easy, and there's no one solution to the problem. It depends on how you want to handle it. – skaffman
@skaffman Do you know a way to handle errors of Spring batch updates? When exceptions translation is used, it seems that the first encountered is translated and thrown, therefore the returned update counts are lost. It's very weird. – manash

2 Answers

7
votes

how batchUpdate() method fo JdbcTemplate handles the exceptions?

Batch update behavior is undefined in JDBC:

If one of the commands in a batch update fails to execute properly, this method throws a BatchUpdateException, and a JDBC driver may or may not continue to process the remaining commands in the batch.

You should check this behavior with your DBMS.

Anyway, BatchUpdateException will be caught by spring and rethrown as RuntimeException after some clean up (see implementation details here).

All this logic will be interwined with transactions - e.g. if insert is within transaction bounds and you rethrow RuntimeException through transaction bounds - transaction (and all successful inserts with it) will be rolled back.

So desired "log error rows only" batch logic cannot be implemented effectively without additional knowledge about your DBMS and it's JDBC driver behaviour on errors during batch inserts.

0
votes

I ran into the same problem that spring jdbc stops insertion in case of any error record and does not continue insertion. Below is my work around :-

// divide the inputlist into batches and for each batch :-
for (int j = 0; j < resEntlSize; j += getEntlBatchSize()) {
            final List<ResEntlDTO> batchEntlDTOList = resEntlDTOList
                    .subList(
                            j,
                            j + getEntlBatchSize() > resEntlSize ? resEntlSize
                                    : j + getEntlBatchSize());
            TransactionDefinition def = new DefaultTransactionDefinition();
            TransactionStatus status = transactionManager
                    .getTransaction(def);
            try {
                //perform batchupdate for the batch
                transactionManager.commit(status);
            } catch (Exception e) {
                transactionManager.rollback(status);
                //perform single update for the error batch
            }

        }