C3p0 Connection Pooling Example
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 ARM
s 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>
3. Download the Source Code
Here, we demonstrated how we can configure C3P0 for connection pooling in our applications.
You can download the source code of this example here: C3P0Example.zip