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.
With Java, we have great set of libraries which are readily available. We only need to configure few properties to use them.
Let’s have a look at below libraries:
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.
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');
Follow below steps to create new project.
Open Eclipse IDE.
Click File menu and select new -> Maven Project
The below screen will be displayed. Select create a simple Project option and click the Next Button.
Click Finish Button.
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.49</version>
</dependency>
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.
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:
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);
}
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.
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
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.
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
Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.
While we believe that this content benefits our community, we have not yet thoroughly reviewed it. If you have any suggestions for improvements, please let us know by clicking the “report an issue“ button at the bottom of the tutorial.
Sign up for Infrastructure as a Newsletter.
Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.
Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.