CONNECTION POOLING AND CACHING in Font

Drawer Code 39 Full ASCII in Font CONNECTION POOLING AND CACHING

The getConnection() method of this interface returns a logical connection instance to the application. Calling the close() method on a pooled connection object closes the physical connection remember, this is performed by the middle-tier code that manages the connection pool. The connection event listeners are used to handle events that arise when an associated logical connection is closed, for example. The OraclePooledConnection class has methods that enable statement caching (both implicit and explicit) for a pooled connection (see 13 for details on this feature). All logical connections obtained from a pooled connection share the same cache, since the underlying physical connection is where the caching happens. This implies that when statement caching is enabled, a statement you create on one logical connection can be reused by another logical connection. It follows that you cannot enable or disable statement caching on individual logical connections. The following are OraclePooledConnection method definitions for statement caching: public public public public public public boolean getExplicitCachingEnabled(); boolean getImplicitCachingEnabled(); int getStatementCacheSize(); void setExplicitCachingEnabled(boolean); void setImplicitCachingEnabled(boolean); void setStatementCacheSize(int);

Let s move on to look at an example of creating a connection pool data source and getting a pooled connection object.

In this example, we demonstrate the following concepts: Creating a pooled connection Getting a logical connection from a pooled connection Closing the logical connection Closing the pooled connection To peek behind the scenes, we will run the query that we discussed in the section Connections and Sessions in Oracle earlier to list the current physical connections actually made to the database. Note that in this example, there is a one-to-one correspondence between a physical connection and a session. We need a way to pause after each of the four steps in the program, in order to run the query in the database and watch how many connections are created. For this, I wrote a class called InputUtil whose waitTillUserHitsEnter() method pauses until you press the Enter key. Please see the section A Utility to Pause in a Java Program of 1 for more details on this method.

The following program, DemoConnectionPooling, illustrates how to create a pooled connection and retrieve a logical connection from it. First, the necessary import statements and the declaration of the main() method are shown: /* This program demonstrates how to create a pooled connection * and obtain a logical connection from it. * COMPATIBLITY NOTE: runs successfully against 9.2.0.1.0 and 10.1.0.2.0 */ import java.sql.Connection; import javax.sql.PooledConnection; import oracle.jdbc.pool.OracleConnectionPoolDataSource; import book.util.InputUtil; class DemoConnectionPooling { public static void main(String args[]) throws Exception { To create a pooled connection, we first create an instance of OracleConnectionPoolDataSource and set its connection properties: OracleConnectionPoolDataSource ocpds = new OracleConnectionPoolDataSource(); ocpds.setURL ( "jdbc:oracle:thin:@usunrat24.us.oracle.com:1521:ora92i" ); ocpds.setUser("scott"); // username ocpds.setPassword("tiger"); // password

Note Instead of using the setURL() method and so on, you can use the individual setter methods such

The next step is to obtain the pooled connection from the OracleConnectionPoolDataSource instance as follows (note the pause we give right after, using the InputUtil.waitTillUser HitsEnter() method): PooledConnection pooledConnection = ocpds.getPooledConnection(); InputUtil.waitTillUserHitsEnter( "Done creating pooled connection."); We then obtain the logical connection from the pooled connection, followed by another pause: Connection connection = pooledConnection.getConnection(); InputUtil.waitTillUserHitsEnter( "Done getting connection from pooled connection object."); After using the logical connection to execute statements, the application can close it: connection.close(); InputUtil.waitTillUserHitsEnter( "Done closing logical connection");

And finally, to close the pooled connection (thus releasing the actual physical connection), we can use the close() method on the pooled connection: pooledConnection.close(); InputUtil.waitTillUserHitsEnter("Done closing pooled connection"); }// end of main }// end of program Before running the program, I made sure that there was no one connected to my test database. In one session, I connected to my database as SYS user. Then I ran the preceding program and ran the query discussed earlier to list connections as SYS after each of the pauses we introduced. Table 14-1 lists the steps and the query results. Table 14-1. Results of Running a Query That Detects Connections After Each Pause in the Program DemoConnectionPooling