Connection Pooling in Java: How to Improve the Efficiency of Your Applications
Introduction to Connection Pooling
Connection Pooling is a crucial concept for optimizing the performance of database applications. It allows for the reuse of database connections instead of creating a new connection for each request. This technique is based on the object pool design pattern, which is useful in scenarios where the creation of new objects (such as database connections) is time- and resource-intensive.
Benefits of Connection Pooling
By implementing Connection Pooling, the performance of an application can be significantly improved, as existing connections can be reused. This leads to faster response times and better resource utilization.
Building a Connection Pool
A connection pooling framework typically needs to fulfill the following tasks:
- Creating connection objects: The framework should be able to generate new connections as needed.
- Managing and validating connections: The system must monitor the status of the connections and ensure they are valid.
- Releasing and destroying connections: Connections need to be released or destroyed when necessary to conserve resources.
Implementations in Java
There are several well-established libraries that can be used for connection pooling in Java:
- Apache Commons DBCP
- HikariCP
- C3P0
Below, we will look at some examples of implementing these libraries.
Database Scripts
Before we begin with the implementation, we will create a database and a table:
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 (empName, dob, designation) VALUES
('Adam', '1998-08-15', 'Manager'),
('Smith', '2001-01-11', 'Clerk'),
('James', '1996-03-13', 'Officer');
Example Project with Apache Commons DBCP
To create a simple Java project, follow these steps in Eclipse:
- Open Eclipse IDE.
- In the menu, select File > New > Maven Project.
- Select the option Create a simple project and click Next.
- Enter a group ID, artifact ID, name, and description, then click Finish.
Then, add the MySQL dependency to your pom.xml:
mysql
mysql-connector-java
5.1.49
Example Code for DBCP
package com.journaldev.example;
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 = new BasicDataSource();
static {
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 {
try (Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement();
ResultSet 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"));
}
}
}
}
Example Project with HikariCP
Add the following dependency to your pom.xml for HikariCP:
com.zaxxer
HikariCP
3.4.5
Example Code for HikariCP
package com.journaldev.example;
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;
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 {
try (Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement();
ResultSet 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"));
}
}
}
}
Example Project with C3P0
Add the following dependency to your pom.xml for C3P0:
com.mchange
c3p0
0.9.5.5
Example Code for C3P0
package com.journaldev.example;
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 = new ComboPooledDataSource();
static {
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 {
try (Connection connection = comboPooledDataSource.getConnection();
Statement statement = connection.createStatement();
ResultSet 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"));
}
}
}
}
Conclusion
Connection Pooling is an essential technique for improving the performance of database applications in Java. With libraries such as Apache Commons DBCP, HikariCP, and C3P0, you can implement effective and efficient connection pooling solutions that significantly accelerate your application. By reusing connections, you optimize resource utilization and reduce latency times for database queries.
