Hibernate, C3p0, and MySQL

In today's applications, database connection pooling is a common best practice. It improves performance by reusing existing connections as establishing a new one is fairly expensive. C3p0 is one of the well-known database connection pool libraries. And Hibernate is now the de facto standard for object-relational mapping (ORM) in Java world. Here is a sample configuration to use C3p0 in Hibernate for MySQL database:

<!-- Basic -->
<property name="hibernate.connection.driver_class">org.gjt.mm.mysql.Driver</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
<property name="hibernate.connection.url">jdbc:mysql://127.0.0.1/test</property>
<property name="hibernate.connection.username">username</property>
<property name="hibernate.connection.password">password</property>
<property name="hibernate.connection.default_schema">test</property>

<!-- C3p0 -->
<property name="hibernate.c3p0.acquire_increment">1</property>
<property name="hibernate.c3p0.idle_test_period">300</property>
<property name="hibernate.c3p0.timeout">120</property>
<property name="hibernate.c3p0.max_size">25</property>
<property name="hibernate.c3p0.min_size">1</property>
<property name="hibernate.c3p0.max_statement">0</property>
<property name="hibernate.c3p0.preferredTestQuery">select 1;</property>

Two properties to put focus on are idle_test_period and timeout. The property idle_test_period defines the interval to test unchecked-out connections while timeout is the time unused connections can live in the pool. However, you might see Communication Link Failure errors occasionally in your log, even though it's configured to have connections tested. Listed are some possible causes:

Network
Make sure your system allows connecting to the database. For example, your unix system may have /etc/hosts.allow or /etc/hosts.deny turned on for access control. Or your firewall could block or kill your connections based on the rules.

Database
Check the system configuration on your database. MySQL has a wait_timeout setting which will close the connections idle over a period of time. If your idle_test_period and timeout are greater than MySQL wait_timeout (28800 seconds by default), your unused connections will have chance to be reset by MySQL server before tested or timed out by C3p0.

Configuration
Choose the connection pool library for your configuration. By default, Hibernate should pick the right one for you. But, if there are more than one connection pool libraries on your classpath, it may not be the way you expect. Hibernate allows you to specify the library by setting the property provider_class as follows:

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

Of course, you could just simply remove other connection pool libraries.

Reference
How to configure the C3P0 connection pool