PreparedStatement batching with different SQL statements

1
votes

I'm wondering if something is possible in Java

String A = "UPDATE blah set x=? y=? z=?"
String B = "UPDATE blah set a=? b=? c=? d=?"

I'd like to have a prepared statement primarily for the speed, secondarily for the security. I want to be able to fill out the bind variables for A, execute A, bind variables for B, and execute B, then commit the entire transaction. Is there a better way to accomplish this?

3
javasqloracle
Potentially other than stored procedures on the server? Not really. Please note that prepared statements only really show performance benefits if they're going to be executed multiple times (they're always a gain security-wise, assuming they're constructed correctly), with different bound parameters. If this is a web-app, I'd probably recommend doing the PREPARE during bean instantiation - don't do it on every service call. – Clockwork-Muse
Besides my suggestions in my answer below (either use anonymous PL/SQL block or use clearBatch and addBatch methods), I'm wondering why you don't just use more than one prepared statement? What kind of app is it that this is for? If you're thinking about performance, would statement pooling be something you would consider? There's some info on statement pooling at this link, about halfway down the page... ibm.com/developerworks/java/library/j-jdbcnew – Jim
a preparedStatement batch always works off the same "prepared" statement publib.boulder.ibm.com/infocenter/iseries/v5r3/… – LiuYan 刘研
Has your question been answered? Is there anything that could be further clarified? – Jim

3 Answers

1
votes

Are you sure you can't achieve the same thing with just one statement:

UPDATE blah set x=?, y=?, z=?, a=?, b=?, c=?, d=?

0
votes

You can utilize multiple PreparedStatements for the desired result:

// Prepare code for PreparedStatement #1
String varOne = "A";
String varTwo = "B";
String varThree = "C";

String queryOne = "UPDATE blah set x=? y=? z=?"
PreparedStatement firstStmt = conn.prepareStatement(queryOne);

firstStmt.setString(1, varOne);
firstStmt.setString(2, varTwo);
firstStmt.setString(3, varThree);

firstStmt.executeUpdate();
conn.commit();

// Prepare code for PreparedStatement #2
String varOneB = "X";
String varTwoB = "Y";
String varThreeB = "Z";
String varFourB = "A";

String queryOne = "UPDATE blah set a=? b=? c=? d=?"
PreparedStatement secondStmt = conn.prepareStatement(queryTwo);

secondStmt.setString(1, varOneB);
secondStmt.setString(2, varTwoB);
secondStmt.setString(3, varThreeB);

secondStmt.executeUpdate();
conn.commit();
0
votes

You can create a string that has an anonymous PL/SQL block that runs both update statements in the same transaction, and use that string with a PreparedStatement to set bind variables.

I know you said you want to 1) fill out bind variables for A, 2) execute A, 3) fill out bind variables for B, 4) execute B, 5) commit transaction.

My suggestions doesn't exactly follow that order. I'm suggesting that you have one big PL/SQL anonymous block that contains the statements for A and B. You'd fill out all bind variables for the entire block before executing. You can control whether to commit / rollback or whatever other logic you want (including more complex exception handling) from within the PL/SQL block.

This is basically like having a stored procedure that you don't store in the database. It's honestly my preferred method for things that don't need to be available outside the context of some single bit of code. So your business logic isn't kept in the database (which many will argue for hours over whether that's a good thing or bad thing; I personally hate business logic at the database level, but it depends on your project).

Also, look at documentation for Statement, which PreparedStatement inherits from.

http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html

You can call clearBatch and addBatch if you really want to use the same PreparedStatement object to execute one SQL statement, then swap out the SQL and execute a completely different statement.