Connection Caching

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

Connection caching uses the connection pooling framework--such as connection pool data sources and pooled connections--in much of its operations. "Connection Pooling", starting, describes this framework.

The JDBC 2.0 specification does not mandate a connection caching implementation, but Oracle provides a simple implementation to serve at least as an example.

This section is divided into the following topics:

• Overview of Connection Caching

• Typical Steps in Using a Connection Cache

• Oracle Connection Cache Specification: OracleConnectionCache Interface

• Oracle Connection Cache Implementation: OracleConnectionCacheImpl Class

• Oracle Connection Event Listener: OracleConnectionEventListener Class

  Note: The concept of connection caching is not relevant to the server-side internal driver, where you are simply using the default connection, and is only relevant to the server-side Thin driver within a single session.

Overview of Connection Caching

Each connection cache is represented by an instance of a connection cache class and has an associated group of pooled connection instances. For a single connection cache instance, the associated pooled connection instances must all represent physical connections to the same database and schema. Pooled connection instances are created as needed, which is whenever a connection is requested and the connection cache does not have any free pooled connection instances. A "free" pooled connection instance is one that currently has no logical connection instance associated with it; in other words, a pooled connection instance whose physical connection is not being used.

Basics of Setting Up a Connection Cache

The middle tier, in setting up a connection cache, will create an instance of a connection cache class and set its data source connection properties as appropriate--for example, serverName, databaseName, or URL. Recall that a connection cache class extends a data source class. For information about data source properties, see "Data Source Properties".

An example of a connection cache class is OracleConnectionCacheImpl. How to instantiate this class and set its connection properties is described in "Instantiating OracleConnectionCacheImpl and Setting Properties". This class extends the OracleDataSource class and so includes the setter methods to set connection properties to specify the database to connect to. All the pooled connection instances in the cache would represent physical connections to this same database, and in fact to the same schema.

Once the middle tier has created a connection cache instance, it can optionally bind this instance to JNDI as with any data source instance, which is described in "Register the Data Source".

Basics of Accessing the Connection Cache

A JDBC application must retrieve a connection cache instance to use the cache. This is typically accomplished through the middle tier, often using a JNDI lookup. In a connection caching scenario, a JNDI lookup would return a connection cache instance instead of a generic data source instance. Because a connection cache class extends a data source class, connection cache instances include data source functionality.

Executing a JNDI lookup is described in "Open a Connection".

If JNDI is not used, the middle tier will typically have some vendor-specific API through which a connection cache instance is retrieved for the application.

Basics of Opening Connections

A connection cache class, as with a pooled connection class, has a getConnection() method. The getConnection() method of a connection cache instance returns a logical connection to the database and schema associated with the cache. This association is through the connection properties of the connection cache instance, as typically set by the middle tier.

Whenever a JDBC application wants a connection to a database in a connection caching scenario, it will call the getConnection() method of the connection cache instance associated with the database.

This getConnection() method checks if there are any free pooled connection instances in the cache. If not, one is created. Then a logical connection instance will be retrieved from a previously existing or newly created pooled connection instance, and this logical connection instance will be supplied to the application.

Basics of Closing Connections: Use of Connection Events

JDBC uses JavaBeans-style events to keep track of when a physical connection (pooled connection instance) can be returned to the cache or when it should be closed due to a fatal error. When a JDBC application calls the close() method of a logical connection instance, an event is triggered and communicated to the event listener or listeners associated with the pooled connection instance that produced the logical connection instance. This triggers a connection-closed event and informs the pooled connection instance that its physical connection can be reused. Essentially, this puts the pooled connection instance and its physical connection back into the cache.

The point at which a connection event listener is created and registered with a pooled connection instance is implementation-specific. This could happen, for example, when the pooled connection instance is first created or each time the logical connection associated with it is closed.

It is also possible for the cache class to implement the connection event listener class. In this case, the connection event listener is part of the connection cache instance. (This is not the case in the Oracle sample implementation.) Even in this case, however, an explicit association must be made between the connection event listener and each pooled connection instance.

Implementation Scenarios

Middle-tier developers have the option of implementing their own connection cache class and connection event listener class.

For convenience, however, Oracle provides the following, all in the oracle.jdbc.pool package:

• a connection cache interface: OracleConnectionCache

• a connection cache class: OracleConnectionCacheImpl

• a connection event listener class: OracleConnectionEventListener

