In this post we learn how to use a connection’s pool using the apache commons dbcp2 framework. A connection pool is an object that maintains a pool of opened connections so the application can simply grab one when it needs to, use it, and then hand it back, eliminating much of the long wait of the creation of connections.
1. What is DBCP2 ?
DBCP2 is a JDBC Framework based on Commons Pool 2 that provides better performance getting Database Connections thorugh a JDBC Driver, and has JMX Support, among other features.
Then, we can make pool of connections, datasources, JMX pools, transactions
2. What We Need ?
- DBCP2 Apache Commons Library
- Pool2 Apache Commons Library
- Apache Commons Logging Library
- JDBC Driver (We use MYSQL 5 Driver)
- An IDE of our taste (We use Eclipse)
- JDK 1.7 (Due to DBCP2 runs on Java 7)
- An DBMS running with a valid Schema (In this example we named it “Test” in MYSQL 5 Engine)
Visit the project site to get the libraries and more information here
Step By Step
3. Create the Project
First download the required libraries, then create a new Java Project, and add the external jars.
4. The Example
Then, we create the PoolConnectionFactory. This class helps us to register the drivers and create the factories.
PoolConnectionFactory.java
package com.javacodegeeks.dbcp2;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.apache.commons.dbcp2.ConnectionFactory;
import org.apache.commons.dbcp2.DriverManagerConnectionFactory;
import org.apache.commons.dbcp2.PoolingDriver;
import org.apache.commons.pool2.ObjectPool;
/**
* @author Andres.Cespedes
* @version 1.0 $Date: 14/01/2015
* @since 1.7
*
*/
public class PoolConnectionFactory {
public static final String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
public static final String ORACLE_DRIVER = "oracle.jdbc.OracleDriver";
public static final String DBCP_DRIVER = "org.apache.commons.dbcp2.PoolingDriver";
public static Class dirverClass;
private static PoolingDriver driver;
/**
*
* @param driver
*/
public static void registerJDBCDriver(String driver) {
try {
dirverClass = Class.forName(driver);
} catch (ClassNotFoundException e) {
System.err.println("There was not able to find the driver class");
}
}
/**
* Get a Connection Factory, the default implementation is a
* DriverManagerConnectionFactory
*
* @param connectionURI
* @param user
* @param password
* @return The Factory
*/
public static ConnectionFactory getConnFactory(String connectionURI,
String user, String password) {
ConnectionFactory driverManagerConnectionFactory = new DriverManagerConnectionFactory(
connectionURI, user, password);
return driverManagerConnectionFactory;
}
/**
*
* @return the DBCP Driver
*/
public static PoolingDriver getDBCPDriver() {
try {
Class.forName(DBCP_DRIVER);
} catch (ClassNotFoundException e) {
System.err.println("There was not able to find the driver class");
}
try {
driver = (PoolingDriver) DriverManager
.getDriver("jdbc:apache:commons:dbcp:");
} catch (SQLException e) {
System.err.println("There was an error: " + e.getMessage());
}
return driver;
}
/**
* Registry a Pool in the DBCP Driver
*
* @param poolName
* @param pool
*/
public static void registerPool(String poolName, ObjectPool pool) {
driver.registerPool(poolName, pool);
}
}
There, we have the registerJDBCDriver method that registers in the jdbc.driver property of the JVM the JDBC Driver’s name. Next, the getConnFactory method provides the functionality to get the factory of connections. This factory returns a javax.sql.Connection. Finally, the getDBCPDriver method provides us the ability to get the final driver which will handle and manage the connections to the database.
Using all this, the DBCP Library we can create an optimal pool to access through a DBCP Driver that wraps a JDBC Driver.
PoolableConnectionMain.java
package com.javacodegeeks.dbcp2;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.apache.commons.dbcp2.ConnectionFactory;
import org.apache.commons.dbcp2.PoolableConnection;
import org.apache.commons.dbcp2.PoolableConnectionFactory;
import org.apache.commons.dbcp2.PoolingDriver;
import org.apache.commons.dbcp2.Utils;
import org.apache.commons.pool2.ObjectPool;
import org.apache.commons.pool2.impl.GenericObjectPool;
/**
* @author Andres.Cespedes
* @version 1.0 $Date: 14/01/2015
* @since 1.7
*
*/
public class PoolableConnectionMain {
private static String URI = "jdbc:mysql://localhost:3307/test";
private static String user = "admin";
private static String password = "admin";
/**
* @param args
*/
public static void main(String[] args) {
// 1. Register the Driver to the jbdc.driver java property
PoolConnectionFactory
.registerJDBCDriver(PoolConnectionFactory.MYSQL_DRIVER);
// 2. Create the Connection Factory (DriverManagerConnectionFactory)
ConnectionFactory connectionFactory = PoolConnectionFactory
.getConnFactory(URI, user, password);
// 3. Instantiate the Factory of Pooled Objects
PoolableConnectionFactory poolfactory = new PoolableConnectionFactory(
connectionFactory, null);
// 4. Create the Pool with the PoolableConnection objects
ObjectPool connectionPool = new GenericObjectPool(
poolfactory);
// 5. Set the objectPool to enforces the association (prevent bugs)
poolfactory.setPool(connectionPool);
// 6. Get the Driver of the pool and register them
PoolingDriver dbcpDriver = PoolConnectionFactory.getDBCPDriver();
dbcpDriver.registerPool("dbcp-jcg-example", connectionPool);
// 7. Get a connection and use them
try {
Connection connJCG = DriverManager
.getConnection("jdbc:apache:commons:dbcp:dbcp-jcg-example");
// Print Some Properties.
System.out.println("Hashcode: " + connJCG.hashCode());
System.out.println("JDBC Driver: "
+ connJCG.getMetaData().getDriverName());
System.out.println("URI DB: " + connJCG.getMetaData().getURL());
// 8. Close the connection to return them to the pool. Instead of
// connJCG.close();
Utils.closeQuietly(connJCG);
} catch (SQLException e) {
System.err.println("There was an error: " + e.getMessage());
}
}
}
What we just did?
- We create the connection factory, this factory returns
javax.sql.connections. - Based on the above factory, we created a factory that makes pools, this factory can get PooledObjects
ObjectPool - Instantiate an
GenericObjectPool, which is itself the pool that will manage the driver. - Instantiate an DBCP driver and check the pool with a name, using a JNDI strategy.
- With this registered pool, we can obtain connections.
- We lookup the pool through the
DriverManager. - Finally, we obtain a connection from the pool and review some of their properties.
Enforces the pool to the Factory with
poolfactory.setPool(connectionPool) to prevent a Bug (NullPointerException) when close a connection.5. Running the Example
To test this out, we will run PoolableConnectionMain
The Output is
Hashcode: 215476380 JDBC Driver: MySQL Connector Java URI DB: jdbc:mysql://localhost:3307/test
6. Download the Eclipse Project
You can download the full source code of this example here PoolableConnectionExample
