Spring JdbcTemplate batchUpdate exception handling
Our code is currently using the batchUpdate method on the JdbcTemplate for batch insertion.
My question is, in case of any exceptions in one of the updates, how to handle it (assume just adding a log) and continue with the following sql update statements?
Also how does the batchUpdate () method for the JdbcTemplate handle exceptions?
The snippet is 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));
}
}
source to share
how does batchUpdate () method for JdbcTemplate handle exceptions?
Batch update undefined behavior in JDBC :
If one of the commands in the batch update does not execute as expected, this method throws a BatchUpdateException and the JDBC driver may or may not continue processing the remaining commands in the batch.
You should test this behavior with your DBMS.
BatchUpdateException
Will depend on spring anyway and will close again as a RuntimeException after some cleanup (see implementation details here ).
All this logic will be intertwined with transactions - for example. if the insert is within the boundaries of transactions and you delete RuntimeException
across the boundaries of the transaction - the transaction (and all successful inserts with it) will be rolled back.
The desired "boolean error strings" only batch logic cannot be efficiently implemented without additional knowledge of your DBMS and its JDBC driver error behavior during batch inserts.
source to share
I faced the same problem that spring jdbc stops inserting in case of any error entry and does not continue inserting. Below is my work: -
// 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
}
}
source to share