A cache for JDBC statement objects.
A key representing a
A factory for creating JDBC statement keys for use with the JDBC statement cache.
Core client side JDBC statement cache implementation files. The purpose of the client side JDBC statement cache is primarily to avoid the performance penalty of going over the network from the client to the server when preparing a statement that has already been prepared on the same connection.
The core cache implementation has three responsibilities:
The current implementation stores either PreparedStatements or CallableStatements in the cache. A key is computed for each item, and there can only by one instance of an item per key.
When a statement is prepared, the cache is consulted to see if there is already a statement available. All statements in the cache are by design free and available for use. A key for the statement is computed to do the lookup. If there is a statement available, it is taken out from the cache. When the client is done with the statement, the cache is consulted again to see if an identical statement (with the same key) has been inserted. If so, the statement is closed. If not, it is reinserted into the cache. This procedure allows us to use the LRU algorithm for eviction.
A LRU (least recently used) algorithm is used to select which item to throw out. When a statement is thrown out, it is permanently closed and all references to the object are released.
When the JDBC cache is enabled, there is one difference in behavior. Some errors that were previously encountered during the prepare call, might now happen during execute. As an example, consider a statement referring a deleted table.
Say table A is created and populated, then queried using a prepared statement. This statement will be cached. If table A is later deleted, the prepared query referring to it will not be invalidated. If the query is prepared again on the same connection, the cached object will be fetched from the cache and the prepare seems to have succeeded (it actually hasn't been performed). When the prepared statement is executed, the error is detected on the server side and the client is notified.
In order to allow for improved performance from using the JDBC statement cache, there are three requirements:
Using the JDBC statement cache will make each physical connection use more memory. The amount depends on how many statements the connection is allowed to cache and how many statements are actually cached.
Currently, the JDBC statement cache can only be enabled by using ClientConnectionPoolDataSource. Sample code:
ClientConnectionPoolDataSource cpds = new ClientConnectionPoolDataSource(); // Set the number of statements the cache is allowed to cache. // Any number greater than zero will enable the cache. cpds.setMaxStatements(20); // This physical connection will have JDBC statement caching enabled. PooledConnection pc = cpds.getPooledConnection(); // Create a logical connection. Connection con = pc.getConnection(); // Interact with the database. PreparedStatement ps = con.prepareStatement( "select * from myTable where id = ?"); ... ps.close(); // Inserts/returns statement to the cache ... con.close(); // The next logical connection can gain from using the cache. con = pc.getConnection(); // This prepare will cause a statement to be fetched from the local cache. PreparedStatement ps = con.prepareStatement( "select * from myTable where id = ?"); ... // This disposes of the cache. pc.close();
The client XA connection pool reuses the same code as the connection pool data source, and enabling the JDBC statement cache for XA connections is a matter of changing a few lines of code. However, it has not been investigated whether the XA aspect requires something more with regards to statement caching.
The main JIRA issue for the client side JDBC statement cache is DERBY-3313.
Most important classes interacting with the statement cache (directly or indirectly):