hibernate連結mysql資料庫異常處理

運行環境：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>