Connection Caching in OC4J 10g 10.1.3 Data Sources

This Technical Note offers a close look at this new feature, and illustrates how it is used in applications.

	Managed Data Source	Native Data Source
Connections retrieved from a data source can participate in global transactions	Yes	No
Data source makes use of OC4J's connection pool and statement caches	Yes	No
Connection returned from a data source rapped with an OC4J Connection Proxy	Yes	No

Connection Caching

Connection caching, generally implemented in the middle tier, allows a single database connection to be shared among different applications.  The middle tier maintains a pool of pre-allocated physical database connections that applications can use to interact with database servers.  When applications request a database connection, the middle tier first looks in the pool to see if there are any available connections that would satisfy the request; if so, the middle tier simply returns one of those connections.

The pooled connections in the cache all have some connection properties or attributes (database name, server name, port number, and so on). Most connection caches consist of a pool of one or more connections to the same database for the same username.

Connection caching in OC4J 10g 10.1.3 data sources leverages all of the new caching features provided by Oracle Database 10g JDBC—specifically, features in the new Implicit Connection Cache.

• Driver independence
• Transparent access to the connection cache
• Multiple users and passwords per cache
• Compliance to JDBC 3.0 standards
• Connection recycling and refresh of stale connections
• Attribute-based search for connections
• Multiple cache enabled DataSource
• Connection cache callback mechanism
• Integrated support for RAC fast connection failover.

For managed data sources, configuring connection caches can be done in two steps:

1. Add a connection cache with the <connection-pool> XML element in data-sources.xml to specify the details of its desired attributes.
2. Use the "connection-pool-name" attribute in each <managed-data-source> XML element in the same file to specify the name of the connection cache that this managed data source uses to pool its connections.

Step 2 is relatively simple, so let's focus only on Step 1.  The <connection-pool> element has the following attributes (each attribute is optional except when marked as 'Required'):

Attribute Name	Description	Default
factory-class	(Required) Defines the fully-pathed class of the connection-factory implementation.  It must be an implementation of java.sql.Driver, javax.sql.DataSource, javax.sql.ConnectionPoolDataSource, or javax.sql.XADataSource.	None
url	(Required) The url to use to connect to the underlying database	None
user	The default user to use to connect to the database	None
password	The default password to use to connect to the database	None
login-timeout	The maximum time (in seconds) to wait while attempting to connect to a database.  A value of zero specifies that the timeout is the default system timeout if there is one; otherwise, it specifies that there is no timeout.	0

Now let's look at some concrete examples:

Managed Data Source Using an XADataSource Connection Factory


<managed-data-source
 jndi-name="jdbc/ManagedXADS"
 description="Managed DataSource"
 connection-pool-name="myConnectionPool"
 name="ManagedXADS"/>

<connection-pool
 name="myConnectionPool"
 min-connections="10"
 max-connections="30"
 inactivity-timeout="30">
  <connection-factory 
   factory-class="oracle.jdbc.xa.client.OracleXADataSource"
   user="scott"
   password="tiger"
   url="jdbc:oracle:thin:@localhost:1521:oracle"/>
   <property name="nativeXA" value="true"/>
  </connection-factory>
</connection-pool>
        

Managed Data Source Using a DataSource Connection Factory

<managed-data-source
 jndi-name="jdbc/ManagedDS"
 description="Managed DataSource">
 connection-pool-name="myConnectionPool"
 name="ManagedDS"/>


<connection-pool
 name="myConnectionPool"
 min-connections="10"
 max-connections="30"
 inactivity-timeout="30">
  <connection-factory 
   factory-class="oracle.jdbc.pool.OracleDataSource"
   user="scott"
   password="tiger"
   url="jdbc:oracle:thin:@localhost:1521:oracle"/>
   <property name="loginTimeout" value="30"/>
  </connection-factory>
</connection-pool>

For native Oracle data sources, configuring connection caches is primarily done programmatically. (See the following section for more details.)  Declarative configuring of connection caches can also be done in two steps:

1. Use "oracle.jdbc.pool.OracleDataSource" as the value for the "data-source-class" attribute in each <native-data-source> XML element in data-sources.xml. This is the data source implementation in the Oracle 10g JDBC drivers that comes with implicit connection caching.
2. Specify in the <property> elements within <native-data-source> any cache-specific properties to control the connection cache's behavior.

Name	Type	Description
connectionCacheName	String	Name of cache; cannot be changed after cache has been created.
connectionCachingEnabled	Boolean	Whether Implicit Connection Caching is enabled
fastConnectionFailoverEnabled	Boolean	Whether RAC Fast Connection Failover is enabled

Here's a concrete example:

<native-data-source
 name="nativeDataSource"
 jndi-name="jdbc/nativeDS"
 description="Native DataSource"
 data-source-class="oracle.jdbc.pool.OracleDataSource"
 user="scott"
 password="tiger"
 url="jdbc:oracle:thin:@localhost:1521:oracle">
  <property name="connectionCacheName" value="ICC1"/>
  <property name="connectionCachingEnabled" value="true"/>
  <property name="fastConnectionFailoverEnabled" value="false"/>

</native-data-source>

• String getConnectionCacheName()
• void setConnectionCacheName(String cacheName)
• java.util.Properties getConnectionCacheProperties()
• void setConnectionCacheProperties(java.util.Properties cp)

• static OracleConnectionCacheManager getCacheManagerInstance()
• void createCache(String cacheName, javax.sql.DataSource ds, Properties cacheProperties)
• String createCache(javax.sql.DataSource ds, Properties cacheProperties)
• void removeCache(String cacheName, int mode)
• void refreshCache(String cacheName, int mode)
• java.util.properties getCacheProperties(String cacheName)

private void configDataSource(OracleDataSource ods) {
......
/* Load the properties file to get the connection properties  
 * from the Connection.properties file
 */
Properties prop = this.loadParams("Connection"); 
ods.setDriverType("thin");
ods.setHostName("localhost");
ods.setNetworkProtocol("tcp");
ods.setPortNumber(1521);
ods.setUser("scott");
ods.setPassword("tiger");
ods.setServiceName("oracle");
ods.setConnectionProperties(prop);
......
}   

private void configCachingInDataSource(OracleDataSource ods)
  throws SQLException
{
  ......
  ods.setConnectionCachingEnabled(true);  // Enable caching
  ods.setConnectionCacheName(CACHE_NAME); // Set the cache name   

  Properties cacheProperties = new Properties();

  cacheProperties.setProperty("MinLimit", "1");  // Set Min Limit for the Cache 
  cacheProperties.setProperty("MaxLimit", "15"); // Set Max Limit for the Cache  
  cacheProperties.setProperty("InitialLimit", "10");   // Set the Initial Limit  

  ods.setConnectionCacheProperties(cacheProperties);

}

private void initializeConnectionCache() throws Exception {
  .............. 
 
  OracleDataSource ods = new OracleDataSource();

  this.configureDataSource(ods);
  ods.setConnectionCachingEnabled(true);  // Enable caching

  // Initialize the Connection Cache
  OracleConnectionCacheManager connMgr =
    OracleConnectionCacheManager.getConnectionCacheManagerInstance();
  
  /* This object holds the properties of a cache and is passed to the 
   * ConnectionCacheManager while creating the cache.
   */
  Properties cacheProperties = new Properties();

  cacheProperties.setProperty("MinLimit", "1");   // Set Min Limit for the Cache 
  cacheProperties.setProperty("MaxLimit", "15");  // Set Max Limit for the Cache 
  cacheProperties.setProperty("InitialLimit", "10");    // Set the Initial Limit  

  /* Create the cache by passing the cache name, data source and the 
   * cache properties 
   */
  connMgr.createCache(CACHE_NAME, ods, properties);

Database connection cache is a powerful part of the Oracle Application Server. With simplified data source configuration and integrated connection cache management from Oracle Database 10g, OC4J 10g 10.1.3 provides a scalable platform for building distributed J2EE applications.