Can commits be automatically collected into a batch?
Is there a way to automatically put multiple updates into batches? For example, transform this program:
PreparedStatement prepStmt 开发者_运维百科= con.prepareStatement("UPDATE DEPT SET MGRNO=? WHERE DEPTNO=?");
prepStmt.setString(1,mgrnum1);
prepStmt.setString(2,deptnum1);
prepStmt.executeUpdate();
prepStmt.setString(1,mgrnum2);
prepStmt.setString(2,deptnum2);
prepStmt.executeUpdate();
con.commit();
into this program:
connection con.setAutoCommit(false);
PreparedStatement prepStmt = con.prepareStatement(
"UPDATE DEPT SET MGRNO=? WHERE DEPTNO=?");
prepStmt.setString(1,mgrnum1);
prepStmt.setString(2,deptnum1);
prepStmt.addBatch();
prepStmt.setString(1,mgrnum2);
prepStmt.setString(2,deptnum2);
prepStmt.addBatch();
int [] numUpdates=prepStmt.executeBatch();
for (int i=0; i < numUpdates.length; i++) {
if (numUpdates[i] == -2)
System.out.println("Execution " + i +
": unknown number of rows updated");
else
System.out.println("Execution " + i +
"successful: " numUpdates[i] + " rows updated");
}
con.commit();
Could this transformation be done by a compiler automatically? What work has been done on this?
Thanks, a database noob.
A compiler could do this transformation but I know no compiler that does.
Potentially you could use (or code yourself) something like Spring's JDBCTemplate to give you some templated behaviour (such as commit at the end of some batches), but as @Peter G. states it's not the compiler that would do automatically do this, it's your (or a third party) code.
AOP is another option here if you're really looking to implement this.
精彩评论