Statement Caching

Using Statement Caching

This section discusses the following topics:

Enabling and Disabling Statement Caching

You can dynamically enable and disable statement caching. Enable statement caching by calling the setStmtCacheSize() method of your connection object, setting the cache to a value greater than zero. The cache size you specify is the maximum number of statements in the cache.

The setStmtCacheSize() method has the following signatures:

setStmtCacheSize (int size)
setStmtCacheSize (int size, boolean clearMetaData)

The size parameter sets the maximum number of statements in the cache. The optional clearMetaData parameter allows you to specify if metadata is to be cleared when the cached statement is retrieved.

Notes:

When you enable or disable statement caching for a particular physical connection, you do this for both implicit and explicit statement caching. Therefore, it is possible to do statement caching both implicitly and explicitly during the same session.

Implicit and explicit statement caching share the same cache. Therefore, consider this when you set the statement cache size

The following code specifies a cache size of ten statements:

((OracleConnection)conn).setStmtCacheSize(10);

Disable statement caching by calling the setStmtCacheSize() method and setting the cache to zero. To check the cache size, use the getStmtCacheSize() method.

The following code disables statement caching by setting the cache size to zero. Additionally, it verifies the new cache setting in the last line of code.

((OracleConnection)conn).setStmtCacheSize(0);
System.out.println("Stmt Cache size is " +
   ((OracleConnection)conn).getStmtCacheSize());

Checking for Statement Creation Status

By calling the creationState() method of a statement object, you can determine if a statement was newly created or if it was retrieved from cache on an implicit or explicit lookup. The creationState() method returns the following integer values for plain, prepared, and callable statements:

NEW - The statement was newly created.
IMPLICIT - The statement was retrieved on an implicit statement lookup.
EXPLICIT - The statement was retrieved on an explicit statement lookup.

For example, the JDBC driver returns OracleStatement.EXPLICIT for an explicitly cached statement. The following code checks the statement creation status for stmt:

int state = ((OracleStatement)stmt).creationState()
   ...(process state)

Physically Closing a Cached Statement

With statement caching enabled, you cannot truly physically close statements manually. The close() method of a statement object caches the statement instead of closing it. The statement is physically closed automatically under one of three conditions: (1) when the associated connection is closed, (2) when the cache reaches its size limit and the least recently used statement object is preempted from cache by the LRU scheme, or (3) if you call the close() method on a statement for which statement caching is disabled. (See "Disabling Implicit Statement Caching for a Particular Statement" for more details.)

Using Implicit Statement Caching

Once you enable statement caching, by default all prepared and callable statements are automatically cached. Implicit statement caching includes the following steps:

Enable statement caching as described in "Enabling and Disabling Statement Caching".
Allocate a statement using one of the standard methods.
Optionally disable implicit statement caching for any particular statement you do not want to cache.
Cache the statement using the close() method.
Retrieve the implicitly cached statement by calling the appropriate standard "prepare" method.

For a complete code example of implicit statement caching, see "Implicit Statement Caching--StmtCache1.java". The following sections explain the implicit statement caching steps in more detail.

Allocating a Statement for Implicit Caching

To allocate a statement for implicit statement caching, use either the prepareStatement() or prepareCall()method as you would normally. (These are methods of the connection object.)

The following code allocates a new statement object called pstmt:

PreparedStatement pstmt = conn.prepareStatement
   ("UPDATE emp SET ename = ? WHERE rowid = ?");

Disabling Implicit Statement Caching for a Particular Statement

With statement caching enabled for a connection, by default all callable and prepared statements of that connection are automatically cached. To prevent a particular callable or prepared statement from being implicitly cached, use the setDisableStmtCaching() method of the statement object. To help you manage cache space, you can call the setDisableStmtCaching() method on any infrequently used statement.

The following code disables implicit statement caching for pstmt:

PreparedStatement pstmt = conn.prepareStatement ("SELECT 1 from DUAL");
((OraclePreparedStatement)pstmt).setDisableStmtCaching (true);
pstmt.close ();

Implicitly Caching a Statement

To cache an allocated statement, call the close() method of the statement object. When you call the close() method on an OraclePreparedStatement or OracleCallableStatement object, the JDBC driver automatically puts this statement in cache, unless you have disabled caching for this statement.

