Connection Pooling in JDBC using Apache Commons DBCP
In this Example We will implement the connection pooling in JDBC using Apache Commons DBCP. Now what is Connection Pool? Connection Pool is a cache of database connections so that connection can be reused when future requests to the database are required. Opening a database connection for each user is a wastage of resource and costly. In connection pooling when a connection is created, it is placed in the connection pool and is used again so there is no need to create a new connection. In connection pooling if all connection is used then a new connection is created and added to the pool
Connection Pooling in JDBC using Apache Commons DBCP Example:-
Project structure:-
Following jar’s must be in classpath
com.mysql.jdbc_5.1.5.jar
commons-dbcp2-2.1.1.jar
commons-pool2-2.4.2.jar
commons-logging-1.2.jar
package com.javaartifacts; import java.sql.Connection; import javax.sql.DataSource; import org.apache.commons.dbcp2.BasicDataSource; public class TestConnection { public static DataSource getDataSource(){ BasicDataSource ds = new BasicDataSource(); ds.setDriverClassName("com.mysql.jdbc.Driver"); ds.setUsername("root"); ds.setPassword(""); ds.setUrl("jdbc:mysql://localhost:3306/test"); //optional setting --- dbcp will use default ds.setMinIdle(5); ds.setMaxIdle(20); ds.setMaxOpenPreparedStatements(180); ds.setMaxTotal(10); return ds; } public static void main(String[] args) { System.out.println("Connection Pooling in JDBC using Apache Commons DBCP"); Connection con = null; try { con = getDataSource().getConnection(); if (con != null) { System.out.println("connection created successfully using Apache commons"); } else { System.out.println(" unable to create connection"); } } catch (Exception e) { e.printStackTrace(); } finally { try { if (con != null){ con.close(); } } catch (Exception ex) { ex.printStackTrace(); } } } }
output:-
Connection Pooling in JDBC using Apache Commons DBCP connection created successfully using Apache commons
There are many other’s API for connection pooling like C3P0, Proxool, BoneCP.