Searching...
Saturday, 9 November 2013

Java Database Connection Pooling example

01:21

Creating a connection to the database server is expensive. It is even more expensive if the server is located on another machine. Connection pool contains a number of open database connections which we can configure as minimum and maximum. There are lot of APIs available to create connection pooling also we can depend application server if it supports this feature, generally all the applications servers support connection pools. It creates the connection pool on behalf of you when it starts.

Here is one simple example which creates a connection pool maximum of size 5.

Prerequisites- mysql-connector-java-5.1.5-bin.jar should be there in the project class path.

Example (DatabaseConnectionPool.java)

package com.pretech;
 
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Enumeration;
import java.util.Vector;
 
public class DatabaseConnectionPool {
 
    private String driverName;
    private String password;
    private String url;
    private String user;
    private Driver driver;
    private Vector freeConnections;
    private int maxConn;
    private int count;
 
    /**
     * DatabaseConnectionPool constructor.
     *
     * @param drivername
     * @param conUrl
     * @param conuser
     * @param conpassword
     * @throws SQLException
     */

    public DatabaseConnectionPool(String drivername, String conUrl,
            String conuser, String conpassword) throws SQLException {
        freeConnections = new Vector();
        driverName = drivername;
        url = conUrl;
        user = conuser;
        password = conpassword;
        try {
            driver = (Driver) Class.forName(driverName).newInstance();
            DriverManager.registerDriver(driver);
        } catch (Exception _ex) {
            new SQLException();
        }
        count = 0;
        maxConn = 5;
    }
 
    /**
     * Method to destroy all connections.
     */

    public void destroy() {
        closeAll();
        try {
            DriverManager.deregisterDriver(driver);
            return;
        } catch (Exception e) {
            e.printStackTrace();
 
            return;
        }
    }
 
    /**
     * Method to add free connections in to pool.
     *
     * @param connection
     */

    public synchronized void freeConnection(Connection connection) {
        freeConnections.addElement(connection);
        count--;
        notifyAll();
    }
 
    /**
     * Method to get connections.
     *
     * @return Connection
     */

    public synchronized Connection getConnection() {
        Connection connection = null;
        if (freeConnections.size() > 0) {
            connection = (Connection) freeConnections.elementAt(0);
            freeConnections.removeElementAt(0);
            try {
                if (connection.isClosed()) {
                    connection = getConnection();
                }
            } catch (Exception e) {
                print(e.getMessage());
 
                connection = getConnection();
            }
            return connection;
        }
        if (count < maxConn) {
            connection = newConnection();
            print("NEW CONNECTION CREATED");
 
        }
        if (connection != null) {
            count++;
        }
        return connection;
    }
 
    /**
     * Method to close all resources
     */

    private synchronized void closeAll() {
        for (Enumeration enumeration = freeConnections.elements(); enumeration
                .hasMoreElements();) {
            Connection connection = (Connection) enumeration.nextElement();
            try {
                connection.close();
            } catch (Exception e) {
                print(e.getMessage());
            }
        }
        freeConnections.removeAllElements();
    }
 
    /**
     * Method to create new connection object.
     *
     * @return Connection.
     */

    private Connection newConnection() {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            print(e.getMessage());
            return null;
        }
        return connection;
    }
 
    private void print(String print) {
        System.out.println(print);
    }
}

Test Connection Pool(ConnectionPoolTest.java)

The above connection pool class we set the maximum number of connections as 5, in this test class we will create first 5 connections and release one connection and creates 6th connection ..see below example

package com.pretech;
 
import java.sql.Connection;
import java.sql.ResultSet;
 
public class ConnectionPoolTest {
    public static void main(String[] argv) {
        String dburl = "jdbc:mysql://localhost:3306/pretech";
        String driver = "com.mysql.jdbc.Driver";
        String sUser = "root";
        String sPwd = "root";
        java.sql.PreparedStatement pstmt;
        String select_sql = "select name,address from customer";
        ResultSet rs;
 
        DatabaseConnectionPool dbConnectionPool;
        // create database connection pool
        try {
            dbConnectionPool = new DatabaseConnectionPool(driver, dburl, sUser,
                    sPwd);
        } catch (Exception e) {
            e.printStackTrace();
            return;
        }
        Connection c1, c2,c3,c4,c5,c6;
        c1 = dbConnectionPool.getConnection();
        c2 = dbConnectionPool.getConnection();
        c3 = dbConnectionPool.getConnection();
        c5 = dbConnectionPool.getConnection();
        c5 = dbConnectionPool.getConnection();
        dbConnectionPool.freeConnection(c5);
        c6 = dbConnectionPool.getConnection();
 
        try {
            pstmt = c6.prepareStatement(select_sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                System.out.println("output value =>" + rs.getString(1) + ""
                        + rs.getString(2));
            }
            rs.close();
            pstmt.close();
            c1.setAutoCommit(true);
        } catch (Exception e) {
            e.printStackTrace();
            return;
        }
        // return first connection to the pool
        dbConnectionPool.freeConnection(c1);
        // release resources
        dbConnectionPool.destroy();
 
    }
 
}

Output

NEW CONNECTION CREATED
NEW CONNECTION CREATED
NEW CONNECTION CREATED
NEW CONNECTION CREATED
NEW CONNECTION CREATED
output value =>Vinod  Bangalore
output value =>Santhosh  Kannur
output value =>Shiva  Mysore

 

0 comments: