The JDBC driver in Oracle Database 10g supports
a new and improved connection caching mechanism, the Implicit Connection
Cache. It provides a rich set of features that address connection caching
requirements through an efficient database connection caching in the
JDBC drivers to optimize database resources and improve scalability. For
example, the Implicit connection cache may be set up such that an abandoned
connection may be detected and claimed automatically. This feature has
been highlighted in the Connection Cache Callback Sample
available on OTN.
Connection Stripping is another useful feature (coming in JDBC 4.0
per JavaOne presentation, but already available in Oracle Database 10g
JDBC). This useful feature lets users define their own set of tags or
attributes, to enable future connection retrieval. This saves valuable
CPU and network round-trips that would otherwise be necessary to set session
state. In this sample application, we demonstrate this feature of Oracle
Connection Caching.
To understand this sample application, users should
have a basic knowledge of JDBC.
Technical Overview
The implicit connection cache supports the idea of striping
a connection before returning the connection back to the cache. This
mechanism allows for any user-defined set of attributes to be applied
on a checked out connection. These attributes can later be used to retrieve
the same connection back from the cache. There are two ways to apply a
connection attribute to a connection from the cache.
By calling applyConnectionAttributes(java.util.properties
connAttr) API on the connection object. This simply sets the
supplied attributes on the connection object. It is possible to apply
attributes incrementally using this API. This allows users to apply
connection attributes over multiple calls. For example, NLS_LANG may
be applied by calling this API from module A. The next call from module
B, may then apply the TXN_ISOLATION attribute, and so on.
By calling 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 overrides the attributes,
if any, set using the applyConnectionAttributes() API.
For retrieving a connection based on the user-defined
attributes, the method getConnection(java.util.properties connAttr)
needs to be invoked. This method is supported only when the DataSource
is cache enabled. Connection Attributes are Key/Value pairs specified
as java.util.Properties on the getConnection()
request. These connectionAttributes are opaque to the JDBC connection
caching mechanism. In other words, these attributes are used only for connection
matching purposes by the connection caching mechanism, and there are no
assumptions made on the Name/Value pairs of attributes supplied. The Connection
Attributes may be set in the two ways mentioned above.
When a call is made on the method getConnection(java.util.properties
connAttr), the cache is searched to retrieve the connection that
matches the attributes. We have four possible outcomes for this operation
as mentioned below
Possibility A: If an
exact match is found, the connection is returned to the caller. This
is assumed to be most common case and the search is optimized for this.
Possibility B: If an
exact match is not found and closest match is desired, then the connection
with the closest match is returned. The closest matched connection
is one that has most of the original set of attributes matched. Note
that the closest matched connection may have a subset of the original
attributes, but does not have any other attributes that are not part
of the original list. For example, if the original list of attributes
are A, B and C, then a closest match may have A and B on it, but never
a D. In order to retrieve the closest matched connection, the ClosestConnectionMatch
property must be set to true on the connection cache.
Possibility C: If there
is no exact match (and a closest match is not desired), then a new
connection is created and returned back to the calling method. The
new connection is created using the user and password set on the DataSource.
Possibility D: If more
than one connection exist in the connection cache with the exact set
of connectionattributes, then the first connection is returned. For
example, if 4 connectionattributes are set in the getConnection() request,
and say 2 connections exist, ConnectionA that matched the first 2
attributes and ConnectionB that matches the remaining 2 attributes.
In this case, ConnectionA is returned. If none of the connectionattributes
are satisfied, a new connection is returned.
Application Overview
This application has been designed to demonstrate various
ways of searching a connection in the cache. The application consists
of a class appropriately named UnderstandCacheAttributes.java. This class
contains methods to demonstrate each possibility that can occur during
a search. The user should pass appropriate parameters to the class to
view them. During every operation, the class sets up an Oracle DataSource
Connection and initializes the Cache. A connection is then retrieved,
some default attributes are set and then the connection is returned back
to the pool. These operations are done within the method setConnectionCacheAttributes().
Now, based on the passed command line argument, a search is conducted
within the cache to retrieve the connection object. To understand more
on this, let us take a brief look at the various arguments that can be
passed to the class.
Argument Default
is passed: In this case, the getConnection() method is invoked
after passing the default attributes. This returns the original connection
(set in the method setConnectionCacheAttributes()) back
as there is an exact match between the attributes. This demonstrates
the Possibility A as described above.
Argument New is
passed: Here, the getConnection() method is invoked
after passing a new set of attributes. In this case, there is no matching
set of connection within the cache. So, this retrieves us a connection
that is new. As previously stated, the new connection is created using
the user and password set on the DataSource. This is Possibility C as mentioned above.
Argument Update
is passed: Here, we consider the possibility of incrementally
applying the attributes on the connection. The method first retrieves
the connection after passing the default set of attributes, and then
updates it with some more new attributes. Please note that the old
attributes (already set on the connection) do not get replaced during
this operation but the new attributes just get added to them. This
is done by calling the method Connection.applyConnectionAttributes(java.util.properties
connAttr). Before returning the connection back to the cache,
this method removes the new attributes set on the connection.
Argument Matching
is passed: In this case, the getConnection() method
is invoked after passing a matching set of attributes. By a matching
set of attributes, it is meant that one of the attribute values is
not correct and is changed. In this case also, the cache will retrieve
the original connection as it is the only one whose attributes match
to the provided ones. Note that this method can be used only when the
flag ClosestConnectionMatch is set to true in the connection
cache properties. This demonstrates the Possibility
B mentioned above.
Argument Clear
is passed: Here, the sample application demonstrates how to
clear the attributes from within the connection cache.
To make this sample application more clear and concise,
Possibility D has not been demonstrated.
But, it is quite self-explanatory.
Software Requirements
List the softwares required for configuring and running
this sample application.
The directory where Oracle Database 10g is installed
<JAVA_HOME>
The directory where J2SE SDK1.4.x
is installed
Configuring
the Application
Download the ConnectionCacheAttributes.jar.
Extract it into <SAMPLE_HOME> folder by executing
the following command
jar xvf ConnectionCacheAttributes.jar
This creates a folder ConnectionCacheAttributes within <SAMPLE_HOME>,
which contains all the source files.
Open the file <SAMPLE_HOME>\ConnectionCacheAttributes\oracle\otnsamples\connectioncache\attributes\ConnectionParams.java.
Edit the serverName, sid, portNo and password fields to match your
database details.
Note:You will find jar.exe in <JAVA_HOME>\bin.
Ensure <JAVA_HOME>\bin is present in your system
path.
Deploying and
Running the Application
Open the command prompt, browse to the folder
<SAMPLE_HOME>\ConnectionCacheAttributes.
Set the CLASSPATH to include Oracle JDBC Driver
file (ojdbc14.jar). This is available under <ORACLE_HOME>\jdbc\lib
folder.
Note that you need to pass appropriate command line arguments while
running the class. The possible arguments are default, new, update,
matching and clear.
Sample Application
Files
Directory
File
Description
docs
readme.html
This file
oracle\otnsamples\connectioncache\attributes
ConnectionParams.java
This class saves the Database Connection Parameters.
Update this file with your database details.
UnderstandCacheAttributes.java
This is the main class of the
sample application. To understand all the various possibilities
of Connection Attributes, run this class with appropriate parameters.
Bookmark
