Skip Headers

Oracle® Database JDBC Developer's Guide and Reference
10g Release 1 (10.1)

Part Number B10979-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

7 Implicit Connection Caching

Connection caching, generally implemented in the middle tier, is a means of keeping and using caches of physical database connections.


Note:

The previous cache architecture, based on OracleConnectionCache and OracleConnectionCacheImpl, is deprecated. We recommend that you take advantage of the new architecture, which is more powerful and offers better performance.

The Implicit Connection Cache is an improved JDBC 3.0-compliant connection cache implementation for DataSource. Java and J2EE applications benefit from transparent access to the cache, support for multiple users, and the ability to request connections based on user-defined profiles.

An application turns the implicit connection cache on by invoking setConnectionCachingEnabled(true) on an OracleDataSource. After implicit caching is turned on, the first connection request to the OracleDataSource transparently creates a connection cache. There is no need for application developers to write their own cache implementations.

This section is divided into the following topics:

7.1 The Implicit Connection Cache

The connection caching architecture has been redesigned so that caching is transparently integrated into the datasource architecture.

The connection cache uses the concept of physical connections and logical connections. Physical connections are the actual connections returned by the database; logical connections are wrappers used by the cache to manipulate physical connections. You can think of logical connections as handles. The caches always return logical connections, which implement all the same interfaces as physical connections.

The implicit connection cache offers:

7.2 Using the Connection Cache

This section discusses how applications use the implicit connection cache.

7.2.1 Turning Caching On

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

Here is a simple example using the implicit connection cache.

Example 7-1 Using 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

For details on the connection cache API, see the Javadoc for OracleDataSource and OracleConnectionCacheManager.

7.2.2 Opening a Connection

After you have turned connection caching on, whenever you retrieve a connection through an OracleDataSource.getConnection(), 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, a logical connection is returned wrapping the physical connection. If no physical connection match is found, 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 sorts of connections the cache may return. The non-cache-specific getConnection() methods behave as normal.

The connection-cache-specific variations are:

  • getConnection(java.util.Properties cachedConnectionAttributes)—requests a database connection that matches the specified cachedConnectionAttributes (see "Other Properties" for a discussion of connection attributes)

  • getConnection(java.lang.String user, java.lang.String passwd, java.util.Properties cachedConnectionAttributes)—requests a database connection from the Implicit Connection Cache that matches the specified user, passwd and cachedConnectionAttributes


Note:

For a discussion of connection cache attributes, see "Connection Attributes".

7.2.3 Setting Connection Cache Name

You can specify the connection cache's name by invoking setConnectionCacheName().

7.2.4 Setting Connection Cache Properties

You can fine-tune the behavior of the Implicit Connection cache using the setConnectionCacheProperties() method to set various connection properties. These properties are documented in "Connection Cache Properties" .


Note:

Although these properties govern the behavior of the connection cache, they are set on the datasource, not on the connection or on the cache itself.

7.2.5 Closing A Connection

An application returns a connection to the cache by invoking close(). There are two variants on the close method: one with no arguments, and one that takes a connection attribute argument, discussed in "Setting Connection Attributes" .


Note:

The close() method is new at this release. Applications must close connections in order ensure that they are returned to the cache.

7.2.6 Implicit Connection Cache Example

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

Example 7-2 Connection Cache Example

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  = conn1.createStatement();
    ResultSet  rset  = stmt.executeQuery("select user from dual");
    conn1.close(); 
 
    ods.close();

7.3 Connection Attributes

Each connection obtained from a datasource can have user-defined attributes. Attributes are specified by the application developer, and are java.lang.Properties name/value pairs.

An application can use connection attributes to supply additional semantics to identify connections. For instance, an application might create an attribute named "connection_type" and then assign it the value "payroll" or "inventory".


Note:

The semantics of connection attributes are entirely application-defined; the connection cache itself enforces no restrictions on the key or value of connection attributes.

The methods that get and set connection attributes are found on OracleConnection.

7.3.1 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 those connections by attribute using the specialized forms of getConnection:

  • getConnection(java.util.Properties cachedConnectionAttributes)—requests a database connection that matches the specified cachedConnectionAttributes

  • getConnection(java.lang.String user, java.lang.String passwd, java.util.Properties cachedConnectionAttributes)—requests a database connection from the Implicit Connection Cache that matches the specified user, passwd and cachedConnectionAttributes. If null values are passed for user and password, the DataSource defaults are used.

The rules for what constitutes an attribute match are discussed in the next section.

7.3.1.1 Attribute Matching Rules

  • The rules for matching connectionAttributes come in two variations:

    Basic, in which the cache is searched to retrieve the connection that matches the attributes.

    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 datasource 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 connectionAttributes are satisfied, a new connection is returned. The new connection is created using the user and password set on the DataSource.

  • Advanced, where 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. See "Attribute Weights And Connection Matching" for further details.

7.3.2 Setting Connection Attributes

