IMPORTANT NOTE : I'm not the author of this post. Ths original location is here : http://blog.csdn.net/hakunamatata2008/archive/2009/05/13/4175764.aspx
High-Performance Oracle JDBC Programming
Learn how to improve performance of Oracle-driven JDBC programs by leveraging connection and statement pooling features.
By Yuli Vasiliev
Published April 2009
Using pooling techniques such as connection pooling and statement pooling can significantly improve performance of database-intensive applications, because it enables the reuse of objects that would otherwise need to be created from scratch, at the expense of time and resources.
Reusing database connection objects representing physical database connections utilized by an application can result in significant performance gains, provided that the application interacts with the database intensively, frequently re-establishing connections with the same parameters. On the other hand, you won’t benefit from using a connection pool if your application connects to its underlying database only rarely. In practice, though, many database-intensive applications can benefit from utilizing a connection pool, provided that the pool’s settings, such as those that put a limit on the maximum and minimum number of connections allowed, are optimized for that particular application.
Like connection pooling, statement pooling is a technique for improving application performance. You can achieve additional performance gains by pooling statements that are executed multiple times in the course of a run of your program. It’s important to realize, though, that statement pooling is not a silver bullet for performance problems. If you cache every single statement without distinguishing how many times it is executed in your program, you are unlikely to achieve any performance improvement. In fact, caching the statements that are issued only once during program execution may actually degrade performance, due to the overhead associated with putting and then keeping such statements in the cache.
This article shows you how to take advantage of pooling connections and statements to improve performance of data-intensive Java DataBase Connectivity (JDBC) programs interacting with Oracle Database via the Oracle JDBC thin driver. In particular, it looks at the Oracle Universal Connection Pool (UCP) for JDBC, which provides a full-featured connection pool implementation for caching JDBC connections. Finally it discusses how you might benefit from statement pooling, utilizing features specific to Oracle’s JDBC drivers as well as the new JDBC 4.0 methods added to the Statement interface and available in Oracle JDBC drivers supporting Java Development Kit (JDK) 1.6 and later versions.
Setting Up Your Working Environment
To follow the examples in this article, in addition to having access to an Oracle database, you’ll need to have the following software components installed on your development machine (see "Downloads" portlet for links:
The Oracle JDBC thin driver is a Type IV JDBC driver, meaning that it’s platform-independent and does not require any extra Oracle software on the client side to interact with an Oracle database. So you can download the JAR file containing the classes of an appropriate thin driver version from the JDBC Driver Downloads page and then install the driver on your machine without having to install/upgrade any other Oracle software. To install the driver, you simply need to copy its JAR files to your local file system and then include paths to these JARs to the CLASSPATH environment variable. For example, you might include the following paths:
ORACLE_HOME/jdbc/lib/ojdbc6.jar
ORACLE_HOME/jlib/orai18n.jar
If you have an Oracle database installed on your machine, the thin driver has been installed with your Oracle Database installation. However, because the thin driver does not depend on any additional Oracle software, you can easily upgrade to the latest release of the driver by using appropriate JAR files that can be found on the JDBC Driver Downloads page.
UCPis a new feature included in Oracle Database 11g, starting with release 11.1.0.7. This feature is also available in Oracle Application Server, beginning with Oracle Application Server 11gRelease 1. If you’re using older software that doesn’t ship the JAR file for UCP(it’s called ucp.jar) or you want to upgrade to the latest UCPrelease, you can pick up ucp.jar from the Oracle Database UCP Downloads page. This package contains UCP’s classes for inclusion in the classpath to enable the feature. The path included might look like this:
ORACLE_HOME/ucp/lib/ucp.jar
Caching JDBC Connections with UCP
If you’re developing a database-intensive application, you might benefit from using a connection pool, which enables you to reuse connections rather than create a new one each time it is requested. Connection pooling conserves resources required for creating new database connections and improves your application performance, because creating a new connection is always a performance-intensive operation.
The Oracle Universal Connection Pool for JDBC represents a full-featured implementation of a connection pool caching JDBC connections. UCP is a very useful feature, in that it lets you reuse connection objects, thus speeding the process of obtaining a connection and saving resources associated with opening new database connections.
Suppose you want to create aUCP JDBC connection pool to reuse connections established to the HR/HR Oracle Database sample schema. The following program, representing a simple example of aUCP JDBC connection pool in action, shows how you might achieve this. Here you first create a pool-enabled data source instance and then set up the connection and pool properties. Once you’ve done that, you borrow a connection from the pool and then use that connection to interact with the database. Finally, you close the connection, returning it to the pool.
/*
*A simple example illustrating aUCP JDBC connection in action
*/
import java.sql.*;
import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;
public class UcpConnection {
public static void main(String args[]) throws SQLException {
try
{
//Creating a pool-enabled data source
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
//Setting connection properties of the data source
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setURL("jdbc:oracle:thin:@//localhost:1521/XE");
pds.setUser("hr");
pds.setPassword("hr");
//Setting pool properties
pds.setInitialPoolSize(5);
pds.setMinPoolSize(5);
pds.setMaxPoolSize(10);
//Borrowing a connection from the pool
Connection conn = pds.getConnection();
System.out.println("\nConnection borrowed from the pool");
//Checking the number of available and borrowed connections
int avlConnCount = pds.getAvailableConnectionsCount();
System.out.println("\nAvailable connections: " + avlConnCount);
int brwConnCount = pds.getBorrowedConnectionsCount();
System.out.println("\nBorrowed connections: " + brwConnCount);
//Working with the connection
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select user from dual");
while(rs.next())
System.out.println("\nConnected as: "+rs.getString(1));
rs.close();
//Returning the connection to the pool
conn.close();
conn=null;
System.out.println("\nConnection returned to the pool");
//Checking the number of available and borrowed connections again
avlConnCount = pds.getAvailableConnectionsCount();
System.out.println("\nAvailable connections: " + avlConnCount);
brwConnCount = pds.getBorrowedConnectionsCount();
System.out.println("\nBorrowed connections: " + brwConnCount);
}
catch(SQLException e)
{
System.out.println("\nAn SQL exception occurred : " + e.getMessage());
}
}
}
An important thing to notice here is what’s happening when a connection is closed. The output of the above program illustrates that closing a connection borrowed from a UCP JDBC connection pool actually returns that connection to the pool, where it becomes available for the next connection request.
Here is what the program output should look like:
Connection borrowed from the pool
Available connections: 4
Borrowed connections: 1
Connected as: HR
Connection returned to the pool
Available connections: 5
Borrowed connections: 0
Borrowing a Connection with JNDI
Instead of creating a pool-enabled data source on the fly, as you did in the preceding example, you can create it in advance and bind it to a Java Naming and Directory Interface (JNDI) context and a logical name. Once you’ve registered a data source with JNDI, you can get an instance of it by performing a JNDI lookup, specifying the JNDI name to which the data source is bound.
Suppose you want to register a pool-enabled data source designed to reuse connections to the HR/HR database schema, associating this data source with logical name jdbc/HRPool in the JNDI tree. To do this, you must create a PoolDataSource object representing the above data source, set its properties, and then register it with a JNDI naming service. This can be done with the following Java program:
/*
*An example of how you can register
* a pool-enabled data source with JNDI
*/
import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;
import javax.naming.*;
import java.util.Hashtable;
public class JNDIRegister {
public static void main(String argv[]) {
try {
//Creating a pool-enabled data source instance and setting its properties
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setURL("jdbc:oracle:thin:@//localhost:1521/XE");
pds.setUser("hr");
pds.setPassword("hr");
pds.setInitialPoolSize(5);
pds.setMinPoolSize(5);
pds.setMaxPoolSize(10);
//Registering the data source with JNDI
Hashtable env = new Hashtable();
env.put(Context.INITIAL_CONTEXT_FACTORY,"com.sun.jndi.fscontext.RefFSContextFactory");
Context ctx = new InitialContext(env);
ctx.bind("jdbc/HRPool", pds);
}
catch (Exception e) {
System.out.println(e);
}
}
}
Before you can run this program, you have to set up Sun's file system JNDI service provider, which can be downloaded from here. Make sure to add the following JAR files to the classpath to be able to run the above program:
install_dir/sun/lib/fs/fscontext.jar;install_dir/sun/lib/fs/providerutil.jar
After you run the above program, you can utilize the jdbc/HRPool pool-enabled data source in your Java applications, whether they be JavaServer Pages, servlets, or standalone applications. The following is a standalone Java application utilizing this data source:
/*
*An example of a JNDI lookup for
* a pool-enabled data source
*/
import java.sql.*;
import oracle.ucp.jdbc.PoolDataSource;
import javax.naming.*;
import java.util.Hashtable;
public class JNDILookup {
public static void main(String argv[]) {
PoolDataSource pds;
//Performing a lookup for a pool-enabled data source registered in JNDI tree
try {
Hashtable env = new Hashtable();
env.put(Context.INITIAL_CONTEXT_FACTORY,"com.sun.jndi.fscontext.RefFSContextFactory");
Context ctx = new InitialContext(env);
pds = (PoolDataSource) ctx.lookup("jdbc/HRPool");
}
catch (NamingException eName) {
System.out.println("Cannot look up " + "jdbc/HRPool" + ": " +eName);
return;
}
//Borrowing a connection from the data source returned by the JNDI lookup
try {
Connection conn = pds.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select user from dual");
while(rs.next())
System.out.println("\nConnected as: "+rs.getString(1));
if (conn != null)
conn.close();
}
catch (SQLException eSQL) {
System.out.println("Cannot obtain a connection: " + eSQL);
}
return;
}
}
The first thing you do in the above program is to initialize the JNDI context, which is then used to perform a JNDI lookup for the jdbc/HROracle pool-enabled data source. Next you borrow a connection from the data source instance returned by the JNDI lookup and use it to issue a query against the database.
As you no doubt have realized, the approach discussed in this section simplifies the process of using connection pools. Registering a pool-enabled data source once and then obtaining an instance of it with a JNDI lookup when needed eliminates the need to set up the connection pool properties each time you initialize it. You just obtain a pool instance with the properties defined in advance.
High Availability and Performance
It’s important to emphasize that UCP supports new JDBC 4.0 high-availability and performance features, such as pool refreshing and connection validating, that are not related to Oracle Real Application Clusters (RAC) and therefore do not require an Oracle RAC database.
Validate connection
Furthermore, UCP provides the ability to validate connections on borrow. Validating connections on borrow is a useful technique, because it enables you to check whether a connection is still valid before you start using it. To help with this problem, a UCP JDBC connection pool instance has the ValidateConnectionOnBorrow property of type Boolean, which you need to set to true with the setValidateConnectionOnBorrow method:
pds.setValidateConnectionOnBorrow(true);
Then you need to specify an SQL statement you want to be issued to make sure that the connection is still valid. You can do this with the setSQLForValidateConnection method:
pds.setSQLForValidateConnection("select user from dual");
When utilizing an Oracle JDBC driver, though, there is no need to set the SQLForValidateConnection property—the pool will perform an internal ping to test the validity of the connection being borrowed.
Verifying connections on borrow is a good thing, but what if a connection becomes stale after it has been successfully validated on borrow? Is there any way to verify a connection after it has been borrowed? To address this issue, the JDBC 4.0 specification added the isValid method to the Connection interface, enabling you to test the validity of a connection when you want to.
Taking it one step further, UCP for JDBC provides the oracle.ucp.jdbc.ValidConnection interface, which includes two methods: isValid and setInvalid. These methods can be especially useful when utilized in conjunction with a retry mechanism implemented with recursion or iteration (looping). For instance, you might implement a method that will borrow and then utilize a connection, making a recursive call to itself in case the connection has become stale and consequently the operation cannot be completed. An important thing to keep in mind when implementing such a recursive mechanism is that it must provide the ability to limit the number of recursive calls to be made and that each new recursive call must reduce that number, thus preventing the possibility of endless looping.
The following is a simple program providing an example of how you might use the oracle.ucp.jdbc.ValidConnection interface methods in conjunction with a retry mechanism based on recursion.
/*
*An example of validating connections on borrow;
*this also shows the use of the ValidConnection interface's methods:
*isValid and setInvalid methods in combination with a retry mechanism
*/
import java.sql.*;
import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.ValidConnection;
import javax.naming.*;
import java.util.Hashtable;
public class ConnectionValidating {
public static void main(String argv[]) {
PoolDataSource pds;
//Looking up for the jdbc/HRPool pool-enabled data source registered in JNDI tree
...
//for actual code see the JNDI lookup example
//discussed in the Borrowing a Connection with JNDI section earlier
...
try {
//Instructing the pool to validate connections on borrow
pds.setValidateConnectionOnBorrow(true);
//Calling the getUser method that borrows a connection from the pool
//limiting the number of recursive calls to 3
System.out.println("\nConnected as :"+getUser(pds, 3));
}
catch (SQLException eSQL) {
System.out.println("\nSQLException: " + eSQL);
return;
}
}
//This method borrows a connection from the pool and will make a recursive call
//if it turns out that the borrowed connection has become unusable
private static String getUser (PoolDataSource pds, int recursiveCalls) throws SQLException {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String user = null;
try {
//Borrowing a connection from the pool
conn = pds.getConnection();
//Working with the connection
stmt = conn.createStatement();
rs = stmt.executeQuery("select user from dual");
while(rs.next())
user = rs.getString(1);
if (conn != null)
conn.close();
}
catch (SQLException eSQL) {
if (recursiveCalls > 0 && !((ValidConnection) conn).isValid()) {
System.out.println("\nConnection is no longer valid: " + eSQL);
//Calling setInvalid often leads to an exception
//so it's a wise idea to put it in a separate try block
try {
((ValidConnection) conn).setInvalid();
} catch (SQLException conEx) {
System.out.println("\nInvalidating failed: " + conEx);
}
conn.close();
conn = null;
System.out.println("\nRetrying to obtain a new connection");
//making a recursive call to getUser in an attempt to obtain a valid connection
//the number of recursive calls allowed is reduced by 1
user = getUser(pds, recursiveCalls - 1);
} else {
System.out.println("\nSQLException: " + eSQL);
}
}
finally {
return user;
}
}
}
In this example, the getUser method calls itself from within the catch clause of the try/catch statement implemented in this same method. Here, you limit the number of allowed recursive calls to three. If it fails to obtain a valid connection three times in a row, you stop trying and get out.
High-availability features
Aside from the generic high-availability and performance features discussed above, UCP for JDBC can be integrated with Oracle RAC features such as Fast Connection Failover (FCF) and Runtime Connection Load Balancing, making it easier to manage connections to an Oracle RAC database.
The following snippet illustrates how you can enable FCF when using a UCP JDBC connection pool managing connections to an Oracle RAC database. Note that using FCF requires you to add the Oracle Notification Service library (ons.jar) to an application’s classpath. The Oracle Notification Service library is shipped as part of Oracle Database, starting with Oracle Database 10g.
...
//Creating a pool-enabled data source
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
//Setting pool properties
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
//Setting a RAC-specific URL
pds.setURL(
"jdbc:oracle:thin:@" +
"(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)" +
"(ADDRESS=(PROTOCOL=TCP)" +
"(HOST=rachost1)(PORT=1521))" +
"(ADDRESS=(PROTOCOL=TCP)" +
"(HOST=rachost2)(PORT=1521)))" +
"(CONNECT_DATA=(SERVICE_NAME=orcl)))");
pds.setUser("usr");
pds.setPassword("pswd");
pds.setMinPoolSize(10);
pds.setMaxPoolSize(20);
//Configuring remote ONS subscription
pds.setONSConfiguration("nodes=rachost1:4200,rachost2:4200");
// Enabling Fast Connection Failover
pds.setFastConnectionFailoverEnabled(true);
Once you have a connection pool set up and FCF enabled, you can borrow a connection from the pool and create queries on it as you would in a non- RAC-specific program.
Connection conn = pds.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = null;
Then you can implement a retry mechanism that will check a connection for validity after a RAC-down event triggers UCP FCF actions, trying again to connect to surviving RAC instances in case the connection has become stale. The following snippet illustrates how this could be implemented within a while loop.
boolean retry = true;
while(retry)
{
try
{
//Getting a RAC connection from the pool
conn = pds.getConnection();
// Executing a query on the connection.
rs = stmt.executeQuery("select user from dual");
rs.next();
System.out.println("\nConnected as : " + rs.getString(1));
//Setting retry to false to exit the loop
retry = false;
}
catch (SQLException eSQL)
{
System.out.println("\nSQLException: " + eSQL);
// Checking connection usability after a RAC-down event triggers UCP FCF actions
if (conn == null || !((ValidConnection) conn).isValid())
{
//Closing the connection
try
{
conn.close();
}
catch (SQLException eClose)
{
System.out.println("\nException arose when closing connection: " + eClose);
}
//Setting retry to true to try again
retry = true;
}
}
Thread.sleep(1000);
}
If a connection has been successfully borrowed from the pool and the statement execution has not triggered an exception, there is no need to retry the operation and therefore the execution flow will jump out of the loop. Otherwise, it will try to reconnect and will then perform the statement execution again.
Optimizing Connection Pools
Optimize pooling behavior with parameters and callbacks
The UCP JDBC connection pool provides a set of properties you can use to optimize pooling behavior. For example, you can regulate the pool size, setting the properties controlling the initial, maximum, and minimum pool size. In the preceding sections, you have seen how you can set up these properties.
Aside from the properties controlling the pool size, there are properties controlling stale connections. For example, you can set up the pool’s MaxConnectionReuseTime property, thus configuring a maximum connection reuse time. In some environments, you may find it useful to have connections removed from the pool after a connection has been borrowed a certain number of times. You can do this by setting up the MaxConnectionReuseCount property.
You can set up the AbandonConnectionTimeout property to instruct the pool to reclaim borrowed connections after a connection has not been used for a certain amount of time. Also, you can set up the TimeToLiveConnectionTimeout property, limiting how long a borrowed connection may be used before it is reclaimed by the pool.
If you anticipate that the pool may run out of connections at some point, you can set the ConnectionWaitTimeout property to the number of seconds an application request waits for a connection when no connections are available in the pool. Also, there is the InactiveConnectionTimeout property, which enables you to specify how long an available connection can remain unborrowed before it is removed from the pool.
Another interesting property is TimeoutCheckInterval, with which you can set up the timeout check interval, controlling how often the timeout properties discussed above will be enforced. By default, this property is set to 30, meaning that the timeout check cycle runs every 30 seconds.
All the optimization features discussed so far in this section require you to set a certain property to an appropriate value in order to get the effect you want, but to enable the connection harvesting feature, which is used to ensure a certain number of available connections in the pool, you need to use a mechanism that’s a bit more complicated. This feature is explained in the rest of this section by example.
Let’s say you set up the pool’s size properties as follows:
...
pds.setInitialPoolSize(10);
pds.setMaxPoolSize(20);
With initialPoolSize set to 10, you will have 10 connections upon initializing the connection pool. Next, with the following code, you enable the connection harvesting feature, thus making the pool’s connections harvestable:
pds.setConnectionHarvestTriggerCount(5);
pds.setConnectionHarvestMaxCount(2);
The properties set above instruct the pool to reclaim two borrowed connections when the number of available connections in the pool drops to five. Let’s now create an array of five connection objects that can be then used to hold five connections borrowed from the pool:
//Creating an array of connection objects
Connection[] conn = new Connection[5];
Before populating the above array with connections, though, you need to create an array of callback objects, each of which will be registered with a connection. A callback object must be an instance of a custom implementation of the ConnectionHarvestingCallback abstract interface. A simple implementation is shown a little later in this section.
With the following code, you create an array of five CustConnectionHarvestingCallback objects:
//Creating an array of callback objects
CustConnectionHarvestingCallback[] callbk = new CustConnectionHarvestingCallback[5];
In the following loop, you borrow four connections from the pool and also create four callback objects, each of which is registered with a connection:
//Borrowing four connections from the pool
for (int i = 0; i <>
{
conn[i] = pds.getConnection();
//Registering the callback object with each connection
callbk[i] = new CustConnectionHarvestingCallback(conn[i]);
((HarvestableConnection) conn[i]).registerConnectionHarvestingCallback(callbk[i]);
}
Before you borrow the fifth connection to trigger harvesting, you can disable harvesting on a certain connection for testing purposes. You might recall that you specified two borrowed connections to be returned to the pool when the number of available connections drops to five. By default, the connection harvest feature will harvest those two connections that were borrowed first. Thus, in this example, conn[0] and conn[1] will be harvested. By setting conn[0] as nonharvestable, however, you make it harvest conn[1] and conn[2].
//Setting conn[0] as nonharvestable
((HarvestableConnection) conn[0]).setConnectionHarvestable(false);
Let’s now trigger harvesting by borrowing the fifth connection from the pool.
//Borrowing the fifth connection to trigger harvesting
conn[4] = pds.getConnection();
callbk[4] = new CustConnectionHarvestingCallback(conn[4]);
((HarvestableConnection) conn[4]).registerConnectionHarvestingCallback(callbk[4]);
Recall from the discussion of the timeout check interval earlier in this section that this interval is set to 30 by default. What this means in this example is that harvesting will not be triggered immediately but within a 30-second interval.
// Waiting for harvesting to happen
Thread.sleep(30000);
To make sure everything has worked as planned, you might want to check through the connections to see which ones have been closed and returned to the pool:
//Checking connections
for (int i = 0; i <>
{
System.out.println("Connection " + i + " returned to the pool - " + conn[i].isClosed());
}
The above should generate output showing that conn[1] and conn[2] have been closed and therefore returned to the pool whereas the other three are still in the borrowed state.
Finally, here is how you might implement the ConnectionHarvestingCallback abstract interface so that its cleanup method closes the connection being harvested:
class CustConnectionHarvestingCallback implements ConnectionHarvestingCallback
{
private Connection conn = null;
public CustConnectionHarvestingCallback(Connection conn)
{
this.conn = conn;
}
public boolean cleanup()
{
try {
conn.close();
}
catch (Exception e) {
return false;
}
return true;
}
}
The above is a simple example of a ConnectionHarvestingCallback abstract interface implementation. In a real-world application, you might want to use a more complicated implementation. In particular, you might need to implement more-complex logic in the cleanup method, such as rolling back the transaction associated with the connection being harvested, before closing that connection.
As you have learned in this section, there are several UCP JDBC connection pool properties you can use to optimize pooling behavior. So, it's often a good idea to experiment with pool settings to find the combination that best fits the needs of your application.
Statement Pooling
This recommendation may seem obvious, but the importance of statement pooling in data-intensive applications cannot be overstated. Oracle JDBC drivers support explicit and implicit statement caching, enabling you to cache prepared and callable statements. Implicit caching doesn’t require you to take any special action to send statements to and retrieve them from a cache—a prepared or callable statement automatically goes to the cache when you invoke the close method of that statement object. The next time you create this statement on this same connection, it will be retrieved from the cache rather than being created from scratch. If implicit caching is turned on, a statement object will be reused from the cache when the following conditions are met:
Although Oracle JDBC drivers are designed with the supposition that implicit caching is enabled, this feature is not turned on by default. To enable implicit caching on the connection, you can set the implicitCachingEnabled property of the corresponding OracleConnection object to true and set the statementCacheSize property to a positive integer. This can be done as follows:
conn.setImplicitCachingEnabled(true);
conn.setStatementCacheSize(10);
When using aUCP JDBC connection pool, you can enable statement caching by setting maxStatements property to a positive integer:
pds.setMaxStatements(10);
If you do this, statement caching will be enabled for each connection within the pool. The following program provides a simplified example of how you can use statement pooling, taking advantage of connection pooling at the same time:
/*
*An example of statement pooling in action
*/
import java.sql.*;
import oracle.ucp.jdbc.PoolDataSource;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OraclePreparedStatement;
import javax.naming.*;
import java.util.Hashtable;
public class StatementPooling {
public static void main(String argv[]) {
PoolDataSource pds;
//Looking up for the jdbc/HRPool pool-enabled data source registered in the JNDI tree
...
//for actual code, see the JNDI lookup example
//discussed in the Borrowing a Connection with JNDI section earlier
...
try {
//Enabling statement caching for the pool's connections
pds.setMaxStatements(10);
//Borrowing a connection from the pool
OracleConnection conn = (OracleConnection) pds.getConnection();
//Checking whether the implicit statement caching is enabled
if (conn.getImplicitCachingEnabled())
System.out.println("\nimplicit caching enabled");
else
System.out.println("\nimplicit caching disabled");
//Looping through calls to the getRegion private class method that executes a prepared statement
for (int i = 1; i <>
System.out.println("\n" + getRegion(conn, i));
}
//Returning the connection to the pool
if (conn != null)
conn.close();
conn = null;
}
catch (SQLException eSQL) {
System.out.println("Cannot obtain a connection: " + eSQL);
}
}
//This method creates, executes, and then closes a prepared statement
private static String getRegion (OracleConnection conn, int region_id ) throws SQLException {
OraclePreparedStatement stmt = null;
ResultSet rs = null;
String region = null;
String sql = "SELECT * FROM regions WHERE region_id = ?";
try {
stmt = (OraclePreparedStatement)conn.prepareStatement(sql);
stmt.setInt(1, region_id);
rs = stmt.executeQuery();
rs.next();
region = rs.getString("REGION_NAME");
}
catch (SQLException eSQL) {
System.out.println("\nSQLException: " + eSQL);
}
//this code is executed under all circumstances
finally {
if (rs != null)
rs.close ();
if (stmt != null)
//if implicit caching is enabled, the statement is not actually closed
//but is sent to the cache
stmt.close ();
return region;
}
}
}
As you can see, the getRegion method of the class shown above creates, executes, and then closes a prepared statement, returning the query result to the calling code. This method is called repeatedly in a loop running in the main method, making implicit statement caching possible. In this example, you enabled implicit caching when you called the setMaxStatements method of the connection pool instance. So calling the close method of the prepared statement in the getRegion method will actually cache the statement instead of closing it, thus letting the program reuse it on the second and subsequent calls to getRegion. To make sure it works as expected, you can add the following code to the getRegion method, putting it right after the call to the prepareStatement method of the OracleConnection object:
...
//Checking the creation state of the prepared statement
int creationState = stmt.creationState();
switch(creationState) {
case 0:
System.out.println("\nCreation state: new");
break;
case 1:
System.out.println("\nCreation state: from the implicit cache");
break;
case 2:
System.out.println("\nCreation state: from the explicit cache");
break;
}
...
If you now execute the program, you should see that the prepared statement’s creation status is new only upon the first call to getRegion—all subsequent getRegion calls reuse the statement cached implicitly.
Being able to enable statement caching for every statement on every connection within the pool is a start, but how you can you apply this technique selectively, disabling caching statements on a certain pooled connection or even disabling caching for a certain statement?
If you recall from the discussion at the beginning of this section, you can use an OracleConnection implicitCachingEnabled property to enable or disable statement caching for that particular connection. For example, you may have enabled statement caching for each connection within the pool and may then disable it on a particular connection as follows:
conn.setImplicitCachingEnabled(false);
As for disabling or enabling caching on particular statements, you can take advantage of the new JDBC 4.0 methods added to the Statement interface. In particular, to make a statement object poolable or not poolable, you can use its setPoolable method, passing in true or false, respectively. To check out the current poolable state of a statement object, you can use the isPoolable method of that object. Here is how you might prevent a particular prepared statement from going to the implicit cache:
if(stmt.isPoolable())
stmt.setPoolable(false);
It’s interesting to note here that if implicit caching is not enabled on the connection, an attempt to enable it for a particular statement with setPoolable(true) won’t force the desired effect. Although the poolable property of a statement object is set to true by default, you still need to first turn implicit caching on for the connection or the entire connection pool, as described at the beginning of this section.
Conclusion
In this article, you learned how to take advantage of connection and statement pooling, utilizing outstanding Oracle-specific JDBC features as well as the standard JDBC 4.0 features. In particular, you looked at the Oracle Universal Connection Pool for JDBC, a new Oracle Database 11g feature providing a connection pool implementation for caching JDBC connections. Then you learned how to take advantage of statement pooling, utilizing features specific to Oracle’s JDBC drivers as well as the new JDBC 4.0 methods added to the Statement interface.
Yuli Vasiliev is a software developer, freelance author, and consultant currently specializing in open source development, Java technologies, databases, and SOA. He is the author of Beginning Database-Driven Application Development in Java EE: Using GlassFish (Apress, 2008).
High-Performance Oracle JDBC Programming
Learn how to improve performance of Oracle-driven JDBC programs by leveraging connection and statement pooling features.
By Yuli Vasiliev
Published April 2009
Using pooling techniques such as connection pooling and statement pooling can significantly improve performance of database-intensive applications, because it enables the reuse of objects that would otherwise need to be created from scratch, at the expense of time and resources.
Reusing database connection objects representing physical database connections utilized by an application can result in significant performance gains, provided that the application interacts with the database intensively, frequently re-establishing connections with the same parameters. On the other hand, you won’t benefit from using a connection pool if your application connects to its underlying database only rarely. In practice, though, many database-intensive applications can benefit from utilizing a connection pool, provided that the pool’s settings, such as those that put a limit on the maximum and minimum number of connections allowed, are optimized for that particular application.
Like connection pooling, statement pooling is a technique for improving application performance. You can achieve additional performance gains by pooling statements that are executed multiple times in the course of a run of your program. It’s important to realize, though, that statement pooling is not a silver bullet for performance problems. If you cache every single statement without distinguishing how many times it is executed in your program, you are unlikely to achieve any performance improvement. In fact, caching the statements that are issued only once during program execution may actually degrade performance, due to the overhead associated with putting and then keeping such statements in the cache.
This article shows you how to take advantage of pooling connections and statements to improve performance of data-intensive Java DataBase Connectivity (JDBC) programs interacting with Oracle Database via the Oracle JDBC thin driver. In particular, it looks at the Oracle Universal Connection Pool (UCP) for JDBC, which provides a full-featured connection pool implementation for caching JDBC connections. Finally it discusses how you might benefit from statement pooling, utilizing features specific to Oracle’s JDBC drivers as well as the new JDBC 4.0 methods added to the Statement interface and available in Oracle JDBC drivers supporting Java Development Kit (JDK) 1.6 and later versions.
Setting Up Your Working Environment
To follow the examples in this article, in addition to having access to an Oracle database, you’ll need to have the following software components installed on your development machine (see "Downloads" portlet for links:
- JDK 1.6
- Oracle JDBC thin driver supporting JDK 1.6
- Oracle Universal Connection Pool library
The Oracle JDBC thin driver is a Type IV JDBC driver, meaning that it’s platform-independent and does not require any extra Oracle software on the client side to interact with an Oracle database. So you can download the JAR file containing the classes of an appropriate thin driver version from the JDBC Driver Downloads page and then install the driver on your machine without having to install/upgrade any other Oracle software. To install the driver, you simply need to copy its JAR files to your local file system and then include paths to these JARs to the CLASSPATH environment variable. For example, you might include the following paths:
ORACLE_HOME/jdbc/lib/ojdbc6.jar
ORACLE_HOME/jlib/orai18n.jar
If you have an Oracle database installed on your machine, the thin driver has been installed with your Oracle Database installation. However, because the thin driver does not depend on any additional Oracle software, you can easily upgrade to the latest release of the driver by using appropriate JAR files that can be found on the JDBC Driver Downloads page.
UCPis a new feature included in Oracle Database 11g, starting with release 11.1.0.7. This feature is also available in Oracle Application Server, beginning with Oracle Application Server 11gRelease 1. If you’re using older software that doesn’t ship the JAR file for UCP(it’s called ucp.jar) or you want to upgrade to the latest UCPrelease, you can pick up ucp.jar from the Oracle Database UCP Downloads page. This package contains UCP’s classes for inclusion in the classpath to enable the feature. The path included might look like this:
ORACLE_HOME/ucp/lib/ucp.jar
Caching JDBC Connections with UCP
If you’re developing a database-intensive application, you might benefit from using a connection pool, which enables you to reuse connections rather than create a new one each time it is requested. Connection pooling conserves resources required for creating new database connections and improves your application performance, because creating a new connection is always a performance-intensive operation.
The Oracle Universal Connection Pool for JDBC represents a full-featured implementation of a connection pool caching JDBC connections. UCP is a very useful feature, in that it lets you reuse connection objects, thus speeding the process of obtaining a connection and saving resources associated with opening new database connections.
Suppose you want to create aUCP JDBC connection pool to reuse connections established to the HR/HR Oracle Database sample schema. The following program, representing a simple example of aUCP JDBC connection pool in action, shows how you might achieve this. Here you first create a pool-enabled data source instance and then set up the connection and pool properties. Once you’ve done that, you borrow a connection from the pool and then use that connection to interact with the database. Finally, you close the connection, returning it to the pool.
/*
*A simple example illustrating aUCP JDBC connection in action
*/
import java.sql.*;
import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;
public class UcpConnection {
public static void main(String args[]) throws SQLException {
try
{
//Creating a pool-enabled data source
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
//Setting connection properties of the data source
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setURL("jdbc:oracle:thin:@//localhost:1521/XE");
pds.setUser("hr");
pds.setPassword("hr");
//Setting pool properties
pds.setInitialPoolSize(5);
pds.setMinPoolSize(5);
pds.setMaxPoolSize(10);
//Borrowing a connection from the pool
Connection conn = pds.getConnection();
System.out.println("\nConnection borrowed from the pool");
//Checking the number of available and borrowed connections
int avlConnCount = pds.getAvailableConnectionsCount();
System.out.println("\nAvailable connections: " + avlConnCount);
int brwConnCount = pds.getBorrowedConnectionsCount();
System.out.println("\nBorrowed connections: " + brwConnCount);
//Working with the connection
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select user from dual");
while(rs.next())
System.out.println("\nConnected as: "+rs.getString(1));
rs.close();
//Returning the connection to the pool
conn.close();
conn=null;
System.out.println("\nConnection returned to the pool");
//Checking the number of available and borrowed connections again
avlConnCount = pds.getAvailableConnectionsCount();
System.out.println("\nAvailable connections: " + avlConnCount);
brwConnCount = pds.getBorrowedConnectionsCount();
System.out.println("\nBorrowed connections: " + brwConnCount);
}
catch(SQLException e)
{
System.out.println("\nAn SQL exception occurred : " + e.getMessage());
}
}
}
An important thing to notice here is what’s happening when a connection is closed. The output of the above program illustrates that closing a connection borrowed from a UCP JDBC connection pool actually returns that connection to the pool, where it becomes available for the next connection request.
Here is what the program output should look like:
Connection borrowed from the pool
Available connections: 4
Borrowed connections: 1
Connected as: HR
Connection returned to the pool
Available connections: 5
Borrowed connections: 0
Borrowing a Connection with JNDI
Instead of creating a pool-enabled data source on the fly, as you did in the preceding example, you can create it in advance and bind it to a Java Naming and Directory Interface (JNDI) context and a logical name. Once you’ve registered a data source with JNDI, you can get an instance of it by performing a JNDI lookup, specifying the JNDI name to which the data source is bound.
Suppose you want to register a pool-enabled data source designed to reuse connections to the HR/HR database schema, associating this data source with logical name jdbc/HRPool in the JNDI tree. To do this, you must create a PoolDataSource object representing the above data source, set its properties, and then register it with a JNDI naming service. This can be done with the following Java program:
/*
*An example of how you can register
* a pool-enabled data source with JNDI
*/
import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;
import javax.naming.*;
import java.util.Hashtable;
public class JNDIRegister {
public static void main(String argv[]) {
try {
//Creating a pool-enabled data source instance and setting its properties
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setURL("jdbc:oracle:thin:@//localhost:1521/XE");
pds.setUser("hr");
pds.setPassword("hr");
pds.setInitialPoolSize(5);
pds.setMinPoolSize(5);
pds.setMaxPoolSize(10);
//Registering the data source with JNDI
Hashtable env = new Hashtable();
env.put(Context.INITIAL_CONTEXT_FACTORY,"com.sun.jndi.fscontext.RefFSContextFactory");
Context ctx = new InitialContext(env);
ctx.bind("jdbc/HRPool", pds);
}
catch (Exception e) {
System.out.println(e);
}
}
}
Before you can run this program, you have to set up Sun's file system JNDI service provider, which can be downloaded from here. Make sure to add the following JAR files to the classpath to be able to run the above program:
install_dir/sun/lib/fs/fscontext.jar;install_dir/sun/lib/fs/providerutil.jar
After you run the above program, you can utilize the jdbc/HRPool pool-enabled data source in your Java applications, whether they be JavaServer Pages, servlets, or standalone applications. The following is a standalone Java application utilizing this data source:
/*
*An example of a JNDI lookup for
* a pool-enabled data source
*/
import java.sql.*;
import oracle.ucp.jdbc.PoolDataSource;
import javax.naming.*;
import java.util.Hashtable;
public class JNDILookup {
public static void main(String argv[]) {
PoolDataSource pds;
//Performing a lookup for a pool-enabled data source registered in JNDI tree
try {
Hashtable env = new Hashtable();
env.put(Context.INITIAL_CONTEXT_FACTORY,"com.sun.jndi.fscontext.RefFSContextFactory");
Context ctx = new InitialContext(env);
pds = (PoolDataSource) ctx.lookup("jdbc/HRPool");
}
catch (NamingException eName) {
System.out.println("Cannot look up " + "jdbc/HRPool" + ": " +eName);
return;
}
//Borrowing a connection from the data source returned by the JNDI lookup
try {
Connection conn = pds.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select user from dual");
while(rs.next())
System.out.println("\nConnected as: "+rs.getString(1));
if (conn != null)
conn.close();
}
catch (SQLException eSQL) {
System.out.println("Cannot obtain a connection: " + eSQL);
}
return;
}
}
The first thing you do in the above program is to initialize the JNDI context, which is then used to perform a JNDI lookup for the jdbc/HROracle pool-enabled data source. Next you borrow a connection from the data source instance returned by the JNDI lookup and use it to issue a query against the database.
As you no doubt have realized, the approach discussed in this section simplifies the process of using connection pools. Registering a pool-enabled data source once and then obtaining an instance of it with a JNDI lookup when needed eliminates the need to set up the connection pool properties each time you initialize it. You just obtain a pool instance with the properties defined in advance.
High Availability and Performance
It’s important to emphasize that UCP supports new JDBC 4.0 high-availability and performance features, such as pool refreshing and connection validating, that are not related to Oracle Real Application Clusters (RAC) and therefore do not require an Oracle RAC database.
Validate connection
Furthermore, UCP provides the ability to validate connections on borrow. Validating connections on borrow is a useful technique, because it enables you to check whether a connection is still valid before you start using it. To help with this problem, a UCP JDBC connection pool instance has the ValidateConnectionOnBorrow property of type Boolean, which you need to set to true with the setValidateConnectionOnBorrow method:
pds.setValidateConnectionOnBorrow(true);
Then you need to specify an SQL statement you want to be issued to make sure that the connection is still valid. You can do this with the setSQLForValidateConnection method:
pds.setSQLForValidateConnection("select user from dual");
When utilizing an Oracle JDBC driver, though, there is no need to set the SQLForValidateConnection property—the pool will perform an internal ping to test the validity of the connection being borrowed.
Verifying connections on borrow is a good thing, but what if a connection becomes stale after it has been successfully validated on borrow? Is there any way to verify a connection after it has been borrowed? To address this issue, the JDBC 4.0 specification added the isValid method to the Connection interface, enabling you to test the validity of a connection when you want to.
Taking it one step further, UCP for JDBC provides the oracle.ucp.jdbc.ValidConnection interface, which includes two methods: isValid and setInvalid. These methods can be especially useful when utilized in conjunction with a retry mechanism implemented with recursion or iteration (looping). For instance, you might implement a method that will borrow and then utilize a connection, making a recursive call to itself in case the connection has become stale and consequently the operation cannot be completed. An important thing to keep in mind when implementing such a recursive mechanism is that it must provide the ability to limit the number of recursive calls to be made and that each new recursive call must reduce that number, thus preventing the possibility of endless looping.
The following is a simple program providing an example of how you might use the oracle.ucp.jdbc.ValidConnection interface methods in conjunction with a retry mechanism based on recursion.
/*
*An example of validating connections on borrow;
*this also shows the use of the ValidConnection interface's methods:
*isValid and setInvalid methods in combination with a retry mechanism
*/
import java.sql.*;
import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.ValidConnection;
import javax.naming.*;
import java.util.Hashtable;
public class ConnectionValidating {
public static void main(String argv[]) {
PoolDataSource pds;
//Looking up for the jdbc/HRPool pool-enabled data source registered in JNDI tree
...
//for actual code see the JNDI lookup example
//discussed in the Borrowing a Connection with JNDI section earlier
...
try {
//Instructing the pool to validate connections on borrow
pds.setValidateConnectionOnBorrow(true);
//Calling the getUser method that borrows a connection from the pool
//limiting the number of recursive calls to 3
System.out.println("\nConnected as :"+getUser(pds, 3));
}
catch (SQLException eSQL) {
System.out.println("\nSQLException: " + eSQL);
return;
}
}
//This method borrows a connection from the pool and will make a recursive call
//if it turns out that the borrowed connection has become unusable
private static String getUser (PoolDataSource pds, int recursiveCalls) throws SQLException {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String user = null;
try {
//Borrowing a connection from the pool
conn = pds.getConnection();
//Working with the connection
stmt = conn.createStatement();
rs = stmt.executeQuery("select user from dual");
while(rs.next())
user = rs.getString(1);
if (conn != null)
conn.close();
}
catch (SQLException eSQL) {
if (recursiveCalls > 0 && !((ValidConnection) conn).isValid()) {
System.out.println("\nConnection is no longer valid: " + eSQL);
//Calling setInvalid often leads to an exception
//so it's a wise idea to put it in a separate try block
try {
((ValidConnection) conn).setInvalid();
} catch (SQLException conEx) {
System.out.println("\nInvalidating failed: " + conEx);
}
conn.close();
conn = null;
System.out.println("\nRetrying to obtain a new connection");
//making a recursive call to getUser in an attempt to obtain a valid connection
//the number of recursive calls allowed is reduced by 1
user = getUser(pds, recursiveCalls - 1);
} else {
System.out.println("\nSQLException: " + eSQL);
}
}
finally {
return user;
}
}
}
In this example, the getUser method calls itself from within the catch clause of the try/catch statement implemented in this same method. Here, you limit the number of allowed recursive calls to three. If it fails to obtain a valid connection three times in a row, you stop trying and get out.
High-availability features
Aside from the generic high-availability and performance features discussed above, UCP for JDBC can be integrated with Oracle RAC features such as Fast Connection Failover (FCF) and Runtime Connection Load Balancing, making it easier to manage connections to an Oracle RAC database.
The following snippet illustrates how you can enable FCF when using a UCP JDBC connection pool managing connections to an Oracle RAC database. Note that using FCF requires you to add the Oracle Notification Service library (ons.jar) to an application’s classpath. The Oracle Notification Service library is shipped as part of Oracle Database, starting with Oracle Database 10g.
...
//Creating a pool-enabled data source
PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();
//Setting pool properties
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
//Setting a RAC-specific URL
pds.setURL(
"jdbc:oracle:thin:@" +
"(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)" +
"(ADDRESS=(PROTOCOL=TCP)" +
"(HOST=rachost1)(PORT=1521))" +
"(ADDRESS=(PROTOCOL=TCP)" +
"(HOST=rachost2)(PORT=1521)))" +
"(CONNECT_DATA=(SERVICE_NAME=orcl)))");
pds.setUser("usr");
pds.setPassword("pswd");
pds.setMinPoolSize(10);
pds.setMaxPoolSize(20);
//Configuring remote ONS subscription
pds.setONSConfiguration("nodes=rachost1:4200,rachost2:4200");
// Enabling Fast Connection Failover
pds.setFastConnectionFailoverEnabled(true);
Once you have a connection pool set up and FCF enabled, you can borrow a connection from the pool and create queries on it as you would in a non- RAC-specific program.
Connection conn = pds.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = null;
Then you can implement a retry mechanism that will check a connection for validity after a RAC-down event triggers UCP FCF actions, trying again to connect to surviving RAC instances in case the connection has become stale. The following snippet illustrates how this could be implemented within a while loop.
boolean retry = true;
while(retry)
{
try
{
//Getting a RAC connection from the pool
conn = pds.getConnection();
// Executing a query on the connection.
rs = stmt.executeQuery("select user from dual");
rs.next();
System.out.println("\nConnected as : " + rs.getString(1));
//Setting retry to false to exit the loop
retry = false;
}
catch (SQLException eSQL)
{
System.out.println("\nSQLException: " + eSQL);
// Checking connection usability after a RAC-down event triggers UCP FCF actions
if (conn == null || !((ValidConnection) conn).isValid())
{
//Closing the connection
try
{
conn.close();
}
catch (SQLException eClose)
{
System.out.println("\nException arose when closing connection: " + eClose);
}
//Setting retry to true to try again
retry = true;
}
}
Thread.sleep(1000);
}
If a connection has been successfully borrowed from the pool and the statement execution has not triggered an exception, there is no need to retry the operation and therefore the execution flow will jump out of the loop. Otherwise, it will try to reconnect and will then perform the statement execution again.
Optimizing Connection Pools
Optimize pooling behavior with parameters and callbacks
The UCP JDBC connection pool provides a set of properties you can use to optimize pooling behavior. For example, you can regulate the pool size, setting the properties controlling the initial, maximum, and minimum pool size. In the preceding sections, you have seen how you can set up these properties.
Aside from the properties controlling the pool size, there are properties controlling stale connections. For example, you can set up the pool’s MaxConnectionReuseTime property, thus configuring a maximum connection reuse time. In some environments, you may find it useful to have connections removed from the pool after a connection has been borrowed a certain number of times. You can do this by setting up the MaxConnectionReuseCount property.
You can set up the AbandonConnectionTimeout property to instruct the pool to reclaim borrowed connections after a connection has not been used for a certain amount of time. Also, you can set up the TimeToLiveConnectionTimeout property, limiting how long a borrowed connection may be used before it is reclaimed by the pool.
If you anticipate that the pool may run out of connections at some point, you can set the ConnectionWaitTimeout property to the number of seconds an application request waits for a connection when no connections are available in the pool. Also, there is the InactiveConnectionTimeout property, which enables you to specify how long an available connection can remain unborrowed before it is removed from the pool.
Another interesting property is TimeoutCheckInterval, with which you can set up the timeout check interval, controlling how often the timeout properties discussed above will be enforced. By default, this property is set to 30, meaning that the timeout check cycle runs every 30 seconds.
All the optimization features discussed so far in this section require you to set a certain property to an appropriate value in order to get the effect you want, but to enable the connection harvesting feature, which is used to ensure a certain number of available connections in the pool, you need to use a mechanism that’s a bit more complicated. This feature is explained in the rest of this section by example.
Let’s say you set up the pool’s size properties as follows:
...
pds.setInitialPoolSize(10);
pds.setMaxPoolSize(20);
With initialPoolSize set to 10, you will have 10 connections upon initializing the connection pool. Next, with the following code, you enable the connection harvesting feature, thus making the pool’s connections harvestable:
pds.setConnectionHarvestTriggerCount(5);
pds.setConnectionHarvestMaxCount(2);
The properties set above instruct the pool to reclaim two borrowed connections when the number of available connections in the pool drops to five. Let’s now create an array of five connection objects that can be then used to hold five connections borrowed from the pool:
//Creating an array of connection objects
Connection[] conn = new Connection[5];
Before populating the above array with connections, though, you need to create an array of callback objects, each of which will be registered with a connection. A callback object must be an instance of a custom implementation of the ConnectionHarvestingCallback abstract interface. A simple implementation is shown a little later in this section.
With the following code, you create an array of five CustConnectionHarvestingCallback objects:
//Creating an array of callback objects
CustConnectionHarvestingCallback[] callbk = new CustConnectionHarvestingCallback[5];
In the following loop, you borrow four connections from the pool and also create four callback objects, each of which is registered with a connection:
//Borrowing four connections from the pool
for (int i = 0; i <>
{
conn[i] = pds.getConnection();
//Registering the callback object with each connection
callbk[i] = new CustConnectionHarvestingCallback(conn[i]);
((HarvestableConnection) conn[i]).registerConnectionHarvestingCallback(callbk[i]);
}
Before you borrow the fifth connection to trigger harvesting, you can disable harvesting on a certain connection for testing purposes. You might recall that you specified two borrowed connections to be returned to the pool when the number of available connections drops to five. By default, the connection harvest feature will harvest those two connections that were borrowed first. Thus, in this example, conn[0] and conn[1] will be harvested. By setting conn[0] as nonharvestable, however, you make it harvest conn[1] and conn[2].
//Setting conn[0] as nonharvestable
((HarvestableConnection) conn[0]).setConnectionHarvestable(false);
Let’s now trigger harvesting by borrowing the fifth connection from the pool.
//Borrowing the fifth connection to trigger harvesting
conn[4] = pds.getConnection();
callbk[4] = new CustConnectionHarvestingCallback(conn[4]);
((HarvestableConnection) conn[4]).registerConnectionHarvestingCallback(callbk[4]);
Recall from the discussion of the timeout check interval earlier in this section that this interval is set to 30 by default. What this means in this example is that harvesting will not be triggered immediately but within a 30-second interval.
// Waiting for harvesting to happen
Thread.sleep(30000);
To make sure everything has worked as planned, you might want to check through the connections to see which ones have been closed and returned to the pool:
//Checking connections
for (int i = 0; i <>
{
System.out.println("Connection " + i + " returned to the pool - " + conn[i].isClosed());
}
The above should generate output showing that conn[1] and conn[2] have been closed and therefore returned to the pool whereas the other three are still in the borrowed state.
Finally, here is how you might implement the ConnectionHarvestingCallback abstract interface so that its cleanup method closes the connection being harvested:
class CustConnectionHarvestingCallback implements ConnectionHarvestingCallback
{
private Connection conn = null;
public CustConnectionHarvestingCallback(Connection conn)
{
this.conn = conn;
}
public boolean cleanup()
{
try {
conn.close();
}
catch (Exception e) {
return false;
}
return true;
}
}
The above is a simple example of a ConnectionHarvestingCallback abstract interface implementation. In a real-world application, you might want to use a more complicated implementation. In particular, you might need to implement more-complex logic in the cleanup method, such as rolling back the transaction associated with the connection being harvested, before closing that connection.
As you have learned in this section, there are several UCP JDBC connection pool properties you can use to optimize pooling behavior. So, it's often a good idea to experiment with pool settings to find the combination that best fits the needs of your application.
Statement Pooling
This recommendation may seem obvious, but the importance of statement pooling in data-intensive applications cannot be overstated. Oracle JDBC drivers support explicit and implicit statement caching, enabling you to cache prepared and callable statements. Implicit caching doesn’t require you to take any special action to send statements to and retrieve them from a cache—a prepared or callable statement automatically goes to the cache when you invoke the close method of that statement object. The next time you create this statement on this same connection, it will be retrieved from the cache rather than being created from scratch. If implicit caching is turned on, a statement object will be reused from the cache when the following conditions are met:
- The SQL string used in the statement is equal to one held in the cache.
- The statement type is also the same, meaning prepared or callable.
- The scrollable type of the result set generated by the statement is also the same, meaning forward-only or scrollable.
Although Oracle JDBC drivers are designed with the supposition that implicit caching is enabled, this feature is not turned on by default. To enable implicit caching on the connection, you can set the implicitCachingEnabled property of the corresponding OracleConnection object to true and set the statementCacheSize property to a positive integer. This can be done as follows:
conn.setImplicitCachingEnabled(true);
conn.setStatementCacheSize(10);
When using aUCP JDBC connection pool, you can enable statement caching by setting maxStatements property to a positive integer:
pds.setMaxStatements(10);
If you do this, statement caching will be enabled for each connection within the pool. The following program provides a simplified example of how you can use statement pooling, taking advantage of connection pooling at the same time:
/*
*An example of statement pooling in action
*/
import java.sql.*;
import oracle.ucp.jdbc.PoolDataSource;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OraclePreparedStatement;
import javax.naming.*;
import java.util.Hashtable;
public class StatementPooling {
public static void main(String argv[]) {
PoolDataSource pds;
//Looking up for the jdbc/HRPool pool-enabled data source registered in the JNDI tree
...
//for actual code, see the JNDI lookup example
//discussed in the Borrowing a Connection with JNDI section earlier
...
try {
//Enabling statement caching for the pool's connections
pds.setMaxStatements(10);
//Borrowing a connection from the pool
OracleConnection conn = (OracleConnection) pds.getConnection();
//Checking whether the implicit statement caching is enabled
if (conn.getImplicitCachingEnabled())
System.out.println("\nimplicit caching enabled");
else
System.out.println("\nimplicit caching disabled");
//Looping through calls to the getRegion private class method that executes a prepared statement
for (int i = 1; i <>
System.out.println("\n" + getRegion(conn, i));
}
//Returning the connection to the pool
if (conn != null)
conn.close();
conn = null;
}
catch (SQLException eSQL) {
System.out.println("Cannot obtain a connection: " + eSQL);
}
}
//This method creates, executes, and then closes a prepared statement
private static String getRegion (OracleConnection conn, int region_id ) throws SQLException {
OraclePreparedStatement stmt = null;
ResultSet rs = null;
String region = null;
String sql = "SELECT * FROM regions WHERE region_id = ?";
try {
stmt = (OraclePreparedStatement)conn.prepareStatement(sql);
stmt.setInt(1, region_id);
rs = stmt.executeQuery();
rs.next();
region = rs.getString("REGION_NAME");
}
catch (SQLException eSQL) {
System.out.println("\nSQLException: " + eSQL);
}
//this code is executed under all circumstances
finally {
if (rs != null)
rs.close ();
if (stmt != null)
//if implicit caching is enabled, the statement is not actually closed
//but is sent to the cache
stmt.close ();
return region;
}
}
}
As you can see, the getRegion method of the class shown above creates, executes, and then closes a prepared statement, returning the query result to the calling code. This method is called repeatedly in a loop running in the main method, making implicit statement caching possible. In this example, you enabled implicit caching when you called the setMaxStatements method of the connection pool instance. So calling the close method of the prepared statement in the getRegion method will actually cache the statement instead of closing it, thus letting the program reuse it on the second and subsequent calls to getRegion. To make sure it works as expected, you can add the following code to the getRegion method, putting it right after the call to the prepareStatement method of the OracleConnection object:
...
//Checking the creation state of the prepared statement
int creationState = stmt.creationState();
switch(creationState) {
case 0:
System.out.println("\nCreation state: new");
break;
case 1:
System.out.println("\nCreation state: from the implicit cache");
break;
case 2:
System.out.println("\nCreation state: from the explicit cache");
break;
}
...
If you now execute the program, you should see that the prepared statement’s creation status is new only upon the first call to getRegion—all subsequent getRegion calls reuse the statement cached implicitly.
Being able to enable statement caching for every statement on every connection within the pool is a start, but how you can you apply this technique selectively, disabling caching statements on a certain pooled connection or even disabling caching for a certain statement?
If you recall from the discussion at the beginning of this section, you can use an OracleConnection implicitCachingEnabled property to enable or disable statement caching for that particular connection. For example, you may have enabled statement caching for each connection within the pool and may then disable it on a particular connection as follows:
conn.setImplicitCachingEnabled(false);
As for disabling or enabling caching on particular statements, you can take advantage of the new JDBC 4.0 methods added to the Statement interface. In particular, to make a statement object poolable or not poolable, you can use its setPoolable method, passing in true or false, respectively. To check out the current poolable state of a statement object, you can use the isPoolable method of that object. Here is how you might prevent a particular prepared statement from going to the implicit cache:
if(stmt.isPoolable())
stmt.setPoolable(false);
It’s interesting to note here that if implicit caching is not enabled on the connection, an attempt to enable it for a particular statement with setPoolable(true) won’t force the desired effect. Although the poolable property of a statement object is set to true by default, you still need to first turn implicit caching on for the connection or the entire connection pool, as described at the beginning of this section.
Conclusion
In this article, you learned how to take advantage of connection and statement pooling, utilizing outstanding Oracle-specific JDBC features as well as the standard JDBC 4.0 features. In particular, you looked at the Oracle Universal Connection Pool for JDBC, a new Oracle Database 11g feature providing a connection pool implementation for caching JDBC connections. Then you learned how to take advantage of statement pooling, utilizing features specific to Oracle’s JDBC drivers as well as the new JDBC 4.0 methods added to the Statement interface.
Yuli Vasiliev is a software developer, freelance author, and consultant currently specializing in open source development, Java technologies, databases, and SOA. He is the author of Beginning Database-Driven Application Development in Java EE: Using GlassFish (Apress, 2008).