Implement Java Connection Pooling with JDBC

Java developers often need to use objects that require a lot of resources to create and destroy. Many times, they find that they need to reuse an object that they created and destroyed only a little while before. As a result, many choose to create objects once and then hold them in memory indefinitely. In some applications, having only one object in memory will not suffice, and you will need to create more. A typical project scenario is to create a number of objects of similar type and use them across an application in different contexts, maybe simultaneously. Holding many objects together like this is known as pooling and the collection of objects itself is called a pool.

A pool is a logical holder of many instances of an object. Because pools are kept completely initialized and ready to use, you effectively can grab an object from one, use the object to get your work/task executed, and then return the object to the pool. Two of the most common objects that are held in pools are database connections and threads. This article discusses database connection pooling in detail.

Anatomy of a Connection Object Pool

1. Creation
2. Initialization
3. Ready for use
4. Destruction
5. Garbage collection

All but the third step are resource intensive; they require a lot of memory and processing time. Therefore, if creating and holding an object in memory requires fewer resources than creating and destroying it, holding the object in memory and continuing to use it when needed is a better option.

Creating a Database Connection Pool

1. Request a connection from the pool.
2. Perform the required database operations.
3. Return the connection to the pool.

In addition to the above, the connection pool manager has to do the following:

• Create the connection pool.
• Create the database connections and add them to the pool.
• Keep track of how many of them are being used.
• When all the elements in the pool are used up, return the appropriate information to the users.
• Allocate connections to the clients when the client requests them.
• De-allocate and put the connections back in the pool when the client returns them.
• Destroy the connection pool when the application is being shutdown.

To fully understand this process, you need to know the fundamentals of JDBC programming. The basis of creating and maintaining a connection are derived from JDBC. Listing 1 shows the code for creating a database connection pool.

The concept illustrated in Listing 1 is to use a vector as a placeholder for all the connection objects created. The maximum number of connections in the pool is defined by the MAX_POOL_SIZE attribute (By default this has a value of 5). The value can be adjusted to suit the application under consideration (i.e., it can vary between applications).

A Task Breakdown of the Database Connection Pool Code

Initialize the Connection Pool

Populating the Pool

Two synchronized methods handle client requests for connection objects. One serves the client with a connection object (to help perform database activities) from the connection pool; the second repopulates the connection pool with the connection object that is returned from the client.

When the client requests a connection object, you can make additional checks to validate the connection object (for connection timeouts, etc.). When the application receives a shutdown request, you can also use a method to destroy all the connections in the pool. You can achieve this by iterating through the connection pool vector and closing the connections one by one.

Optimization

Having all the parameter values be configurable makes it easy to control deployment with any database. Changing the values to those of the required database requires absolutely no code changes (provided your database is JDBC compliant and your code has no native calls). It is essential to follow JDBC recommendations to achieve portability between databases, which is almost a reality today.

Connection Maintenance

You can also explore the data source in the javax.sql package, which has advanced functionalities.

Things to Remember

This is a conceptual discussion that you can be apply to any environment. You can implement this as part of a client/server application, a web-based Servlet implementation, etc. Learn how your application server implements its database connection pool configuration, and then try mapping every configuration attribute provided there to the discussion here.

Code Download