Jax-ws on Tomcat: reconnect from Hibernate to MySQL using c3p0 connection pool

You are on this page because you have seen the following error:

org.hibernate.util.JDBCExceptionReporter: The last packet successfully received from the server was 56697 seconds ago. The last packet sent successfully to the server was 56697 seconds ago, which  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 guess you have already using ?autoReconnect=true in the JDBC URL (this evidently wasn’t working) and wonder why.

I also guess that you have tried google tons of articles trying to solve this problem. Well, you have come to the right place. I spend sometime on this issue and finnally solved this problem, almost encountered every bit of chaos.

Firstly, I would like to reference few good posts:

• http://www.databasesandlife.com/automatic-reconnect-from-hibernate-to-mysql/
• This one throws some light, but it is a bit old, so the connection pool was old fation and the author suggest to “ create a file c3p0.properties which must be in the root of the classpath with content c3p0.testConnectionOnCheckout=true” ， which is expensive and not used anymore. Read it yourself for details. 
• c3p0 - JDBC3 Connection and Statement Pooling This is offical document so refer to this when you can’t decide what to do! 
• Hibernate's C3P0ConnectionProvider explicitly sets 7 c3p0 configuration properties, based on your hibernate configuration, overriding any configuration you may have set in a c3p0.properties file. If you are using Hibernate's C3P0ConnectionProvider you must set the following properties in your hibernate configuration, using hibernate-specific configuration keys. All other properties must be defined as usual in a c3p0.properties file. This is confusing, and will hopefully be simplified some time in the future, but for now...
• The following properties must be set in your hibernate configuration:
• 
• Remember -- these, and only these, properties must be defined in your hibernate configuration, or else they will be set to hibernate-specified defaults. All other configuration properties that you wish to set should be defined in a c3p0.properties file.
• The easiest way to set these are actually using myeclipse hibernate.cfg.xml configuration editer. Make sure you set all config related to c3p0 and add “hibernate.” before the setting. For instance, change “c3p0.min_size” to “hibernate.c3p0.min_size”. See above table as well. If you are not using myeclipse, just edit the xml in text editor.

With the above information, You are almost there, except you need the jar files in the classpath. Otherwise, you will get exception java.lang.NoClassDefFoundError for HibernateSessionFactory. In myeclipse, jar is not included as default hibernate support, so you need to add advanced support.

This problem annoying me for almost two weeks, and everytime I check my log4j and tomcat log, I saw this warning. Not sure if it really is a serious problem, but using connection pool definately enhance Hibernate performace.