Connection Pool in Java & JDBC

Connection Pooling in JDBC In software engineering, a connection pool is a cache of database connections maintained so that the connections can be reused when future requests to the database are required. Connection pools are used to enhance the performance of executing commands on a database. Opening and maintaining a database connection for each user, especially requests made to a dynamic database-driven website application, is costly and wastes resources. In connection pooling, after a connection is created, it is placed in the pool and it is used over again so that a new connection does not have to be established. If all the connections are being used, a new connection is made and is added to the pool. Connection pooling also cuts down on the amount of time a user must wait to establish a connection to the database.(source :wikipedia)

Steps to create connection pool in jdbc
1. Create Configuration class that holds Database Configuration
2. Create JdbcConnectionPool class that Creating and managing the connections
3. Create DataSource class to get connection and returning the connection to connection pool

1. Configuration Class (Configuration.java)
a. Make the configuration class as singleton

public class Configuration {
 private static Configuration configuration = new Configuration();
 
 public static Configuration getInstance(){ 
  return configuration;
 }
}

b. Create initialize method that sets the database configuration properties

public class Configuration {

 public String DB_USER_NAME ;
 
 public String DB_PASSWORD ;
 
 public String DB_URL;
 
 public String DB_DRIVER;
 
 public Integer DB_MAX_CONNECTIONS;
 
 private void init() {
  DB_USER_NAME = "root"
  DB_PASSWORD = "root"
  DB_URL = "jdbc:mysql://localhost:3306/jmanne"
  DB_DRIVER = "com.mysql.jdbc.Driver"
  DB_MAX_CONNECTIONS = 5
 }
 
}

c. Call the init method in the constructor

 public Configuration(){
  init();
 }

Finally Configuration.java

public class Configuration {
 
 public String DB_USER_NAME ;
 
 public String DB_PASSWORD ;
 
 public String DB_URL;
 
 public String DB_DRIVER;
 
 public Integer DB_MAX_CONNECTIONS;
 
 public Configuration(){
  init();
 }
 
 private static Configuration configuration = new Configuration();
 
 public static Configuration getInstance(){ 
  return configuration;
 }
 
 private void init() {
  DB_USER_NAME = "root"
  DB_PASSWORD = "root"
  DB_URL = "jdbc:mysql://localhost:3306/jmanne"
  DB_DRIVER = "com.mysql.jdbc.Driver"
  DB_MAX_CONNECTIONS = 5
 }
}

2. Creating JDBCConnectionPool.java 

a. Create an empty array list to hold the connections

public class JdbcConnectionPool {
 List availableConnections = new ArrayList();
}

b. Get the database configuration from Configuration.java and Create a new Connection

private Connection createNewConnectionForPool() {
    // get the configuraiton object to get the database configuration
 Configuration config = Configuration.getInstance();
 try {
     //load the Database driver using Class.forName
  Class.forName(config.DB_DRIVER);
  // Create connection by using DriverManager
  Connection connection = (Connection) DriverManager.getConnection(
    config.DB_URL, config.DB_USER_NAME, config.DB_PASSWORD);
  return connection;
 } catch (ClassNotFoundException e) {
  e.printStackTrace();
 } catch (SQLException e) {
  e.printStackTrace();
 }
 return null;
 
}

c. Initialize the connection pool and check the connection pool is full and if connection pool is empty then add new connection

private void initializeConnectionPool()
{
 while(!checkIfConnectionPoolIsFull())
 {
  availableConnections.add(createNewConnectionForPool());
 }
} 
private synchronized boolean checkIfConnectionPoolIsFull() {
 final int MAX_POOL_SIZE = Configuration.getInstance().DB_MAX_CONNECTIONS;
 // check the connections size in the available connections
 if(availableConnections.size() < MAX_POOL_SIZE)
 {
  return false;
 }

 return true;
}

