Batch Update fails - Rollback operation

Question

I've created a PreparedStatement and have added a couple of data in a bactch

PreparedStatement ps = conn.PreparedStatement("INSERT into table (a, b) values (?, ?)";

ps.setInt(1, 1);
ps.setInt(2, 2);

ps.addBatch

ps.setInt(1, 4);
ps.setInt(2, 5);

ps.addBatch

Now when I execute ps. I get the result, now lets say ps fails, then I want to insert the records, one by one not in the batch mode.

How do i extract the prepared statements, I can get the size, but not sure how to get the records?

Answer 1

Can you not insert the records one by one in the first place? If your DBMS supports transactions - most modern ones do - you can bundle all your inserts into one atomic transaction and, to other users of the database, it'll look just like you added them all at once (see pseudocode below).

connection.setAutoCommit(false);
try{
    insert(1,2);
}
catch(Exception e){
    System.out.println("1, 2 didn't work");
}
try{
    insert(4,5);
}
catch(Exception e){
    System.out.println("4, 5 didn't work");
}
connection.commit();