Oracle8i JDBC Developer's Guide and Reference Release 3 (8.1.7) Part Number A83724-01 |
Library | Service | Contents | Index

## Using Statement Caching

This section discusses the following topics:

- Enabling and Disabling Statement Caching

- Checking for Statement Creation Status

- Physically Closing a Cached Statement

- Using Implicit Statement Caching

- Using Explicit Statement Caching

### 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 getStmtCacheSiz`e()` 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:

1. Enable statement caching as described in "Enabling and Disabling Statement Caching".

2. Allocate a statement using one of the standard methods.

3. Optionally disable implicit statement caching for any particular statement you do not want to cache.

4. Cache the statement using the `close()` method.

5. 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:

1. Enable statement caching as described in "Enabling and Disabling Statement Caching".

2. Allocate a statement using one of the standard methods.

3. Explicitly cache the statement by closing it with a key, using the `closeWithKey()` method.

4. Retrieve the explicitly cached statement by calling the appropriate Oracle "WithKey" method, specifying the appropriate key.

5. 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

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index