arttuladhar
12/21/2012 - 5:01 AM

Java Connection Pooling using Commons Library

Java Connection Pooling using Commons Library

package helper;
 
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
 
import org.apache.commons.dbcp.BasicDataSource;
import org.eclipse.jdt.core.compiler.InvalidInputException;
 
public class MyConnectionPool {
  
  private static final String DRIVER = ".jdbc.driver.classname";
	private static final String URL = ".jdbc.url";
	private static final String USERNAME = ".jdbc.username";
	private static final String PASSWORD = ".jdbc.password";
	
	//Hashmap for storing the DataSources
	private static Map<String, BasicDataSource> dataSourceMap = new HashMap<String,BasicDataSource>();
	
	
	public static Connection getConnection(String db) throws InvalidInputException {
		Connection conn = null;
		
			BasicDataSource dataSource = dataSourceMap.get(db);
			
			if ( dataSource == null ) {
				// Creating New Data Source
				
				/*
				 * For Creating New Connection We need Driver, URL, Username and Password
				 * I have used properties files to store the property values. 
				 */
				Properties prop = new Properties();
				try {
					prop.load(new FileInputStream("database.properties"));
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				
				
				String driver = prop.getProperty(db + DRIVER);
				String url = prop.getProperty(db + URL);
				String username = prop.getProperty(db + USERNAME);
				String password = prop.getProperty(db + PASSWORD);
				
				if ( ( null == driver) || ( null == url ) || (null == username) ) {
					//Error
					throw new InvalidInputException("Invalid Input" + db);
				}
				
				dataSource = new BasicDataSource();
				dataSource.setDriverClassName(driver);
				dataSource.setUrl(url);
				dataSource.setUsername(username);
				dataSource.setPassword(password);
				
				/*
				 * Validating If the Connection setup has been done property
				 * ORACLE QUERY HAS BEEN USED, FOR OTHER DATABASE CHANGE THE QUERY
				 */
				
				
				String validationQuery = "SELECT SYSDATE FROM DUAL";		//ORACLE Query
				
				if ( driver.contains("sqlserver")) {
					validationQuery = "SELECT CURRENT_TIMESTAMP";
				}
				
				dataSource.setTestOnBorrow(false);
				dataSource.setTestWhileIdle(true);
				dataSource.setNumTestsPerEvictionRun(dataSource.getMaxActive());
				dataSource.setValidationQuery(validationQuery);
				dataSource.setValidationQueryTimeout(1);
				dataSource.setMinEvictableIdleTimeMillis(30*60*1000);
				dataSource.setTimeBetweenEvictionRunsMillis(30*60*1000);
				
				
				dataSourceMap.put(db, dataSource);
			}
			
			try {
				conn = dataSource.getConnection();
			} catch (SQLException e) {
				System.err.println( e.getMessage() );
				
			}
			return conn;
			
		}
	
	}