updated jdbc connection pool implementation using hashtable instead of arraylist

package JoeyUtilities;

import java.sql.*;

public class JoeyConnection {
    private Connection connection;
    private Boolean isInUse;
    private String memoryAddress;

    public JoeyConnection(Connection connection) {
        this.connection = connection;
        this.isInUse = true;// should be used immediately when it is created
        this.memoryAddress = this.connection.toString();
    }

    /**
     * @return the connection
     */
    public Connection getConnection() {
        return connection;
    }

    /**
     * @param connection
     *            the connection to set
     */
    public void setConnection(Connection connection) {
        this.connection = connection;
    }

    /**
     * @return the isInUse
     */
    public Boolean getIsInUse() {
        return isInUse;
    }

    /**
     * @param isInUse
     *            the isInUse to set
     */
    public void setIsInUse(Boolean isInUse) {
        this.isInUse = isInUse;
    }

    /**
     * @return the memoryAddress
     */
    public String getMemoryAddress() {
        return memoryAddress;
    }

    /**
     * @param memoryAddress
     *            the memoryAddress to set
     */
    public void setMemoryAddress(String memoryAddress) {
        this.memoryAddress = memoryAddress;
    }
}

 

 

package JoeyUtilities;

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

public class JoeyConnectionDealer {
    private Hashtable<String, JoeyConnection> pool;
    private String driver;
    private String server;
    private String user;
    private String password;
    private JoeyConnectionCleaner jcc;

    public JoeyConnectionDealer(Hashtable<String, JoeyConnection> pool,
            String driver, String server, String user, String password,
            JoeyConnectionCleaner jcc) {
        this.pool = pool;
        this.driver = driver;
        this.server = server;
        this.user = user;
        this.password = password;
        this.jcc = jcc;
    }

    private Connection createConnection() {
        try {
            Class.forName(driver).newInstance();
            Connection connection = DriverManager.getConnection(server, user,
                    password);
            JoeyConnection jc = new JoeyConnection(connection);
            synchronized (pool) {
                this.pool.put(jc.getMemoryAddress(), jc);
                this.jcc.increaseConnectionsInUse();
            }
            return connection;
        } catch (Exception e) {
            return null;
        }
    }

    public Connection getConnection() {
        Connection connection = null;
        synchronized (pool) {
            ArrayList<String> brokenKeys = new ArrayList<String>();
            connection = check(pool, brokenKeys);
            for (String key : brokenKeys) {
                JoeyConnection jc = pool.get(key);
                try {
                    jc.getConnection().close();
                } catch (SQLException sqle) {
                }
                pool.remove(key);
            }
        }
        if (connection == null) {
            return createConnection();
        } else {
            return connection;
        }
    }

    private Connection check(Hashtable<String, JoeyConnection> pool,
            ArrayList<String> brokenKeys) {
        Enumeration<String> keys = pool.keys();
        while (keys.hasMoreElements()) {
            String key = keys.nextElement();
            JoeyConnection jc = pool.get(key);
            if (!jc.getIsInUse()) {
                try {
                    jc.getConnection().createStatement();
                    if (jc.getConnection().isClosed()) {
                        throw new SQLException();
                    }
                    jc.setIsInUse(true);
                    this.jcc.increaseConnectionsInUse();
                    return jc.getConnection();
                } catch (Exception e) {
                    brokenKeys.add(key);
                }
            }
        }
        return null;
    }

    public void returnConnection(Connection connection) {
        synchronized (pool) {
            JoeyConnection jc = pool.get(connection.toString());
            if (jc != null) {
                try {
                    connection.createStatement();
                    if (connection.isClosed()) {
                        throw new SQLException();
                    }
                    jc.setIsInUse(false);
                } catch (Exception e) {
                    try {
                        connection.close();
                    } catch (SQLException e1) {
                    }
                    pool.remove(jc);
                }
                this.jcc.decreaseConnectionsInUse();
            }
        }
    }
}

 

 

 

package JoeyUtilities;

import java.util.*;

