Connection Pooling in Java

Connection pooling means a pool of Connection Objects. Connection pooling is based on an object pool design pattern. Object pooling design pattern is used when the cost (time & resources like CPU, Network, and IO) of creating new objects is higher. As per the Object pooling design pattern, the application creates an object in advance and place them in Pool or Container. Whenever our application requires such objects, it acquires them from the pool rather than creating a new one.

An application that uses a connection pooling strategy has already DB connection objects which can be reused. So, when there is a need to interact with the database, the application obtains connection instances from Pool. Connection pooling improves application performance that interacts with the database.

We can create our own implementations of Connection pooling. Any connection pooling framework needs to do three tasks.

• Creating Connection Objects
• Manage usage of created Objects and validate them
• Release/Destroy Objects

With Java, we have great set of libraries which are readily available. We only need to configure few properties to use them.

Connection Pooling in Java Application

Let’s have a look at below libraries:

• Apache Commons DBCP 2
• HikariCP
• C3P0

Let’s have a look at below examples of them one by one. For demo purpose we will use MySQL database and Eclipse IDE. We will also create simple Java Project based on maven using JDK 1.8.

Database Scripts

create database empdb;

use empdb;

create table tblemployee(
                    empId integer AUTO_INCREMENT primary key,
                    empName varchar(64),
                    dob date,
                    designation varchar(64)
);

insert into  tblemployee(empId,empName,dob,designation) values (default,'Adam','1998-08-15','Manager');
insert into  tblemployee(empId,empName,dob,designation) values (default,'Smith','2001-01-11','Clerk');
insert into  tblemployee(empId,empName,dob,designation) values (default,'James','1996-03-13','Officer');

Example Project

Follow below steps to create new project.

1. Open Eclipse IDE.

2. Click File menu and select new -> Maven Project

3. The below screen will be displayed. Select create a simple Project option and click the Next Button.

4. Enter any Group Id, Artifact Id, Name and description.

Click Finish Button.

5. Add following dependency in your pom.xml for MySQL.

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.1.49</version>
</dependency>

6. Right-click on Project, select Maven -> Update Project -> Ok. It will download all the dependencies.

1) Apache commons DBCP 2

DBCP is from Apache Common Project. DBCP 2.7 requires Java 8. To use DBCP 2, you need to add following dependency in your project.

<dependency>
	<groupId>org.apache.commons</groupId>
	<artifactId>commons-dbcp2</artifactId>
	<version>2.7.0</version>
</dependency>

Apache DBCP 2.0 provides two types of DataSource (BasicDataSource and PoolingDataSource).

BasicDataSource: As the name suggests, it is simple and suitable for most common use cases. It internally creates PoolingDataSource for us.

Let’s have a look at below steps to initialize connection pool.

1. Create an instance of BasicDataSource
2. Specify JDBC Url, database username and password
3. Specify the minimum number of idle connection ( Minimum number of connections that needs to remain in the pool at any time)
4. Specify the maximum number of idle connection (Maximum number of Idle connection in the pool)
5. Specify the total number of maximum connections.

package com.journaldev.example;

/**
 * Java JDBC Connection pool using Apache commons DBCP2 example program
 * 
 * @author pankaj
 */

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.dbcp2.BasicDataSource;

public class DBCP2Demo {

	private static BasicDataSource dataSource = null;

	static {
		dataSource = new BasicDataSource();
		dataSource.setUrl("jdbc:mysql://localhost:3306/empdb?useSSL=false");
		dataSource.setUsername("root");
		dataSource.setPassword("root");

		dataSource.setMinIdle(5);
		dataSource.setMaxIdle(10);
		dataSource.setMaxTotal(25);

	}

public static void main(String[] args) throws SQLException {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery("select * from tblemployee");
			while (resultSet.next()) {
				System.out.println("empId:" + resultSet.getInt("empId"));
				System.out.println("empName:" + resultSet.getString("empName"));
				System.out.println("dob:" + resultSet.getDate("dob"));
				System.out.println("designation:" + resultSet.getString("designation"));
			}
		} finally {

			resultSet.close();
			statement.close();
			connection.close();
		}
	}

}

Output:

empId:1
empName:Adam
dob:1998-08-15
designation:Manager
empId:2
empName:Smith
dob:2001-01-11
designation:Clerk
empId:3
empName:James
dob:1996-03-13
designation:Officer

PoolingDataSource: It offers more flexibility. You only need to change code which creates DataSource. The rest of the code will remain the same.

Let’s have a look at below steps to initialize connection pool:

1. Create an instance of ConnectionFactory using JDBC URL.
2. Create an instance of PoolableConnectionFactory using an instance of ConnectionFactory which was created in step 1
3. Create an instance of GenericObjectPoolConfig and set maximum idle, minimum idle and maximum connection properties
4. Now initialize ObjectPool using instances created in step 2 and step 3
5. Now set pool as an instance of PoolableConnectionFactory
6. Finally, initialize an instance of DataSource

private static DataSource dataSource = null;

	static {

		Properties properties = new Properties();
		properties.setProperty("user", "root");
		properties.setProperty("password", "root");

		ConnectionFactory connectionFactory = new DriverManagerConnectionFactory("jdbc:mysql://localhost:3306/empdb",
				properties);

		PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, null);

		GenericObjectPoolConfig<PoolableConnection> config = new GenericObjectPoolConfig<>();
		config.setMaxTotal(25);
		config.setMaxIdle(10);
		config.setMinIdle(5);

		ObjectPool<PoolableConnection> connectionPool = new GenericObjectPool<>(poolableConnectionFactory, config);
		poolableConnectionFactory.setPool(connectionPool);

		dataSource = new PoolingDataSource<>(connectionPool);

	}

