commons

org.apache.commons.dbcp2.PoolableConnection Example

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 ?

  1. DBCP2 Apache Commons Library
  2. Pool2 Apache Commons Library
  3. Apache Commons Logging Library
  4. JDBC Driver (We use MYSQL 5 Driver)
  5. An IDE of our taste (We use Eclipse)
  6. JDK 1.7 (Due to DBCP2 runs on Java 7)
  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.

CaptureProject

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.
Tip
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

Download
You can download the full source code of this example here PoolableConnectionExample

Andres Cespedes

Andres is a Java Software Craftsman from Medellin Colombia, who strongly develops on DevOps practices, RESTful Web Services, Continuous integration and delivery. Andres is working to improve software process and modernizing software culture on Colombia.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
Back to top button