public class JoeyConnectionCleaner implements Runnable {
    private Hashtable<String, JoeyConnection> pool;
    private int connectionsInUse;

    public JoeyConnectionCleaner(Hashtable<String, JoeyConnection> pool) {
        this.pool = pool;
    }

    public void increaseConnectionsInUse() {
        this.connectionsInUse++;
    }

    public void decreaseConnectionsInUse() {
        this.connectionsInUse--;
    }

    public void run() {
        try {
            while (true) {
                synchronized (pool) {
                    if (pool.size() > 10) {
                        if (connectionsInUse * 100 / pool.size() < 66) {
                            Enumeration<String> connections = pool.keys();
                            String key = null;
                            JoeyConnection jc = null;
                            Boolean isRemovable = false;
                            while (connections.hasMoreElements()) {
                                key = connections.nextElement();
                                jc = pool.get(key);
                                if (!jc.getIsInUse()) {
                                    isRemovable = true;
                                    break;
                                }
                            }
                            if (isRemovable) {
                                try {
                                    jc.getConnection().close();
                                } catch (Exception e) {
                                }
                                pool.remove(key);
                            }
                        }
                    }
                }
                Thread.sleep(20000);
            }
        } catch (Exception e) {
        }
    }
}

 

 

package JoeyUtilities;

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

public class JoeyJDBCConnectionPool {
    private static JoeyJDBCConnectionPool singletenInstance;
    private JoeyConnectionDealer jcd;

    public static JoeyJDBCConnectionPool getConnectionPool(String driver,
            String server, String user, String password) {
        if (singletenInstance == null) {
            singletenInstance = new JoeyJDBCConnectionPool(driver, server,
                    user, password);
        }
        return singletenInstance;
    }

    private JoeyJDBCConnectionPool(String driver, String server, String user,
            String password) {
        Hashtable<String, JoeyConnection> pool = new Hashtable<String, JoeyConnection>();
        JoeyConnectionCleaner jcc = new JoeyConnectionCleaner(pool);
        JoeyConnectionDealer jcd = new JoeyConnectionDealer(pool, driver,
                server, user, password, jcc);
        this.jcd = jcd;
        (new Thread(jcc)).start();
    }

    public Connection getConnection() throws Exception {
        Connection connection = this.jcd.getConnection();
        if (connection == null) {
            throw new SQLException("Database is done");
        } else {
            return connection;
        }
    }

    public void returnConnection(Connection connection) {
        this.jcd.returnConnection(connection);
    }
}

 

 

package JoeyUtilities;

import java.sql.*;

public class PoolTest {

    /**
     * @param args
     */
    public static void main(String[] args) {
        JoeyJDBCConnectionPool jjcp = JoeyJDBCConnectionPool.getConnectionPool(
                "com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/mysql",
                "root", "adminadmin");
        try {
            Connection c1 = jjcp.getConnection();
            Connection c2 = jjcp.getConnection();
            Connection c3 = jjcp.getConnection();
            Connection c4 = jjcp.getConnection();
            Connection c5 = jjcp.getConnection();
            Connection c6 = jjcp.getConnection();
            Connection c7 = jjcp.getConnection();
            Connection c8 = jjcp.getConnection();
            Connection c9 = jjcp.getConnection();
            Connection c10 = jjcp.getConnection();
            Connection c11 = jjcp.getConnection();
            Connection c12 = jjcp.getConnection();
            Connection c13 = jjcp.getConnection();
            jjcp.returnConnection(c1);
            jjcp.returnConnection(c2);
            jjcp.returnConnection(c3);
            jjcp.returnConnection(c4);
            jjcp.returnConnection(c5);
            jjcp.returnConnection(c6);
            jjcp.returnConnection(c7);
            c2 = jjcp.getConnection();
            jjcp.returnConnection(c8);
            jjcp.returnConnection(c9);
            jjcp.returnConnection(c10);
            jjcp.returnConnection(c11);
            jjcp.returnConnection(c12);
            jjcp.returnConnection(c13);
            c1 = jjcp.getConnection();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }

}