Setting up the statement pool
Prepared statement pools operate on a per-connection basis. If one thread in a program pulls
a JDBC connection out of the pool and uses a prepared statement on that connection, the in-
formation associated with the statement will be valid only for that connection. A second
thread that uses a second connection will end up establishing a second pooled instance of the
prepared statement. In the end, each connection object will have its own pool of all the pre-
pared statements used in the application (assuming that they are all used over the lifetime of
This is one reason why a standalone JDBC application should use a connection pool (JPA
transparently creates a connection pool for Java SE programs, or uses a connection pool from
the application server when used in a Java EE environment). It also means that the size of the
connection pool matters (to both JDBC and JPA programs). That is particularly true early in
the program's execution: when a connection that has not yet used a particular prepared state-
ment is used, that first request will be a little slower.
The size of the connection pool also matters because it is caching those prepared statements,
which take up heap space (and often a lot of heap space). Object reuse is certainly a good
thing in this case, but you must be aware of how much space those reusable objects take up
and make sure it isn't negatively affecting the GC time.
Managing statement pools
The second thing to consider about the prepared statement pool is what piece of code will ac-
tually create and manage the pool. Prepared statement pooling was introduced in JDBC 3.0,
which provides a single method (the setMaxStatements() method of the Connec-
tionPoolDataSource class) to enable or disable statement pooling. Statement pooling is
disabled if the value passed to the setMaxStatements() method is 0. That interface spe-
cifically does not define where the statement pooling should occur—whether in the JDBC
driver or some other layer, such as the application server. And that single interface is insuffi-
cient for some JDBC drivers, which require additional configuration.
So, when writing a Java SE application that uses JDBC calls directly, there are two choices:
either the JDBC driver must be configured to create and manage the statement pool, or the
pool must be created and managed within the application code. Java EE applications have
two (slightly different) possibilities: the JDBC driver can create and manage the pool, or the
application server can create and manage the pool.
The tricky thing is that there are no standards in this area. Some JDBC drivers do not provide
a mechanism to pool statements at all; they expect to be used only within an application serv-