We will be writing a series of examples demonstrating how to configure Connection Pooling in your Java Application using different libraries like DBCP, C3P0, HikariCP etc. In this example, we shall demonstrate how to achieve connection pooling using the Apache DBCP library.
1. Why use Connection Pooling?
To present dynamic data to the users, an application typically connects to a Database and fetches data from there. However, acquiring physical connection every-time and closing them is a time-expensive process. This lag gets exaggerated if the number of connections being acquired and released occur with every request. To address this problem we use connection pooling.
Connection Pooling addresses this problem by creating a pool of connections and storing them in an Object Pool. Whenever the client requests for some data, an idle connection object is retrieved from the connection pool and the database is queried against this connection. If a connection object is not available and the maximum pool limit is not reached a new connection object is returned. If there are no idle connection objects available and the maximum open connection limit for the pool has been reached, the request is queued up. Once any of the requests releases the connection the requests in the queue can use that object.
That is all on the theory side, let’s start with the project set-up and implementation.
2. Project Set-Up
Create a simple Maven Project in Eclipse IDE by selecting the Skip Archetype Selection checkbox from the New Maven Project Pop-up. We are using the below pom.xml to manage the dependencies for DBCP and the JDBC Driver.
pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.jcg.examples.dbcpExample</groupId> <artifactId>DBCPConnectionPoolingExample</artifactId> <version>0.0.1-SNAPSHOT</version> <dependencies> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-dbcp2</artifactId> <version>2.1</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.37</version> </dependency> </dependencies> <build> <finalName>Crossover-BackEnd</finalName> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-compiler-plugin</artifactId> <configuration> <source>1.7</source> <target>1.7</target> </configuration> </plugin> </plugins> </build> </project>
In this pom.xml, we are using JDK 7 so that we can use Automatic Resource Management for the JDBC connections.
Next step is creating a table. As you can already see, we are using the MySql Database server for this example. Here’s the script for the table we shall be using.
CREATE TABLE `account` ( `Account_Number` bigint(20) NOT NULL, `Account_Type` varchar(45) DEFAULT NULL, `Person_name` varchar(50) DEFAULT NULL, PRIMARY KEY (`Account_Number`)
Eclipse will download the required JAR files and add the dependencies in the project classpath. Now that the project is setup and dependencies imported, we can begin writing the actual code.
3. Implementation
Let’s implement a basic DBCP Datasource for our application.
DataBaseUtility.java
package com.jcg.examples;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.dbcp2.BasicDataSource;
public class DataBaseUtility
{
private static BasicDataSource dataSource;
private static BasicDataSource getDataSource()
{
if (dataSource == null)
{
BasicDataSource ds = new BasicDataSource();
ds.setUrl("jdbc:mysql://localhost/test");
ds.setUsername("root");
ds.setPassword("password");
ds.setMinIdle(5);
ds.setMaxIdle(10);
ds.setMaxOpenPreparedStatements(100);
dataSource = ds;
}
return dataSource;
}
public static void main(String[] args) throws SQLException
{
try (BasicDataSource dataSource = DataBaseUtility.getDataSource();
Connection connection = dataSource.getConnection();
PreparedStatement pstmt = connection.prepareStatement("SELECT * FROM account");)
{
System.out.println("The Connection Object is of Class: "+connection.getClass());
try (ResultSet resultSet = pstmt.executeQuery();)
{
while (resultSet.next())
{
System.out.println(resultSet.getString(1) + "," + resultSet.getString(2) + "," + resultSet.getString(3));
}
}
catch (Exception e)
{
connection.rollback();
e.printStackTrace();
}
}
}
}
We create a simple datasource of the type : org.apache.commons.dbcp2.BasicDataSource. The connection parameters like URL, username and password are the compulsory fields which we need to provide to initiate the Datasource.
Apart from these fields we have some optional fields in the BasicDatasource which we can use for finer control over it. Here’s their brief description:
minIdle : Sets the minimum number of connection object that are to be kept alive in the pool.
maxIdle : Sets the maximum number of Idle connections in the pool. If the total number of connections in the pool exceeds this number, the extra connections are released as soon as they are returned to the connection pool.
maxOpenPreparedStatements The maximum number of the java.sql.PreparedStatement that can be cached. The PreparedStatement cache is associated with each connection object. When we create another similar PreparedStatement object, a cached preparedstatement object is returned. This reduces the time spent by the Database server in parsing the new SQL Query again thus improving the overall application throughput. Similarity of the preparedstatement is determined by the same query. If we are passing the query parameters via string concatenation, that makes the query different each time and cache is pretty much useless. To use the benefit of this cache, we need to use parameterized queries.
As you can see, we are using the ARM blocks so we need not explicitly close the connection, PreparedStatement and ResultSet objects. However, when the JVM makes call to the close method of the connection object, not the actual connection to the database is closed. This is because the connection object returned by the BasicDataSource is a proxy jdbc connection object which references the original Connection Object. The close call simply invalidates the proxy object and returns it to the connection pool. Now the next request will simply acquire this connection object and execute queries without the need to open actual database connections.
Here’s a sample output of the above program:
The Connection Object is of Class: class org.apache.commons.dbcp2.PoolingDataSource$PoolGuardConnectionWrapper 123456,Savings,ABC 433445,Current,XYZ
As you can see in the output, it is not the actual java.sql.Connection object but a proxy object.
DBCP integrates smoothly with Spring Container as well so that it can be used seamlessly in Spring Based Applications. Here’s a basic configuration for the datasource bean :
<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost:3306/test" /> <property name="username" value="root" /> <property name="password" value="password" /> </bean>
4. Conclusion
Here, we understood what is connection pooling and how we can implement the same using the DBCP library.
You can download the source code of this example here: DCPConnectionPoolExample.zip
