Configure Hibernate to Use C3P0 Connection Pool

With this tutorial I wanted to share with you how to configure your Java project and it’s hibernate configuration file hibernate.cfg.xml to use a more professional and production ready connection pool provider C3P0.

Hibernate’s internal connection pooling algorithm is rudimentary, and is provided for development and testing purposes.

In my earlier blog posts I used the default hibernate connection pool but it is not as good to be used in production as the C3P0. And the good news is that it does not take much effort to update your Java project that uses Hibernate’s default connection polling to use a better one.

To update our Java project that uses a default connection pool to use C3P0 there are 2 steps that we will need to do:

Update pom.xml by adding a dependency for hibernate-c3p0
Update Hibernate configuration file hibernate.cfg.xml in our project to use the new C3P0 connection pool.

And before you continue further I would like to mention here that you can watch how I add hibernate connection pooling into my project on video. I have recorded this and many other examples on how to create REST Web Service with Java and implement User Sign in and Sign up features with Hibernate and MySQL in my video course: REST API with Java JAX-RS. Create and Deploy to Amazon Cloud.

Add C3P0 Maven Dependency

To add the c3p0 connection pool dependency into our project. Open your pom.xml file and add one more entry into dependencies list.

<!-- https://mvnrepository.com/artifact/org.hibernate/hibernate-c3p0 -->
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-c3p0</artifactId>
    <version>5.2.12.Final</version>
</dependency>

Or you can download the JAR file and add it to your list of libraries manually. Here is a link to download the JAR file:

https://mvnrepository.com/artifact/org.hibernate/hibernate-c3p0/5.2.12.Final

Update hibernate.cfg.xml to use C3P0 Connection Pooling

Hibernate uses C3P0ConnectionProvider for connection pooling if you set the hibernate.c3p0.* properties.

If you used default hibernate’s default connection pool then remove the following line from your hibernate.cfg.xml file:

<property name="connection.pool_size">10</property>

Instead of the above line, add the following 4:

 <property name="hibernate.c3p0.min_size">3</property>
 <property name="hibernate.c3p0.max_size">20</property>
 <property name="hibernate.c3p0.timeout">300</property>
 <property name="hibernate.c3p0.max_statements">50</property>

Where:

hibernate.c3p0.min_size – Is the minimum number of JDBC connections in the pool
hibernate.c3p0.max_size – Is the maximum number of JDBC connections in the pool
hibernate.c3p0.timeout – Specifies when an idle connection is removed from the pool (in second),
hibernate.c3p0.max_statements – Is the number of prepared statements that will be cached. Caching is good to have because it increases application performance. But if you decide to turn off caching, then set this value to 0.

Here is my updated hibernate.cfg.xml file:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/photo_app</property>
        <property name="hibernate.connection.username">Sergey</property>
        <property name="hibernate.connection.password">sergey</property>
        
        <property name="hibernate.id.new_generator_mappings">true</property>  
        <property name="show_sql">true</property> 
        <property name="hibernate.hbm2ddl.auto">update</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
       
        <property name="hibernate.c3p0.min_size">5</property>
        <property name="hibernate.c3p0.max_size">20</property>
        <property name="hibernate.c3p0.timeout">300</property>
        <property name="hibernate.c3p0.max_statements">50</property>
        <property name="hibernate.c3p0.idle_test_period">3000</property>
 
        <mapping class="com.appsdeveloperblog.ws.io.entity.UserProfileEntity"/>
 
    </session-factory>
</hibernate-configuration>

I hope this short tutorial is helpful to you. If you would like to learn more about hibernate check out the below books and video courses.