PoolingConnection

org.apache.commons.dbcp2.PoolingConnection Example

In this example, we shall show you how to use PoolingConnection of Apache commons dbcp2 library. DBCP is a part of Apache common components and intended for database connection pooling.

PoolingConnection is a delegating connection that pools java.sql.PreparedStatement. The prepared statement will be either created or returned from the pool by PoolingConnection as shown in example below. Clients of this class needs to setorg.apache.commons.pool2.KeyedObjectPool implementation. This object is used by PoolingConnection to create and manage underlying PreparedStatement pool. By giving a custom implementation of org.apache.commons.pool2.KeyedObjectPool user can control creation and pooling behavior of underlying pool too.For this example we will be using org.apache.commons.pool2.impl.GenericKeyedObjectPool.

For this example, we will be using Microsoft SQL Server as the Database Management System. We will be creating a table ‘Users’ in this database using SQL query mentioned in code example below. Afterwards PoolingConnection will be used to create and pool java.sql.PreparedStatement for selecting and inserting data in this table.

1. PoolingConnection Constructors

PoolingConnection supports one constructor

    • PoolingConnection(Connection c)

Connection c is the underlying connection on which all statement execution will be delegated.

2. PoolingConnection common methods

Following are some commonly used methods from PoolingConnection

  • void setStatementPool(KeyedObjectPool pool) : This method sets statement pool that will be used to pool PreparedStatements.
  • void close() : Close and free all PreparedStatements or CallableStatements from the pool, and close the underlying connection.
  • PreparedStatement prepareStatement(String sql) : This method creates or returns a prepared statement from the statement pool.
  • CallableStatement prepareCall(String sql) : This method creates or returns a prepared statement from the statement pool.
  • PooledObject makeObject(PStmtKey key) : This method creates PoolablePreparedStatements or PoolableCallableStatements depending upon stmtType field in passed in key.
  • void destroyObject(PStmtKey key,PooledObject p) : This method destroys passed in pooled statement and closes underlying PreparedStatement too.

3. Example of using PoolingConnection in java

PoolingConnectionExample.java

package com.javacodegeeks;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.dbcp2.DelegatingPreparedStatement;
import org.apache.commons.dbcp2.PStmtKey;
import org.apache.commons.dbcp2.PoolingConnection;
import org.apache.commons.pool2.KeyedObjectPool;
import org.apache.commons.pool2.impl.GenericKeyedObjectPool;
import org.apache.commons.pool2.impl.GenericKeyedObjectPoolConfig;

/**
 * Create Table Query for MS SQL Server
 * CREATE TABLE Users(id varchar(255),name varchar(255),passion varchar(255));
 *
 */

public class PoolingConnectionExample {

	private static PoolingConnection poolingConnection;
	static Connection connection = null;

	public static void main(String... s) throws SQLException,
			ClassNotFoundException, InstantiationException,
			IllegalAccessException {
		createPoolingConnection();
		insertQuery("1", "tom", "chasing jerry");
		insertQuery("2", "jerry", "eating cheese");
		insertQuery("3", "jcg", "helping you learn faster");
		selectQuery();
		connection.close();

	}

	private static void createPoolingConnection()
			throws ClassNotFoundException, SQLException {
		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		connection = DriverManager
				.getConnection("jdbc:sqlserver://localhost;database=TestDB;user=enter username here;password=enter database password");

		poolingConnection = new PoolingConnection(connection);

		GenericKeyedObjectPoolConfig config = new GenericKeyedObjectPoolConfig();
		config.setMaxTotalPerKey(-1);
		config.setBlockWhenExhausted(false);
		config.setMaxWaitMillis(0);
		config.setMaxIdlePerKey(1);
		config.setMaxTotal(10);

		KeyedObjectPool stmtPool = new GenericKeyedObjectPool(
				poolingConnection, config);

		poolingConnection.setStatementPool(stmtPool);
	}

	private static void insertQuery(String id, String name, String passion)
			throws SQLException {
		String sql = "INSERT INTO Users VALUES(?,?,?)";
		PreparedStatement statement = poolingConnection.prepareStatement(sql);
		statement.setString(1, id);
		statement.setString(2, name);
		statement.setString(3, passion);
		statement.execute();
		statement.close();

	}

	private static void selectQuery() throws SQLException {
		PreparedStatement statement = null;
		System.out
				.println("*********************Selecting data************************");
		System.out.println();

		try {
			statement = poolingConnection
					.prepareStatement("SELECT * FROM Users");
			ResultSet rs = statement.executeQuery();

			while (rs.next()) {
				String s = String.format("ID:%s Username:%s Passion:%s",
						rs.getString("id"), rs.getString("name"),
						rs.getString("passion"));
				System.out.println(s);
				System.out.println();
			}
			System.out.println("");
			System.out
					.println("*******************************************************");
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			if (statement != null)
				statement.close();
			if (connection != null)
				connection.close();
		}
	}
}

4. Output

*********************Selecting data************************

ID:1 Username:tom Passion:chasing jerry

ID:2 Username:jerry Passion:eating cheese

ID:3 Username:jcg Passion:helping you learn faster


*******************************************************

5. Download source code

This was an example of BasicDataSource of Apache commons dbcp module

Download
You can download the full source code of this example here: PoolingConnectionExample.zip

Arpit Gautam

Arpit has graduated from Computer Science and Engineering Department from the Institute of Technology and Management Gurgaon. He is working in enterprise product development since a decade and worked on desktop, mobile and server side applications using java. During his studies, he participated in various coding contests and technical paper presentations. He is working as a lead Software Engineer in Workforce Management domain where he is mainly involved with projects based on Java application and C++ system programming. He is curious about writing agile code which can adapt as business changes. He likes to experiment with open source technologies and java tech stack in his spare time.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments
Back to top button