Friday, December 18, 2009

User Defined Connection Pool in Java

Dear Friends,
Today I am writing an example on how to write Manual Connection Pooling in Java. Developers Use "DataSource",
"BasicDataSource" from Apache for Connection Pool. In Hibernate we have "c3p0". However the logic is same. A
connection pool is a reservoir of unused connections which are created in bulk whenever application starts or 
on requirement.

Since making DB connection is an expensive process, developers create connections initially and keep in pool.
However once you used the connections, it must be returned to the pool so that other threads can use them. Here 
is the complete example:

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

class ConnectionPoolManager {
    static String databaseUrl = "jdbc:mysql://localhost:3306/eguard"; //This can be taken from Properties file.
    static String userName = "root";
    static String password = "root";
    static final int MAX_POOL_SIZE = 2;
    static Vector<Connection> connectionPool = new Vector<Connection>();
    private ConnectionPoolManager(){
        //Can't be sub-classed.
    public static void main(String args[]) {
    private static void initialize() {

    private static void initializeConnectionPool() {
        while(!checkIfConnectionPoolIsFull()) {
            System.out.println("Connection Pool is NOT full. Adding new Connections to Connection Pool.");
            //Connections will be created till the pool is full, idle connectionPool configuration is not added.
        System.out.println("Connection Pool is full now.");
        for(int i=0;i<MAX_POOL_SIZE+2;i++){
            Connection conn=getConnection();
            //returnConnection(conn);  //Uncomment this line, you will never be out of connections.

    private static synchronized boolean checkIfConnectionPoolIsFull() {
        //Check if the pool size
        if(connectionPool.size() < MAX_POOL_SIZE) { 
            return false; 
        return true; 
    private static Connection addToConnectionPool() { 
        Connection connection = null; 
        try { 
            Class.forName("com.mysql.jdbc.Driver");  //TODO: Load class only once, remove here and put before while loop. 
            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 static Connection getConnection() { 
        Connection connection = null; //Check if there is a connection available. 
        synchronized(connectionPool) {
            if(connectionPool.size() > 0) {
                connection = (Connection) connectionPool.firstElement();
        //Giving away the connection from the connection pool
        return connection;

    public static synchronized void returnConnection(Connection connection) {
        //Adding the connection from the client back to the connection pool
    public Object clone() throws CloneNotSupportedException {
        throw new CloneNotSupportedException("Cloning of ConnectionPoolManager class is not permitted.");  

Connection Pool is NOT full. Adding new Connections to Connection Pool.
Connection Pool is NOT full. Adding new Connections to Connection Pool.
Connection Pool is full now.


For more details check this link too:

No comments:

Post a Comment