Tuesday, December 21, 2010

High-Performance Oracle JDBC Programming

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:

  • 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).

Replace your C3P0 or DBCP by Oracle pool (UCP)

Introduction : ODS or UCP?

I've been using DBCP and C3P0 for a long time. But, a few months ago, I started to have problems with them. For example, sometimes, no way to get any connection to my database (while it's still possible with my Oracle SQL Developer). So, I decided to look at the code of C3P0. What a surprise! Even if it's a very common pool for a lot of projects (such as Hibernate), it seems to be dead. No more development, code complicated with sub projects, no Maven integration (...ok, ok, even if you hate Maven, you have to admit that it is very efficient to modify the code of mavenized projects...). Thus, I looked for a new pool. Of course, I found the recent tomcat jdbc pool. But I finally decided to work with Oracle pool just because I only have Oracle databases.

So, this post is my history with Oracle pool.

When you look for pool features on Oracle doc website, you find to ways to implement it :
  • Oracle DataSource pool (also called ODS) and included in the driver jar file
  • Oracle Universal Connection Pool (UCP), which is packaged in its own jar file (ucp.jar)
ODS : the old way

First of all, ODS is the old way. Its interfaces are still usable BUT deprecated. See it by yourself by looking at the code of OracleConnectionCacheImpl. This class contains tuning parameters. This a an example of my Spring configuration with ODS :

<bean id="OracleNativePoolParent" class="oracle.jdbc.pool.OracleDataSource"
destroy-method="close" lazy-init="true" >
<property name="user" value="****" />
<property name="password" value="****" />
<property name="serverName" value="****" />
<property name="databaseName" value="****" />
<property name="driverType" value="thin" />
<property name="networkProtocol" value="tcp" />
<property name="portNumber" value="1521" />
<property name="connectionCachingEnabled" value="true" />
<property name="connectionCacheProperties">
<props merge="default">
<prop key="InitialLimit">1</prop>
<prop key="MinLimit">1</prop>
<prop key="MaxLimit">10</prop>
</props>
</property>
<property name="maxStatements" value="400" />
<property name="loginTimeout" value="20" />
<property name="connectionProperties">
<props merge="default">
<prop key="AutoCommit">false</prop>
</props>
</property>
</bean>


UCP : you should use it instead of ODS

Now, let's switch to UCP. Curiously, We need to use a factory to obtain an instance of this pool. This is my Spring configuration :


<bean id="OracleNativePoolParent" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource" lazy-init="true" >
<property name="connectionFactoryClassName" value="oracle.jdbc.pool.OracleDataSource"/>
<property name="user" value="****" />
<property name="password" value="****" />
<property name="URL" value="jdbc:oracle:thin:@myServer:1521:myDB" />
<property name="connectionWaitTimeout" value="30" />
<property name="minPoolSize" value="1"/>
<property name="maxPoolSize" value="10"/>
<property name="inactiveConnectionTimeout" value="3600"/>
<property name="validateConnectionOnBorrow" value="true"/>
<property name="maxStatements" value="400"/>
<property name="connectionProperties">
<props merge="default">
<prop key="AutoCommit">false</prop>
</props>
</property>
</bean>

Driver Oracle 11g blocked on startup

While initializing your pool, you have to wait 5 minutes then get an exception? Don't panic. It's just a little but with Sun Java on Linux OS. Add this JVM parameter :

-Djava.security.egd=file:///dev/urandom


Conclusion

Even if I didn't find all the options I had with C3P0, UCP seems to be efficient for me. I think that it's a best choice to work with Oracle database because it is officially supported. But, there's still a negative point. As I don't have the source code, it will be very difficult to qualify bugs and to understand advanced parameters (and I think that the official documentation is not exhaustive at all).

Friday, September 10, 2010

My new best business site

I found it while I was surfing on the JDuchess french site. This site (and of course its author) aims to give the keys to self-teaching of the art of business.

