How to use c3p0 database connection pooling library with Hibernate/JPA

In this tutorial, you will learn how to configure c3p0 - a popular database connection library for Java multi-threaded database applications - in a Hibernate/JPA project.

Hibernate has built-in connection pool but it is not for production use, as state in official Hibernate document and also in the logging information when you start a Hibernate application:

So it is recommended to use a third-party library like c3p0  which is a database connection pooling library for production use, and Hibernate works very well with c3p0.

Basically, it’s simple and easy to add database connection pooling capability to your existing project with c3p0: just add c3p0 dependency to the project’s Maven pom.xml file and specify some properties in Hibernate/JPA configuration file.

1. Add c3p0 dependency to Maven pom.xml file

The first step to use c3p0 is to add its dependency to your project’s pom.xml file, with the following XML code:

<dependency>
	<groupId>org.hibernate</groupId>
	<artifactId>hibernate-c3p0</artifactId>
	<version>5.3.6.Final</version>
</dependency>

You can see this dependency is provided by Hibernate. Note that the version should be compatible with Hibernate version you are using in your project. Save the file, and you can see there are 3 JAR files have been added to the project:

hibernate-c3p0-5.3.6.Final.jar
c3p0-0.9.5.2.jar
mchange-commons-java-0.2.11.jar

That means if you don’t use Maven, you can find, download and add these JAR files to your project.

2. Configure c3p0 properties with Hibernate

c3p0 offers various configuration properties, but basically you need to use the following ones:

• hibernate.c3p0.min_size: the minimum number of connections maintained in the pool at any given time.
• hibernate.c3p0.max_size: the maximum number of connections maintained in the pool at any given time.
• hibernate.c3p0.timeout: the number of seconds an idle connection is kept in the pool. If a connection is idle longer than this timeout value, then it will be replaced by a new one.
• hibernate.c3p0.max_statements: the total number of Statements cached for all connections.

To see the complete list of configuration properties in c3p0, see Configuration Properties.

Then your Hibernate configuration file (hibernate.cfg.xml) would look like this:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
		"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
		"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
	<session-factory>
		<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
		<property name="hibernate.connection.password">password</property>
		<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/yourdbname</property>
		<property name="hibernate.connection.username">user</property>
		<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
		
		<property name="hibernate.c3p0.min_size">5</property>
		<property name="hibernate.c3p0.max_size">200</property>
		<property name="hibernate.c3p0.timeout">180</property>
		<property name="hibernate.c3p0.max_statements">50</property>
		
	</session-factory>
</hibernate-configuration>

In case you use Hibernate with JPA, then the JPA configuration file (persistence.xml) would look like this:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
	xmlns="http://xmlns.jcp.org/xml/ns/persistence" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence 
	http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
	
	<persistence-unit name="YourUnitName">
		<properties>
			<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
			<property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/yourdbname" />
			<property name="javax.persistence.jdbc.user" value="username" />
			<property name="javax.persistence.jdbc.password" value="password" />
			<property name="hibernate.show_sql" value="true" />
			<property name="hibernate.format_sql" value="true" />
			<property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />
			
			<property name="hibernate.c3p0.min_size" value="5" />
			<property name="hibernate.c3p0.max_size" value="20" />
			<property name="hibernate.c3p0.timeout" value="300" />
			<property name="hibernate.c3p0.max_statements" value="50" />
			<property name="hibernate.c3p0.idle_test_period" value="120" />
		</properties>
	</persistence-unit>

</persistence>

And you're done.

3. Check if c3p0 is running

You can look at the Hibernate’s log to check if c3p0 is initialized and running, as shown in the following screenshot:

If you use MySQL Workbench, click Server > Client Connections and you can see there are some sleep connections to your database:

That’s how to configure c3p0 database connection pooling library with Hibernate/JPA.

Other Hibernate Tutorials:

• How to configure Proxool with Hibernate/JPA
• Java Hibernate JPA Annotations Tutorial for Beginners
• Hibernate Hello World Tutorial for Beginners with Eclipse and MySQL
• Hibernate One-to-One Association on Primary Key Annotations Example
• Hibernate One-to-Many Using Join Table XML Mapping Example
• Hibernate Many-to-Many Association with Extra Columns in Join Table Example
• Hibernate Enum Type Mapping Example
• Hibernate Binary Data and BLOB Mapping Example
• Hibernate Basics - 3 ways to delete an entity from the datastore
• Hibernate Query Language (HQL) Example
• Java Hibernate Reverse Engineering Tutorial with Eclipse and MySQL

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.