Statement caching example

The following example uses
org.apache.derby.jdbc.ClientConnectionPoolDataSource to
access the Network Server and use JDBC statement caching:

org.apache.derby.jdbc.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);
// Set other DataSource properties
cpds.setDatabaseName("mydb");
cpds.setCreateDatabase("create");
cpds.setUser("user");
cpds.setPassword("mypass");
cpds.setServerName("localhost");
cpds.setPortNumber(1527);
// This physical connection will have JDBC statement caching enabled.
javax.sql.PooledConnection pc = cpds.getPooledConnection();
// Create a logical connection.
java.sql.Connection con = pc.getConnection();
// Interact with the database.
java.sql.PreparedStatement ps = con.prepareStatement(
"select * from myTable where id = ?");
...
ps.close(); // Inserts or returns statement to the cache
...
con.close();
// The next logical connection can gain from using the cache.
con = pc.getConnection();
// This prepare causes a statement to be fetched from the local cache.
PreparedStatement ps = con.prepareStatement(
"select * from myTable where id = ?");
...
// To dispose of the cache, close the connection.
pc.close();