DB Connection pooling with J2EE

Manage access to shared, server-side resources for high performance

By Siva Visveswaran, JavaWorld.com, 10/27/00

Connection pooling is a technique that was pioneered by database vendors to allow multiple clients to share a cached set of connection objects that provide access to a database resource. In this article, I examine connection pooling in a J2EE environment for server-side resources such as databases, message queues, directories, and enterprise systems.
Why pool resource connections?

Consider the following code example where an EJB accesses a database resource using JDBC 1.0, without connection pooling:

...
import java.sql.*;
import javax.sql.*;
...
public class AccountBean implements EntityBean {
...
public Collection ejbFindByLastName(String lName) {
try {
String dbdriver = new InitialContext().lookup("java:comp/env/DBDRIVER").toString();
Class.forName(dbdriver).newInstance();
Connection conn = null;
conn = DriverManager.getConnection("java:comp/env/DBURL", "userID", "password");
...
conn.close();
}
...
}

Evidently, the main problem in this example is the opening and closing of connections. Given that entity beans are shared components, for every client request, the database connections are acquired and released several times.

You can see from Figure 1 that acquiring and releasing database connections via the database manager, using JDBC 1.0, will impact the performance on the EJB layer. That impact is due to the overhead in creating and destroying those objects by the database resource manager process. Typically, the application server process takes around one to three seconds to establish a database connection (that includes communicating with the server, authenticating, and so forth), and that needs to be done for every client (EJB) request.


Figure 1. Connection management using JDBC 1.0

Connection pooling using service provider facilities

Now I will look at what connection pooling facilities are currently available for database and nondatabase resource types in the J2EE environment.
JDBC 2.0 Standard Extension API

The JDBC 2.0 Standard Extension API specifies that a database service provider can implement a pooling technique that can allow multiple connection objects from a resource pool to be shared transparently among the requesting clients. In that situation, a J2EE component can use connection objects without causing overheads on the database resource manager, since a pool manager creates the connection objects upfront, at startup. The application service provider implements the pool manager in its memory space and can optimize resource usage by dynamically altering the pool size, based on demand. That is illustrated in Figure 2.


Figure 2. Connection pooling using JDBC 2.0 Standard extension

Using the DataSource interface (JDBC 2.0) or the DriverManager (JDBC 1.0) interface, a J2EE component could get physical database connection objects. To obtain logical (pooled) connections, the J2EE component must use these JDBC 2.0 pooling manager interfaces:

* A javax.sql.ConnectionPoolDataSource interface that serves as a resource manager connection factory for pooled java.sql.Connection objects. Each database server vendor provides the implementation for that interface (for example, Oracle implements the oracle.jdbc.pool.OracleConnectionPoolDataSource class).
* A javax.sql.PooledConnection interface that encapsulates the physical connection to a database. Again, the database vendor provides the implementation.



An XA (X/Open specification) equivalent exists for each of those interfaces as well as for XA connections.

The following code example shows how an EJB application might access a database resource by using pooled connection objects (based on JDBC 2.0). The EJB component in this example uses a JNDI lookup to locate the database connection pool resource. The JNDI 1.2 Standard Extension API lets Java applications access objects in disparate directories and naming systems in a common way. Using the JNDI API, an application can look up a directory to locate any type of resource such as database servers, LDAP servers, print servers, message servers, file servers, and so forth. For a good overview of JNDI, refer to "The Java Naming and Directory Interface (JNDI): A More Open and Flexible Model."

Note: The actual code will vary depending on the database vendor implementation classes.

import java.sql.*;
import javax.sql.*;
// import here vendor specific JDBC drivers

public ProductPK ejbCreate() {
try {
// initialize JNDI lookup parameters
Context ctx = new InitialContext(parms);
...
ConnectionPoolDataSource cpds = (ConnectionPoolDataSource)ctx.lookup(cpsource);
...
// Following parms could all come from a JNDI look-up
cpds.setDatabaseName("PTDB");
cpds.setUserIF("XYZ");
...
PooledConnection pc = cpds.getPooledConnection();
Connection conn = pc.getConnection();
...
// do business logic
conn.close();
}
...
}

The key difference between the above code (using JDBC 2.0) and using JDBC 1.0 is that a getConnection() gets an already open connection from the pool, and close() simply releases the connection object back to the pool. JDBC 2.0 drivers are available today from almost every database server vendor such as Oracle, DB2, Sybase, and Informix. And most application server vendors (IBM, BEA, iPlanet, IONA, etc.) today support JDBC 2.0.

Source>>http://www.javaworld.com/javaworld/jw-10-2000/jw-1027-pool.html