Hibernate - Part 6 - c3p0 connection pooling

In an earlier post, I explained a technique for storing a Hibernate SessionFactory in the ServletContex.  The objective was to create the Hibernate SessionFactory once and add it to the ServletContext so that it is available for the life of the application.

This solution seemed sufficient for my needs and I moved on to other issues.  However, I soon noticed my application would hang when left idle overnight.  It wasn't immediately clear what caused this behavior but as I looked through the logs I found the following error.

WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 08S01
ERROR org.hibernate.util.JDBCExceptionReporter - The last packet successfully received from the server was 49,245,917 milliseconds ago.
The last packet sent successfully to the server was 49,245,917 milliseconds ago. is longer than the server configured value of 'wait_timeout'.
You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

I found an informative blog post explaining the root of this problem.  The author explains that MySQL is dropping the connection after the idle timeout expires and by default Hibernate is not able reconnect on its own.  The solution is to use connection pooling, something I was planning on implementing eventually anyway.  The Hibernate documentation addresses this issue as follows:

Hibernate's own connection pooling algorithm is, however, quite rudimentary. It is intended to help you get started and is not intended for use in a production system, or even for performance testing. You should use a third party pool for best performance and stability. Just replace the hibernate.connection.pool_size property with connection pool specific settings. This will turn off Hibernate's internal pool. For example, you might like to use c3p0.

 Adding c3p0 connection pooling was as easy as adding the jar to my classpath and adding the following into my hibernate.cfg.xml

        <!-- Use the C3P0 connection pool provider -->
        <property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
        <property name="hibernate.c3p0.min_size">5</property>
        <property name="hibernate.c3p0.max_size">10</property>
        <property name="hibernate.c3p0.timeout">1800</property>
        <property name="hibernate.c3p0.max_statements">50</property>
        <property name="hibernate.c3p0.idle_test_period">900</property>

And voila, we now have connection pooling.  I was no longer getting the MySQL errors noted at the beginning of this post.

Unfortunatly, I was about to run into another set of issues related to using connection pooling and my existing strategy for creating the Hibernate SessionFactory once and adding it to the ServletContext.  More on that in my next post.