Connection Pooling

• In Software Development, a connection pooling is a concept of making cache of DB connections.
• Those cached connections can be reused in future whenever required.
• Connection Pooling is mainly used to increase the performance of the system/application.
• Actually making connection to database is a heavy operation.
• So, its better to reuse the existing connection.
• When there are frequent raping db operation are performed then it matters a lot to use Connection Pooling.
• Connection Pooling also reduced the waiting time for the user.
• In Enterprise and Web Application the connection pooling is maintained by the Application Server.
• Also local small application can use connection pooling libraries which are freely available.

1) Apache Commons DBCP library by Apache
public class DBCPConnPool {

    private static BasicDataSource basicDataSource = new BasicDataSource();
    static {
        basicDataSource.setUrl("jdbc:h2:mem:test");
        basicDataSource.setUsername("testuser");
        basicDataSource.setPassword("testpassword");
        basicDataSource.setMinIdle(2);
        basicDataSource.setMaxIdle(11);
        basicDataSource.setMaxOpenPreparedStatements(100);
    }
    
    public static Connection getConnection() throws SQLException {
        return basicDataSource.getConnection();
    }
    private DBCPConnPool(){ }
}

2) HikariCP Library by Brett Wooldridge
public class HikariCPConnPool {
    
    private static HikariConfig hikariConfig = new HikariConfig();
    private static HikariDataSource ds;
    
    static {
        hikariConfig.setJdbcUrl("jdbc:h2:mem:mydb");
        hikariConfig.setUsername("testuser");
        hikariConfig.setPassword("testpassword");
        hikariConfig.addDataSourceProperty("cachePrepStmts", "true");
        hikariConfig.addDataSourceProperty("prepStmtCacheSize", "190");
        hikariConfig.addDataSourceProperty("prepStmtCacheSqlLimit", "1024");
        ds = new HikariDataSource(config);
    }
    
    public static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
    private HikariCPConnPool(){}
}
 
3) C3PO Library create by Steve Waldman
public class C3poConnPool {
    private static ComboPooledDataSource c3po = new ComboPooledDataSource();
    static {
        try {
            c3po.setDriverClass("org.h2.Driver");
            c3po.setJdbcUrl("jdbc:h2:mem:mydb");
            c3po.setUser("testuser");
            c3po.setPassword("testpassword");
        } catch (Exception e) {
            System.out.println("Error while Preparing ComboPooledDataSource");
        }
    }
    public static Connection getConnection() throws SQLException {
        return c3po.getConnection();
    }
    private C3poConnPool(){}
}