Oracle8i JDBC Developer's Guide and Reference Release 3 (8.1.7) Part Number A83724-01 |
Library | Service | Contents | Index

## 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:

- Connection pool data sources--similar in concept and functionality to the data sources described previously, but with methods to return pooled connection instances, instead of normal connection instances.

- Pooled connections--a pooled connection instance represents a single physical connection to a database, remaining open during use by a series of logical connection instances.

  A logical connection instance is a simple connection instance (such as a standard `Connection` instance or an `OracleConnection` instance) returned by a pooled connection instance. Each logical connection instance acts as a temporary handle to the physical connection represented by the pooled connection instance.

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

Note: 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).

Note: 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();

    ocpds.setDriverType("oci8");
    ocpds.setServerName("dlsun999");
    ocpds.setNetworkProtocol("tcp");
    ocpds.setDatabaseName("816");
    ocpds.setPortNumber(1521);
    ocpds.setUser("scott");
    ocpds.setPassword("tiger");

    PooledConnection pc = ocpds.getPooledConnection();

    Connection conn = pc.getConnection();
    ...

For a complete sample program, see "Pooled Connection--PooledConnection.java".

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index