The following code caches the pstmt statement:

((OraclePreparedStatement)pstmt).close ();

Retrieving an Implicitly Cached Statement

To recall an implicitly cached statement, call either the prepareStatement() or prepareCall()method, depending on the statement type.

The following code recalls pstmt from cache using the prepareStatement() method:

pstmt = conn.prepareStatement ("UPDATE emp SET ename = ? WHERE rowid = ?");

If you call the creationState() method on the pstmt statement object, the method returns IMPLICIT. If the pstmt statement object was not in cache, then the creationState() method returns NEW to indicate a new statement was recently created by the JDBC driver.

Table 14-2 describes the methods used to allocate statements and retrieve implicitly cached statements.

Table 14-2 Methods Used in Statement Allocation and Implicit Statement Caching

Method	Functionality for Implicit Statement Caching
`prepareStatement()`	triggers a cache search that either finds and returns the desired cached `OraclePreparedStatement` object or allocates a new `OraclePreparedStatement` object if a match is not found
`prepareCall()`	triggers a cache search that either finds and returns the desired cached `OracleCallableStatement` object or allocates a new `OracleCallableStatement` object if a match is not found

Using Explicit Statement Caching

A plain, prepared, or callable statement can be explicitly cached when you enable statement caching. Explicit statement caching includes the following steps:

Enable statement caching as described in "Enabling and Disabling Statement Caching".
Allocate a statement using one of the standard methods.
Explicitly cache the statement by closing it with a key, using the closeWithKey() method.
Retrieve the explicitly cached statement by calling the appropriate Oracle "WithKey" method, specifying the appropriate key.
Re-cache an open, explicitly cached statement by closing it again with the closeWithKey() method. Each time a cached statement is closed, it is re-cached with its key.

For a complete code example of explicit statement caching, see "Explicit Statement Caching--StmtCache2.java". The following sections explain the explicit statement caching steps in more detail.

Allocating a Statement for Explicit Caching

To allocate a statement for explicit statement caching, use either the createStatement(), prepareStatement(), or prepareCall() method as you would normally. (These are methods of the connection object.)

The following code allocates a new statement object called pstmt:

PreparedStatement pstmt =
   conn.prepareStatement ("UPDATE emp SET ename = ? WHERE rowid = ?");

Explicitly Caching a Statement

To explicitly cache an allocated statement, call the closeWithKey() method of the statement object, specifying a key. The key is an arbitrary Java string that you provide. The closeWithKey() method caches a statement as is. This means the data, state, and metadata are retained and not cleared.

The following code caches the pstmt statement with the key "mykey":

((OraclePreparedStatement)pstmt).closeWithKey ("mykey");

Retrieving an Explicitly Cached Statement

To recall an explicitly cached statement, call either the createStatementWithKey(), prepareStatementWithKey(), or prepareCallWithKey() method, depending on the statement type.

If you retrieve a statement with a specified key, the JDBC driver searches the cache for the statement, based on the specified key. If a match is found, the matching statement is returned, along with its state, data, and metadata. This information is returned as it was when last closed. If a match is not found, the JDBC driver returns null.

The following code recalls pstmt from cache using the "mykey" key with the prepareStatementWithKey() method. Recall that the pstmt statement object was cached with the "mykey" key.

pstmt = ((OracleConnection)conn).prepareStatementWithKey ("mykey");

If you call the creationState() method on the pstmt statement object, the method returns EXPLICIT.

Important:
When you retrieve an explicitly cached statement, be sure to use the method that is appropriate for your statement type when specifying the key. For example, if you used the prepareStatement() method to allocate a statement, then use the prepareStatementWithKey() method to retrieve that statement from cache. The JDBC driver cannot verify the type of statement it is returning.

Table 14-3 describes the methods used to retrieve explicitly cached statements.

Table 14-3 Methods Used to Retrieve Explicitly Cached Statements

Method	Functionality for Explicit Statement Caching
`createStatementWithKey()`	specifies the key needed to retrieve a plain statement from cache
`prepareStatementWithKey()`	specifies the key needed to retrieve a prepared statement from cache
`prepareCallWithKey()`	specifies the key needed to retrieve a callable statement from cache