Implement a JDBC Connection Pool via the Object Pool Pattern

By Wiebe deJong

Requirements vs. Constraints

A design pattern describes the requirements that a portion of code must fulfill in an ideal world. When implementing that pattern in the real world, however, constraints appear that may affect the basic design.

In part 1 of this article, I described a JDBC connection pool that was developed from a design pattern. One of the requirements for the JDBCConnection class was that it would encapsulate the database connection. The client would never have to work the connection and would simply get a result set back.

In the real world, as several readers mentioned, this will not work. Before the connection could be returned to the pool, the JDBC ResultSet would have to be copied to an object that could be passed to the client. This would result in performance problems, especially with large result sets.

In this article, I will enhance the connection pool with all the real-world constraints in mind. The result set will not be copied, resulting in increased performance; the connection itself will be passed to the client, resulting in increased versatility; the hard coding of the JDBC driver and connection information will be moved into a parameter file, increasing ease of use; and a way of shutting down the system will be devised.

To start, lets begin with a sample client application, which uses the JDBC Pool developed in this article.

Listing 1. testApp2.java

import java.sql.*;

public class testApp2 {
  
  public static void main() {
    try {
      // establish database connection
      JDBCConnection2 mydb = new JDBCConnection2("mySqlAnywhereDB");   
      // send SQL request to database
      mydb.sendRequest("select * from contact");
      // get result set
      ResultSet rs = mydb.getRs();
      // process result set
      while (rs.next())
        System.out.println(rs.getString("first_name"));
      // release resources
      mydb.closeRequest();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

Storing Parameters

The testApp2 application, shown in Listing 1, begins with the establishment of a database connection. The JDBCConnection2 constructor takes a connection pool identifier as its only parameter. This identifier is stored in the dbPool.properties file, as seen in Listing 2.

Listing 2. dbPool.properties

myOracleDB.driver=oracle.jdbc.driver.OracleDriver
myOracleDB.url=jdbc:oracle:oci8:@
myOracleDB.user=scott
myOracleDB.password=tiger
myOracleDB.maximum=20

mySqlAnywhereDB.driver=com.sybase.jdbc.SybDriver
mySqlAnywhereDB.url=jdbc:sybase:Tds:localhost:2638
mySqlAnywhereDB.user=dba
mySqlAnywhereDB.password=sql
mySqlAnywhereDB.maximum=5

The properties file can hold the parameters for any number of connection pools. The property names start with the connection pool identifier, which must be unique for a particular pool. For each pool, there is a property that describes the name of the JDBC driver class to be used, the URL of the database, the user name and password required to make a connection to that database, and the maximum number of connections allowed in this particular pool.

Performing Queries and Getting Results

Getting back to the example, the client applicationtestApp2 gets a shared connection to the database by creating a JDBCConnection2 object. Due to Lazy Instantiation, the actual database connection won't be created until it is about to be used.

The application then sends a request, in the form of a SQL string, to the database via the

sendRequest()

method.

Listing 3. JDBCConnection2.java

// JDBCConnection2.java

import java.sql.*;

public class JDBCConnection2 {
  
  // database instance name, such as mySqlAnywhereDB
  private String dbName;
  
  // the connection pool for that database instance
  private JDBCConnectionImpl2.JDBCPool2 connectionPool;
  
  // the connection 
  private JDBCConnectionImpl2 impl;
  
  // JDBC statement and resultset
  private Statement stmt;
  private ResultSet rs;
  
  // flag: is there an open ResultSet in use?
  private boolean inUse;
  
  // get connection to database pool
  public JDBCConnection2 (String dbName) {
    this.dbName = dbName;
    connectionPool = JDBCConnectionImpl2.JDBCPool2.getInstance();
    inUse = false; 
  }
  
  // send a request to the database
  void sendRequest (String sqlString)
  throws SQLException, ClassNotFoundException, PoolException {
    if (inUse)
      closeRequest();
    impl = connectionPool.acquireImpl(dbName);
    stmt = impl.getConnection().createStatement();
    rs = stmt.executeQuery(sqlString);
    inUse = true;
  }

  // return the result set of the request
  ResultSet getRs() {
    return rs;
  }
  
  // close request and return resources
  void closeRequest() throws SQLException {
    rs.close();
    stmt.close();
    connectionPool.releaseImpl(impl);
    inUse = false;
  }

  // free resources when object is destroyed
  protected void finalize() throws SQLException {
    if (inUse)
      closeRequest();
  }
  
}

The

sendRequest()

method, as seen in Listing 3, first checks to see if a connection is already in use. If so, it is closed.

Next, the actual database connection is acquired from the pool, the SQL statement is executed, and the resultSet is made available to the calling application. The application can then get a reference to the result set via the

getRs()

method and do whatever processing is required.

Since there is no copying of the result set to the client, the connection must stay open until the client is finished with it. When the result set processing is complete, a call to the

closeRequest()

method will free up the database resources and return the connection to the pool.

The inUse flag is used to determine whether the connection is in use or not. If the client makes another call to

sendRequest()

without first calling

closeRequest()

, the

closeRequest()

method will automatically be called, freeing up the resources of the previous request before making a new one.

When the

sendRequest()

method is called, it in turn requests a connection from the pool via the

acquireImpl()

method.

Listing 4. JDBCConnectionImpl2.java

// JDBCConnectionImpl2.java

import java.util.*;
import java.sql.*;
import java.io.*;

public class JDBCConnectionImpl2 {
  
  // database instance name, such as myOracleDB, mySqlAnywhereDB, myAccessDB
  private String dbName;

  private Connection conn; // the precious connection
  
  private JDBCConnectionImpl2 (String dbName, String dbUrl, String dbUser, String dbPwd) // private constructor
  throws SQLException {
    this.dbName = dbName;
    conn = DriverManager.getConnection (dbUrl, dbUser, dbPwd);
  }
  
  String getDatabaseName() {
    return dbName;
  }
    
  Connection getConnection() {
    return conn;
  }

  void close() throws SQLException {
    conn.close();
  }
  
  // free resources when object is destroyed
  protected void finalize() throws SQLException {
    close();
  }
    
  static class JDBCPool2 {
    
    // dictionary of database names with corresponding vector of connections
    private Hashtable poolDictionary = new Hashtable();
    
    // dictionary of database names with corresponding connection parameters
    private Hashtable parmsDictionary = new Hashtable();

    // methods and attributes for Singleton pattern
    private JDBCPool2() {} // private constructor
    private static JDBCPool2 _instance; // get class instance
    // Singleton getter utilizing Double Checked Locking pattern
    public static JDBCPool2 getInstance() {
      if (_instance == null) {
        synchronized(JDBCPool2.class) {
          if (_instance == null)
            _instance = new JDBCPool2();
        }
      }
      return _instance;
    }

    // get connection from pool
    public synchronized JDBCConnectionImpl2 acquireImpl (String dbName) 
    throws SQLException, ClassNotFoundException, PoolException {  

      // get connection parameters matching database name
      JDBCParms p = (JDBCParms)parmsDictionary.get(dbName);

      // first call to database
      if (p == null) {
        
        Properties props = new Properties();

        // read properties file
        try {
          props.load(new FileInputStream("dbPool.properties"));
        } catch (Exception e) {
          System.err.println("Can't read dbPool.properties");
          return (JDBCConnectionImpl2) null;
        }
        
        // process properties
        p = new JDBCParms();
        p.name = dbName;
        p.driver = props.getProperty(dbName + ".driver");
        
        if (p.driver == null)
          throw new PoolException("parameters not found for " + dbName);
        
        p.url = props.getProperty(dbName + ".url");
        p.user = props.getProperty(dbName + ".user");
        p.password = props.getProperty(dbName + ".password");
        String pMax = props.getProperty(dbName + ".maximum");
        p.maxConn = Integer.valueOf(pMax).intValue();
        p.curConn = 0;
        
        // load database driver        
        Class.forName (p.driver);
        
        // save parms
        parmsDictionary.put(dbName, p);
      }
    
      // get connection pool matching database name
      Vector pool = (Vector)poolDictionary.get(dbName);
      if (pool != null) {
        int size = pool.size();
        if (size > 0) {
          JDBCConnectionImpl2 impl = null;
          // retrieve existing unused connection 
          impl = (JDBCConnectionImpl2)pool.elementAt(size-1);
          // remove connection from pool
          pool.removeElementAt(size-1);
          // return connection
          return impl;
        }        
      } 

      // pool is empty so create new connection
      if (p.curConn < p.maxConn) {
        p.curConn++;
        return new JDBCConnectionImpl2(dbName, p.url, p.user, p.password);
      }
      
      // pool is empty and max connections reached
      throw new PoolException("max connections reached for " + dbName);
      // return;
    }
    
    // return connection to pool
    public synchronized void releaseImpl (JDBCConnectionImpl2 impl) {
      String dbName = impl.getDatabaseName();
      Vector pool = (Vector)poolDictionary.get(dbName);
      if (pool == null) {
        pool = new Vector();
        poolDictionary.put(dbName, pool);
      }
      pool.addElement(impl);
    }
    
    public JDBCParms getParms(String dbName) {
      return (JDBCParms)parmsDictionary.get(dbName);
    }
    
    public void close(String dbName) throws SQLException {
      // get connection pool matching database name
      Vector pool = (Vector)poolDictionary.get(dbName);
      if (pool != null) {
        int size = pool.size();
        if (size > 0) {
          JDBCConnectionImpl2 impl = null;
          // retrieve existing unused connection 
          impl = (JDBCConnectionImpl2)pool.elementAt(size-1);
          // remove connection from pool
          pool.removeElementAt(size-1);
          // close connection
          impl.close();
        }  
        JDBCParms p = (JDBCParms)parmsDictionary.get(dbName);
        p.curConn = 0;
      } 
    }
    
    public void close() throws SQLException {
      for (Enumeration e = parmsDictionary.elements(); e.hasMoreElements();) {
        JDBCParms p = (JDBCParms)e.nextElement();
        close(p.name);
      }
    }
    
    
  }
}

Keeping Count

The

acquireImpl()

method begins by determining the current status of the particular pool. The parmsDictionary contains not only the parameters required to make database connections but the connection counter as well. All of this information is stored in the parmsDictionary in a JDBCParms object, as seen in Listing 5.

Listing 5. JDBCParms.java

public class JDBCParms {
  String name; // pool identifier
  String driver; // JDBC driver class name
  String url; // JDBC database URL
  String user; // user name for the pool
  String password; // user name's password
  int maxConn; // maximum number of connections
  int curConn; // current number of connections in use
}

Pool Exceptions

If this is the first call to the particular pool, there will be no corresponding JDBCParms object, so a new one will have to be created. At this point,

acquireImpl()

will go out, load the parameters file, create the new parameter object and initialize the connection counter to 0. If the pool identifier supplied by the client is misspelled or not in the parameter file, a PoolException will be raised. An exception will also occur here if the JDBC driver class file cannot be found and loaded.

Checking the poolDictionary then creates a vector of the free connections. If there is a connection available, it is returned to the client. If there are no available connections, a new connection needs to be created.

Listing 6. PoolException.java

import java.lang.*;

public class PoolException extends Exception {
  
  // constructors
  public PoolException(){
    super();
  }
  public PoolException(String s){
    super(s);
  }
}

If the maximum number of connections has been reached, a PoolException is raised and returned to the client. If the maximum has not been reached, a new one is created using the parameters from the JDBCParms object.

When the client is finished with the connection, it is returned to the pool via the

releaseImpl()

method.

Performing a Shutdown

Another real-world constraint that wasn't part of the original design pattern is the need to be able to do a shutdown. There are two

close()

methods implemented by the JDBCPool2 class.

The first

JDBCPool2.close()

method takes the pool identifier as a parameter. The corresponding vector of free connections will be obtained and as each JDBCConnectionImpl2 object is removed, it is closed. The

close()

method in the JDBCConnectionImpl2 class is where the connection is actually released back to the database.

If all the pools need to be shutdown, there is a second

JDBCPool2.close()

method. This one iterates though the parmsDictionary, getting the parameters of each pool and closing all of its connections in turn.

Updates, Stored Procedures and Multiple Result Sets

The

JDBCConnection2.sendRequest()

method, in Listing 3, is designed for processing SQL Select statements which return result sets. If other types of database calls are going to be made, additional methods will need to be added to the JDBCConnection2 class.

A call to a stored procedure works the same but with the addition of parameters.

Listing 7.

JDBCConnection2.sendStoredRequest()

CallableStatement callStmt;

  void sendStoredRequest (String sqlString, String parm1)
  throws SQLException, ClassNotFoundException, PoolException {
    if (inUse)
      closeRequest();
    impl = connectionPool.acquireImpl(dbName);
    callStmt = impl.getConnection().prepareCall(sqlString);
    callStmt.setString(1, parm1);
    callStmt.registerOutParameter(2, java.sql.Types.INTEGER);
    rs = stmt.executeQuery();
    inUse = true;
  }

In this example, sqlString will contain the reference to the stored procedure and parm1 will be a parameter to that procedure.

If the query or procedure is performing an update, then the return value will be the number of rows processed.

Listing 8.

JDBCConnection2.sendUpdate()

int sendUpdate (String sqlString)
  throws SQLException, ClassNotFoundException, PoolException {
    if (inUse)
      closeRequest();
    impl = connectionPool.acquireImpl(dbName);
    stmt = impl.getConnection().createStatement();
    int rows = stmt.executeUpdate(sqlString);
    inUse = true;
    return rows;
  }

Here, instead of calling

executeQuery()

, a call to

executeUpdate()

is being made. The return value is the number of rows updated, and this value is returned to the client.

A stored procedure doing an update would be implemented in a similar fashion, replacing the

executeQuery()

call with

executeUpdate()

Multiple result sets can be implemented by again changing the execute calls. In each

send...()

method, change the execute statement with the following two lines of code. A

getNextRs()

method will need to be added in order to work with the additional result sets.

Listing 9. JDBCConnection2.getNextRs()

    stmt.execute(); // return multiple result sets
    rs = stmt.getResultSet(); // get first result set

  ResultSet getNextRs() {
    rs = stmt.getMoreResults(); // get next result set
    return rs;
  }

The

getNextRs()

method would be called by the client application as needed.

Conclusion

The JDBC connection pool shown here is now ready for use. The original pool was developed in the first half of this article to requirements put forth in a design pattern. Real-world shortcomings of the pool were addressed in this follow-up article by considering constraints. This is a classic example of how requirements may change when implementation begins.

About the Author

With over thirteen years' experience in application development, Wiebe de Jong is a Web developer for IMRglobal Ltd. in Vancouver, BC, Canada. He develops Internet and intranet applications for clients using Java, UML, and XML. He also teaches.

This article was originally published on May 9, 2000

IT Solutions Builder TOP IT RESOURCES TO MOVE YOUR BUSINESS FORWARD