aspose file tools*
The moose likes JDBC and the fly likes Insert into 5 tables. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Insert into 5 tables." Watch "Insert into 5 tables." New topic
Author

Insert into 5 tables.

Atul Mishra
Ranch Hand

Joined: Jun 08, 2006
Posts: 140
All,

From the data I get from the JSP, I have to insert values into 5 different tables.
Whats the best way to do it ?

have 5 insert statements in the code or have a stored procedure in the back and call it, or ..
Please assist.

All inputs appreciated.

Thanks
Naseem Khan
Ranch Hand

Joined: Apr 25, 2005
Posts: 809
You can use stored procedure as it will make less databse hit.

Why don't you try addBatch(String sql) and executeBatch() method defined in Statement.

Naseem


Asking Smart Questions FAQ - How To Put Your Code In Code Tags
Atul Mishra
Ranch Hand

Joined: Jun 08, 2006
Posts: 140
Naseem,

Can we use addBatch for inserting into different tables ?
Govindaraman Jankiraman
Greenhorn

Joined: Jun 28, 2006
Posts: 1
Atul,

Yes. You can use addBatch for inserting into N tables.

Ex:
stmt.addBatch("INSERT INTO employees VALUES (1000, 'Joe Jones')");
stmt.addBatch("INSERT INTO departments VALUES (260, 'Shoe')");
stmt.addBatch("INSERT INTO emp_dept VALUES (1000, 260)");
Naseem Khan
Ranch Hand

Joined: Apr 25, 2005
Posts: 809
Yes you can do as Govindaraman mentioned

check it here...

http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/statement.html

Regards

Naseem
[ June 28, 2006: Message edited by: Naseem Khan ]
Atul Mishra
Ranch Hand

Joined: Jun 08, 2006
Posts: 140
Thank you!!!
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
On some databases (such as Oracle), the only performance difference when using batchng in this particular way will be the savings in network transfers (1 transfer versus 5). That can be a significant saving in some situations, particularly bulk loading of data, but it won't matter much at all in others such as many web applications.

However, with Oracle, you would be losing all that performance savings, and much much more, if you didn't use PreparedStatement.
Atul Mishra
Ranch Hand

Joined: Jun 08, 2006
Posts: 140
Saw your quote stu derby.
Now the situation is becoming a bit more worse.

Inserting into multiple tables

1. Table A is a parent and all others are child records.
Said that, there can be more than 1 records for Tables b,c,d,e

Inserts will be
insert into A values();
insert into B values();//can be more depending on user selecting
insert into B values();
......


insert into C values() // can be more depending on how much user selecting
insert into C values()
.....


Whats the best approach to handle this ? Please suggest.

Thanks.
Naseem Khan
Ranch Hand

Joined: Apr 25, 2005
Posts: 809
PreparedStatement is useful only if your query parameters are changing.

For example, if you want to insert into the same table multiple user entered values. If this is the case, then you can go for PreparedStatement and the advantage which you get is that the query is compiled just once not the every time.

But your case is different. You are having just one user selection and multiple queries so I don't think you will get any advantage using PreparedStatement.


Naseem
[ June 30, 2006: Message edited by: Naseem Khan ]
Atul Mishra
Ranch Hand

Joined: Jun 08, 2006
Posts: 140
Ok. Thanks for the comment abt Preparedtstatements.

Now the question is, did you get a chance to see the situation I described above

Table A inserting is first step
In second step I have to insert multiple records into table b, similiarly in tables c,d,e.

Whats the best way here ?
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18113
    
    8

When you say "What's the best way", do you have a list of different ways for us to choose from? Or do you just not know how to do it at all, and you need help with that?
Naseem Khan
Ranch Hand

Joined: Apr 25, 2005
Posts: 809
you are not getting my point?

I am taking here two cases:

Case 1: Appropriate use of PreparedStatement



Here PreparedStatement is more advantageous.

IS IT YOUR CASE

As per your post I think your case is something like this...

CASE 2:




If your case is second, then use batch update or stored procedure but preparedstatement is not correct here.

But if your case is first, then go for PreparedStatement.

Third Case: If your case is combination of both, then you should take best of two. Obivously you have to do some compromise.


Naseem
[ June 30, 2006: Message edited by: Naseem Khan ]
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by Naseem Khan:
PreparedStatement is useful only if your query parameters are changing.


Not true.
1) PreparedStatement helps you avoid SQL injection attacks
2) PreparedStatement helps you avoid implicit data conversions in the database
3) PreparedStatement helps you avoid problems with string quoting (Try to save the name "O'Reilly" using a Statement - you have to escape the embedded quote!)

And on Oracle (and some other databases), the database maintains a cache of parsed SQL statemens it's seen "recently". If it sees the exact same SQL string text again (as determined by a hash match), it can avoid reparsing the SQL. SQL parsing can be 90% or more of the work of a simple insert statement; therefore, if there's any chance at all that the statment will be resubmitted with different values, then PreparedStatement is the only way to go.
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333



1. Table A is a parent and all others are child records.
Said that, there can be more than 1 records for Tables b,c,d,e

Inserts will be
insert into A values();
insert into B values();//can be more depending on user selecting
insert into B values();
......


insert into C values() // can be more depending on how much user selecting
insert into C values()
.....


On an Oracle database, ideally you use a different batch of PreparedStatements for each table. If the number of repetitions is small, say 1 or 2, then maybe the extra work of multiple batches won't be work it. If the average number of repetitions per table gets to be much more than that, then on an Oracle database, using a different batch for each table is well worth the extra coding effort.
Naseem Khan
Ranch Hand

Joined: Apr 25, 2005
Posts: 809

Quote by me:

PreparedStatement is useful only if your query parameters are changing.




Quote from stu derby:

if there's any chance at all that the statment will be resubmitted with different values, then PreparedStatement is the only way to go.



Is there any difference between the two. Both statments are same. What I mean from "query parameter is changing" is same query with different parameter, then only PreparedStatement is useful.


I don't think there is any difference between the two?


Thanks & Regards

Naseem
Atul Mishra
Ranch Hand

Joined: Jun 08, 2006
Posts: 140
Naseem and Stu,

Thanks for all replies. was on vacation.

I did like this for insering into different tables

PreparedStatement ps1 = null;
PreparedStatement ps2 = null;
PreparedStatement ps3 = null; // etc

and ps1.executeUpdate(), for ps2 and ps3 I did it in a for loop.

ps2,ps3 etc I have multiple values stored in a Hashtable. I iterated thru the hashtable with a for loop and did ps2. executeUpdate() each time. Before all, I set conn.setAutocommit as false also.

Thanks
Joe Keller
Greenhorn

Joined: Jan 08, 2013
Posts: 1
JDBC cannot insert into different tables using a PreparedStatement. If you don't use a PreparedStatement, then server cost increases to re-prepare the statements. To avoid these issues, DB2 and pureQuery came up with a way to batch statements that target different tables. Using pureQuery one can send a batch in 1 network trip and still take advantage of PreparedStatements. Google for pureQuery API to learn more.



Atul Mishra wrote:All,

From the data I get from the JSP, I have to insert values into 5 different tables.
Whats the best way to do it ?

have 5 insert statements in the code or have a stored procedure in the back and call it, or ..
Please assist.

All inputs appreciated.

Thanks
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Insert into 5 tables.
 
Similar Threads
how to decide how many entity beans to have
how to Join Multiple tables
One call vs Many for SOAP
problem with bulk update and auto_increment
EJB 3.0 insert data to more than 2 tables with one transaction