Connection Pooling - Apache DBCP

Properties File: db.properties

#Oracle Database -  ojdbc14.jar - http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

#db.connection.driver = oracle.jdbc.OracleDriver

#db.connection.url = jdbc:oracle:thin:@//localhost:1521/[sid]

#MS-SQL Database - jtds.jar - http://jtds.sourceforge.net/

#db.connection.driver = net.sourceforge.jtds.jdbc.Driver

#db.connection.url = jdbc:jtds.sqlserver://localhost:1433/[database]

#MySQL Database - Connector/J - http://dev.mysql.com/downloads/connector/j/

#db.connection.driver = com.mysql.jdbc.Driver

#db.connection.url = jdbc:mysql://localhost:3306/[database]

#PostgreSQL Database - http://jdbc.postgresql.org/download.html

#db.connection.driver = org.postgresql..Driver
#db.connection.url = jdbc:postgresql://localhost:5432/[database]


db.connection.username = [username]
db.connection.password = [password]
db.connection.defaultpool = dbpool

db.connection.max.active = 5
db.connection.max.idle = 5
db.connection.max.wait = 10000



class: DBManager.java

package com.pd.db;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.pool.impl.GenericObjectPool;

/**
 * @author PD
 *
 */
public class DBManager {

private static ConnectionFactory connectionFactory = null;

private static int DEFAULT_MAX_ACTIVE = 30;
private static int DEFAULT_MAX_IDLE = 30;
private static int DEFAULT_MAX_WAIT = 10000;

private static Properties props = null;


public static void load(Properties props) throws Exception {
DBManager.props = props;
//
// Load JDBC Driver class.
//
String driverName = getProperty("db.connection.driver");

try {
Class.forName(driverName);
} catch (ClassNotFoundException e) {
e.printStackTrace();
throw new Exception(
"Could not find suitable classes to load driver "
+ driverName, e);
}
//
        // First, we'll need a ObjectPool that serves as the
        // actual pool of connections.
        //
        // We'll use a GenericObjectPool instance, although
        // any ObjectPool implementation will suffice.
        //
GenericObjectPool connectionPool = new GenericObjectPool(null);
connectionPool.setMaxActive(Integer.parseInt(getProperty("db.connection.max.active", String.valueOf(DEFAULT_MAX_ACTIVE))));
connectionPool.setMaxIdle(Integer.parseInt(getProperty("db.connection.max.idle", String.valueOf(DEFAULT_MAX_IDLE))));
connectionPool.setMaxWait(Integer.parseInt(getProperty("db.connection.max.wait", String.valueOf(DEFAULT_MAX_WAIT))));
//
        // Next, we'll create a ConnectionFactory that the
        // pool will use to create Connections.
        // We'll use the DriverManagerConnectionFactory,
        // using the connect string passed in the command line
        // arguments.
        //
connectionFactory = new DriverManagerConnectionFactory(getProperty("db.connection.url"), getProperty("db.connection.username"), getProperty("db.connection.password"));

PoolableConnectionFactory poolableConnectionFactory = new PoolableConnectionFactory(connectionFactory, connectionPool, null, null, false, true);

//
        // Finally, we create the PoolingDriver itself...
        //
        Class.forName("org.apache.commons.dbcp.PoolingDriver");
        PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");

        driver.registerPool(getProperty("db.connection.defaultpool", "dbpool"), connectionPool);

fireTestQuery();
}

/**
* Call this to get the Connection objects in each DAO
*
* @return java.sql.Connection
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
Connection conn = null;
try {
if (connectionFactory != null) {

conn = connectionFactory.createConnection();
}
} catch (SQLException e) {
System.out.println("Connection already in use. Close connection and creat new Connection");
e.printStackTrace();
throw e;
}
return conn;
}

private static void fireTestQuery() throws Exception {

Connection conn = null;
PreparedStatement statement = null;
ResultSet results = null;
try {
conn = getConnection();
statement = conn.prepareStatement(getProperty("db.connection.query.test", "SELECT now()"));
results = statement.executeQuery();
if (!results.next())
throw new Exception("Not connected ...");
else {
System.out.println("TEST QUERY RESULTS No of rows :: "
+ results.getRow());
}
} catch (SQLException sqle) {

throw new Exception(
"Test Query failed during start up because of SQLException ",
sqle);

} finally {
try {
if (results != null) {
results.close();
results = null;
}
if (statement != null) {
statement.close();
statement = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException ignore) {

}
}
}

private static String getProperty(String key) throws Exception {
return getProperty(key, null);
}

private static String getProperty(String key, String defaultValue) throws Exception {
String str = props.getProperty(key);
if (str == null || str.trim().length() == 0) {
if(defaultValue != null) {
System.out.println("Using default value " + defaultValue + " due to missing entry - " + key);
return defaultValue;
} else {
System.err.println("DBManager :: Could not load driver :: Missing Entry - " + key);
throw new Exception("DBManager :: Could not load driver :: Missing Entry - " + key);
}
}
return str;
}

public static void main(String[] args) {
Properties props = new Properties();
try {
props.load(new FileInputStream("db.properties"));
load(props);
} catch (Exception e) {
e.printStackTrace();
}
}
}