Prepared statement pooling (JDBC 3.0)

18 April 2008

JDBC 3.0 provides improved connection pooling. This post is all about that.

It is also possible to pool prepared statements. A prepared statement allows us to keep frequentry used SQL statement in a pre-compile form, thus improving performance if that statement is executed multiple times. But there is a dark side of this. Creating a PreparedStatement object introduces a certain amount of overhead. There are some developers sometimes change their object models to increase the lifetime of a PreparedStatement object. Good thing is that JDBC 3.0 frees the developer from this, by making data source layer responsible for caching prepared statements.

The code snippet shows how to take advantage of JDBC’s prepared statement pooling support.

String INSERT_BOOK_QUERY = "INSERT INTO BOOKLIST " +
                           "(AUTHOR, TITLE) " +
                           "VALUES (?, ?) ";
Connection conn = aPooledConnection.getConnection();
PreparedStatement ps = conn.prepareStatement(INSERT_BOOK_QUERY);
ps.setString(1, "Orwell, George");
ps.setString(2, "1984");
ps.executeUpdate();
ps.close();
conn.close();
 
// ...
 
conn = aPooledConnection.getConnection();
// Since the connection is from a PooledConnection, the data layer has
// the option to retrieve this statement from its statement pool,
// saving the VM from re-compiling the statement again.
PreparedStatement cachedStatement =
conn.prepareStatement(INSERT_BOOK_QUERY);
// ...