Connection Pooling2:JAVA-JDBC

In a basic DataSource implementation, there is a 1:1 correspondence between the
client’s Connection object and the physical database connection. When the Connection object is closed, the physical connection is dropped. Thus, the overhead of opening, initializing, and closing the physical connection is incurred for each client session. A connection pool solves this problem by maintaining a cache of physical database connections that can be reused across client sessions. Connection pooling greatly improves performance and scalability, particularly in a three-tier environment where multiple clients can share a smaller number of physical database connections. In

The algorithm used to manage the connection pool is implementation-specific and varies with application servers. The application server provides its clients with an implementation of the DataSource interface that makes connection pooling transparent to the client. As a result, the client gets better performance and scalability while using the same JNDI and DataSource APIs as before.


This chapter also describes some important differences

Application Server
JDBC
Application
Cache of
PooledConnection objects
JDBC Driver
logical
Connection
object
physical
PooledConnection
object
ConnectionPoolDataSource API
DataSource API

between a basic DataSource object and one that implements connection pooling. In addition, it discusses how a pooled connection can maintain a pool of reusable PrepredStatement objects.
Although much of the discussion in this chapter assumes a three-tier environment, connection pooling is also relevant in a two-tier environment. In a two-tier environment, the JDBC driver implements both the DataSource and ConnectionPoolDataSource interfaces. This implementation allows an application that opens and closes multiple connections to benefit from connection pooling.

ConnectionPoolDataSource and PooledConnection

Typically, a JDBC driver implements the ConnectionPoolDataSource interface, and the application server uses it to obtain PooledConnection objects.
CODE EXAMPLE shows the signatures for the two versions of the getPooledConnection method.

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

CODE EXAMPLE The ConnectionPoolDataSource interface A PooledConnection object represents a physical connection to a data source. The JDBC driver’s implementation of PooledConnection encapsulates all of the details of maintaining that connection. An application server caches and reuses PooledConnection objects within its implementation of the DataSource interface. When a client calls the method DataSource.getConnection, the application server uses the physical PooledConnection object to obtain a logical Connection object.

CODE EXAMPLE shows the PooledConnection interface definition.

public interface PooledConnection {
Connection getConnection() throws SQLException;
void close() throws SQLException;
void addConnectionEventListener(
ConnectionEventListener listener);
void removeConnectionEventListener(
ConnectionEventListener listener);
}

CODE EXAMPLE The PooledConnection interface When an application is finished using a connection, it closes the logical connection using the method Connection.close. This closes the logical connection but does not close the physical connection. Instead, the physical connection is returned to the pool so that it can be reused. Connection pooling is completely transparent to the client: A client obtains a pooled connection and uses it just the same way it obtains and uses a nonpooled connection.

Connection Events

Recall that when an application calls the method Connection.close, the underlying physical connection—the PooledConnection object—is available for reuse. JavaBeans-style events are used to notify the connection pool manager (the application server) that a PooledConnection object can be recycled. In order to be notified of an event on a PooledConnection object, the connection pool manager must implement the ConnectionEventListener interface and then
be registered as a listener by that PooledConnection object. The ConnectionEventListener interface defines the following two methods, which correspond to the two kinds of events that can occur on a PooledConnection object:

• connectionClosed — triggered when the logical Connection object associated with this PooledConnection object is closed, that is, the application called the method Connection.close
• connectionErrorOccurred — triggered when a fatal error, such as the server crashing, causes the connection to be lost

A connection pool manager registers itself as a listener for a PooledConnection object using the PooledConnection.addConnectionEventListener method. Typically, a connection pool manager registers itself as a ConnectionEventListener before returning a Connection object to an
application. The driver invokes the ConnectionEventListener methods connectionClosed and connectionErrorOccurred when the corresponding events occur. Both methods take a ConnectionEvent object as a parameter, which can be used to determine which PooledConnection object was closed or had an error. When the JDBC application closes its logical connection, the JDBC driver notifies the connection pool manager (the listener) by calling the listener’s implementation of the method connectionClosed. At this point, the connection pool manager can return the PooledConnection object to the pool for reuse. When an error occurs, the JDBC driver notifies the listener by calling its connectionErrorOccurred method and then throws an SQLException object to the application to notify it of the same error. In the event of a fatal error, the bad PooledConnection object is not returned to the pool. Instead, the connection pool manager calls the PooledConnection.close method on the PooledConnection object to close the physical connection.

Connection Pooling in a Three-tier Environment

The following sequence of steps outlines what happens when a JDBC client requests a connection from a DataSource object that implements connection pooling:

• The client calls DataSource.getConnection.
• The application server providing the DataSource implementation looks in its

connection pool to see if there is a suitable PooledConnection object— a physical database connection—available. Determining the suitability of a given PooledConnection object may include matching the client’s user authentication information or application type as well as using other implementation-specific criteria. The lookup method and other methods associated with managing the connection pool are specific to the application server.

• If there are no suitable PooledConnection objects available, the application server calls the ConnectionPoolDataSource.getPooledConnection method to get a new physical connection. The JDBC driver implementing ConnectionPoolDataSource creates a newPooledConnection object and returns it to the application server.
  

