Connection pooling with Hibernate 3.3.x and C3P0

What is C3P0?

Hibernate uses it's own built-in connection pool out of the box. There are some pretty heavy duty sites running with the default connection pool enabled in production despite the obvious warning against doing this:

INFO DriverManagerConnectionProvider:64 - Using Hibernate built-in connection pool (not for production use!)
INFO DriverManagerConnectionProvider:65 - Hibernate connection pool size: 1

A few minor problems creep up with this default configuration left alone, such as the inability for Hibernate to reconnect closed connections. MySQL closes unused connections after 8 hours by default, which causes problems for low-volume applications that may not experience any database usage overnight. 

One solution is to use C3P0, an incredibly simple library that augments the standard JDBC drivers essentially making them "enterprise-ready". One of the main features of C3P0 is:

Transparent pooling of Connection and PreparedStatements behind DataSources which can "wrap" around traditional drivers or arbitrary unpooled DataSources.

In other words, you don't have to change any of your code to use C3P0 connection pools if you're using Hibernate. C3P0 takes care of maintaining the pool and testing connections, so your code stays in tact.

Configuring C3P0

1. Add the C3P0 jar to your classpath. If you're using Maven, simply add the following to your pom.xml file:

<dependency>
	<groupId>org.hibernate</groupId>
	<artifactId>hibernate-c3p0</artifactId>
	<version>3.3.2.GA</version>
</dependency>

2. Make the following changes to your hibernate.cfg.xml file:

<property name="connection.url">jdbc:mysql://127.0.0.1/your_db</property>
<property name="connection.username">your_username</property>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>

<!-- JDBC connection pool (C3P0) -->
<property name="c3p0.min_size">5</property>
<property name="c3p0.max_size">20</property>
<property name="c3p0.timeout">1800</property>
<property name="c3p0.max_statements">50</property>

Pay special attention to the provider_class property. Previous versions did not require this property, so most of the tutorials on the net are missing this key piece of info.

3. Create a c3p0.properties file

In order to configure C3P0 for your specific needs, you'll need a c3p0.properties file on the classpath. Take a look here for all the available settings you can tweak. Here's an example that ensures any closed connection is tested and re-opened rather than MySQL throwing an exception:

c3p0.testConnectionOnCheckout=true

Verifying it worked

Configuring C3P0 is pretty simple! If everything works correctly, you'll see the following in your logs:

INFO ConnectionProviderFactory:95 - Initializing connection provider: org.hibernate.connection.C3P0ConnectionProvider
INFO C3P0ConnectionProvider:103 - C3P0 using driver: com.mysql.jdbc.Driver at URL: jdbc:mysql://127.0.0.1/your_db
INFO C3P0ConnectionProvider:104 - Connection properties: {user=****, password=****}