Apache Commons Database Connection Pooling (DBCP)
20 Dec 2012Connection Pooling provides efficient way of using database connections. It uses shared pool of connections to perform database activities. With a connection pool, user doesn’t need to get a connection by itself, use a shared pool of connection.
Apache Commons DBCP provide efficient way to perform this task. his Commons package provides an opportunity to coordinate the efforts required to create and maintain an efficient, feature-rich package under the ASF license.
Apache commons provides framework for connection pooling code base, which makes it really easy to manage the pool.
http://commons.apache.org/dbcp/
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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; | |
| } | |
| } |