24.2 Using Connections and Connection Pools

In a JDBC-based application, a lot revolves around the java.sql.Connection interface. Before any database operations can take place, the application must create a Connection to the database. It then acts as the communication channel between the application and the database, carrying the SQL statements sent by the application and the results returned by the database. A Connection is associated with a database user account to allow the database to enforce access control rules for the SQL statements submitted through the Connection. Finally, the Connection is also the boundary for database transactions. Only SQL statements executed through the same Connection can make up a transaction. A transaction consists of a number of SQL statements that must either all succeed or all fail as one atomic operation. A transaction can be committed (the changes resulting from the statements are permanently saved) or rolled back (all changes are ignored) by calling Connection methods.

In a standalone application, a Connection is typically created once and kept open until the application is shut down. This isn't surprising, since a standalone application serves only one user at a time, and all database operations initiated by a single user are typically related to each other. In a server application that deals with unrelated requests from many different users, it's not so obvious how to deal with connections. There are three things to consider: a Connection is time-consuming to create, it must be used for only one user at a time to avoid transaction clashes, and it's expensive to keep open.

Creating a Connection is an operation that can actually take a second or two to perform. Besides establishing a network connection to the database, the database engine must authenticate the user and create a context with various data structures to keep track of transactions, cached statements, results, and so forth. Creating a new Connection for each request received by the server, while simple to implement, is far too time-consuming in a high-traffic server application.

One way to minimize the number of times a connection needs to be created is to keep one Connection per servlet or JSP page that need access to the database. A Connection can be created when the web resource is initialized and be kept in an instance variable until the application is shut down. As you will discover when you deploy an application based on this approach, this route leads to numerous multithreading issues. Each request executes as a separate thread through the same servlet or JSP page. Some JDBC drivers don't support multiple threads accessing the same Connection at all, causing all kinds of runtime errors. Others support it by serializing all calls, leading to poor scalability. Another serious problem with this approach is that requests from multiple users, all using the same Connection, operate within the same transaction. If one request leads to a rollback, all other database operations using the same Connection are also rolled back.

A connection is expensive to keep open in terms of server resources such as memory. Many commercial database products use licenses that are priced based on the number of simultaneously open connections, so a connection can also be expensive in terms of real money. Therefore, it's wise to try to minimize the number of connections the application needs. An alternative to the "one Connection per resource" approach is to create a Connection for each user when the first request is received and keep it as a session scope object. However, a drawback with this approach is that the Connection will be inactive most of the time, because the user needs time to look at the result of one request before making the next.

The best alternative is to use a connection pool. A connection pool contains a number of Connection objects shared by all servlets and JSP pages. For each request, one Connection is checked out from the pool, used, and checked back in. Using a pool solves the problems described for the other alternatives:

It's time consuming to create a Connection

A pooled Connection is created only once and then reused. Most pool implementations let you specify an initial number of Connection objects to create at start up, as well as a maximum number. New Connection objects are created as needed up to the maximum number. Once the maximum number has been reached, the pool clients wait until an existing Connection object becomes available instead of creating a new one.

There are multithreading problems with a shared Connection

With a pool, each request gets its own Connection so it's used by only one thread at a time, eliminating any potential multithreading issues.

A Connection is a limited resource

With a pool, each Connection is used efficiently. It never sits idle if there are requests pending. If the pool allows you to specify a maximum number of Connection objects, you can also balance a license limit for the number of simultaneous connections against acceptable response times.

A connection pool doesn't solve all problems, however. Because all users are using the same Connection objects, you can't rely on the database engine to limit access to protected data on a per-user basis. Instead, you have to define data-access rules in terms of roles (groups of users with the same access rights). You can then use separate pools for different roles, each pool creating Connection objects with a database account that represents the role.

24.2.1 Using a JDBC 2.0 Optional Package Connection Pool

Connection pools exist in many forms. You find them in books, articles, and on the Web. Yet prior to JDBC 2.0, there was no standard defined for how a Java application would interact with a connection pool. The JDBC 2.0 Optional Package (formerly known as a Standard Extension), now part of JDBC 3.0 and included in the Java SDK 1.4, changed this by introducing a set of interfaces that connection pools should implement:

javax.sql.DataSource

A DataSource represents a database. This is the interface the application always uses to get a Connection. The class that implements the interface can provide connection-pooling capabilities or hand out regular, unpooled Connection objects; the application code is identical for both cases, as described later.

javax.sql.ConnectionPoolDataSource

A DataSource implementation that provides pooling capabilities uses a class that implements the ConnectionPoolDataSource interface. A ConnectionPoolDataSource is a factory for PooledConnection objects. The application code never calls methods in this interface directly.

javax.sql.PooledConnection

The objects a DataSource with pooling capabilities keeps in its pool implement the PooledConnection interface. When the application asks the DataSource for a Connection, it locates an available PooledConnection object or gets a new one from its ConnectionPoolDataSource if the pool is empty.

