Need Code to create Connection Pool in java

Question

Need code to create the connection pool in java? How does we make sure that connection pool doesn't return the same object which is already in use? How happens if client closed the connection after taking it out from Connection pool?

Update 1:

I want to create this in Simple Java terms and want to see how it works in Multithreading Env. I mean which methods would be synchronized and which are not. Also will this class would be a public class? If yes then any one can access this class and reinitialize the connection pool?

Update 2:

I have some code as below. But i don't how "Closing a connection coming from a pool returns it to the pool, it doesn't close the connection physically." Also i didn't understood this "Because if a connection has been borrowed from the pool and not returned yet, it's not "available" and can't be redistributed to another client of the pool."

import java.util.*;
import java.sql.*;

class ConnectionPoolManager
{

 String databaseUrl = "jdbc:mysql://localhost:3306/myDatabase";
 String userName = "userName";
 String password = "userPass";

 Vector connectionPool = new Vector();

 public ConnectionPoolManager()
 {
  initialize();
 }

 public ConnectionPoolManager(
  //String databaseName,
  String databaseUrl,
  String userName,
  String password
  )
 {
  this.databaseUrl = databaseUrl;
  this.userName = userName;
  this.password = password;
  initialize();
 }

 private void initialize()
 {
  //Here we can initialize all the information that we need
  initializeConnectionPool();
 }

 private void initializeConnectionPool()
 {
  while(!checkIfConnectionPoolIsFull())
  {
   System.out.println("Connection Pool is NOT full. Proceeding with adding new connections");
   //Adding new connection instance until the pool is full
   connectionPool.addElement(createNewConnectionForPool());
  }
  System.out.println("Connection Pool is full.");
 }

 private synchronized boolean checkIfConnectionPoolIsFull()
 {
  final int MAX_POOL_SIZE = 5;

  //Check if the pool size
  if(connectionPool.size() < 5)
  {
   return false;
  }

  return true;
 }

 //Creating a connection
 private Connection createNewConnectionForPool()
 {
  Connection connection = null;

  try
  {
   Class.forName("com.mysql.jdbc.Driver");
   connection = DriverManager.getConnection(databaseUrl, userName, password);
   System.out.println("Connection: "+connection);
  }
  catch(SQLException sqle)
  {
   System.err.println("SQLException: "+sqle);
   return null;
  }
  catch(ClassNotFoundException cnfe)
  {
   System.err.println("ClassNotFoundException: "+cnfe);
   return null;
  }

  return connection;
 }

 public synchronized Connection getConnectionFromPool()
 {
  Connection connection = null;

  //Check if there is a connection available. There are times when all the connections in the pool may be used up
  if(connectionPool.size() > 0)
  {
   connection = (Connection) connectionPool.firstElement();
   connectionPool.removeElementAt(0);
  }
  //Giving away the connection from the connection pool
  return connection;
 }

 public synchronized void returnConnectionToPool(Connection connection)
 {
  //Adding the connection from the client back to the connection pool
  connectionPool.addElement(connection);
 }

 public static void main(String args[])
 {
  ConnectionPoolManager ConnectionPoolManager = new ConnectionPoolManager();
 }

}

Answer 1

Use one of the existing, e.g. Apache DBCP

The connections returned by the pool are often proxies which "ignore" the call to close() from the application. When the connections are returned to the pool, they can be reused. Pools also close and reopen automatically if necessary.

Answer 2

Need code to create the connection pool in java?

Not sure what the question is but don't create yet another connection pool, use an existing solution like C3P0, Apache DBCP, Proxool or BoneCP (a new player in that field). I would use C3P0.

How does we make sure that connection pool doesn't return the same object which is already in use?

Because if a connection has been borrowed from the pool and not returned yet, it's just not in the pool and can't be assigned to another client of the pool (resources are removed from the pool until they are returned).

How happens if client closed the connection after taking it out from Connection pool?

The connection a client gets from a pool is not really a java.sql.Connection, it's a wrapper (a proxy) for a java.sql.Connection that customizes the behavior of some methods. The close() method is one of them and does not close the Connection instance but returns it to the pool.

Answer 3

If your application runs on a server, then configure as Data Source, where server will take care of Pooling or else if a simple Java Client then use Apache DBCP(if to the database) or else use Apache Commons Pooling API See here: Apache Commons

Answer 4

Don't write your own. There are plenty of librarires out there that will do this for you that are open source and easy to use and will have solved all the problems you'll encounter trying to make it yourself.

Here is a simple example that uses Apache's Commons DBCP and Commons Pool:

First set up a DataSource.

javax.sql.DataSource source = new org.apache.commons.dbcp.BasicDataSource();
source.setDriverClassName("com.mysql.jdbc.Driver");
source.setUsername("username");
source.setPassword("password");
source.setUrl("jdbc:mysql://localhost:3306/myDatabase");

Once you have a DataSource, it is easy to get a connection from the pool.

java.sql.Connection connection = source.getConnection();

closing the connection will return it to the pool for you.

connection.close();