An application sets connection attributes using one of two methods:

  • applyConnectionAttributes(java.util.Properties connAttr)

    No validation is done on connAttr. Applying connection attributes is cumulative: each time you invoke applyConnectionAttributes, the connAttr you supply are added to those previously in force.

  • close((java.util.Properties connAttr)

    This obliterates the attributes of the specified connection and replaces them with the attributes found in connAttr.


Note:

We recommend you do not invoke applyConnectionAttributes(connAttr) and close(connAttr) on the same connection.

7.3.3 Checking a Returned Connection's Attributes

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, since the cache manager is ignorant of the semantics of the attributes.


Note:

If the closestConnectionMatch property has been set, the cache manager looks for "close" attribute matches rather than exact matches; see "ClosestConnectionMatch" for details.

For this reason, applications should always check the attributes of a returned connection. To do this, use the method java.util.Properties getUnMatchedConnectionAttributes(), 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.

7.3.4 Connection Attribute Example

Example 7-3 illustrates using connection attributes.

Example 7-3 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);

7.4 Connection Cache Properties

The connection cache properties govern the characteristics of a connection cache. This section lists the supported connection cache properties.

Applications set cache properties in one of the following ways:

7.4.1 Limit Properties

These properties control the size of the cache.

7.4.1.1 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

7.4.1.2 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)

7.4.1.3 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 datasource automatically closes all cursors beyond the specified MaxStatementsLimit.

Default: 0

7.4.1.4 MinLimit

Sets the minimum number of connections the cache maintains. This guarantees that the cache will not shrink below this minimum limit.

Setting the MinLimit property does not initialize the cache to contain the minimum number of connections. To do this, use the InitialLimit property. See "InitialLimit".

Default0

7.4.2 Timeout Properties

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

7.4.2.1 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 has been set.

Default0 (no timeout in effect)

7.4.2.2 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)

7.4.2.3 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 invoked 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)

7.4.2.4 PropertyCheckInterval

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

Default: 900 seconds (15 minutes)

7.4.3 Other Properties

These properties control miscellaneous cache behaviors.

7.4.3.1 AttributeWeights

See "AttributeWeights" .

7.4.3.2 ClosestConnectionMatch

See "ClosestConnectionMatch" .

7.4.3.3 ConnectionWaitTimeout

Specifies cache behavior when a connection is requested and there are already MaxLimit connections active. If ConnectionWaitTimeout is greater than zero (0), 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, the connection request returns null.

Default: 0 (no timeout)

7.4.3.4 LowerThresholdLimit

Sets the lower threshold limit on the cache. The default is 20% of the MaxLimit on the connection cache. This property is used whenever a releaseConnection() cache callback method is registered. For details, see "Connection Cache Callbacks" .

7.4.3.5 ValidateConnection

Setting ValidateConnection to true causes the connection cache to test every connection it retrieves against the underlying database.

Default: false

7.4.4 Connection Property Example

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

Example 7-4 Using 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

7.5 Connection Cache Manager API

OracleConnectionCacheManager provides administrative APIs that the middle tier can use to manage available connection caches. The administration methods are listed below; for full details, see the Javadoc.

7.5.1 createCache