• Regardless of whether the PooledConnection was retrieved from the pool or was newly created, the application server does some internal bookkeeping to indicate that the physical connection is now in use.
• The application server calls the method PooledConnection.getConnection to get a logical Connection object. This logical Connection object is actually a “handle” to a physical PooledConnection object, and it is this handle that is returned by the DataSource.getConnection method when connection pooling is in effect.
  

• The application server registers itself as a ConnectionEventListener by calling the method PooledConnection.addConnectionEventListener. This is done so that the application server will be notified when the physical connection is available for reuse.
  

• The logical Connection object is returned to the JDBC client, which uses the same Connection API as in the basic DataSource case. Note that the underlying physical connection cannot be reused until the client calls the method Connection.close. Connection pooling can also be implemented in a two-tier environment where there is no application server. In this case, the JDBC driver provides both the implementation of DataSource which is visible to the client and the underlying ConnectionPoolDataSource implementation.
  

DataSource Implementations and Connection Pooling

Aside from improved performance and scalability, a JDBC application should not see any difference between accessing a DataSource object that implements connection pooling and one that does not. However, there are some important differences in the application server and driver level implementations. A basic DataSource implementation, that is, one that does not implement connection pooling, is typically provided by a JDBC driver vendor. In a basic
DataSource implementation, the following are true:

• The DataSource.getConnection method creates a new Connection object that represents a physical connection and encapsulates all of the work to set up and manage that connection.
• The Connection.close method shuts down the physical connection and frees the associated resources. In a DataSource implementation that includes connection pooling, a great deal happens behind the scenes. In such an implementation, the following are true:
• The DataSource implementation includes an implementation-specific connection pooling module that manages a cache of PooledConnection objects. The DataSource object is typically implemented by the application server as a layer on top of the driver’s implementations of the ConnectionPoolDataSource and PooledConnection interfaces.
• The DataSource.getConnection method calls PooledConnection.getConnection to get a logical handle to an underlying physical connection. The overhead of setting up a new physical connection is incurred only if there are no existing connections available in the connection pool. When a new physical connection is needed, the connection pool manager will call the ConnectionPoolDataSource method getPooledConnection to create one. The work to manage the physical connection is delegated to the PooledConnection object.
  

• The Connection.close method closes the logical handle, but the physical connection is maintained. The connection pool manager is notified that the underlying PooledConnection object is now available for reuse. If the application attempts to reuse the logical handle, the Connection implementation throws an SQLException.
  

• A single physical PooledConnection object may generate many logical Connection objects during its lifetime. For a given PooledConnection object, only the most recently produced logical Connection object will be valid. Any previously existing Connection object is automatically closed when the associated PooledConnection.getConnection method is called. Listeners (connection pool managers) are not notified in this case. This gives the application server a way to take a connection away from a client. This is an unlikely scenario but may be useful if the application server is trying to force an orderly shutdown.
  

• A connection pool manager shuts down a physical connection by calling the method PooledConnection.close. This method is typically called only in certain circumstances: when the application server is undergoing an orderly shutdown, when the connection cache is being reinitialized, or when the application server receives an event indicating that an unrecoverable error has occurred on the connection.
  

Deployment

Deploying a DataSource object that implements connection pooling requires that both a client-visible DataSource object and an underlying ConnectionPoolDataSource object be registered with a JNDI-based naming service.

The first step is to deploy the ConnectionPoolDataSource implementation, as is
done in CODE EXAMPLE

// ConnectionPoolDS implements the ConnectionPoolDataSource
// interface. Create an instance and set properties.
com.acme.jdbc.ConnectionPoolDS cpds =
new com.acme.jdbc.ConnectionPoolDS();
cpds.setServerName(“bookserver”);
cpds.setDatabaseName(“booklist”);
cpds.setPortNumber(9040);
cpds.setDescription(“Connection pooling for bookserver”);
// Register the ConnectionPoolDS with JNDI, using the logical name
// “jdbc/pool/bookserver_pool”
Context ctx = new InitialContext();
ctx.bind(“jdbc/pool/bookserver_pool”, cpds);
CODE EXAMPLE 11-3 Deploying a ConnectionPoolDataSource object
Once this step is complete, the ConnectionPoolDataSource implementation is
available as a foundation for the client-visible DataSource implementation. The
DataSource implementation is deployed such that it references the
ConnectionPoolDataSource implementation, as shown in CODE EXAMPLE 11-4.
// PooledDataSource implements the DataSource interface.
// Create an instance and set properties.
com.acme.appserver.PooledDataSource ds =
new com.acme.appserver.PooledDataSource();
ds.setDescription(“Datasource with connection pooling”);
// Reference the previously registered ConnectionPoolDataSource
ds.setDataSourceName(“jdbc/pool/bookserver_pool”);
// Register the DataSource implementation with JNDI, using the logical
// name “jdbc/bookserver”.
Context ctx = new InitialContext();
ctx.bind(“jdbc/bookserver”, ds);
CODE EXAMPLE Deploying a DataSource object backed by a
ConnectionPoolDataSource object