Implement Java Connection Pooling with JDBC
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 stageeven after partial population. So, you can do some other high-priority tasks and return to populating the pool at a later time.
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
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
For Further Reading
About the Author
Sridhar M S is a Java developer from Bangalore, India. He holds a master's degree in Computer Science.Page 2 of 2