This method exists in two signature variants:

  • void createCache(String cacheName, javax.sql.DataSource ds, 
       java.util.Properties cacheProps)
    
    

    Creates a new cache identified by a unique cache name. The newly-created cache is bound to the specified DataSource object. Cache properties, when specified, are applied to the cache that gets created. When cache creation is successful, the Connection Cache Manager adds the new cache to the list of caches managed. Creating a cache with a user defined cache name facilitates specifying more meaningful names. For example, DMS metrics collected on a per cache basis could display metrics attached to a meaningful cache name. createCache throws an exception, if a cache already exists for the DataSource object passed in.

  • String createCache(javax.sql.DataSource ds, java.util.Properties cacheProps
    
    

    Creates a new cache using a generated unique cache name and returns this cache name. The standard convention used in cache name generation is DataSourceName#HexRepresentationOfNumberOfCaches. The semantics are otherwise identical to the previous form.

7.5.2 removeCache

void removeCache(String cacheName, int timeout)

Removes the cache specified by cacheName. All its resources are closed and freed. The second parameter is a wait timeout value that is specified in seconds. If the wait timeout value is 0, then all in-use or checked out connections are reclaimed (similar to TimeToLive timeout) without waiting for the connections in-use to be done. When invoked with a wait timeout value greater than 0, the operation waits for the specified period of time for checked out connections to be closed before removing the connection cache. This includes connections that are closed based on timeouts specified. Connection cache removal is not reversible.

7.5.3 reinitializeCache

void reinitializeCache(String cacheName, java.util.properties cacheProperties)

Reinitializes the cache using the specified new set of cache properties. This supports dynamic reconfiguration of caches; the new properties take effect on all newly-created connections, as well as on existing connections that are not in use. When the reinitializeCache() method is called, all in-use connections are closed. The new cache properties are then applied to all the connections in the cache.


Note:

Invoking reinitializeCache() closes all connections obtained through this cache.

7.5.4 existsCache

boolean existsCache(String CacheName)

Checks whether a specific connection cache exists among the list of caches that the Connection Cache Manager handles. Returns true if the cache exists, false otherwise.

7.5.5 enableCache

void enableCache(String cacheName)

Enables a disabled cache. This is a no-op if the cache is already enabled.

7.5.6 disableCache

void disableCache(String cacheName)

Temporarily disables the cache specified by cacheName. This means that, temporarily, connection requests will not be serviced from this cache. However, in-use connections will continue to work uninterrupted.

7.5.7 refreshCache

void refreshCache(String cacheName, int mode)

Refreshes the cache specified by cacheName. There are two modes supported, REFRESH_INVALID_CONNECTIONS and REFRESH_ALL_CONNECTIONS. When invoked with REFRESH_INVALID_CONNECTIONS, each Connection in the cache is checked for validity. If an invalid Connection is found, that connection's resources are removed and replaced with a new Connection. The test for validity is basically a simple query to the dual table: select 1 from dual. When invoked with REFRESH_ALL_CONNECTIONS, all available connections in the cache are closed and replaced with new valid physical connections.

7.5.8 purgeCache

void  purgeCache(String cacheName, boolean cleanupCheckedOutConnections)

Removes connections from the connection cache, but does not remove the cache itself. If the cleanupCheckedOutConnections parameter is set to true, then the checked out connections are cleaned up, as well as the available connections in the cache. If the cleanupCheckedOutConnections parameter is set to false, only the available connections are cleaned up.

7.5.9 getCacheProperties

java.util.properties getCacheProperties(String cacheName)

Retrieves the cache properties for the specified cacheName.

7.5.10 getCacheNameList

String[] getCacheNameList()

Returns all the connection cache names that are known to the Connection Cache Manager. The cache names may then be used to manage connection caches using the Connection Cache Manager APIs.

7.5.11 getNumberOfAvailableConnections

int getNumberOfAvailableConnections(String cacheName)

Returns the number of connections in the connection cache, that are available for use. The value returned is a snapshot of the number of connections available in the connection cache at the time the API was processed; it may become invalid quickly.

7.5.12 getNumberOfActiveConnections

int getNumberOfActiveConnections(String cacheName)

Returns the number of checked out connections, connections that are active or busy, and hence not available for use. The value returned is a snapshot of the number of checked out connections in the connection cache at the time the API was processed; it may become invalid quickly.

7.5.13 setConnectionPoolDataSource

void setConnectionPoolDataSource(String cacheName, ConnectionPoolDataSource ds)

Allows connections to be created from an external OracleConnectionPoolDataSource, instead of the default DataSource, for the given connection cache. When such a ConnectionPoolDataSource is set, all DataSource properties, such as url, are derived from this new DataSource.

7.5.14 Example Of ConnectionCacheManager Use

Example 7-5 demonstrates the OracleConnectionCacheManager interfaces.

Example 7-5 Connection Cache Manager Example

import java.sql.*;
import javax.sql.*;
import java.util.*;
import javax.naming.*;
import javax.naming.spi.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;
...
// Get singleton ConnectionCacheManager instance
  OracleConnectionCacheManager occm =
  OracleConnectionCacheManager.getConnectionCacheManagerInstance();
  String cacheName = "foo";  // Look for a specific cache
  // Use Cache Manager to check # of available connections 
  // and active connections
  System.out.println(occm.getNumberOfAvailableConnections(cacheName)
      " connections are available in cache " + cacheName);
 
  System.out.println(occm.getNumberOfActiveConnections(cacheName)
      + " connections are active");
  // Refresh all connections in cache 
  occm.refreshCache(cacheName,
    OracleConnectionCacheManager.REFRESH_ALL_CONNECTIONS);
  // Reinitialize cache, closing all connections
  java.util.Properties newProp = new java.util.Properties();
  newProp.setProperty("MaxLimit", "50");
  occm.reinitializeCache(cacheName, newProp);

7.6 Advanced Topics

This section discusses cache functionality that is useful for advanced users, but is not essential to understanding or using the Implicit Connection Cache. This is divided into the following sections:

7.6.1 Attribute Weights And Connection Matching

There are two connection cache properties that allow the developer to specify which connections in the connection cache are accepted in response to agetConnection() 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.

7.6.1.1 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

7.6.1.2 AttributeWeights

Sets the weights for each connectionAttribute. 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 Key/Value pairs that set the weights for each connectionAttribute for which the user intends to request a connection. 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

7.6.2 Connection Cache Callbacks

The implicit connection cache offers a way for the application to specify callbacks to be invoked by the connection cache. Callback methods are supported with the OracleConnectionCacheCallback interface. This callback mechanism is useful to take advantage of the application's special knowledge of 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:

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

In this interface, cbk is the user's 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 invoked. The cbkflag parameter specifies which callback method should be invoked. 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 invoked. 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 callback's handleAbandonedConnection() before reclaiming the connection. If the callback returns true, the connection is reclaimed. If the callback returns false, the connection remains active. For details, see "Connection Cache Manager API" .

The UserConnectionCacheCallback interface supports two callback methods to be implemented by the user, releaseConnection() and handleAbandonedConnection(). For details on these methods, see the Javadoc.