The OracleConnectionCacheImpl class is a simple connection cache class implementation that Oracle supplies as an example, providing sufficient but minimal functionality. It implements the OracleConnectionCache interface and uses instances of the OracleConnectionEventListener class for connection events.

If you want more functionality than OracleConnectionCacheImpl has to offer but still want to use OracleConnectionEventListener for connection events, then you can create your own class that implements OracleConnectionCache.

Or you can create your own connection cache class and connection event listener class from scratch.

Typical Steps in Using a Connection Cache

This section lists the general steps in how a JDBC application and middle-tier will use a connection cache in opening and closing a logical connection.

Preliminary Steps in Connection Caching

Presume the following has already been accomplished:

1. The middle tier has created a connection cache instance, as described in "Basics of Setting Up a Connection Cache".

2. The middle tier has provided connection information to the connection cache instance for the database and schema that will be used. This can be accomplished when constructing the connection cache instance.

3. The application has retrieved the connection cache instance, as described in "Basics of Accessing the Connection Cache".

General Steps in Opening a Connection

Once the JDBC application has access to the connection cache instance, the application and middle tier perform the following steps to produce a logical connection instance for use by the application:

1. The application requests a connection through the getConnection() method of the connection cache instance. No input is necessary, because a connection cache instance is already associated with a particular database and schema.

2. The connection cache instance examines its cache as follows: a) to see if there are any pooled connection instances in the cache yet; and b) if so, if any are free--that is, to see if there is at least one pooled connection instance that currently has no logical connection instance associated with it.

3. The connection cache instance chooses an available pooled connection instance or, if none is available, might create a new one (this is implementation-specific). In creating a pooled connection instance, the connection cache instance can specify connection properties according to its own connection properties, because the pooled connection instance will be associated with the same database and schema.

   Note: Exactly what happens in a situation where no pooled connection instances are available depends on the implementation schemes and whether the cache is limited to a maximum number of pooled connections. For the Oracle sample implementation, this is discussed in "Schemes for Creating New Pooled Connections in the Oracle Implementation".

4. Depending on the situation and implementation, the connection cache instance creates a connection event listener (a process that associates the listener with the connection cache instance) and associates the listener with the chosen or newly created pooled connection instance. The association with the pooled connection instance is accomplished by calling the standard addConnectionEventListener() method specified by the PooledConnection interface. This method takes the connection event listener instance as input. If the connection cache class implements the connection event listener class, then the argument to the addConnectionEventListener() method would be the this object.

   In some implementations, the creation and association of the connection event listener can occur only when the pooled connection instance is first created. In the Oracle sample implementation, this also occurs each time a pooled connection instance is reused.

   Note that in being associated with both the connection cache instance and a pooled connection instance, the connection event listener becomes the bridge between the two.

5. The connection cache instance gets a logical connection instance from the chosen or newly created pooled connection instance, using the pooled connection getConnection() method.

   No input is necessary to getConnection(), because a pooled connection instance is already associated with a particular database and schema.

6. The connection cache instance passes the logical connection instance to the application.

The JDBC application uses this logical connection instance as it would any other connection instance.

General Steps in Closing a Connection

Once the JDBC application has finished using the logical connection instance, its associated pooled connection instance can be returned to the connection cache (or closed, as appropriate, if a fatal error occurred). The application and middle tier perform the following steps to accomplish this:

1. The application calls the close() method on the logical connection instance (as it would with any connection instance).

2. The pooled connection instance that produced the logical connection instance triggers an event to the connection event listener or listeners associated with it (associated with it through previous calls by the connection cache instance to the pooled connection instance addConnectionEventListener() method).

3. The connection event listener performs one of the following:
   • It puts the pooled connection instance back into the cache and flags it as available (typical).

   or:

   • It closes the pooled connection instance (if a fatal error occurred during use of its physical connection).

   The connection event listener will typically perform these steps by calling methods of the connection cache instance, which is implementation-specific. For the Oracle sample implementation, these functions are performed by methods specified in the OracleConnectionCache interface, as discussed in"Oracle Connection Cache Specification: OracleConnectionCache Interface".

4. Depending on the situation and implementation, the connection cache instance disassociates the connection event listener from the pooled connection instance. This is accomplished by calling the standard removeConnectionEventListener() method specified by the PooledConnection interface.

   In some implementations, this step can be performed only when a pooled connection instance is closed, either because of a fatal error or because the application is finished with the physical connection. In the Oracle sample implementation, however, the connection event listener is disassociated with the pooled connection instance each time the pooled connection is returned to the available cache (because in the Oracle implementation, a connection event listener is associated with the pooled connection instance whenever it is reused).

Oracle Connection Cache Specification: OracleConnectionCache Interface

Middle-tier developers are free to implement their own connection caching scheme as desired, but Oracle offers the OracleConnectionCache interface, which you can implement in a connection cache class and which uses instances of the OracleConnectionEventListener class for its listener functionality.

In addition, Oracle offers a class that implements this interface, OracleConnectionCacheImpl, which can be used as is. This class also extends the OracleDataSource class and, therefore, includes a getConnection() method. For more information about this class, see "Oracle Connection Cache Implementation: OracleConnectionCacheImpl Class".

These Oracle classes and interfaces are all in the oracle.jdbc.pool package.

The OracleConnectionCache interface specifies the following methods (in addition to data source methods that it inherits), to be implemented in a connection cache class:

• reusePooledConnection(): Takes a pooled connection instance as input and returns it to the cache of available pooled connections (essentially, the available physical connections).

  This method would be invoked by a connection event listener after a JDBC application has finished using the logical connection instance provided by the pooled connection instance (through previous use of the pooled connection getConnection() method).

• closePooledConnection(): Takes a pooled connection instance as input and closes it.

  A connection event listener would invoke this method after a fatal error has occurred through the logical connection instance provided by the pooled connection instance. The listener would call closePooledConnection(), for example, if it notices a server crash.

• close(): Closes the connection cache instance, after the application has finished using connection caching with the associated database.

The functionality of the reusePooledConnection() and closePooledConnection() methods is an implementation of some of the steps described generally in "General Steps in Closing a Connection".

Oracle Connection Cache Implementation: OracleConnectionCacheImpl Class

Oracle offers a sample implementation of connection caching and connection event listeners, providing the OracleConnectionCacheImpl class. This class implements the OracleConnectionCache interface (which you can optionally implement yourself in some other connection cache class) and uses instances of the OracleConnectionEventListener class for listener functionality.

These Oracle classes and interfaces are all in the oracle.jdbc.pool package.

If you use the OracleConnectionCacheImpl class for your connection caching functionality, you should be familiar with the following topics, discussed immediately below:

• Instantiating OracleConnectionCacheImpl and Setting Properties

• Setting a Maximum Number of Pooled Connections

• Schemes for Creating New Pooled Connections in the Oracle Implementation

• Additional OracleConnectionCacheImpl Methods

  Note: OracleConnectionCacheImpl support for JNDI is not complete in Oracle8i release 8.1.6 and higher--an instance obtained through a lookup call does not have any connections opened; however, the connection properties, caching scheme, and limits for the number of pooled connections are retained.

Instantiating OracleConnectionCacheImpl and Setting Properties

A middle tier that uses the Oracle implementation of connection caching can construct an OracleConnectionCacheImpl instance and set its connection properties in one of three ways:

• It can use the OracleConnectionCacheImpl constructor that takes an existing connection pool data source as input. This is convenient if the middle tier has already created a connection pool data source instance and set its connection properties. For example, where cpds is a connection pool data source instance:

  OracleConnectionCacheImpl ocacheimpl = new OracleConnectionCacheImpl(cpds);
  
  

or:

• It can use the default OracleConnectionCacheImpl constructor (which takes no input) and then the setConnectionPoolDataSource() method, which takes an existing connection pool data source instance as input. Again, this is convenient if the middle tier already has a connection pool data source instance with its connection properties set. For example, where cpds is a connection pool data source instance:

  OracleConnectionCacheImpl ocacheimpl = new OracleConnectionCacheImpl();
  
  ocacheimpl.setConnectionPoolDataSource(cpds);
  
  

  Notes: You can also use the setConnectionPoolDataSource() method to override a previously set pooled connection data source or previously set connection properties. If you call setConnectionPoolDataSource() when there is already a connection pool data source with associated logical connections in use, then an exception will be thrown if the new connection pool data source specifies a different database schema than the old connection pool data source.

or:

