Presents your 
JAVA E-NEWSLETTER for October 24, 2002

<------------------------------------------->

BATCH JDBC STATEMENTS TO INCREASE PROCESSING SPEED

Sometimes, JDBC just isn't running fast enough, which creates the urge
to write a database-specific stored procedure. As an alternative, try
using the batch feature of a Statement to see if executing all the SQL 
at the same time improves things.

The simplest form of a stored procedure is a procedure containing a set
of SQL statements. Putting these statements together helps to manage them
in the same place and also helps to improve speed. The Statement class
has the ability to contain a series of SQL statements, thus allowing the
statements to be executed in one database session and not in a series 
of execution calls to the database.

Using the batch functionality involves two methods:

* The addBatch(String) method
* The executeBatch method

The addBatch method accepts either a normal piece of SQL, if you are
using a Statement, or nothing, if you are using a PreparedStatement.

The executeBatch method then executes the SQL statements and returns an
array of int values. This array contains the number of rows affected by
each statement. Putting a SELECT statement or other ResultSet returning
SQL in a batch will result in a SQLException.

A simple example for java.sql.Statement would be:

Statement stmt = conn.createStatement();
stmt.insert("DELETE FROM Users");
stmt.insert("INSERT INTO Users VALUES('rod', 37, 'circle')");
stmt.insert("INSERT INTO Users VALUES('jane', 33, 'triangle')");
stmt.insert("INSERT INTO Users VALUES('freddy', 29, 'square')");

int[] counts = stmt.executeBatch();

A PreparedStatement is slightly different. It can only handle the one
piece of SQL syntax, but it can have many parameters. So, rewriting 
part of the above example, we get:

// note that we don't do the delete anymore.

PreparedStatement stmt = conn.prepareStatement(
    "INSERT INTO Users VALUES(?,?,?)"
    );

User[ ] users = ...;
for(int i=0; i<users.length; i++) {
    stmt.setInt(1, users[i].getName());
    stmt.setInt(2, users[i].getAge());
    stmt.setInt(3, users[i].getShape());
     stmt.addBatch( );
}

int[ ] counts = stmt.executeBatch();

This is a nice way to handle SQL code in which you don't know how many
times the statement will run. Without batching, if 50 users were added,
the performance may be affected. If someone writes a script to add 10,000
users, it may get nasty. Adding batching helps to improve the performance,
and in the latter case, even improves the readability of the code.

------------------------------------------------------------------------------