Java Programming with Oracle JDBC

Batching

Batching allows you to gather multiple SQL statements for the same PreparedStatement into a batch. The statements in that batch are in turn sent to the database together instead of sent one statement at a time. This reduces the consumption of network bandwidth by eliminating the overhead of redundant packet headers in small packets. Instead, the statements are transmitted in one or more larger packets. Batching also eliminates the extra data required by Oracle to packetize and unpacketize the sent data.

There are two forms of batching available: the JDBC 2.0 standard model and the Oracle proprietary model. You’ll want to use the standard implementation if you are concerned with portability, and use Oracle’s implementation if you want to get the best performance, but you cannot mix the two batching formats. If you do, you’ll get a SQLException.

Warning

Oracle supports batching only for prepared statements. Although it does provide the methods for batching statements and callable statements, it does not actually support batching for them. So if you want to receive any benefit from batching, you must use prepared statements.

Standard Batching Implementation

Taking a look at the big picture, standard batching works as follows. First, you turn off auto-commit and create a prepared statement. Next, you set column values as necessary. Then, instead of calling the executeUpdate( ) method to send the SQL statement to the database immediately, call the addBatch( ) method to add a SQL ...