The PooledConnection provides a getConnection( ) method that returns a Connection object. The DataSource calls this method and returns the Connection to the application. This Connection object behaves like a regular Connection with one exception: when the application calls the close( ) method, instead of closing the connection to the database, it informs the PooledConnection it belongs to that it's no longer used. The PooledConnection relays this information to the DataSource, which returns the PooledConnection to the pool.

Figure 24-2 outlines how an application uses implementations of these interfaces to obtain a pooled connection and how to return it to the pool.

Figure 24-2. Application using a JDBC connection pool

The application calls the DataSource getConnection( ) method. The DataSource looks for an available PooledConnection object in its pool. If it doesn't find one, it uses its ConnectionPoolDataSource object to create a new one. It then calls the getConnection( ) method on the PooledConnection object and returns the Connection object associated with the PooledConnection. The application uses the Connection and calls its close( ) method when it's done. This results in a notification event being sent to the DataSource, which puts the corresponding PooledConnection object back in the pool. If you would like to learn more about the JDBC 2.0 connection pool model, you can download the JDBC 2.0 Optional Package specification or the JDBC 3.0 specification from http://java.sun.com/products/jdbc/.

By implementing these JDBC 2.0 interfaces, JDBC driver vendors and middleware vendors can offer portable connection pooling implementations. The latest version of the JDBC specification, JDBC 3.0, adds statement pooling to the list of features a DataSource can provide. What this means is that in addition to pooling connections, an implementation can pool prepared statements associated with each pooled connection. The result can be dramatically improved performance, while leaving the application untouched; it doesn't need to do anything different compared to using a JDBC 2.0 DataSource. When I write this, very few (if any) vendors offer statement pooling, but you should ask your vendor if they support it.

24.2.2 Making a JDBC 1.0 Connection Pool Behave as a JDBC 2.0 Connection Pool

If you can't find a JDBC 2.0 connection pool implementation for your database, there are plenty of implementations based on JDBC 1.0 available. A popular one is the DBConnectionBroker, available at http://www.javaexchange.com/. Another one, DBCP, is developed by the Jakarta Commons project: http://jakarta.apache.org/commons/index.html and is bundled with Tomcat. In this section I describe a couple of wrapper classes you can use with minimal changes for implementations like these so they can be used in place of a JDBC 2.0 connection pool implementation. This way the JSTL database access actions and other generic database tools can use your wrapped JDBC 1.0 pool, and it's easy to replace it with a real JDBC 2.0 pool when one becomes available from your database vendor or a third party.

The interaction between the wrapper classes and a connection pool implementation is illustrated in Figure 24-3.

Figure 24-3. A connection pool wrapped with JDBC 2.0 interface classes

The application calls the DataSourceWrapper getConnection( ) method. The DataSourceWrapper obtains a Connection object from its ConnectionPool object (which represents the JDBC 1.0 pool implementation). The ConnectionPool either finds an available Connection in its pool or creates a new one. The DataSourceWrapper creates a new ConnectionWrapper object for the Connection it obtained or created and returns the ConnectionWrapper to the application. The application uses the ConnectionWrapper object as a regular Connection. The ConnectionWrapper relays all calls to the corresponding method in the Connection it wraps except for the close( ) method. When the application calls the close( ) method, the ConnectionWrapper returns its Connection to the DataSourceWrapper, which in turn returns it to its ConnectionPool.

