Hibernate Connection Pooling with c3p0

By default, Hibernate uses JDBC connections in order to interact with a database. Creating the database connections is expensive, probably the most expensive operation Hibernate executes in a typical usecase. For this reason, we are advised to use a connection pool that can store the opened connections ahead of time and close them only when they are not needed.

Thankfully, Hibernate is designed to use a connection pool by default, an internal implementation. However, Hibernate’s built-in connection pooling isn’t designed for production use. In production, we would use an external connection pool by using either a database connection provided by JNDI or an external connection pool configured via parameters and classpath.

C3P0 is an example of an external connection pool. In this tutorial, we will learn to use C3P0 with Hibernate 6.

1. Maven Dependencies

To configure c3p0 with hibernate, we need to add c3p0 and Hibernate’s c3p0 connection provider hibernate-c3p0 as dependencies in the pom.xml.

Please note that the version of the hibernate-c3p0 dependency should match Hibernate’s compatible version.

<dependency>
    <groupId>org.hibernate.orm</groupId>
    <artifactId>hibernate-core</artifactId>
    <version>6.0.0.Final</version>
</dependency>
<dependency>
    <groupId>org.hibernate.orm</groupId>
    <artifactId>hibernate-c3p0</artifactId>
    <version>6.0.0.Final</version>
</dependency>
<dependency>
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.5.5</version>
</dependency>

2. Enabling C3P0 Connection Pooling

The best part is that the whole configuration of C3P0 with hibernate is really very easy. In most cases, if we do not have any other connection provider, just adding any hibernate.c3p0.x property inside the hibernate.cfg.xml file will configure it with defaults.

<property name="hibernate.c3p0.min_size">10</property>

Connection pooling with C3P0 is now configured. We are good to start testing things. Really easy, Isn’t it?

If there is more than one connection pool then to enforce c3p0 poling, we can provide the provider_class property.

<property name="hibernate.connection.provider_class">
      org.hibernate.connection.C3P0ConnectionProvider
</property>

A rather detailed configuration can be setup using the following properties in hibernate.cfg.xml. All keys are prefixed with hibernate.c3p0.

<property name="hibernate.c3p0.min_size">10</property>
<property name="hibernate.c3p0.max_size">20</property>
<property name="hibernate.c3p0.acquire_increment">1</property>
<property name="hibernate.c3p0.idle_test_period">3000</property>
<property name="hibernate.c3p0.max_statements">50</property>
<property name="hibernate.c3p0.timeout">1800</property>
<property name="hibernate.c3p0.validate">1800</property>

We can find detailed information about the above configuration switches in official documentation.

3. Debugging Connection Leaks

Sometimes applications talk to many other applications and some applications or interactions take a longer time to respond. This can overwhelm the connection pool (when the pool grows to maxPoolSize) and degrade the performance of the whole application.

c3p0 can help us debug the pool where connections have been checked out and don’t get checked in.

<property name="hibernate.c3p0.unreturnedConnectionTimeout">30</property>
<property name="hibernate.c3p0.debugUnreturnedConnectionStackTraces">true</property>

• unreturnedConnectionTimeout helps in fixing leaks. It defines the time (in seconds) to how long a Connection may remain checked out. Checked-out Connections that exceed this limit will be destroyed, and then created a new one in the pool.
• debugUnreturnedConnectionStackTraces helps in debugging the root cause. when set to true, whenever an unreturned Connection times out, that stack trace will be printed, revealing where a Connection was checked out that was not checked in.

4. Demo

4.1. Application Logs Without C3P0 Configuration

Without C3P0 configured, if you see the debug logs of hibernate, we will see something like this:

DEBUG Configuration:1841 - Preparing to build session factory with filters : {}
WARN DriverManagerConnectionProviderImpl:93 - HHH000402: Using Hibernate built-in connection pool (not for production use!)
INFO DriverManagerConnectionProviderImpl:166 - HHH000401: using driver [org.hsqldb.jdbcDriver] at URL [jdbc:hsqldb:mem:howtodoinjava]
INFO DriverManagerConnectionProviderImpl:172 - HHH000046: Connection properties: {user=sa, password=}
INFO DriverManagerConnectionProviderImpl:180 - HHH000006: Autocommit mode: false
INFO DriverManagerConnectionProviderImpl:102 - HHH000115: Hibernate connection pool size: 20 (min=1)
DEBUG DriverManagerConnectionProviderImpl:104 - Initializing Connection pool with 1 Connections
...
...
...
EBUG JdbcTransaction:113 - committed JDBC Connection
DEBUG SessionFactoryImpl:1339 - HHH000031: Closing
DEBUG AbstractServiceRegistryImpl:406 - Implicitly destroying ServiceRegistry on de-registration of all child ServiceRegistries
INFO DriverManagerConnectionProviderImpl:281 - HHH000030: Cleaning up connection pool [jdbc:hsqldb:mem:howtodoinjava]

4.2. Application Logs With C3P0 Configuration

After configuring C3P0 Connection Pool, you will be able to see in the logs that connections are now acquired from C3P0 Connection Pool itself.

DEBUG Configuration:1841 - Preparing to build session factory with filters : {}
 INFO C3P0ConnectionProvider:133 - HHH010002: C3P0 using driver: org.hsqldb.jdbcDriver at URL: jdbc:hsqldb:mem:howtodoinjava
 INFO C3P0ConnectionProvider:134 - HHH000046: Connection properties: {user=sa, password=****}
 INFO C3P0ConnectionProvider:137 - HHH000006: Autocommit mode: false
 INFO MLog:92 - MLog clients using log4j logging.
 INFO C3P0Registry:216 - Initializing c3p0-0.9.2.1 [built 20-March-2013 10:47:27 +0000; debug? true; trace: 10]
DEBUG DynamicPooledDataSourceManagerMBean:258 - MBean: com.mchange.v2.c3p0:type=PooledDataSource,identityToken=19tu9of94ho8s13xij3fm|34e475e1,name=19tu9of94ho8s13xij3fm|34e475e1 registered.
DEBUG DynamicPooledDataSourceManagerMBean:253 - MBean: com.mchange.v2.c3p0:type=PooledDataSource,identityToken=19tu9of94ho8s13xij3fm|34e475e1,name=19tu9of94ho8s13xij3fm|34e475e1 unregistered, in order to be reregistered after update.
DEBUG DynamicPooledDataSourceManagerMBean:258 - MBean: com.mchange.v2.c3p0:type=PooledDataSource,identityToken=19tu9of94ho8s13xij3fm|34e475e1,name=19tu9of94ho8s13xij3fm|34e475e1 registered.
 INFO AbstractPoolBackedDataSource:522 - Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@d29bbf50 [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@a9bbd924 [ ... ]
 ...
 ...
 ...
 DEBUG ActiveManagementCoordinator:97 - C3P0Registry mbean unregistered.
DEBUG BasicResourcePool:1022 - Preparing to destroy resource: com.mchange.v2.c3p0.impl.NewPooledConnection@1d1fcfbb
DEBUG C3P0PooledConnectionPool:616 - Preparing to destroy PooledConnection: com.mchange.v2.c3p0.impl.NewPooledConnection@1d1fcfbb
DEBUG AbstractPoolBackedDataSource:477 - com.mchange.v2.c3p0.PoolBackedDataSource@34e475e1 has been closed.
java.lang.Exception: DEBUG STACK TRACE for PoolBackedDataSource.close().

That’s all for this easy but useful tutorial about configuring C3P0 Connection Pool with hibernate 6.

Happy Learning !!

Source Code on Github