hibernate链接mysql数据库异常处理

转[http://www.cnblogs.com/safeking/archive/2007/02/01/637084.html]

运行环境：tomcat +mysql
项目用到了 hibernate3.0   c3po  mysql的数据层组合，开发部署非常顺利，但每天早上访问应用都抛出 Could not open Hibernate session for transaction 异常，Caused by: com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception，但经过几次访问后又恢复正常
原来是Mysql在经过8小时不使用后会自动关闭已打开的连接，摘录原文如下：

5.4.

I have a servlet/application that works fine for a day, and then stops working overnight
	

MySQL closes connections after 8 hours of inactivity. You either need to use a connection pool that handles stale connections or use the "autoReconnect" parameter (see "Developing Applications with MySQL Connector/J").

Also, you should be catching SQLExceptions in your application and dealing with them, rather than propagating them all the way until your application exits, this is just good programming practice. MySQL Connector/J will set the SQLState (see java.sql.SQLException.getSQLState() in your APIDOCS) to "08S01" when it encounters network-connectivity issues during the processing of a query. Your application code should then attempt to re-connect to MySQL at this point.

通过以上信息的收集隐隐知道了问题所在（mysql经过28800秒也就是8个小时后关掉空闲链接，而c3p0是经过6000000妙才断开链接，就有可能出现c3po保持的连接有可能已经被mysql关掉了，自然就出现了hibernate不能打开session，并且都是第二天一早就出错）；为了验证我的想法，决定在开发机上重现这个错误；首先在mysql配置文件my.ini 加上 wait_timeout=30  让mysql经过30妙就关掉链接，重启应用，第一次访问成功，等待一分钟后访问果然出现同样的错误，说明问题诊断正确，着手修改配置如下：

<property name="maxIdleTime">
   <value>28000</value>
  </property> 
  <property name="idleConnectionTestPeriod">
   <value>28000</value>
  </property> 

配置解释：28000<28800 使c3p0 在mysql关不连接之前关闭自己持有的链接，配置idleConnectionTestPeriod 参数使c3po每隔28000检查已有的连接是否可用，这样应该确保拿到的连接都是可用的，如果还不放心可以加上 testConnectionOnCheckout参数每当拿出连接的时候就检查一下是否可以，这个可能会使mysql有一定的性能牺牲；

  <property name="testConnectionOnCheckout">
   <value>true</value>
  </property>