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.