Statement Caching

Statement caching improves performance by caching statements that are used repeatedly. Statements can be cached only when created through a com.nuodb.jdbc.DataSource object. When the connection is created via java.sql.DriverManager.getConnection(), in other words through the NuoDB JDBC Driver, statement pooling is not available.The prepared and callable statements are cached and retrieved using standard connection object and statement object methods.

When a statement is created, the cache is searched for a matching statement based on the following criteria:

  1. it has the same associated Connection object
  2. it has the same type: statement, prepared statement, or callable statement
  3. it has the same SQL string (case-sensitive)
  4. the number of auto generated keys returned by this Statement object is the same
  5. the array of indexes for columns used by this Statement object is the same
  6. the array of column names used by this Statement object is the same

See DataSource versus Driver Connections about how to create a DataSource object to use for statement caching. In addition to the properties above, the following two properties apply to statement caching.

Property Description Default
maxStatements The number of statements cached for all connections in the connection pool. 0
maxStatementsPerConnection The number of statements cached for a single pooled connection. If this property is non-zero, maxStatements will not be enforced. 0

So if for example you have:

properties.put("maxStatements", "1000");
properties.put("maxStatementsPerConnection", "20");

This would mean that you can have a total of 1000 statements cached globally, but only 20 per connection in the pool.

Statement Caching Example

Properties properties = new Properties();
// ...
// add other data source properties here such as connection url, valid username, password
properties.put("maxStatementsPerConnection", 100);
javax.sql.DataSource dataSource = new com.nuodb.jdbc.DataSource(properties);
// obtain new connection from the connection pool
java.sql.Connection connection = dataSource.getConnection();
// here, behind the scenes, a new physical statement is created and a statement proxy is returned
PreparedStatement statement1 = connection.prepareStatement("select * from system.tables");
ResultSet resultSet = statement1.executeQuery();
// the statement proxy does not close the physical statement, but returns it to the 
// statement pool instead
PreparedStatement statement2 = connection.prepareStatement("select * from system.tables");
// here statement2 is statement proxy, it wraps the already created physical statement 
// which is borrowed from the statement pool
// note, the connection reference is also a connection proxy, which doesn’t close the 
// physical connection and only returns that connection to the connection pool and puts 
// the prepared statement back into to the statement pool