How to ignore all erroneous statements and execute all good ones in JDBC executeBatch?

Question

I have java.sql.Statement with lots of batch statements added with addBatch. Executing executeBatch will throw BatchUpdateException after first statement which violates database constrains and won't execute all remaining statements in batch. Is there any way I can execute all statements in batch ignoring erroneous ones?

Answer 1

I think a better solution would be to fix the erroneous ones. What's wrong? The SQL? The underlying tables? The data?

Exceptions are supposed to be "exceptional". What situation is so common that you're likely to see it more often with this batch?

The exception is telling you that something is amiss. I would not want to recommend a fix that would allow you to ignore the message. If there's an exception thrown, that means you have an opportunity to ameliorate its root problem in the catch block.

If you're looping over each statement in the batch, you can catch the exception from each one individually and simply log the error. Just don't ignore it completely.

But unless you can think of another recovery strategy, you're out of luck.