Java JDBC batchExecute insert

Question

I am using JDBC addBatch and batchExecute for insert statements. I have autocommit mode to on. My problem is, when I have for example 20 inserts, and insert number 10 raises an exception (for example null values not allowed), no data is inserted. Shouldn't be the first 10 ok statements be inserted?.

My code:

 try { int[] results = stmt.executeBatch(); return results; } catch (BatchUpdateException e) { int[] tmpres = e.getUpdateCounts(); for (i = 0; i < tmpres.length; i++) { System.out.println(tmpres[i]); } }

I see on the output that the update count of the first 10 statements is 1. So why no data is inserted?

Best regards, Peter

Gord Thompson · Accepted Answer · 2015-12-07 15:08:08Z

The other answers to this question are somewhat misleading, at least with regard to the general case.

In most cases, when using executeBatch() with setAutoCommit(true):

the statements in the batch are not wrapped in an implicit transaction, and
the statements processed prior to the BatchUpdateException will be committed.

This is definitely true for MySQL Connector/J (with rewriteBatchedStatements=false, which is the default), the Microsoft SQL Server JDBC driver, Derby, and HSQLDB. (I just ran actual Java code to confirm.)

As with many other aspects of JDBC, the actual behaviour in your particular case depends on the specific implementation of the JDBC driver you are using.

To complete the picture: Oracle and DB2 behave as you describe. Postgres treats the batch as a single transaction.
Firebird behaves the same as Postgres (the batch is considered a single transcation)
You're right, my answer sounded like "this is always given" which is not true.
Oh, that is a problem for my application. Can I somehow check on runtime if the driver handles the execute batch as one transaction?
@Peter ... however, it might be better if your code used setAutoCommit(false) and explicitly handled the transaction behaviour, rather than relying on the (possibly inconsistent) behaviour of different JDBC drivers with setAutoCommit(true).

antoniodvr · Accepted Answer · 2015-12-06 09:49:59Z

In this case all the statements are involved in the same transaction so any statement that will cause an error will rollback the whole transaction.

Jan · Accepted Answer · 2015-12-06 17:16:23Z

Seems like in your configuration, all the statements inside one batch are in fact executed in one transaction. So if any of the inserts fail, the whole transaction (that's the whole batch) will be rolled back. Even with autocommit on.

Collectives™ on Stack Overflow

Java JDBC batchExecute insert

3 Answers 3

7 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Related