Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Batch inserts using PreparedStatements

Posted on 2005-04-14
8
Medium Priority
?
410 Views
Last Modified: 2008-03-17
Hello,

I've been trying to figure out the fastest way to insert data into an Oracle database. After some testing, it is clear that PreparedStatements are the fastest when reused within the came connection. They are even 5 times faster if done in batch.

I started out with this code:

        PreparedStatement pstmtBatch;
        pstmtBatch = conn.prepareStatement("INSERT INTO test VALUES (?)");
        for(int i=0; i < 50000; i++){
            pstmtBatch.setInt(1, i);
            pstmtBatch.addBatch();
            if(i%10 == 0){
                pstmtBatch.executeBatch();
                pstmtBatch.clearBatch();
            }
        }
        pstmtBatch.executeBatch();
        pstmtBatch.close();


Now, I have the requirement to update the data if it already exists in the DB (this will not happen often). For obvious performance reasons, I can't check if the values exists before each insert.

I know the Oracle error code for an exception because the primary key already exists. I try to catch it, but I don't know how to figure out which of the statements genereated the exception within the batch and how to continue the process in the loop after such an error.

Can someone help me out? Thanks
0
Comment
Question by:whiteeagl
8 Comments
 
LVL 30

Expert Comment

by:Mayank S
ID: 13779667
Catch the SQLException in a catch block and use the getErrorCode () method of the SQLException object to get the error-code.
0
 
LVL 92

Accepted Solution

by:
objects earned 300 total points
ID: 13779722
executeBatch() returns an array of int's indicating the result of each statement.

http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Statement.html#executeBatch()

NB. That won't happens when an error occurs in a batch is dependant on the driver you are using.
0
 

Author Comment

by:whiteeagl
ID: 13779802
mayankeagle: this does not give me the specific statement that failed...

objects: if the executeBatch() does not fail, I don't need to check the result. I really need to know what statement failled upon exception.


0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 92

Expert Comment

by:objects
ID: 13779817
Yes, thats what the return value tells you.
The int[] is also availble from the BatchUpdateException if the driver throws that exception

http://java.sun.com/j2se/1.4.2/docs/api/java/sql/BatchUpdateException.html#getUpdateCounts()
0
 

Author Comment

by:whiteeagl
ID: 13779868
You say: NB. That won't happens when an error occurs in a batch is dependant on the driver you are using.


This means I can't get the information of successfull inserts if an SQLException is thrown, right?
0
 
LVL 92

Expert Comment

by:objects
ID: 13779902
the driver may not continue processing once an error is recieved.
0
 
LVL 15

Assisted Solution

by:aozarov
aozarov earned 300 total points
ID: 13782491
BTW, if you are using Oracle DB/driver and are doing batch updates then you might want to read these two sections:
http://www.stanford.edu/dept/itss/docs/oracle/9i/java.920/a96654/oraperf.htm#1057093
http://www.stanford.edu/dept/itss/docs/oracle/9i/java.920/a96654/oraperf.htm#1059011

I had to read them when I encountered strange behavior when used batching with PreparedStatements.
That article basically suggests when getting BatchUpdateException you will not have any useful information when using PreparedStatements and will have to rollback your transaction (if you want to maintain your data integrity).

In your case if you know that PK violations are really rare then you can still apply batching (useful mostly to save DB roundtrips) but when you get an SQL Exception you should rollback and the apply the same process again but this time without batching and catching the individual PK violators.
0
 

Author Comment

by:whiteeagl
ID: 13782996
There is some very interesting information in these pages. Thanks for the links.

I'll continue testing. I'm also investigation database side solutions...
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
Suggested Courses
Course of the Month10 days, 16 hours left to enroll

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question