Connection pooling is the process of maintaining a pool of connection and reusing them each time some statement is to be executed in the database. It is a general concept which can be applied in any programming language, but here we will implement it in Java programming language.

Design:
A class will maintain all the connections in a vector (will be explained why vector only). Only one instance of this class will be available to other classes and is achieved by implementing the singleton pattern. This particular class will maintain the vector.

A class which wants a connection to database will get the instance of this class and then get the vector member object. From this vector, it will get one connection by calling the getPoolConn() method. After using the object, the object will be returned to pool by calling returnPoolConn() method.

We choose vector (not Arraylist) for storing the connections because a vector is threadsafe which in our case makes sure that two requester classes don’t get the same connection object.

The complete implementation has been attached along with this post.

e.g.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Vector;


 public class ConnPool {
	 static private ConnPool staticInstance;
	 private Vector allConnections;
	 private String dbDriverName = "";
	 private String dbURL = null;
	 private String dbUserName = null;
	 private String dbPassword = null;
	 private int maxConnections = 10;

	 static synchronized public ConnPool getInstance() {
		   if (staticInstance == null) {
			   staticInstance = new ConnPool();
			}
		   return staticInstance;
	  }

	  private ConnPool() {
		  Connection tempConnection;
		  try {
			   Class.forName(dbDriverName ).newInstance();
			   allConnections = new Vector (maxConnections);
			   for ( int i = 0; i < maxConnections; i++ ) {
				   tempConnection = DriverManager.getConnection(dbURL, dbUserName, dbPassword);
				   allConnections.addElement(tempConnection);
			   }
		  } catch (Exception setupException) {
			   System.err.println("Unable to setup JDBC connection pool.");
		  }
	  }

 public Connection getPoolConn() {
		   Connection tempConnection = null;
		   if (allConnections.size() > 0) {
			   tempConnection = (Connection)allConnections.firstElement();
			   allConnections.removeElementAt(0);
			   try {
				   if (tempConnection.isClosed()) {
					   tempConnection = getPoolConn();
				   }
				   else {
					   tempConnection.getMetaData().getTableTypes();
				   }
			   }
			   catch (SQLException sqlE) {
				   tempConnection = getPoolConn();
			   }
		   }
		   return tempConnection;
 }

 public void returnPoolConn(Connection checkedInConn) {
		   if ( checkedInConn != null ) {
			   try {
				   if (!checkedInConn.isClosed()) {
					   allConnections.addElement(checkedInConn);
				   }
			   }
			   catch (SQLException sqlE) {
				 ; // Do nothing. Bad connection checked in.
			   }
		   }
 }
}