Java Connection Pool - Design and Sample Implementation

[former_member202202]

Java Connection Pool - Design and Sample Implementation

                                                                                                    -  Aavishkar Bharara

Introduction

In the world of application designing, we often encounter the need to fetch, update or delete objects stored in the database. To enable this, we would require a database connection object. Opening and closing the database connection object can be very costly as it involves, creating the network connection to the database followed by doing the authentication using the username and password. Hence, database connection is a scarce resource and need to be utilized efficiently and effectively.

Connection Pooling

The connection pooling helps to utilize and efficiently re-use the database connection in a multithreading environment. The idea is to share the database connections (limited connections) with the users (unlimited numbers) in the most efficient way.

Design

Constraints

There are multiple design options for creating an efficient and re-usable connection pool application programming interface.

The design constraints include:-

• A singleton "Connection Pool" class
• An option to "request" for the connection from the connection pool.
• An option to "release" the connection to the connection pool.

Apart from these core design constraints, there are some administration requirements as well, which includes:-

• Tracking of "who" and "when" was the connection requested. This helps to track "connection leaks" in the code and take corrective steps accordingly.
• Tracking of "lost" connections or "idle" connections which were not returned back to the connection pool and "claim" them back to the connection pool.

Design Options

These requirements are very similar to many frameworks already available in the market.Microsoft COM/DCOM provides a common interface called as IKNOWN interface to accomplish the similar set of requirements.  ...see link for more details

Java has the similar requirements for the "Garbage Collector" usage to free up unused java objects....see link for more details

Implementation

Interface IConnectionPool

An interface IConnectionPool provides the basic methods of the connection pool class.

Interface : IConnectionPoolAdmin

This provides the administrative functions for the connection pool class.

Connection Container Class

The implementation starts with creating a container for the "connection object" first.

This connection container class stores the attributes of the connection like,

• Connection is in use or idle
• Who and when accessed the connection

Connection Class

The connection class implements the IConnectionPool interface to provide the basic features of connection pool. It also implements the IConnectionPoolAdmin to provide the administrative functions to the connection pool implementation.

        public class ConnectionPool implements IConnectionPool, IConnectionPoolAdmin {

This implements the "singleton" feature in the Connnection via the "getInstance()" function. The function getConnection() has the complete logic built to get "idle connection" and set the required attributes to the connection object so that it is "marked" as currently "in-use".

A Sample Implemented Code

Attached is the complete implemented code using the jar file AviConPool.jar which you can include in your project and start re-using it. The following set of methods needs to be called to utilize this jar file.

First (and only once) Call should be,

      ConnectionPool.getInstance().setup(
                    String DBDriver, String DBURL, String username, String password, int maxConnections);

This needs to be called from the application central component for once.


Subsequently, the calls to the connection pool would be something like this,

public void functionABC

{

  Connection con = ConnectionPool.getInstance().getConnection();

  try {

  <<< Write Your Code Here >>>

                        Statement stmt = con.createStatement();

  ResultSet rs = stmt

  .executeQuery("select ......");

  if (rs.next()) {

  // Read Data

  }

  // Help GC

  rs.close();

  stmt.close();

  rs = null;

  stmt = null;

  } catch (Exception ex) {

  // Log Errors

  }

  finally

  {

  ConnectionPool.getInstance().releaseConnection(con);

  }

}

Also, attaching the jar file [ConPool.txt which can be renamed to ConPool.jar] which you can use in building your applications.