Oracle8i JDBC Developer's Guide and Reference
Release 3 (8.1.7)

Part Number A83724-01





Go to previous page Go to beginning of chapter Go to next page

Connection Pooling

Connection pooling in the JDBC 2.0 extension API is a framework for caching database connections. This allows reuse of physical connections and reduced overhead for your application. Connection pooling functionality minimizes expensive operations in the creation and closing of sessions.

The following are central concepts:

For further introductory and general information about connection pooling, refer to the Sun Microsystems specification for the JDBC 2.0 Optional Package.


The concept of connection pooling is not relevant to the server-side internal driver, where you are simply using the default connection, and is only relevant to the server-side Thin driver within a single session.  

Connection Pooling Concepts

If you do not use connection pooling, each connection instance (java.sql.Connection or oracle.jdbc.driver.OracleConnection instance) encapsulates its own physical database connection. When you call the close() method of the connection instance, the physical connection itself is closed. This is true whether you obtain the connection instance through the JDBC 2.0 data source facility described under "Data Sources", or through the DriverManager facility described under "Open a Connection to a Database".

With connection pooling, an additional step allows physical database connections to be reused by multiple logical connection instances, which are temporary handles to the physical connection. Use a connection pool data source to return a pooled connection, which is what encapsulates the physical database connection. Then use the pooled connection to return JDBC connection instances (one at a time) that each act as a temporary handle.

Closing a connection instance that was obtained from a pooled connection does not close the physical database connection. It does, however, free the resources of the connection instance, clear the state, close statement objects created from the connection instance, and restore the defaults for the next connection instance that will be created.

To actually close the physical connection, you must invoke the close() method of the pooled connection. This would typically be performed in the middle tier.

Connection Pool Data Source Interface and Oracle Implementation

The javax.sql.ConnectionPoolDataSource interface outlines standard functionality of connection pool data sources, which are factories for pooled connections. The overloaded getPooledConnection() method returns a pooled connection instance and optionally takes a user name and password as input:

public interface ConnectionPoolDataSource
   PooledConnection getPooledConnection() throws SQLException;
   PooledConnection getPooledConnection(String user, String password)
      throws SQLException;

Oracle JDBC implements the ConnectionPoolDataSource interface with the oracle.jdbc.pool.OracleConnectionPoolDataSource class. This class also extends the OracleDataSource class, so it includes all the connection properties and getter and setter methods described in "Data Source Properties".

The OracleConnectionPoolDataSource class getPooledConnection() methods return the Oracle implementation of pooled connection instances, which are OraclePooledConnection instances (as discussed in the next section).


You can register connection pool data sources in JNDI using the same naming conventions as discussed for non-pooling data sources in "Register the Data Source".  

Pooled Connection Interface and Oracle Implementation

A pooled connection instance encapsulates a physical connection to a database. This database would be the one specified in the connection properties of the connection pool data source instance used to produce the pooled connection instance.

A pooled connection instance is an instance of a class that implements the standard javax.sql.PooledConnection interface. The getConnection() method specified by this interface returns a logical connection instance that acts as a temporary handle to the physical connection, as opposed to encapsulating the physical connection, as does a non-pooling connection instance:

public interface PooledConnection
   Connection getConnection() throws SQLException;
   void close() throws SQLException;
   void addConnectionEventListener(ConnectionEventListener listener) ... ;
   void removeConnectionEventListener(ConnectionEventListener listener);
   void setStmtCacheSize(int size);
   void setStmtCacheSize(int size, boolean clearMetaData);
   int getStmtCacheSize();

(Event listeners are used in connection caching and are discussed in "Typical Steps in Using a Connection Cache".)

Oracle JDBC implements the PooledConnection interface with the oracle.jdbc.pool.OraclePooledConnection class. The getConnection() method returns an OracleConnection instance.

A pooled connection instance will typically be asked to produce a series of connection instances during its existence, but only one of these connection instances can be open at any particular time.

Each time a pooled connection instance getConnection() method is called, it returns a new connection instance that exhibits the default behavior, and it closes any previous connection instance that still exists and has been returned by the same pooled connection instance. You should explicitly close any previous connection instance before opening a new one, however.

Calling the close() method of a pooled connection instance closes the physical connection to the database. The middle-tier layer typically performs this.

The OraclePooledConnection class includes methods to enable statement caching for a pooled connection. The cache for statements is maintained for the pooled connection as a whole, and all logical connections obtained from the pooled connection share it. Therefore, when statement caching is enabled, a statement you create on one logical connection can be re-used on another logical connection. For the same reason, you cannot enable or disable statement caching on individual logical connections. This function applies to both implicit and explicit statement caching.

The following are OraclePooledConnection method definitions for statement caching:

public void setStmtCacheSize (int size)
   throws SQLException

public void setStmtCacheSize (int size, boolean clearMetaData)
   throws SQLException

public int getStmtCacheSize()

See Chapter 14, "Statement Caching", for more details on statement caching.

Creating a Connection Pool Data Source and Connecting

This section contains an example of the most basic use of a connection pool data source to connect to a database without using JNDI functionality. You could optionally use JNDI, binding the connection pool data source instance to a JNDI logical name, in the same way that you would for a generic data source instance (as "Register the Data Source" illustrates).

Summary of Imports for Oracle Connection Pooling

You must import the following for Oracle connection pooling functionality:

import oracle.jdbc.pool.*;

This package contains the OracleDataSource, OracleConnectionPoolDataSource, and OraclePooledConnection classes, in addition to classes for connection caching and event-handling, which "Connection Caching" discusses.

Oracle Connection Pooling Code Sample

This example first creates an OracleConnectionPoolDataSource instance, next initializes its connection properties, then gets a pooled connection instance from the connection pool data source instance, and finally gets a connection instance from the pooled connection instance. (The getPooledConnection() method actually returns an OraclePooledConnection instance, but in this case only generic PooledConnection functionality is required.)

OracleConnectionPoolDataSource ocpds = new OracleConnectionPoolDataSource();


PooledConnection pc = ocpds.getPooledConnection();

Connection conn = pc.getConnection();

For a complete sample program, see "Pooled".

Go to previous page
Go to beginning of chapter
Go to next page
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.