Gain extended functionality by writing your own JDBC connection pool

Using a connection pool can make database access much more efficient. In this article, you won't just learn more about connection pools; you'll actually make one. Follow along, gain some insights, and get a working application as a bonus.

A recent Java tip recommended that developers use a JDBC connection pool to optimize connections to database resources. In this article, you will be able to see the actual implementation of a connection pool and have a fully functional class to use in future projects. We will also discuss future modifications you could make to the code.

Reasons for writing your own connection pool
There are many connection pool implementations you can download and use in your projects, and many JDBC drivers include a connection pooling facility. So why should you write your own? For one thing, a free or purchased product won't always include the functionality you need. For another, writing and maintaining your own connection pool is a learning experience you won’t get unless you do it yourself. Our sample code offers some functionality that is not typical of most connection pools, and you'll gain the benefit of seeing how a connection pool works.

Design considerations
To create an efficient connection pool, we must first discuss a few key design concepts. The connection pool must be accessible to multiple processes at the same time yet have only one set of connections. To accomplish this, the pool code will implement a Singleton pattern. A Singleton pattern ensures that there is only one instance of a class and provides a single point of access to that instance.

Having one instance of the pool seems like a good idea, but it can create issues with concurrent access. Therefore, the connection pool must have a method to control concurrent access to the pool to prevent issues with multiple threads. There should also be an easy way to manage the pool, although for the sake of simplicity, much of the code for managing connection pools has been left out of this implementation.

More about Singleton patterns
There are many uses for Singletons in almost every programming project. The definitive reference on patterns is Design Patterns: Elements of Reusable Object Oriented Software, by Erich Gamma, John Vlissides, Ralph Johnson, and Richard Helm. These authors are often referred to as the “gang of four.” Another great book covering this material but with Java-specific implementations is Java Design Patterns, by James W. Cooper.

Environment and setup
To use connection pooling for JDBC, you will need a JDBC driver and a database. For this code, I tested using two free JDBC drivers, which you can download as well:

Both of these drivers supply you with JAR files you can use with this project. The JAR files are named Mm.mysql-2.0.7-bin.jar, Mssqlserver.jar, Msbase.jar, and Msutil.jar. Since both drivers are free, I've included the JAR files in the TechRepublic download. But when you download the programs, be sure to check all the license agreements so that you don’t run into any licensing issues.

For your platform, you can choose either Linux or Windows 2000. I have tested the code on Redhat Linux 7.1 and Windows 2000 Workstation. For both platforms, I tested the MySQL and MSSQL 2000 connections. I used JDK 1.3.1, which is available from Sun.

Enough setup, get to the code!
Finally, it is time to look at the code. I have removed many pieces of functionality to keep this code as small as possible, but there is still quite a bit to cover. I am going to go over the code rather quickly since there are already many comments in the code that explain the various implementation pieces.