d. Get the connection from connection pool

 
public synchronized Connection getConnectionFromPool() {
 Connection connection = null;
 if(availableConnections.size() > 0)
 {
  connection = (Connection) availableConnections.get(0);
  availableConnections.remove(0);
 }
 return connection;
}

e. Return the connection to connection pool

  
public synchronized void returnConnectionToPool(Connection connection) {
 availableConnections.add(connection);
}

f. initialize the connection pool in the constructor

public JdbcConnectionPool() {
 initializeConnectionPool();
}

3. Creating DataSource class to get the connection and return the connection

import java.sql.SQLException;
import com.mysql.jdbc.Connection;

public class DataSource {
 
 static JdbcConnectionPool pool = new JdbcConnectionPool();
 
 public static Connection getConnection() throws ClassNotFoundException, SQLException{
  Connection connection = pool.getConnectionFromPool();
  return connection;
 }
 
 public static void returnConnection(Connection connection) {
  pool.returnConnectionToPool(connection);
 }
}
 

Now We can use DataSource class to get the connection

Connection connection = DataSource.getConnection();

Final Source code
1. Configuration.java

package com.jmanne.utils;

public class Configuration {
 
 public String DB_USER_NAME ;
 
 public String DB_PASSWORD ;
 
 public String DB_URL;
 
 public String DB_DRIVER;
 
 public Integer DB_MAX_CONNECTIONS;
 
 public Configuration(){
  init();
 }
 
 private static Configuration configuration = new Configuration();
 
 public static Configuration getInstance(){ 
  return configuration;
 }
 
 private void init(){
  DB_USER_NAME = "root"
  DB_PASSWORD = "root"
  DB_URL = "jdbc:mysql://localhost:3306/jmanne"
  DB_DRIVER = "com.mysql.jdbc.Driver"
  DB_MAX_CONNECTIONS = 5
 }

}

2. JdbcConnectionPool.java

package com.jmanne.db;

import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.jmanne.utils.Configuration;
import com.mysql.jdbc.Connection;

public class JdbcConnectionPool {
 
 List availableConnections = new ArrayList();

 public JdbcConnectionPool()
 {
  initializeConnectionPool();
 }

 private void initializeConnectionPool()
 {
  while(!checkIfConnectionPoolIsFull())
  {
   availableConnections.add(createNewConnectionForPool());
  }
 }

 private synchronized boolean checkIfConnectionPoolIsFull()
 {
  final int MAX_POOL_SIZE = Configuration.getInstance().DB_MAX_CONNECTIONS;

  if(availableConnections.size() < MAX_POOL_SIZE)
  {
   return false;
  }

  return true;
 }

 //Creating a connection
 private Connection createNewConnectionForPool()
 {
  Configuration config = Configuration.getInstance();
  try {
   Class.forName(config.DB_DRIVER);
   Connection connection = (Connection) DriverManager.getConnection(
     config.DB_URL, config.DB_USER_NAME, config.DB_PASSWORD);
   return connection;
  } catch (ClassNotFoundException e) {
   e.printStackTrace();
  } catch (SQLException e) {
   e.printStackTrace();
  }
  return null;
  
 }

 public synchronized Connection getConnectionFromPool()
 {
  Connection connection = null;
  if(availableConnections.size() > 0)
  {
   connection = (Connection) availableConnections.get(0);
   availableConnections.remove(0);
  }
  return connection;
 }

 public synchronized void returnConnectionToPool(Connection connection)
 {
  availableConnections.add(connection);
 }
}

3. DataSource.java

package com.jmanne.db;

import java.sql.SQLException;

import com.mysql.jdbc.Connection;

public class DataSource {
 
 static JdbcConnectionPool pool = new JdbcConnectionPool();
 
 public static Connection getConnection() throws ClassNotFoundException, SQLException{
  Connection connection = pool.getConnectionFromPool();
  return connection;
 }
 
 public static void returnConnection(Connection connection) {
  pool.returnConnectionToPool(connection);
 }
}

Categories: Core Java , JDBC