|
DEVELOPER: JDBC
Keep Your Connections
By Kuassi Mensah
Use Implicit Connection Cache for transparent cache access.
The new generation of Java Database Connectivity (JDBC) drivers in Oracle Database 10g can make a big difference in how well your Java applications perform. Whether you're working with Java 2 Standard Edition applications, Servlets, JavaServer Pages, stateful session beans, Enterprise JavaBean containers, persistence engines, or object-relational mapping frameworks, the latest Oracle JDBC drivers significantly simplify a range of DataSource connectivity issues for Java developers. JDBC connection caching was available in previous releases, but Oracle Database 10g has added a new level to it. Generally speaking, connection caching is a way to keep a small number of physical database connections in a pool that can be shared and reused by a large number of concurrent users, thus avoiding the expensive operations of creating a new physical database connection each time a connection is requested as well as closing the connection when it is released. In this way, for example, a pool of 100 physical database connections can be satisfactorily shared by 1,000 concurrent users.
There are, however, several shortcomings in the current JDBC connection cache specification and the implementations that are based on it. Although JDBC in Oracle9i Release
2 implemented much of the current JDBC connection cache specification, there were limitations to the standard that made using the connection
cache challenging and reduced the benefits. In Oracle Database 10g, the JDBC implementation brings the following enhancements to the connection cache:
- Transparent cache access
- Caching of heterogeneous user/password pairs
- Retrieval of connections based
on connection attributes
These enhancements come from features of the new Oracle Database 10g JDBC Implicit Connection Cache. Note that you enable most of the capabilities in the Implicit Connection Cache by changing default settings. To use the Implicit Connection Cache capabilities, customize your environment by explicitly setting or changing connection cache properties or connection attributes.
Transparent Cache Access
By default, the getConnection() method in the standard OracleDataSource API creates a new database session and a physical connection to a database—thus incurring performance and scalability penalties. Using Implicit Connection Cache, with the DataSource property ConnectionCachingEnabled set to true, getConnection() services all connection requests from the connection cache. The following sets the DataSource property ConnectionCachingEnabled to true:
ods.setConnectionCachingEnabled(True);
// optional, set name of cache
ods.setConnectionCacheName("MyCache");
// optional, set cache properties
ods.setConnectionCacheProperties(cp);
ctx.bind("MyDS", ods);
// look up DataSource
ods =(OracleDataSource) ctx.lookup("MyDS");
You can create an Oracle Database 10g JDBC connection cache either implicitly, by the first invocation of the getConnection() method, or explicitly, by using the Cache Manager API. The Cache Manager API is intended for use by J2EE containers and enterprise resource planning (ERP) frameworks that can shield the Java application developer from managing the connection cache. Using the API can also help preserve application code portability.
The following creates the MyCache cache and a connection to the MyDS data source:
conn = ods.getConnection();
The following also creates a MyCache cache and a connection to the MyDS data source, but it is authenticated by user SCOTT/TIGER:
conn = ods.getConnection(
"SCOTT","TIGER");
Subsequent getConnection() invocations either create a new connection (if the cache was not previously initialized) or simply retrieve an existing connection from the cache. Once you retrieve the connection, you can proceed with statement creation.
You can create a statement by using the following syntax:
Statement stmt = conn.createStatement ();
...
You can close a statement by using the following syntax:
stmt.close();
stmt = null;
You can populate the cache in one of two ways: either by preinitializing it with the Cache Manager APIs or, incrementally, upon the release of connections back to the cache.
The following syntax returns a connection to the cache:
conn.close();
conn = null;
Cache Heterogeneous User/Password Pairs
Although a database does not impose any restriction on multiple connection identities, a traditional connection cache may impose such a limitation on pairs of user/password values. The Implicit Connection Cache, however, can handle any user-authenticated connection. For example, a sudhakar.krishna connection can coexist very well with a john.smith connection in the same connection cache.
Retrieve Connections Based on Connection Attributes
One of the great new features in the Implicit Connection Cache is the notion of connection striping. Connection striping (or labeling) involves applying user-defined attributes to a connection and making the state of the attributes persist when the connection is returned back to the cache. This striping speeds up future connection retrieval, because cached connections don't have to reinitialize their state every time they are retrieved.
Make connection requests for specific attributes and values by calling getConnection(connAttr) from the data source. Following are several examples that show how to use connection attributes to retrieve connections with specific attributes from the cache.
This example retrieves a connection from the cache based on the NLS_LANG attribute:
java.util.Properties connAttr = null;
connAttr.setProperty("NLS_LANG",
"ISO-LATIN-1");
conn = ds.getConnection(connAttr);
...
This example retrieves a connection from the cache based on the isolation level (TRANSACTION_ISOLATION) and attribute value (SERIALIZABLE) and returns the connection to the cache:
java.util.Properties connAttr = null;
connAttr.setProperty(
"TRANSACTION_ISOLATION", "SERIALIZABLE"
);
// retrieve connection that
// matches TRANSACTION_ISOLATION
conn = ds.getConnection(connAttr);
...
// preserve attribute settings for
// the connection and return it
// to the connection cache
conn.close(connAttr);
Note that when returning a connection to the cache, applications have the opportunity to preserve current connection attribute settings for future use. See the following section, "Apply Connection Attributes to a Cached Connection," for information on using conn.close(connAttr) to save attributes.
This example retrieves a connection from the cache, based on the CONNECTION_TAG attribute value (JOE'S_CONNECTION), returns the connection to the cache, and retrieves the same connection:
...
java.util.Properties connAttr = null;
connAttr.setProperty("CONNECTION_TAG",
"JOE'S_CONNECTION"
);
// retrieve connection that
// matches JOE'S_CONNECTION
conn = ds.getConnection(connAttr);
// preserve attribute settings for
// the connection and return it
// to the connection cache
conn.close(connAttr);
// retrieve same connection
conn = ds.getConnection(connAttr);
Apply Connection Attributes to a Cached Connection
A connection attribute can be applied to a connection in the cache in two ways:
- One approach is to call the applyConnectionAttributes(java.util.Properties connAttr) API on the connection object. This simply sets the supplied attributes on the connection object. It's possible to apply attributes incrementally by using this API, allowing users
to apply connection attributes over multiple calls. For example, you can apply NLS_LANG by calling this API from module A. The next call from module B can
then apply the TRANSACTION_ISOLATION attribute, and so on.
- A second approach is to call the close(java.util.Properties connAttr) API on the connection object. This API closes the logical connection and then applies the supplied connection
attributes on the underlying PooledConnection (physical connection). The attributes set via this close() API override any attributes set with the applyConnectionAttributes() API.
The following example shows a
call to the close(connAttr) API on the connection object that lets the cache apply the matched connection attributes to the connection before returning it to the cache. This ensures that when a subsequent connection request with the same connection attributes is made, the cache will find a match.
// set attributes for connection
java.util.properties connAttr = null;
connAttr.setProperty("NLS_LANG",
"ISO-LATIN-1");
// request connection based on
// NLS_LANG attributes
conn = ds.getConnection(connAttr);
java.util.properties unmatchedAttr =
conn.getUnMatchedConnectionAttributes();
...
// preserve attribute settings for
// the connection and return it
// to the connection cache
conn.close(connAttr);
Retrieve Connections Based on Attributes and Weights
Connections may be selectively retrieved from the connection cache based on a combination of connection attributes and attribute weights. Weights are assigned to each key in a connection attribute in a one-time operation that also changes cache properties. The cache property CacheAttributeWeights is one of the java.util.Properties that allows the setting of attribute weights. Each weight is an integer value that defines how expensive a particular key is in terms of resources. Once the weights are specified in the cache, connection requests are made on the data source by a call to getConnection(connAttr). The connAttr argument refers to the keys and their associated values. The connection retrieval from the cache involves searching for a connection that satisfies a combination of the following:
- A key/value match on a connection from the cache
- The maximum total weight of all the keys of the connection attributes that were matched on the connection
Consider the example in Listing 1, where a cache is configured with CacheAttributeWeights and a connection request is made.
The getConnection() request in Listing 1 tries to retrieve a connection from the MyCache cache. During the connection matching and retrieval from the cache, one of two things
can happen:
- An exact match is found. As in the example in Listing 1, an exact match is a connection that has all the defined keys defined by property values—NLS_LANG, SecurityGroup, and Application—and the same attribute values.
- An exact match is not found. In this case, a closest match based on the attribute key/value and their associated weights is used (but only if the ClosestConnectionMatch property is set). For example, the closest match may be a connection that matches the attributes of NLS_LANG and Application but not SecurityGroup. It is also possible to find connections that match the same number of different keys but have different combined weights. For example, connection1 could have an attribute match of SecurityGroup with its associated weight of 8 and Application with its weight of 4, for a total weight of 12, whereas connection2 could have an attribute match of NLS_LANG (10) and Application (4), with their combined weight being 14. In this case, connection2 is returned. In other words, connection2 is the closest match and more expensive to reconstruct (from the caller's perspective), compared to connection1. When none of the connection attributes matches, a new connection is returned. The new connection is created with the user and password set on the data source.
Once the connection is returned, the user can invoke the getUnMatchedConnectionAttributes() API on the connection object to
return a set of attributes (java.util.Properties) that did not match the criteria. The unmatched-attribute list can then be used by the caller (or application) to reinitialize these values before using the connection.
Conclusion
Oracle Database 10g JDBC Implicit Connection Cache greatly simplifies JDBC connection caching for Java/JDBC applications and J2EE components. In addition to providing transparent cache access, Implicit Connection Cache provides systems for tagging and retrieving connections by using attributes and weights to speed connection reuse.
Kuassi Mensah (kuassi.mensah@oracle.com) is a group product manager in the Server Technologies division at Oracle Corporation.
| 
Bookmark
