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 PoolAt any given time, a connection object is involved in one of the following major steps of its lifecycle:
- Ready for use
- 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 PoolThe following are the major steps involved in accessing a connection object from a connection pool:
- Request a connection from the pool.
- Perform the required database operations.
- 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).