5

I am making a request SQL to commit some updates. The updates are done and good, but I would like to know if the batch is executed properly , ie are the statements commited in one go or do the statement excute one after the other ? This is because the autocommit is set implicitly to true, and I wonder if, for the batch to execute as a real "batch", I have to set it to false.

this is a question in relation with this one:

do I need a connection.commit() after executeBatch()?

The code is:

private void pdate(JdbcTemplate jdbcTemplate, List<Long> saisineIdsToUpdate,Connection connection) throws SQLException {
   String sqlUpdate = "UPDATE SAISINES SAI WHERE SAI.IDSAISINE = ?"; //request simplified

   PreparedStatement psUpdate = connection.prepareStatement(sqlUpdate);

   for (Long saisineId : saisineIdsToUpdate) {
      psUpdate.setLong(1, saisineId );
      psUpdate.addBatch();
   }

   psUpdate.executeBatch();
   psUpdate.close();

}
Community
  • 1
  • 1
Makoto
  • 765
  • 2
  • 17
  • 45

2 Answers2

5

According to the JDBC specification the behavior for batch execution under auto-commit is implementation specific. That is: it is up to the driver (or database) whether a batch is a single transaction or a transaction per item when auto-commit is true. It is also implementation specific whether an error rolls back everything or just that item under auto-commit.

This is why in general it is advisable to disable auto-commit when using batched execution and explicitly commit (or rollback) yourself.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
1

It seems that every statement in the batch is executed separately. Would have guessed otherwise. A lot of tutorials suggest to set setAutoCommit(false) before a batch to be able to rollback the whole batch if one step fails. See e.g. http://www.jguru.com/faq/view.jsp?EID=5079 which states:

Before carrying out a batch update, it is important to disable the auto-commit mode by calling setAutoCommit(false). This way, you will be able to rollback the batch transaction in case one of the updates fail for any reason.

This argumentation implicitly confirms that every statement is committed by itself when autocommit is enabled.

André Stannek
  • 7,773
  • 31
  • 52
  • 4
    cc: @stackSaru - re: *"every statement in the batch is executed separately"* - There is at least one case where that is not quite true: If a MySQL Connector/J connection string includes `rewriteBatchedStatements=true` and a series of individual single-row `INSERT INTO ... VALUES ...` statements are added to a Batch of a PreparedStatement then the MySQL JDBC driver will re-write multiple single-row INSERTs into one or more multi-row INSERTs when the batch is executed. For details, see the related question [here](http://stackoverflow.com/q/26307760/2144390). – Gord Thompson Nov 12 '14 at 21:12