C3P0 connection pool configuration Spring Hibernate

Recently I faced an error with our server, every morning we received an 500 error as follow:

HTTP Status 500 - Request processing failed; nested exception is org.springframework.dao.DataAccessResourceFailureException: could not execute query;

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 54,840,265 milliseconds ago. The last packet sent successfully to the server was 54,840,266 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.

 For a while I was browsing and searching in google to solve this issue, and then I figured out what I need, a connection pool, so I started to configure our Java projects with C3P0 (see http://www.mchange.com/projects/c3p0/ for completing info).

First step: You should add the c3p0 dependency to your pom.xml


<dependency>
<groupid>c3p0</groupid>
<artifactid>c3p0</artifactid>
<version>0.9.1.2</version>
<type>jar</type>
<scope>compile</scope>
</dependency>



Second step: Set dataSource configuration to some like this:



<bean class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close" id="dataSource">
<property name="driverClass" value="${jdbc.driverClassName}">
<property name="jdbcUrl" value="${jdbc.url}">
<property name="user" value="${jdbc.username}">
<property name="password" value="${jdbc.password}">

<!-- C3P0 properties -->
<property name="minPoolSize" value="${hibernate.c3p0.min_size}">
<property name="maxPoolSize" value="${hibernate.c3p0.max_size}">
<property name="maxIdleTime" value="${hibernate.c3p0.idle_test_period}">
</property></property></property></property></property></property></property></bean>

<bean class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean" id="sessionFactory">
<property name="dataSource" ref="dataSource">
<property name="annotatedClasses">
<list>
<value>com.vapaya.shared.model.User</value>
</list>
</property>
<property name="hibernateProperties">
<props>
        <prop key="hibernate.dialect">${hibernate.dialect}</prop>
<prop key="hibernate.hbm2ddl.auto">${hibernate.hbm2ddl.auto}</prop>
<prop key="hibernate.cache.provider_class">${hibernate.cache.provider_class}</prop>
<prop key="hibernate.show_sql">${hibernate.show_sql}</prop>
<prop key="hibernate.format_sql">${hibernate.format_sql}</prop>
<prop key="hibernate.show_comments">${hibernate.show_comments}</prop>
</props>
</property>
</property></bean>

Third step: Add this to your pom.xml



<properties>
<jdbc .driverclassname=".driverclassname">com.mysql.jdbc.Driver</jdbc>
<jdbc .url=".url">jdbc:mysql://localhost:3306/vapaya</jdbc>
<jdbc .username=".username">vapayaUser</jdbc>
<jdbc .password=".password">v4p4y4DB</jdbc>

<hibernate .dialect=".dialect">org.hibernate.dialect.MySQLDialect</hibernate>
<hibernate .hbm2ddl.auto=".hbm2ddl.auto">update</hibernate>
<hibernate .cache.provider_class=".cache.provider_class">org.hibernate.cache.NoCacheProvider</hibernate>
<hibernate .show_sql=".show_sql">true</hibernate>
<hibernate .format_sql=".format_sql">false</hibernate>
<hibernate .show_comments=".show_comments">true</hibernate>

<hibernate .c3p0.min_size=".c3p0.min_size">5</hibernate>
<hibernate .c3p0.max_size=".c3p0.max_size">20</hibernate>
<hibernate .c3p0.idle_test_period=".c3p0.idle_test_period">3000</hibernate>
</properties>

Fourth step: Add this to your properties file


jdbc.driverClassName=${jdbc.driverClassName}
jdbc.url=${jdbc.url}
jdbc.username=${jdbc.username}
jdbc.password=${jdbc.password}

hibernate.dialect=${hibernate.dialect}
hibernate.hbm2ddl.auto=${hibernate.hbm2ddl.auto}
hibernate.cache.provider_class=${hibernate.cache.provider_class}
hibernate.show_sql=${hibernate.show_sql}
hibernate.format_sql=${hibernate.format_sql}
hibernate.show_comments=${hibernate.show_comments}

hibernate.c3p0.min_size=${hibernate.c3p0.min_size}
hibernate.c3p0.max_size=${hibernate.c3p0.max_size}
hibernate.c3p0.idle_test_period=${hibernate.c3p0.idle_test_period}


That's all, in order to know is everything is set, you should see this output in catalina.out:


Dec 16, 2012 5:35:17 PM com.mchange.v2.c3p0.C3P0Registry banner
INFO: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]

Some additional information about if it's working is as follow:

1. Connect to mysql, command line.
2. type: show processlist;

This should show you 5 connections (minimum pool size).

I hope you find this helpful.