Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Caught failed record statement in batch execution but how to keep adding further remaining records?

 
Ganish Patil
Ranch Hand
Posts: 529
19
Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Netbeans IDE Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have written a program which adds 6 employees information in table employeeinfo having id as pk, name and age columns. Referred similar question link.
Just to understand I set batch size only 2. Total records 6.

Observation:
  • What I've observed getUpdateCounts() method of BatchUpdateException returns int array of status of execution of SQL statements ( read here executeBatch() and getUpdateCounts() ) and its order is the order in which those sql statements were added to batch, which are there only in current batch which raised exception.

  • Example: Suppose my employeeinfo table is empty. When I execute above code.
  • First batch execution( Incorporates two sql statements of two emp records i.e. "Ganesh" and "Rajesh"): Which executes successfully without error and also adds records in database.
  • Second  batch execution( Incorporates two sql statements of two emp records):
    • Which executes only one sql statement of emp "Shoib" successfully and also adds it's record in database BUT sql statement of emp "John" cause BatchUpdateException and not added to database because of same primary key.
    • Here getUpdateCounts() method returns int array of status of execution of SQL statements which are present only in current batch. Those are the status of sql statements of "Shoib" and "John". So "John" return EXECUTE_FAILED and "Shoib" returns a number greater than or equal to zero.
  • Third batch is not executed because of exception occured in second batch execution

  • Question:
  • I hope I undertood the concept please correct me If I'm wrong somewhere
  • Here I printed the records in that current batch execution which are not added in database successfully. But what about the records which are remaining there in  empInfoList[] to be added to database. Just because of employee record "John" who has same pk should we leave rest records who may not have same pk problem like in my above example record of "Robin" and "Steve" can be added because they don't have same pk already existsed in database.?
  • Should I write code which again calls to getaddBatchANDexecuteBatchMethod() method to start adding next remaining records of empInfoList[] but start only from remaining records. I will keep track of record index so start adding from next index of record which caused error?
  •  
    Ganish Patil
    Ranch Hand
    Posts: 529
    19
    Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Netbeans IDE Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Output: At beginning when employeeinfo table is empty

    Connection created successfully!
    Sequence of Employee Added to batch:
    Added Emp record sql statement to batch: Ganesh
    Added Emp record sql statement to batch: Rajesh
    Added Emp record sql statement to batch: John
    Added Emp record sql statement to batch: Shoib
    After Exception no of statements in batch :2
    Failed Record Adding Index: 0
    No of Successful Updated SQL Statements in Batch : 1
    No of Successful Updated SQL statements in Batch But Row Affected Info Not Available: 0
    No of Unsuccessful Updated SQL Statements in Batch: 1
    ***Information of failed SQL statements in batch***
    Emp Id: 100
    Emp Name: John
    Emp Age: 22
    ----------------------------------------------------
    java.sql.BatchUpdateException: Duplicate entry '100' for key 'PRIMARY'
    at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:1139)
    at batchfailrecord.BatchFailedRecordDemo.getaddBatchANDexecuteBatchMethod(BatchFailedRecordDemo.java:104)
    at batchfailrecord.BatchFailedRecordDemo.main(BatchFailedRecordDemo.java:206)
     
    Brian Tkatch
    Bartender
    Posts: 567
    25
    Linux Notepad Oracle
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    That looks complicated. I got lost in the middle.

    Whatever it is that you are trying to do, can you do it from the command line? That is, running mysql from he prompt and typing insert manually.
     
    Dave Tolls
    Ranch Hand
    Posts: 2103
    15
    • Likes 1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    If you want to continue sending batches after one has failed then you need to put your try/catch closer in, around the executeBatch call.
    At the moment it is surrounding the loop, so any exception results in the loop being exited.

    Also, can I suggest moving all that code in the catch block into a method (at least)?
    That's a lot of lines to have in a catch, and makes the code hard to follow.  It took several read throughs to actually see where the try/catch sat.
     
    Ganish Patil
    Ranch Hand
    Posts: 529
    19
    Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Netbeans IDE Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Opps! forgot to post EmployeeInformation class code.
     
    Ganish Patil
    Ranch Hand
    Posts: 529
    19
    Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Netbeans IDE Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Brian Tkatch wrote:That looks complicated. I got lost in the middle.
    Sorry, I tried to compress it as much as possible.
    Brian Tkatch wrote:running mysql from he prompt and typing insert manually.
    Yes I can but inserting records through code is necessary to make this program raise BatchUpdateException and track failed updating records.
     
    Ganish Patil
    Ranch Hand
    Posts: 529
    19
    Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Netbeans IDE Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Dave Tolls wrote:If you want to continue sending batches after one has failed then you need to put your try/catch closer in, around the executeBatch call.
    At the moment it is surrounding the loop, so any exception results in the loop being exited.
    I was like    ,why the hell I can't think like that. You were just awesome it worked.
    Dave Tolls wrote:Also, can I suggest moving all that code in the catch block into a method (at least)?
    That's a lot of lines to have in a catch, and makes the code hard to follow. It took several read throughs to actually see where the try/catch sat.
    Apology for obscure code. Yes moved catch clause's code in a separate method. I think from next time I should surround only line of codes by try block which may raise exception although I have to create multiple try catch. Thank you so much Dave Tolls and Brian Tkatch    
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic