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.