How to configure the C3P0 connection pool in Hibernate

With connection pool enabled, application will create many database connections in initial stage, each time Java application that need to work on database transaction will request a connection from connection pool and return it to the connection pool when the transaction is done. This is used to prevent Java application create a connection each time interact with the database and minimizes the cost of opening and closing connections.

Hibernate has C3P0 as build in connection pool. You need to configure the C3P0 connection pool properties in Hibernate XML configuration file – “hibernate.cfg.xml” and Hibernate will handle the pool for you

Configure the C3P0 connection pool in Hibernate

Add the C3P0 properties in Hibernate XML configuration file (hibernate.cfg.xml).

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

1. hibernate.c3p0.min_size

Minimum number of JDBC connections in the pool. Hibernate default: 1

2. hibernate.c3p0.max_size

Maximum number of JDBC connections in the pool. Hibernate default: 100

3. hibernate.c3p0.timeout

When an idle connection is removed from the pool (in second). Hibernate default: 0, never expire.

4. hibernate.c3p0.max_statements

Number of prepared statements will be cached. Increase performance. Hibernate default: 0 , caching is disable.

5. hibernate.c3p0.idle_test_period

idle time in seconds before a connection is automatically validated. Hibernate default: 0

hibernate.cfg.xml

<?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.bytecode.use_reflection_optimizer">false</property>
        <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/mkyong</property>
        <property name="hibernate.connection.username">root</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>
 
    </session-factory>
</hibernate-configuration>

Run it, output

Run your application with new C3P0 connection pool properties in Hibernate XML configuration file. During the initialize process, Hibernate will create 5 connections in connection pool , and ready for use in application.

Reference

https://www.hibernate.org/214.html