Connection Pooling Example in Java

By Atul Rai | Last Updated: July 15, 2018 Previous       Next

On this page, you will learn how to create JDBC connection pool using Java programming language. To create a connection pool in our application, Sun Microsystem has given an interface DataSource

By using interface DataSource there are many third party API developed. For example.

1- Apache has been developed BasicDataSource.

2- Mchange-cp30 vendor developed ComboPooledDataSource.

3- Oracle WebLogic has been given WebLogicDataSource.

In my example I have used Oracle Universal Connection Pool and Apache DBCP API. You can download these jar file from the below link.

ucp.jar ——————————————— click here

dbcp.jar ——————————————- click here

You can also add the DBCP dependency in your pom.xml file if you are using Maven.

<dependency>
    <groupId>commons-dbcp</groupId>
    <artifactId>commons-dbcp</artifactId>
    <version>1.4</version>
</dependency>

What is Connection Pooling?

Connection pooling is a mechanism to create and maintain the JDBC connection object.
Connection pools are used to enhance the performance of an application and executing commands on a database.
A new connection object is created only when there are no connection objects available to reuse.
This technique can improve the overall performance of the application. Wikipedia

Using Oracle UCP

This example will help you to create the connection pool using oracle universal connection API.

Connection Properties

Set the connection properties of the data source.

dataSource.setConnectionFactoryClassName("oracle.jdbc.OracleDriver");
dataSource.setURL("jdbc:oracle:thin:@localhost:1521:xe");
dataSource.setUser("user_name");
dataSource.setPassword("password");

Set Pool Properties

dataSource.setInitialPoolSize(10) – The pool manager will be initiated with 10 physical connections.

dataSource.setMinPoolSize(20) – The pool maintenance thread will make sure that there are 20 physical connections available.

dataSource.setMaxPoolSize(50) – The pool maintenance thread will check that there are no more than 50 physical connections available.

dataSource.setPropertyCycle(20) – The pool maintenance thread will wake up and check the pool every 20 seconds.

dataSource.setMaxIdleTime(300) – The pool maintenance thread will remove physical connections that are inactive for more than 300 seconds.

dataSource.getAvailableConnectionsCount() – Checking the number of available connections.

dataSource.getBorrowedConnectionsCount() – Checking the number of borrowed connections.

Check the full example.

package org.websparrow.connection;

import java.sql.Connection;

import oracle.ucp.jdbc.PoolDataSource;
import oracle.ucp.jdbc.PoolDataSourceFactory;

public class OracleUCP {

	public static void main(String[] args) {

		try {

			PoolDataSource dataSource = PoolDataSourceFactory.getPoolDataSource();

			// dataSource.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
			dataSource.setConnectionFactoryClassName("oracle.jdbc.OracleDriver");
			dataSource.setURL("jdbc:oracle:thin:@localhost:1521:xe");
			dataSource.setUser("user_name");
			dataSource.setPassword("password");

			dataSource.setInitialPoolSize(10);
			dataSource.setMinPoolSize(20);
			dataSource.setMaxPoolSize(50);
			dataSource.setPropertyCycle(20);
			dataSource.setMaxIdleTime(300);

			for (int i = 1; i <= 1000; i++) {

				Connection conn = dataSource.getConnection();
				System.out.println(conn + " : " + i);

				int avlConnCount = dataSource.getAvailableConnectionsCount();
				System.out.println("Available connections: " + avlConnCount);
				int brwConnCount = dataSource.getBorrowedConnectionsCount();
				System.out.println("Borrowed connections: " + brwConnCount);

				conn.close();
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

Using Apache DBCP

Apache BasicDataSource class implements the DataSource interface for the implementation of connection pool.

Create the object of BasicDataSource class.

BasicDataSource bds = new BasicDataSource();

Connection Properties

Set the connection properties of the data source.

bds.setDriverClassName("oracle.jdbc.OracleDriver");
bds.setUrl("jdbc:oracle:thin:@localhost:1521:xe");
bds.setUsername("user_name");
bds.setPassword("password");

Set Pool Properties

bds.setMaxActive(10) -Sets the maximum number of active connections that can be allocated at the same time.

bds.setMaxIdle(5) -Sets the maximum number of connections that can remain idle in the pool.

bds.setMaxWait(1000 * 5) -Sets the maximum wait time in miliseconds.

Check the full example.

package org.websparrow.connection;

import java.sql.Connection;

import org.apache.commons.dbcp.BasicDataSource;

public class ApacheDBCP {
	public static void main(String[] args) {

		BasicDataSource bds = new BasicDataSource();

		bds.setDriverClassName("oracle.jdbc.OracleDriver");
		bds.setUrl("jdbc:oracle:thin:@localhost:1521:xe");
		bds.setUsername("user_name");
		bds.setPassword("password");

		bds.setMaxActive(10);
		bds.setMaxIdle(5);
		bds.setMaxWait(1000 * 5);

		try {
			for (int i = 1; i <= 200; i++) {

				Connection conn = bds.getConnection();
				System.out.println(conn + " : " + i);

				conn.close();
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

	}
}