How to create a connection pool of JDBC

In this tutorial you will learn to how to create the JDBC Connection pool, and why you require it.

If you have used a SQL or other similar tool to connect to a database and act on the data, you probably know that getting the connection and logging in is the part that takes the most time. An application can easily spend several seconds every time it needs to establish a connection.

Cached connections are kept in a runtime object pool and can be used and reused as needed by the application. One way to implement the object pool is to make a simple vector of connection objects.

Database connections are very expensive to create because of the overhead of establishing a network connection and initializing a database connection session in the back end database. In turn, connection session initialization often requires time consuming processing to perform user authentication, establish transactional contexts and establish other aspects of the session that are required for subsequent database usage. JDBC database connections are both expensive to initially create and then maintain over time.

Below is the example of Connection Pool and you can use straightforward in your application.

How to use connection pool example?

To use the below Connection pool you can follow the below steps.

First: create the object of ConnectionPool by passing required information to its constructor.

Second: call the init() method, this will setup the connections in pool.

Third: get the Connection object by calling checkOut() method whenever you require the connection.

Forth: Use the connection object and release it after done by calling the checkIn() method.

Whenever you are going to stop your application call the release method, it will release all the connections from Connection pool.

Below is full example of JDBC Connection Pool class.

package com.java.connect.jdbc;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Vector;
 
/**
 * The is class which store the Connections and gives you the connection on
 * demand, you have to initiate this class by passing driver, url username
 * ,passowrd and poolsize of you connection.
 * 
 * @author Java-Connect.com
 */
public class ConnectionPool {
 
    private String driver;
    private String url;
    private String username;
    private String password;
    private int poolSize;
    private Vector<connection> pool;
 
    /**
     * Consructor which has to call to create object of this class
     * 
     * @param driver
     * @param url
     * @param username
     * @param password
     * @param poolSize
     */
    public ConnectionPool(String driver, String url, String username, String password, int poolSize) {
        super();
        this.driver = driver;
        this.url = url;
        this.username = username;
        this.password = password;
        this.poolSize = poolSize;
    }
 
    /**
     * Call this method to start to get the connection in your application
     */
    public void init() {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
 
        Connection connection = null;
        for (int i = 0; i < poolSize; i++) {
            try {
                connection = DriverManager.getConnection(url, username, password);
                pool.add(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
 
    /**
     * Call this method to get the Connection from Connection pool
     * 
     * @return Connection
     */
    public Connection checkOut() {
        Connection connection = null;
        if (pool.size() > 0) {
            connection = pool.elementAt(0);
        } else {
            try {
                connection = DriverManager.getConnection(url, username, password);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return connection;
    }
 
    /**
     * Put back the connection to connction pool after use
     * 
     * @param connection
     */
    public void checkIn(Connection connection) {
        if (connection instanceof Connection) {
            if (pool.size() < poolSize) {
                pool.addElement(connection);
            }
        } else {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
 
    /**
     * Release all the connection inside pool
     */
    public void release() {
        Connection connection = null;
        for (int i = 0; i < pool.size(); i++) {
            connection = pool.elementAt(i);
            pool.remove(connection);
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}