Hibernate c3p0 Connection Pooling Configuration

This tutorial shows you how to configure Hibernate c3p0 Connection Pooling. By default hibernate comes with a built-in connection pool. But this connection pool is by no means production ready. They even advice not to use it in production, as you can see in the logging.

HHH10001002: Using Hibernate built-in connection pool (not for production use!)

What is c3p0?

c3p0 is an easy-to-use library for making traditional JDBC drivers “enterprise-ready” by augmenting them with functionality defined by the jdbc3 spec and the optional extensions to jdbc2. As of version 0.9.5, c3p0 fully supports the jdbc4 spec.

Maven Dependencies

We are using Apache Maven to manage our project dependencies. Add the following dependencies to your projects pom.xml file.

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>6.0.4</version>
</dependency>
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>5.2.3.Final</version>
</dependency>
<dependency>
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.5.2</version>
</dependency>
<dependency>
    <groupId>ch.qos.logback</groupId>
    <artifactId>logback-classic</artifactId>
    <version>1.1.7</version>
</dependency>

We are using the latest version of com.mchange:c3p0. You could also use the org.hibernate:hibernate-c3p0, but I noticed this is using an older version of com.mchange:c3p0. The latest version has some advantages over the old. Like integrated support for modern logging frameworks like logback and log4j.

Hibernate c3pO Connection Pooling Configuration

The database connections and hibernate c3p0 connection pooling configuration are in the hibernate.cfg.xml file, located on the classpath in the src/main/resources folder.

By default, c3p0 uses sensible defaults, but you can override these settings by setting the following properties.

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

        <!-- database connection properties -->
        <property name="hibernate.connection.driver_class">com.mysql.cj.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/bookstore?serverTimezone=Europe/Brussels</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.connection.password"></property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>

        <!-- show mysql queries output in console -->
        <property name="hibernate.show_sql">false</property>

        <!-- manage automatic database creation -->
        <property name="hibernate.hbm2ddl.auto">create-drop</property>

        <!-- hibernate c3p0 connection pooling configuration  -->
        <property name="hibernate.c3p0.acquire_increment">1</property>
        <property name="hibernate.c3p0.idle_test_period">60</property> <!-- seconds -->
        <property name="hibernate.c3p0.min_size">5</property>
        <property name="hibernate.c3p0.max_size">10</property>
        <property name="hibernate.c3p0.max_statements">50</property>
        <property name="hibernate.c3p0.timeout">0</property> <!-- seconds -->
        <property name="hibernate.c3p0.acquireRetryAttempts">1</property>
        <property name="hibernate.c3p0.acquireRetryDelay">250</property>

        <!-- add your annotated resources here -->
        <mapping class="com.memorynotfound.hibernate.Book"/>

    </session-factory>
</hibernate-configuration>

• acquire_increment: Determines how many connections at a time c3p0 will try to acquire when the pool is exhausted.
• idle_test_period: If this is a number greater than 0, c3p0 will test all idle, pooled but unchecked-out connections, every this number of seconds.
• min_size: Minimum number of Connections a pool will maintain at any given time.
• max_size: Maximum number of Connections a pool will maintain at any given time.
• max_statements: The size of c3p0’s global PreparedStatement cache. Zero means statement caching is turned off. maxStatements controls the total number of Statements cached, for all Connections. If set, it should be a fairly large number, as each pooled Connection requires its own, distinct flock of cached statements. As a guide, consider how many distinct PreparedStatements are used frequently in your application, and multiply that number by maxPoolSize to arrive at an appropriate value.
• timeout: Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire.
• acquireRetryAttempts: Defines how many times c3p0 will try to acquire a new Connection from the database before giving up. If this value is less than or equal to zero, c3p0 will keep trying to fetch a Connection indefinitely.
• acquireRetryDelay: Milliseconds, time c3p0 will wait between acquire attempts.

Demo

When using the previous configuration, c3p0 initializes the connection pool with 5 connections at startup. So when we run the application – we can see in the following screenshot – that there are 5 connections waiting in the pool. Also that there is one active connection being used.

Download