Listing A shows the properties file used to configure the connection pool. (Note that this file, as well as the other listings we discuss here, are part of the TechRepublic download I mentioned earlier.) This file lists the driver name, database URL string, database user, and password. By default, the database is set to the localhost, so you may want to change these values to suit your environment. Since this implementation of the connection pool supports only a single pool at a time, the MS SQL pool is currently commented out (pound sign [#] at the beginning of the line). If you want to use the MS SQL example, simply comment out the MySQL lines and uncomment the MS SQL lines. The elements in the properties file will be referenced by the connection pool code in Listing B.

The first significant line of code in this class is the declaration of the private static variable called mInstance. This variable is the same type as the class and will hold the reference to the single instance of the ConnectionPool class that is used by all threads within the same Java virtual machine. Just after mInstance is a private variable of type Vector called mConnections. This Vector will hold each of the connections to the database we establish as part of our pool. Since the Vector is already thread safe, it takes care of the majority of the work involved in checking out and checking in connections. The remaining variables match to the settings in the property file and are used to store the information and the driver.

The getInstance method is static so that calling programs can use it without instantiating a ConnectionPool class first. The getInstance method checks the internal mInstance variable to make sure that it has not already created a ConnectionPool object. If the object has already been created, that instance is returned. If the object has not been created, a new ConnectionPool is created, and the reference is set to the mInstance variable before returning to the calling code.

Creating the pool
The constructor for the ConnectionPool class is private so that code outside of the ConnectionPool cannot directly instantiate the class. The private constructor is necessary to complete the implementation of the Singleton pattern. The rest of constructor code performs most of the work necessary to set up the connection pool. First, the properties are loaded from the property file. They can then be easily accessed through the getProperty method of the Properties object.

Once the properties have been loaded, the code enters a try block to catch any unexpected errors. The lines shown below take the driver name and create a new Driver object that is stored in the private variable mDBDriver. The driver is then registered with the JDBC DriverManager using the registerDriver method.
String dbDriverName = poolProperties.getProperty("dbDriver");
mDBDriver = (Driver)Class.forName(dbDriverName).newInstance();

Once the driver is registered, the remaining connection pool properties are read from the Property object and stored in local private variables. The numberOfConnections property is then used to control a for loop, as shown here:
for ( int i = 0; i < mNumberOfConnections; i++ ) {
   tempConnection = DriverManager.getConnection(mDBURL, mDBUserName,

The for loop uses the DriverManager to get a new connection and store that connection in the Vector called mConnections. At this point, the connection pool is established and waiting for the first request to check out a connection. The remainder of the code in the constructor is for error handling.

Checking in and returning connections
Once the ConnectionPool instance is obtained, the user’s code can get a connection from the pool by calling the checkOut method. This method contains the enhancement mentioned earlier, which you won’t find in many connection pools. The listing for the checkOut method is here for reference:
Connection dbConnection = null;
if ( mConnections.size() > 0 ) {
   dbConnection = (Connection)mConnections.firstElement();
   try {
      if (dbConnection.isClosed()) {
         dbConnection = checkOut();
      else {
   catch (SQLException sqlE) {
      dbConnection = checkOut();
return dbConnection;

First, a Connection object called dbConnection is declared and set to null. Next, the current size of the pool is checked. If the pool size indicates that the pool is empty, no further action is taken and a null is returned. This would be a good place to throw an exception instead of returning null. (In a future article, I’ll discuss exceptions and explain how to create your own for uses such as the null connection.)

Now, the method firstElement of the Vector is called to retrieve the first connection in the pool. Since connections are added back to the end of the Vector, we get a round-robin effect by always grabbing the first element from the Vector. That element is then removed from the pool with the removeElementAt(0) method call.

Once the connection is retrieved from the pool, it could be returned to the calling process without any further processing. However, this connection pool goes a few steps further by first validating the connection. The isClosed method checks to see whether the connection has been closed. If it has, it is discarded by making a recursive call to the checkOut method. If the connection has not been closed, the getMetaData().getTableTypes method is used to interact with the database and test the connection.

I added this code so that a connection can be properly validated before returning it from the pool. In the full implementation of this object, new connections are established in case the existing connections fail to connect. I ran into this problem if the database was disconnected from the application server for even a brief time. Once the disconnection occurred, the connection objects in my pool did not register as closed, but they would fail once you tried to use them. With this getMetaData().getTableTypes call, the connection pool validates the connection before returning it to the calling code.

It does introduce some overhead, but it beats writing retry code in every process that gets a connection or restarting the application just because a switch failed for a few seconds or a database server process is restarted. You could have a JDBC driver that does not support this metadata call, in which case you could substitute a query or just remove the validation code. A catch block takes care of failures and once again uses a recursive call to attempt to get another connection. The number of connections in the pool limits the recursion.

Checking in a connection is by far the easiest method. It does verify that the connection is not null and that it is not closed before adding it back to the Vector. The addElement method places the connection back into the pool once the verification checks are made.

Shutting down the connection pool is a good step for any code that uses this object. It is not absolutely required, since the stopping of the Java virtual machine will cause all the connections to close and deregister the driver. However, it is good code practice to clean up the mess. The shutdown method takes care of closing all the pooled connections and deregistering the driver. In addition, it destroys the internal reference so that the ConnectionPool object no longer has a reference to itself. Be careful that your code is written in such a way that only one process is calling the shutdown method. If every routine called shutdown after checking in a connection, the effects of connection pooling would be lost and actually reversed by re-creating this object every time it is used.

I have included another class for testing the pool, TestPool, shown in Listing C. This class provides a simple way to test your connection pool. The download includes two scripts that start the TestPool class. The TestPool class gets a reference to the ConnectionPool object and then uses its property file to figure out how many connections should be available. The test then checks out each connection and uses it to run a query. It does not return the connections until all of them have been checked out, and it verifies that no more connections are available. It then checks all the connections back into the pool and cleans up by shutting down the ConnectionPool object.

This connection pool has the basic functionality necessary to make it useful in your programs. But like any project, there is still more work you could do to make it more robust. The pool management has been stripped out to keep this code as small as possible. In my implementation, the property file allows you to specify a number of different JDBC drivers simultaneously and have multiple connection pools. This can be put into the code by storing a Hashtable of connection pools and referencing the pool in the checkout and checkin methods. Each pool can be an object that stores a number of connections. I will leave the rest of the implementation to you.

In addition, the connection pool currently has a flaw that could be a problem if your code is not well behaved. The flaw lies in the fact that the connection pool does not track the number of connections checked out and the number it is supposed to have in the pool. If the code tracked the connections, it could dynamically establish a new connection any time one was requested and there was still availability in the pool. This would also allow the pool to replenish connections that are removed because they have been closed or are no longer valid because the database is not available.

Currently, the connections are removed but not replenished, so it is possible for the pool to become empty and no longer supply connections. If you want to use this with a servlet engine such as Tomcat, you may want to modify the code so that instead of a getInstance() method, the class exposes a normal public constructor. That way, you can specify the scope of the object in your include tag and let Tomcat control the number of instances that are created. Specifying application scope will prevent multiple copies of the object from being created.

Another nice addition would be tracking of metrics, such as number of connections checked out, running time, and total number of connections. I use a metric management class to track statistics like these, which can then be logged on a routine basis so that accurate metrics can be evaluated against running applications. I’ll cover some of these techniques in future articles.

I am sure you can think of more modifications. As you do come up with ideas or fixes, please share them here in the discussions for this article.


Editor's Picks