• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Insert into 5 tables.

 
Ranch Hand
Posts: 140
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Ranch Hand
Posts: 809
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Atul Mishra
Ranch Hand
Posts: 140
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Naseem,

Can we use addBatch for inserting into different tables ?
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 809
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 140
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you!!!
 
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 140
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 809
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 140
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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 ?
 
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 809
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 333
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 333
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator




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
Posts: 809
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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
Posts: 140
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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

 
reply
    Bookmark Topic Watch Topic
  • New Topic