Java - how to batch database inserts and updates

问题:

I want to batch up multiple types of database calls in one PreparedStatement. Is this possible?

Is there anyway to do something like

PreparedStatement pstmt = connection.prepareStatement("?");

where the ? can either be INSERT INTO MY_TABLE VALUES(1,2,3,4) or it could be UPDATE MY_TABLE, SET MY_VAL='1' WHERE MY_VAL IS NULL

Or do I always need to specify a table and action for my prepared statement?

回答1:

Java will not allow you add only ? in preparedstatement string parameter, as it expects the ? for the place holder only for the parameters to the give SQL.

For your case, you may have to have 2 prepared statement objects, and in loop through, you can make a decision which one to call. So it would be something like below:

PreparedStatement insertPstmt = connection.prepareStatement("INSERT INTO MY_TABLE VALUES(?,?,?,?)");
PreparedStatement updatePstmt = connection.prepareStatement("UPDATE MY_TABLE, SET MY_VAL=? WHERE MY_VAL IS NULL");

While (<condition>) {
  If (<insert condition>) {
    // use insert pstmt and add batch
  } else {
    // use update pstmt and add batch
  }
}

insertPstmt.executeBatch(); 
updatePstmt.executeBatch();

if you have any insert , which has dependency on the update, you might execute the batches accordingly. This will make sure that the update will work correctly. I would think of executing insert first, as they might not depend on update.

回答2:

On a PreparedStatement, after binding the variables for the first execution, call

pstmt.addBatch();

then bind the variables for the next, and each time calling addBatch(). Then, when you're done adding batches you execute the bacth by alling

pstmt.executeBatch();

See :

http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#addBatch%28%29

and

http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#executeBatch%28%29

BTW : injecting the entire statment into a variable won't work. This batch mechanism exists to reuse the same statement binding different variables each time.

回答3:

Insert and Update commands don't return any data that has to be processed. If you want to do just things like in your examples, you can simply run a non executing query command and provide a concatenated string of all your sql strings separated by a semicolon.

"INSERT INTO MY_TABLE VALUES(1,2,3,4)" + ";"  +"UPDATE MY_TABLE, SET MY_VAL='1' WHERE MY_VAL IS NULL" + ";" +...

You don't need to prepare the statement in that case and also wouldn't receive any performance gain by doing so.