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);
}
}
