2

I'm using Java Prepared Statements Batch to insert multiple rows in to database. Sometimes in the batch there are two identical insert statements with the same primary key value and this throws an exception which is normal. My question is what happens with the data that was inserted before the exception? Should it be inserted (committed) or is rollback triggered? I can't find any documentation about this.

I have done some experiments with two identical SQL inserts in one batch. This is pseudocode:

int[] inserted;
PreparedStatement PreparedStatementInsert ... prepared with two identical SQL inserts.

try {
    inserted = PreparedStatementInsert.executeBatch();
} catch (BatchUpdateException e) {
    // program enters this part
    inserted = e.getUpdateCounts();
}

In my experiment the catch block is executed and the inserted array has one element inserted[0] = 1. From documentation I have concluded that one (first) INSERT is done successfully and the second one is not. But when I look at the database it's empty as if no INSERT was done. Is there some documentation or explanation of this behavior? I'm using Java 6 with Postgres 9.0 database.

CyberMuz
  • 359
  • 4
  • 12
  • To be safe, should you look at using transactions? – vikingsteve Jul 10 '13 at 14:18
  • 1
    Postgres has an interesting behavior if you use a single transaction for your bulk insert. Whenever a constraint violation occurs, the transaction is rolled back automatically. No rows are inserted at all. See: http://stackoverflow.com/questions/9436122/how-can-i-tell-postgresql-not-to-abort-the-whole-transaction-when-a-single-const – nif Jul 10 '13 at 14:19
  • 1
    If you want to know the specified behavior, look it up in the JDBC 4.1 spec. – Mark Rotteveel Jul 10 '13 at 15:02

1 Answers1

0

You have to use addBatch() method with your PreparedStatement object i.e PreparedStatementInsert1.addBatch(), PreparedStatementInsert2.addBatch() .. PreparedStatementInsertN.addBatch()

Then you have to use PreparedStatementInsert.executeBatch();

Mukesh S
  • 2,856
  • 17
  • 23