Thanks to Mathilde Lemée (http://www.java-freelance.fr/)

Thursday, September 09, 2010

My AJP connector is sick!!!

Today, I had a problem between my Apache balancer and my Tomcat cluster. A virtual machine that was hosting two Tomcats was frozen. Something was wrong in my config and the Apache didn't show these Tomcats in error in the jk_status web interface. After a few minutes, I realized that the uriworkermap file was not configure on 'balancer' but directly on 'tomcat1' (one of the two Tomcats that were down).

So, I concluded that the Apache jk doesn't do any test on a worker if it isn't mapped with an uri.

But let'go back to my virtual machine that was frozen. It was not completely dead and a telnet 8010 was responding. Thus, my first conclusion was that I had a problem with my Apache. BAD WAY!!! The telnet was not efficient. I demonstrated that with a tiny perl script found on the net made to simulate an ajp ping.

You can use the script as this : time ./ajp_ping.pl server:8009

Many thanks to the author of this script. I don't have the link to his post anymore.

Here is the script content :

#!/usr/bin/perl -w

use warnings;
use strict;

use Socket;

my ($remote, $port) = split /:/, shift @ARGV, 2;

if (! $remote) {
$remote = 'localhost';
}
print "remote = $remote\n";

if (! $port) {
$port = 8009;
}
print "port = $port\n";

my ($iaddr, $paddr, $proto);

# If the port has anything other than numbers, we're assuming it is an
# /etc/services name.
if ($port =~ /\D/) {
$port = getservbyname $port, 'tcp' ;
}

die "Bad port, stopped" unless $port;
print "port = $port\n";

$iaddr = inet_aton($remote) || die "No host: $remote, stopped";
print "iaddr = $iaddr\n";

$paddr = sockaddr_in($port, $iaddr) || die "sockaddr: $!, stopped";
print "paddr = $paddr\n";

# Grab the number for TCP out of /etc/protocols.
$proto = getprotobyname 'tcp' ;
print "proto = $proto\n";

my $sock;
# PF_INET and SOCK_STREAM are constants imported by the Socket module. They
# are the same as what is defined in sys/socket.h.
socket $sock, PF_INET, SOCK_STREAM, $proto || die "socket: $!, stopped";
print "sock = $sock\n";

print "BEFORE CONNECT\n";
connect $sock, $paddr || die "connect: $!, stopped";
print "AFTER CONNECT\n";

# This is the ping packet. For detailed documentation, see
# http://tomcat.apache.org/connectors-doc/ajp/ajpv13a.html
# I stole the exact byte sequence from
# http://sourceforge.net/project/shownotes.php?group_id=128058&release_id=438456
# instead of fully understanding the packet structure.
my $ping = pack 'C5' # Format template.
, 0x12, 0x34 # Magic number for server->container packets.
, 0x00, 0x01 # 2 byte int length of payload.
, 0x0A # Type of packet. 10 = CPing.
;

my @ping_values = unpack 'C5', $ping;
print "ping_values = " , join ' ', @ping_values , "\n";

# This is the expected pong packet. That is, this is what Tomcat sends back
# to indicate that it is operating OK.
my $expected = pack 'C5' # Format template.
, 0x41, 0x42 # Magic number for container->server packets.
, 0x00, 0x01 # 2 byte int length of payload.
, 0x09 # Type of packet. 9 = CPong reply.
;

syswrite $sock, $ping || die "syswrite: $!, stopped";

my $pong;
$pong = 'empty';
print "BEFORE READ\n";
sysread $sock, $pong, 5 || die "read: $!, stopped";
print "AFTER READ\n";

my @pong_values = unpack 'C5', $pong;
print "pong_values = " , join ' ', @pong_values , "\n";

close $sock || die "close: $!, stopped";

exit 0;


Wednesday, August 18, 2010

Tomcat and the web.xml's webapprootkey

I n my company, I have to deal with many Spring based applications deployed on a Tomcat cluster. For each one, we set a property 'webapprootkey' in the web.xml file to avoid errors on startup. Today, I decided to dig about that (... erratum... I decided to tell Google to dig about that). Thus, I found this excellent post.

http://groups.google.com/group/riotfamily/browse_thread/thread/9a0edf69575d3ab6?pli=1


Hi all,

when deploying two applications build from the riot skeleton within the same Tomcat servlet container, you get an IllegalStateException: Web app root system property already set to different value: 'webapp.root' = [/Users/joe/Workspace/.metadata/.plugins/org.eclipse.wst.server.core/tmp0/webapps/webapp-A/] instead of [/Users/joe/Workspace/.metadata/.plugins/org.eclipse.wst.server.core/tmp0/webapps/webapp-B/] - Choose unique values for the 'webAppRootKey' context-param in your web.xml files!

I will try to explain where this comes from and how to circumvent it, but first the quick fix for the impatient reader: Place a context parameter named 'webAppRootKey' in every project's web.xml and assign a value to it, that is unique for every of your projects like the project name itself.

The webAppRootKey context parameter is introduced by Spring. Along with the WebAppRootListener it allows exposing the web applications root directory as a system property. The value of the context parameter 'webAppRootKey' names the system property to use. If the context parameter 'webAppRootKey' is not set in the application's web.xml, Spring chooses the default value 'app.root'. While some servlet containers like Resin do isolate each web application's system properties, others like Tomcat do not. And that's what the former mentioned IllegalStateException is telling us: The system property 'app.root' already contains the root directory of the first web application when Spring tries to assign the root directoty of the second application to it.

Ok, that's the background information. A deeper look into the web.xml tells us, that there ist no WebAppRootListener configured. Why does this initialisation take place anyway? The stack trace from the exception reveals the culprit: The Log4jConfigListener also tries to set the webAppRootKey, because this is an interesting mechanism for the Spring/Log4j integration. It allows log and config file locations relative to the web applications root directory. The Log4jConfigListener supports three init parameters at the servlet context level: 'log4jConifgLocation', 'log4jRefreshInterval' and 'log4jExposeWebAppRoot'. See JavaDocs for more informations.

But, none of these parameters are set in the riot project skeleton's web.xml and none of the Log4jWebConfigureres features are used by the riot project skeleton. As long as you do stay with default log4j setup, the Log4jConfigListener is superflous.

At the end there are three possible solutions for the initial problem:

(1) Provide any of your applications with a unique 'webAppRootKey'.
(2) Set the servlet context parameter 'log4jExposeWebAppRoot' to 'false'. This eliminates the use of log file locations relative to the web application's root directory but still allows a log4j config location outside the classpath.
(3) Remove the 'Log4jConfigListener' from your application's web.xml.

What do you think is the best solution and should be incorporated into the riot skeleton project?

-alf

Alf Werder

Technische Leitung
Head of Engineering

http://www.glonz.com

Thursday, July 22, 2010

rpmdb: Lock table is out of available locker entries

This is what to do to fix this kind of problem :

tar cvzf rpmdb-backup.tar.gz /var/lib/rpm
rm /var/lib/rpm/__db.00*
rpm --rebuilddb
rpm -qa | sort

The first line is to backup your database
The second to remove locks
The third to rebuild your database
The fourth to check if all is ok

Thursday, May 20, 2010

Friday, March 12, 2010

Detect intrusion attacks on Linux

grep "Invalid" /var/log/auth.log | grep -i ssh | awk '{a[$1$2]++}END{for (i in a){print i " " a[i]}}'

Thanks Marco!

Tuesday, February 23, 2010

How to fix mouse click problem on flash player 64 bits with Ubuntu 9.10

I just write here some informations I found on the web. On my Linux Ubuntu 9.10 64 bits, the flash plugin sometimes doesn't register mouse clicks. To fix it, edit the following file by doing :

sudo gedit /usr/lib/nspluginwrapper/noarch/npviewer

and add this export

export GDK_NATIVE_WINDOWS=1

like this

#!/bin/sh
#
# nsplugin viewer wrapper script...
OS="`uname -s`"
ARCH="`uname -m`"
NPW_LIBDIR="/usr/lib/nspluginwrapper"
export GDK_NATIVE_WINDOWS=1

if test -z "$TARGET_OS"; then
...

Enjoy!

Friday, February 19, 2010

An awesome Lego robot

Be careful, this is an awesome video! You can also observe that the programmer is using an Acer netbook.