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 underlyingPreparedStatementtoo.
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
You can download the full source code of this example here: PoolingConnectionExample.zip
