A Connection Pool is a collection of reusable database connections that avoids creating a new connection for every request. The DataSource interface provides a standard way to obtain database connections. Using a connection pool improves performance by reusing existing connections instead of repeatedly opening and closing them. This approach is commonly used in enterprise JDBC applications.
How the Connection Pool Works ?
Explanation
-
The Java Application requests a connection.
-
The Connection Pool checks for an available connection.
-
If available, it returns an existing connection instead of creating a new one.
-
The application executes SQL queries using the borrowed connection.
-
After the work is completed, the connection is returned to the pool.
-
The returned connection becomes available for the next request, reducing the overhead of creating new connections repeatedly.
Follow the below steps:
Step 1: Create a Database
CREATE DATABASE jdbc_demo;
use jdbc_demo;
Step 2: Create an Employee Table
CREATE TABLE employee (employee_id INT PRIMARY KEY, employee_name VARCHAR(50), department VARCHAR(30), salary DECIMAL(10,2));
Step 3: Insert Sample Data
INSERT INTO employee VALUES (101,’Ravi’,’IT’,45000), (102,’Priya’,’HR’,42000), (103,’Arjun’,’Finance’,50000);
Step 4: Add the JDBC Driver
- Download MySQL Connector/J.
- Add the JAR file to your project’s classpath.
- No Maven or Gradle dependency is required.
Step-5: Create below .java files and paste the code.
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.logging.Logger;
import javax.sql.DataSource;
public class SimpleDataSource implements DataSource {
private String url;
private String username;
private String password;
public SimpleDataSource(String url, String username, String password) {
this.url = url;
this.username = username;
this.password = password;
}
@Override
public Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
@Override
public Connection getConnection(String user, String pass)
throws SQLException {
return DriverManager.getConnection(url, user, pass);
}
@Override
public PrintWriter getLogWriter() {
return null;
}
@Override
public void setLogWriter(PrintWriter out) {
}
@Override
public void setLoginTimeout(int seconds) {
}
@Override
public int getLoginTimeout() {
return 0;
}
@Override
public Logger getParentLogger() {
return Logger.getGlobal();
}
@Override
public <T> T unwrap(Class<T> iface) {
return null;
}
@Override
public boolean isWrapperFor(Class<?> iface) {
return false;
}
}import java.sql.Connection;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.Queue;
import javax.sql.DataSource;
public class SimpleConnectionPool {
private Queue<Connection> pool = new LinkedList<>();
private DataSource dataSource;
public SimpleConnectionPool(DataSource dataSource, int poolSize)
throws SQLException {
this.dataSource = dataSource;
for (int i = 0; i < poolSize; i++) {
pool.offer(dataSource.getConnection());
}
}
public synchronized Connection getConnection() throws SQLException {
if (pool.isEmpty()) {
return dataSource.getConnection();
}
return pool.poll();
}
public synchronized void releaseConnection(Connection connection) {
if (connection != null) {
pool.offer(connection);
}
}
public int availableConnections() {
return pool.size();
}
}import java.sql.*;
public class ConnectionPoolDemo {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/jdbc_demo";
String username = "root";
String password = "MySql_Password";
try {
SimpleDataSource dataSource =
new SimpleDataSource(url, username, password);
SimpleConnectionPool pool =
new SimpleConnectionPool(dataSource, 2);
System.out.println("Available Connections: "
+ pool.availableConnections());
Connection connection = pool.getConnection();
System.out.println("Connection borrowed.");
Statement statement = connection.createStatement();
ResultSet result = statement.executeQuery(
"SELECT * FROM employee");
while (result.next()) {
System.out.println(result.getInt("employee_id")
+ " "
+ result.getString("employee_name")
+ " "
+ result.getString("department")
+ " "
+ result.getDouble("salary"));
}
result.close();
statement.close();
pool.releaseConnection(connection);
System.out.println("Connection returned.");
System.out.println("Available Connections: "
+ pool.availableConnections());
} catch (Exception e) {
e.printStackTrace();
}
}
}Execution and Output:
PS D:\test> javac -classpath ".;D:\test\mysql-connector.jar" SimpleDataSource.java SimpleConnectionPool.java ConnectionPoolDemo.java
PS D:\test> java -classpath ".;D:\test\mysql-connector.jar" ConnectionPoolDemo
Available Connections: 2
Connection borrowed.
101 Ravi IT 45000.0
102 Priya HR 42000.0
103 Arjun Finance 50000.0
Connection returned.
Available Connections: 2Code language: CSS (css)