JDBC Batch insert exception handling to know the particular failed record

0
votes

I want to read a txt file which has oracle insert statements in java program and execute as a batch, If any of the record gets failed in the batch, How do i get to know the particular insert statement only got failed.

for Ex.if i have 1000 records in file and 659 record gets failed in insertion, how can i get to know the 659 record got failed in executebatch update.Is there any other process to do this please let me know.

1
jdbcbatch-processing

1 Answers

3
votes

There are two ways to handle the above situation either you check in Java code or write the procedure

  1. Java Code : this will either load everything or rollback the whole batch.

    updateResult = preparedStatement.executeBatch(); checkSuccessfulUpdates(updateResult);

    public static void checkSuccessfulUpdates(int[] updateCounts) throws Exception { for (int i :updateCounts) { if (i == Statement.EXECUTE_FAILED) { throw new Exception("Not all rows were updated. Rolling back entire batch "); } } }

  2. Write a SQL procedure

Send the entire the data in custom object to procedure , load it 1 by 1 by record, handle the exception within loop , on error load the failed record in temporary table.