This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes Use Multiple Insert Statements in Prepared Statements  Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Use Multiple Insert Statements in Prepared Statements  " Watch "Use Multiple Insert Statements in Prepared Statements  " New topic
Author

Use Multiple Insert Statements in Prepared Statements

vikram karne
Ranch Hand

Joined: Jun 05, 2007
Posts: 34
Hi Friends

i am stuck in a problem for which i need your help.i will brief you about the issue below.

I am having Multiple Insert Statements which i need to execute to put the data in the database.i have a doubt about the use of the executeUpdate() function in the statement interface. sample code is as below.

stmt = conn.prepareStatement("INSERT INTO Client ( clientPk, provincePk, countryPk, statusPk, branchPk, salutation, ) VALUES(PID, 2, '123123123', '123', '66', 1, 1 );");
int n =stmt.executeUpdate();

Now the problem is how do i insert multiple Insert statements using the conn.prepareStatement().should i do like
stmt = conn.prepareStatement("")
int n =stmt.executeUpdate();
stmt = conn.prepareStatement("")
int n =stmt.executeUpdate();
stmt = conn.prepareStatement("")
int n =stmt.executeUpdate(); ..................................

should i use the same steps to execute each individual query and then use executeUpdate().
Please let me know the correct procedure to be followed to execute these tasks.waiting for a positive reply from your side.

Thanks & Regards
Vikram K
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by vikram karne:

I am having Multiple Insert Statements which i need to execute to put the data in the database.


Are you inserting in same table every time? if yes, then you can use addBatch() method of PreparedStatement class, to inster multiple data.

Also use place holders when you create a prepared statment, the code you have posted here,is not suggested way of using prepared Statement




rather you should make a prepared statment like




If you make perparestatement using place holders, then you can resue same.


Thanks,
Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3700
    
    5

No, you do not need to call prepareStatement() every line. If the SQL code to perform the insert is not changing, you can call the same prepareStatement() object multiple times with different parameters such as:



The trick is to replace the data with "?" with the stuff you need changed every line. This code is a lot safer (from SQL injection) and performance than what you had. For completeness, you should really replace every input parameter with ? and use setString/setDouble/etc to set it.


My Blog: Down Home Country Coding with Scott Selikoff
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Use Multiple Insert Statements in Prepared Statements
 
Similar Threads
Auto Increment ID Field Table in the Oracle Database
SQL syntax error
Error inserting byte[] to Oracle BLOB
Class.forName error
About getGeneratedKeys()