JDBC connection pooling tutorial

This tutorial provides information to help developers implement a connection pooling strategy for applications that must handle connection pooling. To start, we will guide you through an overview of JDBC connection pooling as specified by the JDBC 3.0 specification. Then, we will demonstrate how to use the Progress DataDirect Connection Pool Manager (which is shipped with Progress DataDirect® for JDBC Drivers) for your applications.

Establishing JDBC connections is resource-expensive, especially when the JDBC API is used in a less than optimal server environment, such as when a DataDirect for JDBC Driver is running on a Java-enabled web server. In this type of environment, performance can be improved significantly when connection pooling is used. Connection pooling allows you to reuse connections rather than create a new one every time an application needs to connect to the database. To facilitate connection reuse, a memory cache of database connections, called a connection pool, is maintained by a connection pooling module as a layer on top of a standard JDBC driver product.

Connection pooling is performed in the background and does not affect how an application is coded; however, the application must use a DataSource object (an object implementing the DataSource interface) to obtain a connection, instead of using the DriverManager class. A class implementing the DataSource interface may or may not provide connection pooling. A DataSource object registers with a JNDI naming service. Once a DataSource object is registered, the application retrieves it from the JNDI naming service in the standard way.

For example:

Context ctx = new InitialContext();
ConnectionPoolDataSource ds =
(ConnectionPoolDataSource)ctx.lookup("EmployeeDB");
Connection conn = ds.getConnection("scott", "tiger");

First, the lookup determines whether there is a data source that matches the specified value, such as EmployeeDB. If the DataSource object provides connection pooling, the getConnection method returns a connection from the pool if one is available. If the DataSource object does not provide connection pooling or, if there are no available connections in the pool, the getConnection method creates a new connection. Note that reused connections from the pool behave the same way as newly created physical connections. When the application has finished its work with the connection, the application explicitly closes the connection.

For example:

Connection con = ds.getConnection("scott", "tiger");
// Do some database activities using the connection...
con.close();

The closing event on a pooled connection signals the pooling module to place the connection back in the connection pool for future reuse.

The JDBC 3.0 API provides a general framework with hooks to support connection pooling rather than specifying a particular connection pooling implementation. This way, third-party vendors or users can implement the specific caching or pooling algorithms that best fit their needs.

The JDBC 3.0 API specifies a ConnectionEvent class and the following interfaces as the hooks for any connection pooling implementation:

• ConnectionPoolDataSource
  The ConnectionPoolDataSource interface acts as a factory that creates PooledConnection objects.
• PooledConnection
  A PooledConnection object acts as a factory that creates Connection objects. A PooledConnection object maintains the physical connection to the database, while the Connection object created by the PooledConnection object is simply a handle to the physical connection.
• ConnectionEventListener
  The ConnectionEventListener interface receives ConnectionEvent objects from PooledConnection objects when a connection closes or a connection error occurs. When a connection created by a PooledConnection closes, the connection cache module returns the PooledConnection object to the cache.
• JDBCDriverVendorDataSource
  A JDBC driver vendor must provide a class that implements the standard ConnectionPoolDataSource interface. This interface provides hooks that third-party vendors can use to implement pooling as a layer on top of their JDBC drivers.

• JDBCDriverVendorPooledConnection
  A JDBC driver vendor must provide a class that implements the standard PooledConnection interface. This interface allows third-party vendors to implement pooling on top of their JDBC drivers.

• PoolingVendorDataSource
  A third-party vendor must provide a class that implements the DataSource interface. This interface is the entry point that allows interaction with their pooling module. The pooling vendor's class uses the JDBC driver's PooledConnectionDataSource object to create the PooledConnections that the pool manages.

• PoolingVendorConnectionCache
  The JDBC 3.0 API does not specify the interfaces to be used between the DataSource object and the connection cache. The pooling vendor determines how these components will interact. Usually, a connection cache module contains one or multiple classes. In Figure 1, the PoolingVendorConnectionCache class is used as a simple way to convey this concept. The connection cache module should have a class that implements the standard ConnectionEventListener interface.

