This example show 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 = "root";
private static final String PASSWORD = "";
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 authors")) {
demo.printStatus();
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
System.out.println("Name: " + rs.getString("name"));
}
}
demo.printStatus();
}
public DataSource setUp() {
// Creates a connection factory object which will be use 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 connections object.
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 show the following status as output example:
Max : 0; Active: 0; Idle : 0
Max : 1; Active: 1; Idle : 0
Name: Raoul-Gabriel Urma
Name: Mario Fusco
Name: Alan Mycroft
Max : 0; Active: 0; Idle : 1
Maven Dependencies
<dependencies>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.9.0</version>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.0.32</version>
</dependency>
</dependencies>
Latest posts by Wayan (see all)
- How do I build simple search page using ZK and Spring Boot? - March 8, 2023
- How do I calculate days between two dates excluding weekends and holidays? - January 12, 2023
- How do I discover the quarter of a given date? - January 11, 2023
Hi,
What is the use of PoolableConnectionFactory object. It is used nowhere.
Hi,
And as result ConnectionFactory is also not required there.
But, once you commented out PoolableConnectionFactory demo.setUp() returns null
getConnectionPool()
is not used anywhere. Where is the method to release the connection back to pool?Hi Deepak,
The
getConnectionPool()
method is use by theprintStatus()
method to read connection pool information, at the end of the code snippet above.In the code above we use try-with-resources statement, it will automatically closes the resource, in this case the connection object. So when the connection closed it will be returned back to the pool.