aspose file tools*
The moose likes JDBC and the fly likes can a batch have multiple prepared statements Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "can a batch have multiple prepared statements" Watch "can a batch have multiple prepared statements" New topic
Author

can a batch have multiple prepared statements

manish ahuja
Ranch Hand

Joined: Oct 23, 2003
Posts: 312
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
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

no. But you can batch different SQL updates using the Statement interface.
Jamie
Anurag Gupta
Ranch Hand

Joined: Dec 13, 2001
Posts: 40
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.


Anurag Gupta
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

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
 
jQuery in Action, 2nd edition
 
subject: can a batch have multiple prepared statements