Vyoms OneStopTesting.com - Testing EBooks, Tutorials, Articles, Jobs, Training Institutes etc.
OneStopGate.com - Gate EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopMBA.com - MBA EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopIAS.com - IAS EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopSAP.com - SAP EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
OneStopGRE.com - of GRE EBooks, Tutorials, Articles, FAQs, Jobs, Training Institutes etc.
Bookmark and Share Rss Feeds

High-Performance Oracle JDBC Programming | Articles | Recent Articles | News Article | Interesting Articles | Technology Articles | Articles On Education | Articles On Corporate | Company Articles | College Articles | Articles on Recession
Sponsored Ads
Hot Jobs
Fresher Jobs
Experienced Jobs
Government Jobs
Walkin Jobs
Placement Section
Company Profiles
Interview Questions
Placement Papers
Resources @ VYOMS
Companies In India
Consultants In India
Colleges In India
Exams In India
Latest Results
Notifications In India
Call Centers In India
Training Institutes In India
Job Communities In India
Courses In India
Jobs by Keyskills
Jobs by Functional Areas
Learn @ VYOMS
GATE Preparation
GRE Preparation
GMAT Preparation
IAS Preparation
SAP Preparation
Testing Preparation
MBA Preparation
News @ VYOMS
Freshers News
Job Articles
Latest News
India News Network
Interview Ebook
Get 30,000+ Interview Questions & Answers in an eBook.
Interview Success Kit - Get Success in Job Interviews
  • 30,000+ Interview Questions
  • Most Questions Answered
  • 5 FREE Bonuses
  • Free Upgrades

VYOMS TOP EMPLOYERS

Wipro Technologies
Tata Consultancy Services
Accenture
IBM
Satyam
Genpact
Cognizant Technologies

Home » Articles » High-Performance Oracle JDBC Programming

High-Performance Oracle JDBC Programming








Article Posted On Date : Thursday, May 21, 2009


High-Performance Oracle JDBC Programming
Advertisements

High-Performance Oracle JDBC Programming

