1

In my java code I have a Prepared statement ps_temp.

PreparedStatement ps_temp;

// loop

ps_temp.setString(1,'abc');
....
....
ps_temp.addBatch();

// end loop

ps_temp.executeBatch();

Now one of the records in the batch created a Unique Index constraint, which eventually lead to the whole ps_temp.executeBatch() to fail. Not even the good records in the batch are inserted into the table.

So, is it possible that if my Batch has some good and some bad records(which can cause integrity constraint), THEN AT LEAST THE GOOD RECORDS GETS INSERTED INTO THE TABLE ?

Thanks in advance !! Noman

prunge
  • 22,460
  • 3
  • 73
  • 80
Noman K
  • 277
  • 1
  • 5
  • 15

1 Answers1

1

It is quite frequent for a batch of records to contain few bad ones. If you try to insert all the records in one go, and one record fails, the whole lot of insertions will be rejected. That is expected and is the core purpose of "transaction handling".

Usually for batch inserts, you can take two approaches:

1)Commit after each record insertion --> Very performance intensive process.

2)Divide the total records into smaller "chunks" and insert into database. So that just the chunk containing the bad record will fail and other chunks will be inserted into the database.

Alternatively, if you dont want to handle these things yourself, go for a framework. Spring batch

Could be one of your options in that case

Hirak
  • 3,601
  • 1
  • 22
  • 33