JDBC Batch insert exception handling

Question

I am performing a JDBC batch insert (inserting 1000 rows approx at a time) each time my program is executed. But i am not able to handle the exception thrown by some of the records properly.

Suppose, the 100th record out of 1000 records is throwing an exception because of an invalid data or size of some value exceeds the column size. Once the exception has occured, the remaining records are not getting inserted and the program fails in between.

What I want is even if the 100th record is throwing exception, the remaining insertions should happen as usual before my program ends.

I am not able to understand how to achieve this. Please suggest.

EDIT:

Here is a sample code I am using in my app for batch insert. Assume that the result set has got approx 1000 records.

PreparedStatement ps = null; while(rs.next()){   //Retrieve the value and set it to a Prepared statement    String name = rs.getString("Name");   int age = rs.getInt("Age");   ps.setInt(1, age);   ps.setString(2, name);    //Finally invoke addBatch   ps.addBatch(); }  //Finally call the executeBatch method ps.executeBatch(); 

If the 100th record is throwing an exception, then I want to trigger the process only from the 100th too 1000th record. Is there some way to do this such that I can restart the process from the record which threw exception onwards till the end again? How to achieve this?

Answer 1

You state that you are executing batches of SQL statements so I'm assuming that you are using executeBatch. When you executeBatch a batch of statements, some of the statements may succeed and some of the statements may fail. If one statement fails, that does not mean that the JDBC driver rolls back the statements that succeeded. The JDBC driver may choose to attempt to execute all the statements in the batch if one statement fails or it may choose to stop executing statements in the batch once a statement fails (it sounds like the driver you're using chooses to stop executing statements as soon as there is a failure).

When one statement in a batch fails, you should get a BatchUpdateException. In your exception handler, you need to call getUpdateCounts. That will give you an array of int that tells you either how many rows the statement updated, a Statement.SUCCESS_NO_INFO indicating the statement succeeded but no row count was available, or a Statement.EXECUTE_FAILED indicating that the statement failed. If the first 99 statements succeed, the 100th statement generates an error, and the remaining statements are not executed, you should get back a 100 element array where the first 99 elements indicate success and the 100th element indicates Statement.EXECUTE_FAILED. Your code would then need to retry those statements that were not executed (in this case, statements 101-1000).