Learn how to improve performance of Oracle-driven JDBC programs by leveraging connection and statement pooling features.

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 11g Release 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(" Connection borrowed from the pool");
//Checking the number of available and borrowed connections
int avlConnCount = pds.getAvailableConnectionsCount();
System.out.println(" Available connections: " + avlConnCount);
int brwConnCount = pds.getBorrowedConnectionsCount();
System.out.println(" Borrowed connections: " + brwConnCount);
//Working with the connection
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select user from dual");
while(rs.next())
System.out.println(" Connected as: "+rs.getString(1));
rs.close();
//Returning the connection to the pool
conn.close();
conn=null;
System.out.println(" Connection returned to the pool");
//Checking the number of available and borrowed connections again
avlConnCount = pds.getAvailableConnectionsCount();
System.out.println(" Available connections: " + avlConnCount);
brwConnCount = pds.getBorrowedConnectionsCount();
System.out.println(" Borrowed connections: " + brwConnCount);
}
catch(SQLException e)
{
System.out.println(" An 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(" Connected 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.

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(" Connected as :"+getUser(pds, 3));
}
catch (SQLException eSQL) {
System.out.println(" SQLException: " + 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(" Connection 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(" Invalidating failed: " + conEx);
}
conn.close();
conn = null;
System.out.println(" Retrying 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(" SQLException: " + 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.

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(" Connected as : " + rs.getString(1));
//Setting retry to false to exit the loop
retry = false;
}
catch (SQLException eSQL)
{
System.out.println(" SQLException: " + 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(" Exception 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

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 < 4; 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 < 5; 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(" implicit caching enabled");
else
System.out.println(" implicit caching disabled");
//Looping through calls to the getRegion private class method that executes a prepared statement
for (int i = 1; i < 5; i++ ) {
System.out.println(" " + 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(" SQLException: " + 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(" Creation state: new");
break;
case 1:
System.out.println(" Creation state: from the implicit cache");
break;
case 2:
System.out.println(" Creation 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.






Sponsored Ads



Interview Questions
HR Interview Questions
Testing Interview Questions
SAP Interview Questions
Business Intelligence Interview Questions
Call Center Interview Questions

Databases

Clipper Interview Questions
DBA Interview Questions
Firebird Interview Questions
Hierarchical Interview Questions
Informix Interview Questions
Microsoft Access Interview Questions
MS SqlServer Interview Questions
MYSQL Interview Questions
Network Interview Questions
Object Relational Interview Questions
PL/SQL Interview Questions
PostgreSQL Interview Questions
Progress Interview Questions
Relational Interview Questions
SQL Interview Questions
SQL Server Interview Questions
Stored Procedures Interview Questions
Sybase Interview Questions
Teradata Interview Questions

Microsof Technologies

.Net Database Interview Questions
.Net Deployement Interview Questions
ADO.NET Interview Questions
ADO.NET 2.0 Interview Questions
Architecture Interview Questions
ASP Interview Questions
ASP.NET Interview Questions
ASP.NET 2.0 Interview Questions
C# Interview Questions
Csharp Interview Questions
DataGrid Interview Questions
DotNet Interview Questions
Microsoft Basics Interview Questions
Microsoft.NET Interview Questions
Microsoft.NET 2.0 Interview Questions
Share Point Interview Questions
Silverlight Interview Questions
VB.NET Interview Questions
VC++ Interview Questions
Visual Basic Interview Questions

Java / J2EE

Applet Interview Questions
Core Java Interview Questions
Eclipse Interview Questions
EJB Interview Questions
Hibernate Interview Questions
J2ME Interview Questions
J2SE Interview Questions
Java Interview Questions
Java Beans Interview Questions
Java Patterns Interview Questions
Java Security Interview Questions
Java Swing Interview Questions
JBOSS Interview Questions
JDBC Interview Questions
JMS Interview Questions
JSF Interview Questions
JSP Interview Questions
RMI Interview Questions
Servlet Interview Questions
Socket Programming Interview Questions
Springs Interview Questions
Struts Interview Questions
Web Sphere Interview Questions

Programming Languages

C Interview Questions
C++ Interview Questions
CGI Interview Questions
Delphi Interview Questions
Fortran Interview Questions
ILU Interview Questions
LISP Interview Questions
Pascal Interview Questions
Perl Interview Questions
PHP Interview Questions
Ruby Interview Questions
Signature Interview Questions
UML Interview Questions
VBA Interview Questions
Windows Interview Questions
Mainframe Interview Questions


Copyright © 2001-2022 Vyoms.com. All Rights Reserved. Home | About Us | Advertise With Vyoms.com | Jobs | Contact Us | Feedback | Link to Us | Privacy Policy | Terms & Conditions
Placement Papers | Get Your Free Website | IAS Preparation | C++ Interview Questions | C Interview Questions | Report a Bug | Romantic Shayari | CAT 2022

Fresher Jobs | Experienced Jobs | Government Jobs | Walkin Jobs | Company Profiles | Interview Questions | Placement Papers | Companies In India | Consultants In India | Colleges In India | Exams In India | Latest Results | Notifications In India | Call Centers In India | Training Institutes In India | Job Communities In India | Courses In India | Jobs by Keyskills | Jobs by Functional Areas

Testing Articles | Testing Books | Testing Certifications | Testing FAQs | Testing Downloads | Testing Interview Questions | Testing Jobs | Testing Training Institutes

Gate Articles | Gate Books | Gate Colleges | Gate Downloads | Gate Faqs | Gate Jobs | Gate News | Gate Sample Papers | Gate Training Institutes

MBA Articles | MBA Books | MBA Case Studies | MBA Business Schools | MBA Current Affairs | MBA Downloads | MBA Events | MBA Notifications | MBA FAQs | MBA Jobs
MBA Job Consultants | MBA News | MBA Results | MBA Courses | MBA Sample Papers | MBA Interview Questions | MBA Training Institutes

GRE Articles | GRE Books | GRE Colleges | GRE Downloads | GRE Events | GRE FAQs | GRE News | GRE Training Institutes | GRE Sample Papers

IAS Articles | IAS Books | IAS Current Affairs | IAS Downloads | IAS Events | IAS FAQs | IAS News | IAS Notifications | IAS UPSC Jobs | IAS Previous Question Papers
IAS Results | IAS Sample Papers | IAS Interview Questions | IAS Training Institutes | IAS Toppers Interview

SAP Articles | SAP Books | SAP Certifications | SAP Companies | SAP Study Materials | SAP Events | SAP FAQs | SAP Jobs | SAP Job Consultants
SAP Links | SAP News | SAP Sample Papers | SAP Interview Questions | SAP Training Institutes |


Copyright ©2001-2022 Vyoms.com, All Rights Reserved.
Disclaimer: VYOMS.com has taken all reasonable steps to ensure that information on this site is authentic. Applicants are advised to research bonafides of advertisers independently. VYOMS.com shall not have any responsibility in this regard.