C3p0 Connection Pooling Example

Chandan SinghMarch 11th, 2016Last Updated: March 11th, 2016

0 1,253 3 minutes read

In the previous example, we understood the concept of connection pooling and how we can use it to improve the performance and throughput of the application. In that example, we demonstrated how we can implement connection pooling using the Apache DBCP. In this example, we shall be using the C3P0 connection library.

1. 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 C3P0 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.c3p0Example</groupId>
	<artifactId>C3P0Example</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<dependencies>

		<dependency>
			<groupId>com.mchange</groupId>
			<artifactId>c3p0</artifactId>
			<version>0.9.5.1</version>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.37</version>
		</dependency>

	</dependencies>
	
		<build>
		<finalName>C3P0 Example</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>

Eclipse will download the required JAR files and add the dependencies in the project classpath.

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`)

Now that the project is setup and dependencies imported, we can begin writing the actual code.

2. Implementation

Let’s implement a basic C3P0 Datasource for our application.

DatabaseUtility.java

package com.jcg.examples;


import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mchange.v2.c3p0.ComboPooledDataSource;


public class DatabaseUtility
{
	public static ComboPooledDataSource getDataSource() throws PropertyVetoException
	{
		ComboPooledDataSource cpds = new ComboPooledDataSource();
		cpds.setJdbcUrl("jdbc:mysql://localhost/test");
		cpds.setUser("root");
		cpds.setPassword("password");

		// Optional Settings
cpds.setInitialPoolSize(5);
		cpds.setMinPoolSize(5);
		cpds.setAcquireIncrement(5);
		cpds.setMaxPoolSize(20);
cpds.setMaxStatements(100);

		return cpds;
	}

	public static void main(String[] args) throws SQLException
	{
		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet resultSet = null;
		try
		{
			ComboPooledDataSource dataSource = DatabaseUtility.getDataSource();
			connection = dataSource.getConnection();
			pstmt = connection.prepareStatement("SELECT * FROM account");
			
			System.out.println("The Connection Object is of Class: " + connection.getClass());
			
			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 : com.mchange.v2.c3p0.ComboPooledDataSource. The connection parameters like URL, username and password are the necessary fields which we need to provide to initiate the Datasource.
Apart from these fields we have some optional fields in the ComboPooledDataSource which we can use for finer control over it. Here’s their brief description:

InitialPoolSize : The number of connection objects in the pool when the Datasource is setup. The numbers may increase as the demand increases.
MinPoolSize : Minimum number of connections to be kept alive in the pool at any given point.
AcquireIncrement : The number of connections to be added to pool when there are no idle available connections in it.
MaxPoolSize : The maximum number of connections, idle or busy, that can be present in the pool.
MaxStatements : The maximum number of prepared statements that can be cached per connection.

As described in the previous example, the connection object that we get from the C3P0 Datasource is not the actual java.sql.Connection object but a proxy object. We have printed the class of the output proxy object.

Attentive readers might notice that we have not used ARMs in this examples despite using JDK 1.7. This is because for the ARM to work the resources class should implement the AutoCloseable interface. The ComboPooledDataSource class does not implement this interface, and as such we cannot use it in the ARM block.

Here’s a sample output of the above program:

The Connection Object is of Class: class com.mchange.v2.c3p0.impl.NewProxyConnection
123456,Savings,ABC
433445,Current,XYZ

C3P0 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="com.mchange.v2.c3p0.ComboPooledDataSource"
		destroy-method="close">
		<property name="driverClass" value="com.mysql.jdbc.Driver" />
		<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/test" />
		<property name="user" value="root" />
		<property name="password" value="password" />
</bean>