Performance has always been one of the top concerns of database vendors, and they often go to some lengths to find ways to speed up queries. One of the bottlenecks is the time taken to package up a query, ship it over TCP/IP, and get it into the database where the SQL interpreter can start working on it. In other words, the network latency has a cost.
To reduce the overhead of network latency, many vendors support a way to batch several SQL statements together and send them to the database as a group. You can batch together any statements that have an int return type, which basically means “any SQL statements except for select.” You can see why. You are sending over a group of SQL statements to be executed together, but there is no mechanism defined for getting back the result set for each select. It is not that hard to invent such a mechanism (e.g., executing a batch returns an array of ResultSet), but this has not been done.
To bundle a group of SQL statements in a batch, you create a Statement object as usual:
Statement myStmt = conn.createStatement();
Then, instead of issuing an execute call for the statement, you instead do a series of addBatch(), like this:
myStmt.addBatch( myNonSelectSQL0 );
myStmt.addBatch( myNonSelectSQL1 );
myStmt.addBatch( myNonSelectSQL2 );
Finally, when you are ready to send the whole batch to the database, invoke the executeBatch() method:
int [] res = myStmt.executeBatch();
Batching SQL statements is so easy, there's no reason to avoid it. That will cause all the statements to be sent to the database, and executed as a batch one after the other. The results come back in the form of an array of int, where the ith element holds the row count result of the ith statement in the batch (or zero if it did not return a row count). Support for batches of statements came in with JDBC 2.0.
In Chapter 23 we referred to “transaction integrity” and explained how a fairly common situation required either all of a group of statements to be executed or else none of them. The way you do this is to group the statements in a “transaction.” You execute the transaction in a temporary working area internal to the database. Then, based on other information from your environment, you either “commit” or “rollback” the transaction. Committing the transaction means you let all the data from the working area be copied to the database so your statements have taken effect. A rollback of the transaction means you delete the working area without copying it to the main database so none of your statements affect the database.
Transaction commitment is done through the Connection object. When a JDBC driver starts up, the Connection is in auto-commit mode. That means the Connection automatically commits changes after executing each individual statement. You can turn that off and control when commits or rollbacks are done by invoking this method on your Connection object:
boolean savedCommitValue = conn.getAutoCommit(); // save the current value
conn.setAutoCommit(false); // turn off stmt-by-stmt commits
Then execute as many SQL statements as makes sense for your transaction; these will frequently be grouped in a batch. Look to see if they all completed successfully, and commit the transaction. You can also restore the old setting of autocommit:
int [] res = myStmt.executeBatch();
conn.commit(); // commit the changes
conn.setAutoCommit(savedCommitValue); // restore previous value.
If, however, an SQLException was raised, part of the recovery from that might be to issue a rollback:
conn.rollback(); // drop the partially completed changes.
The statements within a transaction are all the statements that you issued on a given connection since the previous commit() or rollback()Another way of looking at this is to note that Connection and transaction are almost synonymous—you can only have one open transaction per connection. So if you want to update a database concurrently and transactionally, the most practical way can be to use one connection per transaction per thread.