WS WhereIsStuff
Tutorial Guides
Log In Sign Up

Advertisement

Google Ad Slot: content-top

JDBC Connection Pooling

Previous Next

What is Connection Pooling?

Connection Pooling is a technique used in JDBC to manage and reuse database connections efficiently. Instead of creating and closing connections for every request, a pool of connections is maintained and reused, improving performance and resource utilization.

Why Use Connection Pooling?

✔ Reduces overhead of repeatedly creating and closing database connections.

✔ Improves performance by reusing connections.

✔ Handles concurrent database access efficiently.

✔ Manages connection limits efficiently in multi-threaded environments.

How Connection Pooling Works?

  1. A pool of database connections is created at application startup.
  2. When an application needs a database connection, it borrows one from the pool.
  3. After completing the operation, the connection is returned to the pool for reuse.
  4. If all connections are busy, a new connection is created (if within the limit) or the request waits until a connection is available.

Implementing Connection Pooling in JDBC

There are multiple ways to implement connection pooling in Java JDBC, using:

  • Apache DBCP (Database Connection Pooling)
  • HikariCP (Fastest & Recommended)
  • C3P0
  • BoneCP
  • Tomcat JDBC Connection Pool

Connection Pooling Using HikariCP (Recommended):

✔ Static block initializes the connection pool at application startup.

✔ HikariConfig sets connection pooling properties like maxPoolSize, idleTimeout, etc.

✔ getConnection() method fetches a connection from the pool.

✔ Try-with-resources ensures proper connection closing after use.

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

import java.sql.Connection;
import java.sql.SQLException;

public class HikariCPExample {
private static HikariDataSource dataSource;

static {
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
config.setUsername("root");
config.setPassword("password");
config.setMaximumPoolSize(10); // Max connections in the pool
config.setMinimumIdle(5); // Minimum idle connections
config.setIdleTimeout(30000); // 30 seconds idle timeout
config.setMaxLifetime(1800000); // 30 minutes max lifetime

dataSource = new HikariDataSource(config);
}

public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}

public static void main(String[] args) {
try (Connection connection = getConnection()) {
if (connection != null) {
System.out.println("✅ Connection successful using HikariCP!");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}

Connection Pooling Using Apache DBCP:

✔ Works similarly to HikariCP but is slightly slower.

import org.apache.commons.dbcp2.BasicDataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class DBCPExample {
private static BasicDataSource dataSource;

static {
dataSource = new BasicDataSource();
dataSource.setUrl("jdbc:mysql://localhost:3306/mydb");
dataSource.setUsername("root");
dataSource.setPassword("password");
dataSource.setMinIdle(5);
dataSource.setMaxIdle(10);
dataSource.setMaxOpenPreparedStatements(100);
}

public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}

public static void main(String[] args) {
try (Connection connection = getConnection()) {
if (connection != null) {
System.out.println("✅ Connection successful using Apache DBCP!");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}

Configure C3P0 Connection Pool:

✔ C3P0 is slower but easy to configure.

import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class C3P0Example {
private static ComboPooledDataSource dataSource = new ComboPooledDataSource();

static {
try {
dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
dataSource.setUser("root");
dataSource.setPassword("password");
dataSource.setMinPoolSize(5);
dataSource.setMaxPoolSize(20);
dataSource.setAcquireIncrement(5);
} catch (Exception e) {
e.printStackTrace();
}
}

public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}

public static void main(String[] args) {
try (Connection connection = getConnection()) {
if (connection != null) {
System.out.println("✅ Connection successful using C3P0!");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}

Previous Next