Accessing the Network Server by using a DataSource object

Using statement caching

Derby supports JDBC statement caching, which can improve the performance of applications that use PreparedStatement or CallableStatement objects. Statement caching avoids the performance penalty incurred by going over the network from the client to the server to prepare a statement that has already been prepared on the same connection.

To use statement caching, you must use an org.apache.derby.jdbc.ClientConnectionPoolDataSource, org.apache.derby.jdbc.ClientConnectionPoolDataSource40, or org.apache.derby.jdbc.BasicClientConnectionPoolDataSource40 object. After you instantiate this object, perform these steps:

1. Specify the desired size of your statement cache by calling the setMaxStatements method on the DataSource object, specifying an argument greater than zero.
2. Call the getPooledConnection method on the DataSource object to obtain a javax.sql.PooledConnection object (a physical connection).
3. Call the javax.sql.PooledConnection.getConnection method to obtain a java.sql.Connection object (a logical connection).

After you obtain a connection, use either prepared statements or callable statements to interact with the database. Close each statement to return it to the cache after you finish using it. The statements you create are held in the cache on the client side and reused when needed.

See Statement caching example for a code example.

Use of the JDBC statement cache makes 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.

If you enable JDBC statement caching, error handling changes slightly. Some errors that previously appeared when the prepareStatement method was executed may now appear during statement execution. For example, suppose you query a table using a prepared statement that is then cached. If the table is deleted, the prepared statement that queries the table is not invalidated. If the query is prepared again on the same connection, the cached object is fetched from the cache, and the prepareStatement call seems to have succeeded, although the statement has not actually been prepared. When the prepared statement is executed, the error is detected on the server side, and the client is notified.