http://www.developer.com/

Back to article

Implement Java Connection Pooling with JDBC


November 11, 2009

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

At any given time, a connection object is involved in one of the following major steps of its lifecycle:
  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

The following are the major steps involved in accessing a connection object from a 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

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.

    Code Download

  • ConnexPooling_src.zip

    For Further Reading

  • "Chapter 8 Continued: Connection Pooling" (from Sun Developer Network)

    About the Author

    Sridhar M S is a Java developer from Bangalore, India. He holds a master's degree in Computer Science.

  • Sitemap | Contact Us

    Thanks for your registration, follow us on our social networks to keep up-to-date