Connection pooling

The JDBC 2.0 Optional Package (formerly the JDBC 2.0 Standard Extension API), Chapter 6, “Connection Pooling.”

• javax.sql.ConnectionPoolDataSource

• javax.sql.PooledConnection

Traditional database applications create one connection to a database that you use for each session of an application. However, a Web-based database application may need to open and close a new connection several times when using the application.

An efficient way to handle Web-based database connections is to use connection pooling, which maintains open database connections and manages connection sharing across different user requests to maintain performance and to reduce the number of idle connections. On each connection request, the connection pool first determines if there is an idle connection in the pool. If there is, the connection pool returns that connection instead of making a new connection to the database.

The com.sybase.jdbc4.jdbc.ConnectionPoolDataSource class is provided to interact with connection pooling implementations. When you use ConnectionPoolDataSource, pool implementations listen to the PooledConnection. The implementation is notified when you close the connection, or if you have an error that destroys the connection. At this point, the pool implementation decides what to do with the PooledConnection.

Without connection pooling, a transaction:

1. Creates a connection to the database.

2. Sends the query to the database.

3. Gets back the result set.

4. Displays the result set.

5. Destroys the connection.

With connection pooling, the sequence looks more like this:

1. Sees if an unused connection exists in the “pool” of connections.

2. If so, uses it; otherwise creates a new connection.

3. Sends the query to the database.

4. Gets back the result set.

5. Displays the result set.

6. Returns the connection to the “pool.” The user still calls “close( )”, but the connection remains open, and the pool is notified of the close request.

It is less costly to reuse a connection than to create a new one every time a client needs to establish a connection to a database.

To enable a third party to implement the connection pool, the jConnect implementation has the ConnectionPoolDataSource interface produce PooledConnections, similar to the way the DataSource interface produces Connections.The pool implementation creates “real” database connections, using the getPooledConnection( ) methods of ConnectionPoolDataSource. Then, the pool implementation registers itself as a listener to the PooledConnection. Currently, when a client requests a connection, the pool implementation invokes getConnection( ) on an available PooledConnection. When the client finishes with the connection and calls close, the pool implementation is notified through the ConnectionEventListener interface that the connection is free and available for reuse.

The pool implementation is also notified through the ConnectionEventListener interface if the client somehow corrupts the database connection, so that the pool implementation can remove that connection from the pool.For more information, refer to Appendix B in the JDBC 2.0 Optional Package (formerly the JDBC 2.0 Standard Extension API).

This procedure initializes three properties (INITIAL_CONTEXT_FACTORY, PROVIDER_URL, and OBJECT_FACTORIES as shown on page 78), and retrieves a ConnectionPoolDataSource object. For a more complete code example, see sample2/SimpleConnectionPool.java. The fundamental difference is:

...
ConnectionPoolDatabase cpds = (ConnectionPoolDataSource)
    ctx.lookup("servername=myASE");
PooledConnection pconn = cpds.getPooledConnection();