21 Implicit Connection Caching

Turning Caching On

An application turns the implicit connection cache on by calling OracleDataSource.setConnectionCachingEnabled(true). After implicit caching is turned on, the first connection request to the OracleDataSource class transparently creates a connection cache.

Example 21-1 provides a sample code that uses the implicit connection cache.

// Example to show binding of OracleDataSource to JNDI,
// then using implicit connection cache 
 
import oracle.jdbc.pool.*; // import the pool package
 
Context ctx = new InitialContext(ht);
OracleDataSource ods = new OracleDataSource();
 
// Set DataSource properties
ods.setUser("Scott");
ods.setConnectionCachingEnabled(true);    // Turns on caching
ctx.bind("MyDS", ods);
// ...
// Retrieve DataSource from the InitialContext
ods =(OracleDataSource) ctx. lookup("MyDS");  
 
// Transparently create cache and retrieve connection 
conn = ods.getConnection();
// ...
conn.close();  // return connection to the cache
// ...
ods.close() // close datasource and clean up the cache

Opening a Connection

After you have turned connection caching on, whenever you retrieve a connection through the OracleDataSource.getConnection method, the JDBC drivers check to see if a connection is available in the cache.

The getConnection method checks if there are any free physical connections in the cache that match the specified criteria. If a match is found, then a logical connection is returned, wrapping the physical connection. If no physical connection match is found, then a new physical connection is created, wrapped in a logical connection, and returned.

There are four variations on getConnection, two that make no reference to the connection cache and two that specify which sort of connections the cache may return. The non-cache-specific getConnection methods behave in the standard manner.

Setting Connection Cache Name

The ConnectionCacheName property of OracleDataSource is an optional property used by the Connection Cache Manager to manage a connection cache. You can set this property by calling the following method:

public void synchronized setConnectionCacheName(String cacheName) throws SQLException

When this property is set, the name is used to uniquely identify the cache accessed by the cache-enabled OracleDataSource. If the property is not set, then a default cache name is created using the convention DataSourceName#HexRepresentationOfNumberOfCaches.

Setting Connection Cache Properties

You can fine-tune the behavior of the implicit connection cache using the setConnectionCacheProperties method to set various connection properties.

Closing a Connection

An application returns a connection to the cache by calling the close method. There are two variants of the close method: one with no arguments and one that takes a Connection object as argument.

Implicit Connection Cache Example

Example 21-2 demonstrates creating a data source, setting its caching and data source properties, retrieving a connection, and closing that connection in order to return it to the cache.

import java.sql.*;
import javax.sql.*;
import java.util.*;
import javax.naming.*;
import javax.naming.spi.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;
 
...
  
    // create a DataSource    
    OracleDataSource ods = new OracleDataSource();
    
    // set cache properties 
    java.util.Properties prop = new java.util.Properties();
    prop.setProperty("MinLimit", "2");    
    prop.setProperty("MaxLimit", "10");    
 
    // set DataSource properties    
    String url = "jdbc:oracle:oci8:@";    
    ods.setURL(url);    
    ods.setUser("hr");    
    ods.setPassword("hr");    
    ods.setConnectionCachingEnabled(true); // be sure set to true    
    ods.setConnectionCacheProperties (prop);    
    ods.setConnectionCacheName("ImplicitCache01"); // this cache's name    
 
    // We need to create a connection to create the cache 
    Connection conn = ds.getConnection(user, pass);
    Statement  stmt  = conn.createStatement();
    ResultSet  rset  = stmt.executeQuery("select user from dual");
    conn.close(); 
     
    ods.close();

Getting Connections

The first connection you retrieve has no attributes. You must set them. After you have set attributes on a connection, you can request the connection by specifying its attribute, using the specialized forms of the getConnection method:

• getConnection(java.util.Properties cachedConnectionAttributes

  Requests a database connection that matches the specified cachedConnectionAttributes.

  See Also:

  For a discussion on connection attributes, see "Other Properties" .

• getConnection(java.lang.String user, java.lang.String password, java.util.Properties cachedConnectionAttributes)

  Requests a database connection from the implicit connection cache that matches the specified user, password and cachedConnectionAttributes. If null values are passed for user and password, the DataSource defaults are used.

Attribute Matching Rules

The rules for matching connectionAttributes come in two variations:

• Basic

  In this variation, the cache is searched to retrieve the connection that matches the attributes. The connection search mechanism as follows:

  1. If an exact match is found, the connection is returned to the caller.

  2. If an exact match is not found and the ClosestConnectionMatch data source property is set, then the connection with the closest match is returned. The closest matched connection is one that has the highest number of the original attributes matched. Note that the closest matched connection may match a subset of the original attributes, but does not have any attributes that are not part of the original list. For example, if the original list of attributes is A, B and C, then a closest match may have A and B set, but never a D.

  3. If none of the existing connections matches, a new connection is returned. The new connection is created using the user name and password set on the DataSource. If getConnection(String, String, java.util.Properties) is called, then the user name and password passed as arguments are used to open the new connection.

• Advanced

  In this variation, the attributes may be associated with weights. The connection search mechanism is similar to the basic connectionAttributes based search, except that the connections are searched not only based on the connectionAttributes, but also using a set of weights that are associated with the keys on the connectionAttributes. These weights are assigned to the keys as a one-time operation and is supported as a connection cache property, AttributeWeights.

Setting Connection Attributes

An application sets connection attributes using the following:

applyConnectionAttributes(java.util.Properties connAttr)

No validation is done on connAttr. Applying connection attributes is cumulative. Each time you call applyConnectionAttributes, the connAttr attribute you supply is added to those previously in force.

Checking Attributes of a Returned Connection

When an application requests a connection with specified attributes, it is possible that no match will be found in the connection cache. When this happens, the connection cache creates a connection with no attributes and returns it. The connection cache cannot create a connection with the requested attributes, because the Connection Cache manager is ignorant of the semantics of the attributes.

For this reason, applications should always check the attributes of a returned connection. To do this, use the getUnMatchedConnectionAttributes method, which returns a list of any attributes that were not matched in retrieving the connection. If the return value of this method is null, you know that you must set all the connection attributes.

Connection Attribute Example

Example 21-3 illustrates using connection attributes.

java.util.Properties connAttr = new java.util.Properties();
    connAttr.setProperty("connection_type", "payroll");
 
    // retrieve connection that matches attributes
    Connection conn = ds.getConnection(connAttr); 
    // Check to see which attributes weren't matched
   unmatchedProp = ((OracleConnection)conn).getUnMatchedConnectionAttributes();
   if ( unmatchedProp != null )
    {
    // apply attributes to the connection
    ((OracleConnection)conn).applyConnectionAttributes(connAttr);  
    }
    // verify whether conn contains property after apply attributes
    connProp = ((OracleConnection)conn).getConnectionAttributes(); 
    listProperties (connProp);

Limit Properties

These properties control the size of the cache.

InitialLimit

Sets how many connections are created in the cache when it is created or reinitialized. When this property is set to an integer value greater than 0, creating or reinitializing the cache automatically creates the specified number of connections, filling the cache in advance of need.

Default: 0

MaxLimit

Sets the maximum number of connection instances the cache can hold. The default value is Integer.MAX_VALUE, meaning that there is no limit enforced by the connection cache, so that the number of connections is limited only by the number of database sessions configured for the database.

Default: Integer.MAX_VALUE (no limit)

MaxStatementsLimit

Sets the maximum number of statements that a connection keeps open. When a cache has this property set, reinitializing the cache or closing the data source automatically closes all cursors beyond the specified MaxStatementsLimit.

Default: 0

MinLimit

Sets the minimum number of connections the cache maintains.

Default: 0

TIMEOUT Properties

These properties control the lifetime of an element in the cache.

InactivityTimeout

Sets the maximum time a physical connection can remain idle in a connection cache. An idle connection is one that is not active and does not have a logical handle associated with it. When InactivityTimeout expires, the underlying physical connection is closed. However, the size of the cache is not allowed to shrink below minLimit, if it has been set.

Default: 0 (no timeout in effect)

TimeToLiveTimeout

Sets the maximum time in seconds that a logical connection can remain open. When TimeToLiveTimeout expires, the logical connection is unconditionally closed, the relevant statement handles are canceled, and the underlying physical connection is returned to the cache for reuse.

Default: 0 (no timeout in effect)

AbandonedConnectionTimeout

Sets the maximum time that a connection can remain unused before the connection is closed and returned to the cache. A connection is considered unused if it has not had SQL database activity.

When AbandonedConnectionTimeout is set, JDBC monitors SQL database activity on each logical connection. For example, when stmt.execute is called on the connection, a heartbeat is registered to convey that this connection is active. The heartbeats are set at each database execution. If a connection has been inactive for the specified amount of time, the underlying connection is reclaimed and returned to the cache for reuse.

Default: 0 (no timeout in effect)

PropertyCheckInterval

Sets the time interval at which the Connection Cache Manager inspects and enforces all specified cache properties. PropertyCheckInterval is set in seconds.

Default: 900 seconds

Other Properties

These properties control miscellaneous cache behavior.

AttributeWeights

AttributeWeights sets the weight for each attribute set on the connection.

ClosestConnectionMatch

ClosestConnectionMatch causes the connection cache to retrieve the connection with the closest approximation to the specified connection attributes.

ConnectionWaitTimeout

Specifies cache behavior when a connection is requested and there are already MaxLimit connections active. If ConnectionWaitTimeout is equal to zero, then each connection request waits for zero seconds, that is, null connection is returned immediately. If ConnectionWaitTimeout is greater than zero, then each connection request waits for the specified number of seconds or until a connection is returned to the cache. If no connection is returned to the cache before the timeout elapses, then the connection request returns null.

Default: zero

LowerThresholdLimit

Sets the lower threshold limit on the cache. The default is 20 percent of the MaxLimit on the connection cache. This property is used whenever a releaseConnection() cache callback method is registered.

ValidateConnection

Setting ValidateConnection to true causes the connection cache to test every connection it retrieves against the underlying database. If a valid connection cannot be retrieved, then an exception is thrown.

Default: false

Connection Property Example

Example 21-4 demonstrates how an application uses connection properties.

import java.sql.*;
import javax.sql.*;
import java.util.*;
import javax.naming.*;
import javax.naming.spi.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;
...
  OracleDataSource ds =  (OracleDataSource) ctx.lookup("...");
  java.util.Properties prop = new java.util.Properties ();
  prop.setProperty("MinLimit", "5");     // the cache size is 5 at least 
  prop.setProperty("MaxLimit", "25");
  prop.setProperty("InitialLimit", "3"); // create 3 connections at startup
  prop.setProperty("InactivityTimeout", "1800");    //  seconds
  prop.setProperty("AbandonedConnectionTimeout", "900");  //  seconds
  prop.setProperty("MaxStatementsLimit", "10");
  prop.setProperty("PropertyCheckInterval", "60"); // seconds

  ds.setConnectionCacheProperties (prop);  // set properties
  Connection conn = ds.getConnection();
  conn.dosomework();
  java.util.Properties propList=ds.getConnectionCacheProperties();  // retrieve

Attribute Weights and Connection Matching

There are two connection cache properties that enable the developer to specify which connections in the connection cache are accepted in response to a getConnection request. When you set the ClosestConnectionMatch property to true, you are telling the Connection Cache Manager to return connections that match only some of the attributes you have specified.

If you do not specify attributeWeights, then the Connection Cache Manager returns the connection that matches the highest number of attributes. If you specify attributeWeights, then you can control the priority the manager uses in matching attributes.

ClosestConnectionMatch

Setting ClosestConnectionMatch to true causes the connection cache to retrieve the connection with the closest approximation to the specified connection attributes. This can be used in combination with AttributeWeights to specify what is considered a closest match.

Default: false

AttributeWeights

Sets the weights for each connectionAttribute. This property is used when ClosestConnectionMatch is set to true to determine which attributes are given highest priority when searching for matches. An attribute with a high weight is given more importance in determining a match than an attribute with a low weight.

AttributeWeights contains a set of Strings representing key-value pairs. Each key/value pair sets the weights for each connectionAttribute for which the user intends to request a connection. Each String is in the format written by the java.util.Properties.Store(OutputStream, String) method, and thus can be read by the java.util.Properties.load(InputStream) method. The Key is a connectionAttribute and the Value is the weight. A weight must be an integer value greater than 0. The default weight is 1.

For example, TRANSACTION_ISOLATION could be assigned a weight of 10 and ROLE a weight of 5. If ClosestConnectionMatch is set to true, when a connectionAttribute based connection request is made on the cache, connections with a matching TRANSACTION_ISOLATION will be favored over connections with a matching ROLE.

Default: No AttributeWeights

Connection Cache Callbacks

The implicit connection cache offers a way for the application to specify callbacks to be called by the connection cache. Callback methods are supported with the OracleConnectionCacheCallback interface. This callback mechanism is useful to take advantage of the special knowledge of the application about particular connections, supplementing the default behavior when handling abandoned connections or when the cache is empty.

OracleConnectionCacheCallback is an interface that must be implemented by the user and registered with OracleConnection. The registration API is as follows:

public void
registerConnectionCacheCallback(
OracleConnectionCacheCallback cbk, Object usrObj,  int cbkflag);

In this interface, cbk is the user implementation of the OracleConnectionCacheCallback interface. The usrObj parameter contains any parameters that the user wants supplied. This user object is passed back, unmodified, when the callback method is called. The cbkflag parameter specifies which callback method should be called. It must be one of the following values:

• OracleConnection.ABANDONED_CONNECTION_CALLBACK

• OracleConnection.RELEASE_CONNECTION_CALLBACK

• OracleConnection.ALL_CALLBACKS

When ALL_CALLBACKS is set, all the connection cache callback methods are called. For example,

// register callback, to invoke all callback methods
((OracleConnection)conn).registerConnectionCacheCallback( new UserConnectionCacheCallback(), 
  new SomeUserObject(), 
OracleConnection.ALL_CALLBACKS);

An application can register a ConnectionCacheCallback on an OracleConnection. When a callback is registered, the connection cache calls the handleAbandonedConnection method of the callback before reclaiming the connection. If the callback returns true, then the connection is reclaimed. If the callback returns false, then the connection remains active.

The UserConnectionCacheCallback interface supports two callback methods to be implemented by the user, releaseConnection and handleAbandonedConnection.

Use Cases for TimeToLiveTimeout and AbandonedConnectionTimeout

The following are the use cases for the TimeToLiveTimeout and AbandonedConnectionTimeout timeout mechanisms when used with implicit connection cache. Note that these timeout mechanisms are applicable to the logical connection when it is retrieved from the connection cache.

• The application considers the connections completely stateless.

  When the connections are stateless, either of the timeout mechanisms can be used. The connections for which the timeout expires are put back into the connection cache for reuse. These connections are valid for reuse because there is no session state associated with them.

• The application maintains state on each connection, but has a cleanup routine that can render the connections stateless when they are returned to the connection cache.

  In this case, TimeToLiveTimeout cannot be used. There is no way for the connection cache to ensure that a connection returned to the cache is in a reusable condition.However, AbandonedConnectionTimeout can be used in this scenario, only if OracleConnectionCacheCallback is registered on the connection. The handleAbandonedConnection callback method is implemented by the application and ensures that the necessary cleanup is done. The connection is closed when the timeout processing invokes this callback method. The closing of this connection by the callback method causes the connection to be put back into the connection cache in a state where it is reusable.

  Note:

  Do not to close the connection after calling handleAbandonedConnection method because the connection could be in an invalid state. JDBC internally knows how to reclaim a connection even when it is in an invalid state.

  See Also:

  "Connection Cache Callbacks"

• The application maintains state on each connection, but has no control over the connection and, therefore, cannot ensure cleaning up of state for reuse of connections by other applications or users.

  The use of either of the timeout mechanisms is not recommended.