Insert into 5 tables.

[Atul Mishra]

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]

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]

Naseem,

Can we use addBatch for inserting into different tables ?

[Govindaraman Jankiraman]

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]

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]

Thank you!!!

[stu derby]

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]

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]

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]

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]

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]

you are not getting my point?

I am taking here two cases:

Case 1: Appropriate use of PreparedStatement

public class Test{
  public static void main(String args[]){
    try {
       // rest code
       String strArray={"str1", "str2", "str3", "str4", "str5"};
       String query="insert into SomeTable values (?)";    
       PreparedStatement pstmt=conn.prepareStatement(query);
       for(String str: strArray) {
         psmt.setString(1, str);
         pstmt.executeUpdate();
       }
     }
     catch(Exception e){}
  }
}

Here PreparedStatement is more advantageous.

IS IT YOUR CASE

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

CASE 2:

package com.bo;
import com.dao.*;
public class SomeBO{
  public void aMethod(){
    SomeDAO dao=new SomeDAO();
    dao.aMethod("SOME USER ENTERED VALUE");
  }
}
 
----------------------------------------------------
 
package com.dao;
// import statement here
public class SomeDAO {
  public void aMethod(String str){
    try {
      // rest code
      stmt.addBatch(insert_query1 taking str value);
      stmt.addBatch(insert_query2 taking str value);
      stmt.addBatch(insert_query3 taking str value);
      stmt.addBatch(insert_query4 taking str value);
      stmt.addBatch(insert_query5 taking str value);
      stmt.executeUpdate();
    }
  }
}

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]

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]

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]

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]

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]

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