Figure 1 shows this general framework.

Figure 1: JDBC 3.0 Connection Pooling Architecture

When an application makes a connection by calling DataSource.getConnection() on a PoolingVendorDataSource object, the PoolingVendorDataSource object performs a lookup in the connection cache to determine if a PooledConnection object is available. If one is available, it is used. If a PooledConnection object is not available, the JDBC driver vendor's ConnectionPoolDataSource creates a new PooledConnection object. In either case, a PooledConnection object is made available.

The PoolingVendorDataSource object then invokes the PooledConnection.getConnection() method to obtain a Connection object, which it returns to the application to use as a normal connection. Because the JDBC driver vendor implements the PooledConnection interface, the JDBC driver creates the Connection object; however, this Connection object is not a physical connection as in the non-pooling case. The Connection object is a handle to the physical connection maintained by the PooledConnection object.

When the application closes the connection by calling the Connection.close() method, a ConnectionEvent is generated and is passed to the cache module. The cache module returns the PooledConnection object to the cache to be reused. The application does not have access to the PooledConnection.close() method. Only the connection pooling module, as part of its clean-up activity, issues the PooledConnection.close() method to actually close the physical connection.

This section provides examples on how to create pooled and non-pooled DataSource objects for DataDirect for JDBC drivers and register them to a JNDI naming service

Creating a DataDirect Data Source Object

This example shows how to create a DataDirect for JDBC drivers DataSource object and register it to a JNDI naming service. The DataSource class provided by the DataDirect for JDBC drivers is database-dependent. In the following example, we use Oracle, so the DataSource class is com.ddtek.jdbcx.oracle.OracleDataSource.

If you want the client application to use:

• A non-pooled data source, the application can specify the JNDI name of this data source object as registered in the following code example (jdbc/ConnectSparkyOracle).
• A pooled data source, the application must specify the JNDI name (jdbc/SparkyOracle) as registered in the code example in the following Creating a data source using the DataDirect Connection Pool Manager.

Creating a Data Source Using the DataDirect Connection Pool Manager

The following Java code example creates a data source for DataDirect for JDBC Drivers and registers it to a JNDI naming service. The PooledConnectionDataSource class is provided by the DataDirect com.ddtek.pool package. In the following code example, the PooledConnectionDataSource object references a pooled DataDirect for JDBC Drivers data source object. Therefore, the example performs a lookup by setting the DataSourceName attribute to the JNDI name of a registered pooled data source (in this example, jdbc/ConnectSparkyOracle, which is the DataDirect for JDBC Drivers DataSource object created in Creating a DataDirect Data Source Object.

Client applications that use this data source must perform a lookup using the registered JNDI name (jdbc/SparkyOracle in this example).

Whether connection pooling is used does not affect application code. Code changes are not required because the application performs a lookup on a JNDI name of a previously registered data source. If the data source specifies a connection pooling implementation during JNDI registration (as described in Creating a data source using the DataDirect Connection Pool Manager), the client application benefits from faster connections through connection pooling.

The following example shows code that can be used to look up and use a JNDI-registered data source for connections. You can specify the JNDI lookup name for the data source you created (as described in Creating a Data Source Using the DataDirect Connection Pool Manager).

NOTE: The DataDirect Connect for JDBC DataSource object class implements the DataSource interface for non-pooling in addition to ConnectionPoolDataSource for pooling. To use a non-pooling data source, use the JNDI name registered in the example code in section Creating a DataDirect data source object and run the TestDataSourceApp. For example:

java TestDataSourceApp jdbc/ConnectSparkyOracle

To ensure that the connection pool is closed correctly, the DataDirect Connection Pool Manager is notified automatically when an application stops running. However, the PooledConnectionDataSource.close method can also be used to explicitly close the connection pool while the application is running. For example, if changes are made to the pool configuration using a pool management tool, the PooledConnectionDataSource.close method can be used to force the connection pool to close and re-create the pool using the new configuration values.

Connection pooling provides a significant improvement on performance by reusing connections rather than creating a new connection for each connection request, without requiring changes in your JDBC application code.