CODEWITHSUNDEEP
javamysqljdbcexception
Radheshyam Karnani
Radheshyam Karnani

Reputation: 23

JDBC BatchUpdate exception handling using BatchUpdateExcpetion

I am trying to update data in the table using batch update. When I am updating the data, there are some failures. I want to get the update statement of the failed query. For example, if the query which failed is "update table set abc= 123". Then I want the exception handler to return this SQL query.

I have tried several ways to get the SQL query but I am not able to do so.

Below is the code:

 String line=null;
 int cnt=0;
 int batchSize=100;
 BufferedReader br2=new BufferedReader(new FileReader("clean.txt"));
 stmt1=conn.createStatement();
 while((line = br2.readLine()) != null)
 {
    try {
        String sql = line;
           stmt1.addBatch(sql);
         //System.out.println(cnt);
           if(++cnt % batchSize == 0)
           {
               stmt1.executeBatch();
               conn.commit();
           }                           
        } 
    catch (BatchUpdateException ex) {
        System.out.println(ex.getLocalizedMessage());
                 int[] updateCount = ex.getUpdateCounts();
                    int cnt1 = 1;
                    for (int i : updateCount) {
                        if  (i == Statement.EXECUTE_FAILED) {
                            System.out.println("Error on request " + cnt1 +": Execute failed");
                      } else {
                            System.out.println("Request " + cnt1 +": OK");
                        }
                        cnt1++;
                    } 
                }
}
System.out.println("done");
br2.close();

Please let me know what should I do to get the failed SQL query. So, if I have the query I can store that in a file again.

Upvotes: 0

Views: 912

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109081

The exact meaning of getUpdateCounts depends on the driver implementation: the JDBC specification allows that either it only contains the update counts of successfully executed statements before the first failure, or it contains all statements executes, where failures are marked with a value of EXECUTE_FAILED; from BatchUpdateException:

After a command in a batch update fails to execute properly and a BatchUpdateException is thrown, the driver may or may not continue to process the remaining commands in the batch. If the driver continues processing after a failure, the array returned by the method BatchUpdateException.getUpdateCounts will have an element for every command in the batch rather than only elements for the commands that executed successfully before the error. In the case where the driver continues processing commands, the array element for any command that failed is Statement.EXECUTE_FAILED.

In other words, if you want to know the failed statements there are two cases to handle:

  1. The length of the update counts array is smaller than the number of batched statements.
    In this case the first (array length) statements have been successfully executed. Statement (array length) + 1 failed and the rest of the statements have not been executed.
  2. The length of the array is equal to the number of batched statements.
    In this case you can iterate over the array and all indexes with a value of Statement.EXECUTE_FAILED were failed statements.

For both cases, you need to do the bookkeeping to track which statements were in the batch and to match them to the indexes of failed and/or not executed statements.

Upvotes: 1

Related Questions