2) HikariCP

HikariCP is fast, reliable, and simple. It is one of the preferred solutions for Connection Pooling. Frameworks like Spring Boot 2.x uses it as a default connection manager.

To use HikariCP, add following dependency in pom.xml of our project.

<dependency>
	<groupId>com.zaxxer</groupId>
	<artifactId>HikariCP</artifactId>
	<version>3.4.5</version>
</dependency>

HikariCP Configuration:

We can use Java based configuration as shown in our below example program or we can use property file to configure HikariCP. Let’s have a look at below properties.

• idleTimeout: Time in milliseconds for which connection object can stay in the pool as idle. It works with minimumIdle and maximumPoolSize properties. After a specified time connection object will be released.
• connectionTimeout: Time in milliseconds for which the client will wait for connection object from Pool. If the time limit is reached then SQL Exception will be thrown.
• autoCommit: We can specify true or false and if it is set to true then it will automatically commit every SQL statements you execute and if it is set to false then we need to commit SQL statements manually
• cachePrepStmts: Enable caching for Prepare Statement
• minimumIdle: Minimum number of connection objects needs to remain in the pool at any time.
• maximumPoolSize: Maximum number of connections that can stay in the pool.

package com.journaldev.example;

/**
 * Java JDBC Connection pool using HikariCP example program
 * 
 * @author pankaj
 */

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class HikariCPDemo {

	private static HikariDataSource dataSource = null;

	static {
		HikariConfig config = new HikariConfig();
		config.setJdbcUrl("jdbc:mysql://localhost:3306/empdb");
		config.setUsername("root");
		config.setPassword("root");
		config.addDataSourceProperty("minimumIdle", "5");
		config.addDataSourceProperty("maximumPoolSize", "25");

		dataSource = new HikariDataSource(config);
	}

	public static void main(String[] args) throws SQLException {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = dataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery("select * from tblemployee");
			while (resultSet.next()) {
				System.out.println("empId:" + resultSet.getInt("empId"));
				System.out.println("empName:" + resultSet.getString("empName"));
				System.out.println("dob:" + resultSet.getDate("dob"));
				System.out.println("designation:" + resultSet.getString("designation"));
			}
		} finally {
			resultSet.close();
			statement.close();
			connection.close();
		}
	}
}

Output:

empId:1
empName:Adam
dob:1998-08-15
designation:Manager
empId:2
empName:Smith
dob:2001-01-11
designation:Clerk
empId:3
empName:James
dob:1996-03-13
designation:Officer

3) C3P0

C3P0 is one of oldest library. Generally, it is used with Hibernate. To use C3P0, we need to add following dependency to project.

<dependency>
	<groupId>com.mchange</groupId>
	<artifactId>c3p0</artifactId>
	<version>0.9.5.5</version>
</dependency>

We can configure following properties with C3P0.

• driverClass: Preferred Jdbc Driver
• jdbcUrl: JDBC Url for the database.
• initialPoolSize: Number of connections created in the pool at startup.
• acquireIncrement: Number of new connections needs to be created when the current size is not enough.
• maxIdleTime: Number of seconds Connection can remain in Pool without being used.
• maxPoolSize: Maximum number of connections that can stay in Pool.
• minPoolSize: Minimum number of connection objects needs to remain in Pool at any time.

package com.journaldev.example;

/**
 * Java JDBC Connection pool using C3PO example program
 * 
 * @author pankaj
 */

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Demo {

	static ComboPooledDataSource comboPooledDataSource = null;

	static {
		comboPooledDataSource = new ComboPooledDataSource();

		comboPooledDataSource.setJdbcUrl("jdbc:mysql://localhost:3306/empdb?useSSL=false");
		comboPooledDataSource.setUser("root");
		comboPooledDataSource.setPassword("root");

		comboPooledDataSource.setMinPoolSize(3);
		comboPooledDataSource.setAcquireIncrement(3);
		comboPooledDataSource.setMaxPoolSize(30);

	}

public static void main(String[] args) throws SQLException {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = comboPooledDataSource.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery("select * from tblemployee");
			while (resultSet.next()) {
				System.out.println("empId:" + resultSet.getInt("empId"));
				System.out.println("empName:" + resultSet.getString("empName"));
				System.out.println("dob:" + resultSet.getDate("dob"));
				System.out.println("designation:" + resultSet.getString("designation"));
			}
		} finally {
			resultSet.close();
			statement.close();
			connection.close();
		}
	}

}

Output:

Aug 29, 2020 8:59:05 PM com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource 
INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 1hge9kqacgbp7hjpftse6|77a567e1, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> null, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 1hge9kqacgbp7hjpftse6|77a567e1, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://localhost:3306/empdb?useSSL=false, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 30, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]

empId:1
empName:Adam
dob:1998-08-15
designation:Manager
empId:2
empName:Smith
dob:2001-01-11
designation:Clerk
empId:3
empName:James
dob:1996-03-13
designation:Officer

That’s all for the JDBC Connection pool example tutorial, I hope nothing important got missed here.

References: HikariCP, Apache Commons DBCP, C3P0