Implement Java Connection Pooling with JDBC

By Sridhar M S

A Task Breakdown of the Database Connection Pool Code

The following are the major tasks involved in the Listing 1 code.

Initialize the Connection Pool

When the application starts, the connection pool gets initialized. However, that may not be ideal for a given scenario. You may not want to start creating all the connections in a single stretch, which is time consuming. A better approach may be to execute the initialization as a background task or only when you require it. You also may create just one connection and keep it ready, if you choose. In cases where you have multiple requests from clients, you can create and serve the required new connection objects. This is similar to the lazy loading concept.

Populating the Pool

If the connection pool is full, you do not proceed with populating it. Populating the pool can be invoked at any stage—even after partial population. So, you can do some other high-priority tasks and return to populating the pool at a later time.

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

As always, things can keep improving as new things evolve day by day. Something that is the best-known mechanism today may be overridden tomorrow with a new mechanism. You can enhance this example by storing all the configuration information (the database-related details such as the URL, user name, password, port number, etc.) in a properties file and load them during application startup.

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

The database connections created in the example code are not closed after every use; they are returned to the connection pool, where they are maintained. This code does not create a connection when required. It pre-populates instead.

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

Things to Remember

Keep the MAX_POOL_SIZE value to an optimized size. Simply setting this value very high (say, 100) will not necessarily eliminate all issues and insure that all database requests will be served. Every database connection is a resource-intensive object; maintaining and creating even hundreds of connections could literally kill your application by consuming massive amounts of memory.

There is no optimal number for the MAX_POOL_SIZE. You have to determine that value experimentally; it may vary depending on hardware and software combinations.

Databases behave differently when more connections are active.

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.