DataSource Connection Pooling with JAVA

Use the following class.call init from your main method: 

DBResource.java

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class DBResource {

private DBResource() {
}
private static DBResource obj = new DBResource();

public static DBResource getInstance() {
return obj;
}
private static final Log tracer = LogFactory.getLog(DBResource.class);
private String url = null;
private String userid = null;
private String password = null;
private int poolInitialSize;
private int maxActive;
private int maxIdle;
private int minIdle;
private BasicDataSource dataSource;
private int maxStmts;
private String driver = null;
private String validationQuery;
private long maxWait;
private boolean testOnBorrow;
private boolean testOnReturn;


public void init(Properties properties) throws Exception {
try {
driver = properties.getProperty(ConfigKeys.DB_DRIVERCLASS);
tracer.debug("DBResource driver [" + driver + "]");
url = properties.getProperty(ConfigKeys.DB_URL);
tracer.debug(" url [" + url + "]");
userid = properties.getProperty((ConfigKeys.DB_USERNAME));
tracer.debug("userid [" + userid + "]");
password = properties.getProperty((ConfigKeys.DB_PASSWORD));
tracer.debug(" password [" + password + "]");
poolInitialSize = Integer.parseInt(properties.getProperty((ConfigKeys.POOL_INITIAL_SIZE)));
tracer.debug(" poolinitialsize [" + poolInitialSize + "]");
maxActive = Integer.parseInt(properties.getProperty((ConfigKeys.POOL_MAX_ACTIVE)));
tracer.debug(" maxactive [" + maxActive + "]");
maxIdle = Integer.parseInt(properties.getProperty((ConfigKeys.POOL_MAX_IDE)));
tracer.debug(" maxidle [" + maxIdle + "]");
minIdle = Integer.parseInt(properties.getProperty((ConfigKeys.POOL_MIN_IDLE)));
tracer.debug("minidle [" + minIdle + "]");
maxStmts = Integer.parseInt(properties.getProperty((ConfigKeys.POOL_MAX_STATEMENTS)));
tracer.debug(" maxstmts [" + maxStmts + "]");

maxWait = Integer.parseInt(properties.getProperty((ConfigKeys.POOL_MAX_WAIT)));
tracer.debug(" maxWait [" + maxWait + "]");
validationQuery = properties.getProperty((ConfigKeys.POOL_VALIDATION_QUERY));
tracer.debug(" validationQuery [" + validationQuery + "]");
testOnBorrow = Boolean.parseBoolean(properties.getProperty((ConfigKeys.POOL_TEST_ON_BORROW)));
tracer.debug("testOnBorrow [" + testOnBorrow + "]");
testOnReturn = Boolean.parseBoolean(properties.getProperty((ConfigKeys.POOL_TEST_ON_RETURN)));
tracer.debug("testOnReturn [" + testOnReturn + "]");
//createWriteServices();
tracer.info("Finished Initializing DBResource ");

} catch (Exception ex) {
tracer.error("Exception while initializing DBResource ");
throw ex;
}
}

public void start() throws Exception {
try {

dataSource = new BasicDataSource();
dataSource.setDriverClassName(driver);
dataSource.setUrl(url);
dataSource.setUsername(userid);
dataSource.setPassword(password);
dataSource.setInitialSize(poolInitialSize);
dataSource.setMaxActive(maxActive);
dataSource.setMaxIdle(maxIdle);
dataSource.setMinIdle(minIdle);
dataSource.setPoolPreparedStatements(true);
dataSource.setMaxOpenPreparedStatements(maxStmts);
/*validationQuery:-The SQL query that will be used to validate connections from this
pool before returning them to the caller. If specified, this query
MUST be an SQL SELECT statement that returns at least one row. */
dataSource.setValidationQuery(validationQuery);
/*maxWait:-The maximum number of milliseconds that the pool will wait
(when there are no available connections) for a connection to be
returned before throwing an exception, or -1 to wait indefinitely.*/
dataSource.setMaxWait(maxWait);
/* testOnBorrow:-The indication of whether objects will be validated before being
borrowed from the pool. If the object fails to validate, it will
be dropped from the pool, and we will attempt to borrow another.
NOTE - for a true value to have any effect, the validationQuery
parameter must be set to a non-null string*/
dataSource.setTestOnBorrow(testOnBorrow);
/*The indication of whether objects will be validated before being
returned to the pool.
NOTE - for a true value to have any effect, the validationQuery
parameter must be set to a non-null string. */
dataSource.setTestOnReturn(testOnReturn);
// this is to notify all adpters when DB resource is restrtting because
//of database problem or network problem
tracer.info(" Started dataSource and services ");
} catch (Exception e) {
tracer.error("Error in starting IntraDayStorageAdapter", e);
throw e;
}
}

/**
* Stops The DBResource. *
*/
public void stop() {
try {

dataSource.close();
dataSource = null;

tracer.debug("Stoped DBResource : ");
} catch (Exception e) {
tracer.error("Error while stoping intraday storage", e);
}
}

public BasicDataSource getResource() {
return dataSource;
}

/**
*
* @param resulSet
* @param statement
* @param connection
*/
public static void closeConnection(ResultSet resulSet, Statement statement, Connection connection) {
try {
if (resulSet != null) {
resulSet.close();
}
} catch (Exception ex) {
tracer.error("Error in close resulSet ", ex);
}
try {
if (statement != null) {
statement.close();
}
} catch (Exception ex) {
tracer.error("Error in statement close ", ex);
}
try {
if (connection != null && !connection.isClosed()) {
connection.close();
}
} catch (Exception ex) {
tracer.error("Error in connection close ", ex);
}
}
}

ConfigKey.java

/**
*
* @author prasobh
*/
public class ConfigKeys {
public static final String PERIOD_FOR_FETCHING_XML_FROM_FTP = "timer.period.millisec";
public final static String DB_DRIVERCLASS="db.driver";
public final static String DB_URL="db.url";
public final static String DB_USERNAME="db.username";
public final static String DB_PASSWORD="db.password";
public final static String POOL_INITIAL_SIZE="pool.initialsize";
public final static String POOL_MAX_ACTIVE="pool.maxactive";
public final static String POOL_MAX_IDE="pool.maxidle";
public final static String POOL_MIN_IDLE="pool.mindle";
public final static String POOL_MAX_STATEMENTS="pool.maxstatements";
public final static String POOL_MAX_WAIT="pool.maxwait";
public final static String POOL_VALIDATION_QUERY="pool.validation.query";
public final static String POOL_TEST_ON_BORROW="pool.testonborrow";
public final static String POOL_TEST_ON_RETURN="pool.testonreturn";

}

property files is as follows:

db.url=***
db.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
db.username=sa
db.password= ****
pool.initialsize=1
pool.maxactive=3
pool.maxidle=3
pool.mindle=3
pool.maxstatements=10
pool.maxwait=1000
pool.validation.query=SELECT 1
pool.testonborrow=true
pool.testonreturn=true

DBResource.getInstance().start(); from main method then,
DBResource.getInstance().getResource().getConnection(); will return connection object.

use following jar files:

1.commons-pool-1.5.4
2.commons-dbcp-1.4