Insane Programming Java ramblings and finds on the net

Don’t forget about C3P0

Using Hibernate is a breeze. You can find numerous tutorials, blog entries and a extensive manual on the net. However, that last item is something people only tend to look at when things go awry and you don't have a clue why. As I had with a MySQL problem recently.

It's a little known fact that the JDBC driver MySQL provides drops connections after being idle for 8 hours. Granted, it can be solved easily by providing a simple parameter on the JDBC url to reconnect when this happens, but it's often overlooked (and not really recommended, as it doesn't really promote safe programming). Another thing easily overlooked is the default pool Hibernate uses for its connections. This pool is, as the manual clearly warns, very basic and doesn't do any clean-up when it comes to idle connections. The result is, after 8 hours of inactivity, your Hibernate application will grind to a halt and only a restart will fix the problem.

And that is when C3P0 comes into play. It's been around for quite a while and it does a really nice job when it comes to pooling JDBC connections. Unfortunately, the current Hibernate manual is incorrect when it comes to enabling C3P0.

Normally, you only need to add a couple of properties to your hibernate.cfg.xml file, like this:.

<property name="c3p0.acquire_increment">1</property>
<property name="c3p0.idle_test_period">100</property> <!-- seconds -->
<property name="c3p0.max_size">100</property>
<property name="c3p0.max_statements">0</property>
<property name="c3p0.min_size">10</property>
<property name="c3p0.timeout">100</property> <!-- seconds -->

Unfortunately, in one of the last Hibernate releases (don't know which one though, but 3.3.2.GA has it) you need to add another property to make C3P0 work:

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

This should force Hibernate to use the C3P0 pooling instead of the default built-in pooling.

When using Maven, you also have to add a dependency for the C3P0 pooling support in Hibernate, as it is no longer part of the hibernate-core library. Just add this dependency and you're out of the woods.

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