Connection Pool

Connection Pool

• DBCP
• C3P0
• HikariCP

https://www.baeldung.com/java-connection-pooling

Why Connection Pooling？

a typical database connection life cycle：

1. Opening a connection to the database using the database driver
2. Opening a TCP socket for reading/writing data
3. Reading / writing data over the socket
4. Closing the connection
5. Closing the socket

database connections are fairly expensive operations

The most relevant point to stress here is that once the pool is created, connections are fetched from the pool, so there's no need to create new ones.

Furthermore, when a connection is released, it's actually returned back to the pool, so other clients can reuse it.

There's no any further interaction with the underlying database, such as an explicit call to the Connection's close() method.

---

Apache Commons DBCP

private static BasicDataSource ds = new BasicDataSource();

static {
  ds.setUrl("jdbc:h2:mem:test");
  ds.setUsername("user");
  ds.setPassword("password");
  ds.setMinIdle(5);
  ds.setMaxIdle(10);
  ds.setMaxOpenPreparedStatements(100);
}

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

private DBCPDataSource(){ }

HikariCP

a lightning fast JDBC connection pooling framework created by Brett Wooldrid

private static HikariConfig config = new HikariConfig();
private static HikariDataSource ds;

static {
  config.setJdbcUrl("jdbc:h2:mem:test");
  config.setUsername("user");
  config.setPassword("password");
  config.addDataSourceProperty("cachePrepStmts", "true");
  config.addDataSourceProperty("prepStmtCacheSize", "250");
  config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
  ds = new HikariDataSource(config);
}

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

private HikariCPDataSource(){}

C3p0

a powerful JDBC4 connection and statement pooling framework developed by Steve Waldman:

https://www.baeldung.com/hibernate-c3p0

https://developer.jboss.org/docs/DOC-14014

https://www.mchange.com/projects/c3p0/#configuration

c3p0 is an easy-to-use library for making traditional JDBC drivers "enterprise-ready" by augmenting them with functionality defined by the jdbc3 spec and the optional extensions to jdbc2. As of version 0.9.5, c3p0 fully supports the jdbc4 spec.

In particular, c3p0 provides several useful services:
- A class whichs adapt traditional DriverManager-based JDBC drivers to the newer javax.sql.DataSource scheme for acquiring database Connections. - Transparent pooling of Connection and PreparedStatements behind DataSources which can "wrap" around traditional drivers or arbitrary unpooled DataSources.

  # Basic Pool Configuration
  acquireIncrement
  initialPoolSize
  maxPoolSize
  maxIdleTime
  minPoolSize
  
  # Managing Pool Size and Connection Age
  maxConnectionAge
  maxIdleTime
  
  # Configuring Connection Testing
  idleConnectionTestPeriod

# Configuring Statement Pooling
maxStatements

initialPoolSize, minPoolSize, maxPoolSize define the number of Connections that will be pooled

acquireIncrement determines how many Connections a c3p0 pool will attempt to acquire when the pool has run out of Connections.

By default, pools will never expire Connections. If you wish Connections to be expired over time in order to maintain "freshness", set maxIdleTime and/or maxConnectionAge.

maxIdleTime defines how many seconds a Connection should be permitted to go unused before being culled from the pool.

maxConnectionAge forces the pool to cull any Connections that were acquired from the database more than the set number of seconds in the past.

idleConnectionTestPeriod this is a number greater than 0, c3p0 will test all idle, pooled but unchecked-out connections, every this number of seconds.

maxStatements is JDBC's standard parameter for controlling statement pooling. maxStatements defines the total number PreparedStatements a DataSource will cache. The pool will destroy the least-recently-used PreparedStatement when it hits this limit. This sounds simple, but it's actually a strange approach, because cached statements conceptually belong to individual Connections; they are not global resources. To figure out a size for maxStatements that does not "churn" cached statements, you need to consider the number of frequently used PreparedStatements in your application, and multiply that by the number of Connections you expect in the pool (maxPoolSize in a busy application).