This example shows you how to create a connection pool implementation using the Apache Commons DBCP library.
package org.kodejava.commons.dbcp;
import org.apache.commons.dbcp2.*;
import org.apache.commons.pool2.impl.GenericObjectPool;
import org.apache.commons.pool2.impl.GenericObjectPoolConfig;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class ConnectionPoolExample {
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "kodejava";
private static final String PASSWORD = "s3cr*t";
private GenericObjectPool<PoolableConnection> connectionPool = null;
public static void main(String[] args) throws Exception {
ConnectionPoolExample demo = new ConnectionPoolExample();
DataSource dataSource = demo.setUp();
demo.printStatus();
try (Connection conn = dataSource.getConnection();
PreparedStatement stmt = conn.prepareStatement("SELECT * FROM author")) {
demo.printStatus();
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
System.out.println("Author name: " + rs.getString("author_name"));
}
}
demo.printStatus();
}
public DataSource setUp() {
// Creates a connection factory object, which will be used by
// the pool to create the connection object. We pass the
// JDBC url info, username and password.
ConnectionFactory cf = new DriverManagerConnectionFactory(
ConnectionPoolExample.URL,
ConnectionPoolExample.USERNAME,
ConnectionPoolExample.PASSWORD);
// Creates a PoolableConnectionFactory that will wrap the
// connection object created by the ConnectionFactory to add
// object pooling functionality.
PoolableConnectionFactory pcf = new PoolableConnectionFactory(cf, null);
pcf.setValidationQuery("SELECT 1");
// Creates an instance of GenericObjectPool that holds our
// pool of connection objects.
GenericObjectPoolConfig<PoolableConnection> config = new GenericObjectPoolConfig<>();
config.setTestOnBorrow(true);
config.setMaxTotal(10);
connectionPool = new GenericObjectPool<>(pcf, config);
pcf.setPool(connectionPool);
return new PoolingDataSource<>(connectionPool);
}
private GenericObjectPool<PoolableConnection> getConnectionPool() {
return connectionPool;
}
/**
* Prints connection pool status.
*/
private void printStatus() {
System.out.println("Max : " + getConnectionPool().getNumActive() + "; " +
"Active: " + getConnectionPool().getNumActive() + "; " +
"Idle : " + getConnectionPool().getNumIdle());
}
}
The code shows the following status as output example:
Max : 0; Active: 0; Idle : 0
Max : 1; Active: 1; Idle : 0
Author name: Raoul-Gabriel Urma
Author name: Mario Fusco
Author name: Alan Mycroft
Max : 0; Active: 0; Idle : 1
Maven Dependencies
<dependencies>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.10.0</version>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.1.0</version>
</dependency>
</dependencies>