This week's book giveaway is in the OCMJEA forum.
We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line!
See this thread for details.
The moose likes JDBC and the fly likes JDBC Batch insert exception handling Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC Batch insert exception handling" Watch "JDBC Batch insert exception handling" New topic
Author

JDBC Batch insert exception handling

Anthony D'Souz
Ranch Hand

Joined: Oct 13, 2011
Posts: 63
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.

Here is a sample code i am using in my app for batch insert. Suppose result set have got approx 1000 records:



If the 100th record is throwing 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?

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

Regards,
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

My advice would be to perform the checks on the Java side and only attempt to insert records that are expected to go through. Then, if an error happens, it is something really unexpected (such as running out of space, or a deadlock) and the whole action should be called off (using rollback).

If you insist on relying on DB checks, don't use batch inserts. Insert one row at a time. That way you simply catch the exception and continue inserting other rows. I'd strongly suggest to investigate the SQLException you catch and only continue if the underlying database error is an expected one (such as exceeding field size, violating DB constraint and so on; that's gonna be tricky if you want to support more databases).

(Batch inserts might not buy you anything in this case: after every failed row you'd have to resend the rest of the batch, resulting perhaps in more traffic and work than doing it one row at a time; moreover, some JDBC drivers/databases might require you to perform a rollback in this situation, as the state of the transaction could be indeterminate after batch insert fails.)
Anthony D'Souz
Ranch Hand

Joined: Oct 13, 2011
Posts: 63
Martin Vajsar wrote:My advice would be to perform the checks on the Java side and only attempt to insert records that are expected to go through. Then, if an error happens, it is something really unexpected (such as running out of space, or a deadlock) and the whole action should be called off (using rollback).

If you insist on relying on DB checks, don't use batch inserts. Insert one row at a time. That way you simply catch the exception and continue inserting other rows. I'd strongly suggest to investigate the SQLException you catch and only continue if the underlying database error is an expected one (such as exceeding field size, violating DB constraint and so on; that's gonna be tricky if you want to support more databases).


Thanks for the suggestion. It works like a charm when i implemented the batch logic using single insert statements.

Though the work is completed, still I am not able to throw the Batch insert thing out of my mind and wanna give it a try offline(i.e. not in production, only for my learning purpose ).

Can you please tell is there some way like i can catch the record throwing exception and proceed with further processing (I have seen when a record of batch insert fails then the entire process is aborted) ? I don't want to retry the insertion of the failed record. All i want is to log the record details and proceed with the further execution of the batch.

I am using Oracle 10g db and classes12.jar for connecting with the DB

Regards,
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Anthony D'Souz wrote:I am using Oracle 10g db and classes12.jar for connecting with the DB

You aren't on JDK 1.2, are you? You should probably use a newer driver, see http://www.coderanch.com/how-to/java/OracleFaq#driverversion.

Out of interest, I've digged into the 11g R2 documentation:

Oracle wrote:Error Handling in the Oracle Implementation of Standard Batching

If any one of the batched operations fails to complete successfully or attempts to return a result set during an executeBatch call, then the processing stops and a java.sql.BatchUpdateException is generated.

After a batch exception, the update counts array can be retrieved using the getUpdateCounts method of the BatchUpdateException object. This returns an int array of update counts, just as the executeBatch method does. In the Oracle implementation of standard update batching, contents of the update counts array are as follows, after a batch is processed:

  • For a prepared statement batch, it is not possible to know which operation failed. The array has one element for each operation in the batch, and each element has a value of -3. According to the JDBC 2.0 specification, a value of -3 indicates that an operation did not complete successfully. In this case, it was presumably just one operation that actually failed, but because the JDBC driver does not know which operation that was, it labels all the batched operations as failures.

    You should always perform a ROLLBACK operation in this situation.


  • For a generic statement batch or callable statement batch, the update counts array is only a partial array containing the actual update counts up to the point of the error. The actual update counts can be provided because Oracle JDBC cannot use true batching for generic and callable statements in the Oracle implementation of standard update batching.

    For example, if there were 20 operations in the batch, the first 13 succeeded, and the 14th generated an exception, then the update counts array will have 13 elements, containing actual update counts of the successful operations.

    You can either commit or roll back the successful operations in this situation, as you prefer.

  • In your code, upon failed processing of a batch, you should be prepared to handle either -3 or true update counts in the array elements when an exception occurs. For a failed batch processing, you will have either a full array of -3 or a partial array of positive integers.

    Emphasis added.

    You want to use PreparedStatement; for many reasons, one of them being that without using PreparedStatement the performance benefit of batch update (Oracle's "array insert") does not much exists. Therefore, you cannot determine which rows failed.

    The same is true for Oracle's own update batching.

    So, no batch updates for you.
     
    wood burning stoves
     
    subject: JDBC Batch insert exception handling