• It can use the default OracleConnectionCacheImpl constructor and then set the properties individually, using setter methods. For example:

  OracleConnectionCacheImpl ocacheimpl = new OracleConnectionCacheImpl();
  
  ocacheimpl.setDriverType("oci8");
  ocacheimpl.setServerName("dlsun999");
  ocacheimpl.setNetworkProtocol("tcp");
  ocacheimpl.setDatabaseName("816");
  ocacheimpl.setPortNumber(1521);
  ocacheimpl.setUser("scott");
  ocacheimpl.setPassword("tiger");
  
  

  This is equivalent to setting properties in any generic data source or connection pool data source, as discussed in "Initialize Connection Properties".

Setting a Maximum Number of Pooled Connections

In any connection caching implementation, the middle-tier developer must decide whether there should be a maximum number of pooled connections in the cache, and how to handle situations where no pooled connections are available and the maximum number has been reached.

The OracleConnectionCacheImpl class includes a maximum cache size that you can set using the setMaxLimit() method (taking an int as input). The default value is 1.

Following is an example, presuming ocacheimpl is an OracleConnectionCacheImpl instance:

ocacheimpl.setMaxLimit(10);

This example limits the cache to a maximum size of 10 pooled connection instances.

Schemes for Creating New Pooled Connections in the Oracle Implementation

The OracleConnectionCacheImpl class supports three connection cache schemes. Use these schemes in situations where (1) the application has requested a connection, (2) all existing pooled connections are in use, and (3) the maximum number of pooled connections in the cache have been reached.

• dynamic

  In this default scheme, you can create new pooled connections above and beyond the maximum limit, but each one is automatically closed and freed as soon as the logical connection instance that it provided is no longer in use. (As opposed to the normal scenario when a pooled connection instance is finished being used, where it is returned to the available cache.)

• fixed with no wait

  In this scheme, the maximum limit cannot be exceeded. Requests for connections when the maximum has already been reached will return null.

• fixed wait

  Same as the "fixed with no wait" scheme except that a request for a new connection will wait if the limit for the number of connections has been reached. In this case, the connection request waits until another client releases a connection.

Set the cache scheme by invoking the setCacheScheme() method of the OracleConnectionCacheImpl instance. Use one of the following class static constants as input:

• DYNAMIC_SCHEME

• FIXED_RETURN_NULL_SCHEME

• FIXED_WAIT_SCHEME

For example, presuming ocacheimpl is an OracleConnectionCacheImpl instance:

ocacheimpl.setCacheScheme(OracleConnectionCacheImpl.FIXED_RETURN_NULL_SCHEME);

An example of each scheme is available in the Sample Applications chapter--see "Oracle Connection Cache (dynamic)--CCache1.java" and "Oracle Connection Cache ("fixed with no wait")--CCache2.java".

Additional OracleConnectionCacheImpl Methods

In addition to the key methods already discussed in "Oracle Connection Cache Specification: OracleConnectionCache Interface", the following OracleConnectionCacheImpl methods may be useful:

• getActiveSize(): Returns the number of currently active pooled connections in the cache (pooled connection instances with an associated logical connection instance being used by the JDBC application).

• getCacheSize(): Returns the total number of pooled connections in the cache, both active and inactive.

Oracle Connection Event Listener: OracleConnectionEventListener Class

This section discusses OracleConnectionEventListener functionality by summarizing its constructors and methods.

Instantiating an Oracle Connection Event Listener

In the Oracle implementation of connection caching, an OracleConnectionCacheImpl instance constructs an Oracle connection event listener, specifying the connection cache instance itself (its this instance) as the constructor argument. This instance associates the connection event listener with the connection cache instance.

In general, however, the OracleConnectionEventListener constructor can take any data source instance as input. For example, where ds is a generic data source:

OracleConnectionEventListener ocel = new OracleConnectionEventListener(ds);

There is also a default constructor that takes no input and can be used in conjunction with the OracleConnectionEventListener class setDataSource() method:

OracleConnectionEventListener ocel = new OracleConnectionEventListener();
...
ocel.setDataSource(ds);

The input can be any kind of data source, including an OracleConnectionCacheImpl instance (because that class extends OracleDataSource).

Oracle Connection Event Listener Methods

This section summarizes the methods of the OracleConnectionEventListener class:

• setDataSource() (previously discussed): Used to input a data source to the connection event listener, in case one was not provided when constructing the listener. This can take any type of data source as input.

• connectionClosed(): Invoked when the JDBC application calls close() on its representation of the connection.

• connectionErrorOccurred(): Invoked when a fatal connection error occurs, just before a SQLException is issued to the application.

---