The wrapper classes included with the book examples wrap the connection pool described in Java Servlet Programming by Jason Hunter and William Crawford (O'Reilly). It's a simple connection pool implementation, intended only to illustrate the principles of connection pooling. The source code for the connection pool is included with the code for this book, but I will not discuss the implementation of the pool itself, only how to make it look like a JDBC 2.0 connection pool using the wrapper classes. For production use, I recommend that you use a pool intended for real use instead of this code, such as one of the implementations mentioned earlier. The first wrapper class is called com.ora.jsp.sql.ConnectionWrapper, shown in Example 24-1.

Example 24-1. The ConnectionWrapper class

package com.ora.jsp.sql;
  
import java.sql.*;
import java.util.*;
  
class ConnectionWrapper implements Connection {
    private Connection realConn;
    private DataSourceWrapper dsw;
    private boolean isClosed = false;
  
    public ConnectionWrapper(Connection realConn, 
        DataSourceWrapper dsw) {
        this.realConn = realConn;
        this.dsw = dsw;
    }
  
    /**
     * Inform the DataSourceWrapper that the ConnectionWrapper
     * is closed.
     */
    public void close(  ) throws SQLException {
        isClosed = true;
        dsw.returnConnection(realConn);
    }
  
    /**
     * Returns true if the ConnectionWrapper is closed, false
     * otherwise.
     */
    public boolean isClosed(  ) throws SQLException {
        return isClosed;
    }
  
    /*
     * Wrapped methods.
     */
    public void clearWarnings(  ) throws SQLException {
        if (isClosed) {
            throw new SQLException("Pooled connection is closed");
        }
        realConn.clearWarnings(  );
    }
    ...
}

An instance of this class is associated with a real Connection object, retrieved from a connection pool, in the constructor. The constructor parameter list also includes a reference to the DataSourceWrapper instance that creates it.

The ConnectionWrapper class implements the Connection interface. The implementations of all the methods except two simply relay the call to the real Connection object, so it can perform the requested database operation. The implementation of the close( ) method, however, doesn't call the real Connect object's method. Instead, it calls the DataSourceWrapper object's returnConnection( ) method, to return the Connection to the pool. The isClosed( ) method, finally, returns the state of the ConnectionWrapper object as opposed to the real Connection object.

Example 24-2 shows how the com.ora.jsp.sql.DataSourceWrapper gets a connection from a pool and returns it when the pool client is done with it.

Example 24-2. The DataSourceWrapper class

package com.ora.jsp.sql;
  
import java.io.*;
import java.sql.*;
import javax.sql.*;
  
public class DataSourceWrapper implements DataSource {
    private ConnectionPool pool;
    private String driverClassName;
    private String url;
    private String user;
    private String password;
    private int initialConnections;
  
    public void setDriverClassName(String driverClassName) {
        this.driverClassName = driverClassName;
    }
  
    public void setUrl(String url) {
        this.url = url;
    }
  
    public void setUser(String user) {
        this.user = user;
    }
  
    public void setPassword(String password) {
        this.password = password;
    }
  
    private void setInitialConnections(int initialConnections) {
        this.initialConnections = initialConnections;
    }
  
    /**
     * Gets a connection from the pool and returns it wrapped in
     * a ConnectionWrapper.
     */
    public Connection getConnection(  ) throws SQLException {
        if (pool == null) {
            createConnectionPool(  );
        }
        return new ConnectionWrapper(pool.getConnection(  ), this);
    }
    
    /**
     * Returns a Connection to the pool. This method is called by
     * the ConnectionWrapper's close(  ) method.
     */
    public void returnConnection(Connection conn) {
        pool.returnConnection(conn);
    }
    
    /**
     * Always throws an SQLException. Username and password are set
     * with the setter methods and can not be changed.
     */
    public Connection getConnection(String username, String password) 
            throws SQLException {
        throw new SQLException("Not supported");
    }
    
    public int getLoginTimeout(  ) throws SQLException {
        throw new SQLException("Not supported");
    }
    ...
    /**
     * Create a Connection pool based on the configuration properties.
     */
    private void createConnectionPool(  ) throws SQLException {
  
        try {
            pool = new ConnectionPool(driverClassName, url, user,
                password, initialConnections);
        }
        catch (SQLException e) {
            throw e;
        }
        catch (Exception e) {
            SQLException sqle = 
                new SQLException("Error creating pool: " + 
                    e.getClass().getName() + " : " + e.getMessage(  ));
            throw sqle;
        }
    }
}

The DataSourceWrapper class implements the DataSource interface, so it can be used as a JDBC 2.0 connection pool implementation:

DataSource ds = null;
try {
    ds = new DataSourceWrapper(  );
    ds.setDriverClassName("org.gjt.mm.mysql.Driver");
    ds.setUrl("jdbc:mysql:///test");
    ds.setUser("scott");
    ds.setPassword("tiger");
}
catch (Exception e) {
    // Deal with it
}
  
Connection conn = ds.getConnection(  );

The getConnection( ) method creates an instance of the real connection pool class the first time it's called, using the JDBC driver, URL, user, and password information provided through the corresponding setter methods. The two most interesting methods are getConnection( ) and returnConnection( ).

The pool client application calls the getConnection( ) method, and the DataSourceWrapper relays the call to the connection pool class. It then wraps the Connection object it receives in a ConnectionWrapper object and returns it to the client application.

As described earlier, the ConnectionWrapper object calls the returnConnection( ) method when the pool client calls close( ) on the ConnectionWrapper object. The returnConnection( ) method hands over the Connection to the real connection pool so it can be recycled.

All other DataSource interface methods throw an SQLException in this implementation. If you modify the wrapper classes presented here to wrap a more sophisticated connection pool, you may be able to relay some of these method calls to the real connection pool instead.

The real beauty of the JDBC 2.0 connection pool interfaces is that the application doesn't have to be aware it's using a connection pool. All configuration data, such as which driver class and JDBC URL to use, the number of initial and maximum number of pooled connections, and the database account name and password, are set by a server administrator. The completely configured DataSource object is made available to the application, as described in the next section, and then any component can get, use, and return a Connection with code like this:

Connection conn = null;
try {
  conn = ds.getConnection(  );
  // Use the Connection
}
catch (SQLException e) {
  // Deal with it
}
finally {
  // Return the Connection to the pool
  if (conn != null)
    try {
      conn.close(  );
    }
    catch (SQLException e) {}
}

If the DataSource provides connection-pooling capabilities, the close( ) call returns the Connection to the pool; otherwise it's really closed. The application doesn't care; these details are in the hands of the server administrator, as they should be. As shown here, you should always use a try/catch/finally statement for all code that uses the Connection, and close it in the finally block to make sure it's closed no matter what happens.