can a batch have multiple prepared statements

[manish ahuja]

Hi All
is it possible to have a multiple prepared statements in a batch.
or we can have only one.

normally we have something like this in a batch
PreparedStatement pstmt = null;
pstmt = conn.prepareStatement("insert into emp(empno,ename) values(?,?)");
pstmt.setLong(1,1000);
pstmt.setString(2,"Sam");
pstmt.addBatch();
pstmt.setLong(1,1002);
pstmt.setString(2,�Steve");
pstmt.addBatch(); ...
int results[] = pstmt.executeBatch();

PreparedStatement pstmt = null;
PreparedStatement pstmt1 = null;
pstmt = conn.prepareStatement("insert into emp(empno,ename) values(?,?)");
pstmt.setLong(1,1000);
pstmt.setString(2,"Sam");
pstmt.addBatch();
pstmt1 = conn.prepareStatement("insert into dept(deptno,dname) values(?,?)");
pstmt.setLong(1,10);
pstmt.setString(2,�hrd");
pstmt.addBatch(); ...

can i have somthing like this
Rgrds
Manish

[Jamie Robertson]

no. But you can batch different SQL updates using the Statement interface.
Jamie

[Anurag Gupta]

You can use Statements for various queries to add in the batch. However 2 things to be considered :
1) Performance while using Statements.
2) If there are any user inputs (in the query) from the front end, like posting a HTML Form. Then there is a possibility of SQL Injection,and the application security can be compromised. SQL Injection is easily possible if u r using Statements, but not so easy with PreparedStatement.

[Jamie Robertson]

Originally posted by Anurag Gupta:
You can use Statements for various queries to add in the batch. However 2 things to be considered :
1) Performance while using Statements.
2) If there are any user inputs (in the query) from the front end, like posting a HTML Form. Then there is a possibility of SQL Injection,and the application security can be compromised. SQL Injection is easily possible if u r using Statements, but not so easy with PreparedStatement.

Not sure what you are saying for consideration 1. Performance is largely dependent on the driver implementation. For Performance using the Oracle drivers, have a look at Java Programming with Oracle JDBC, Chapter 19 - Performance. Even if you are not using Oracle's driver/database, it is a good benchmark for JDBC performance in a general sense as well.
regarding consideration 2, this is more a product of sloppy programming practices, than a deficiency in the Statement/JDBC implementations. If you are wondering what SQL injection is, have a read of Application-Level Attacks ( on Oracle ).
Jamie