最近的一个项目在hibernate使用c3p0的连接池，数据库为mysql。开发测试没有问题，在运行中每个一段长的空闲时间就出现异常: java 代码 org.hibernate.exception.jdbcconnectionexception: could not execute query at org.hibernate.exception.sqlstateconve
最近的一个项目在hibernate使用c3p0的连接池，数据库为mysql。开发测试没有问题，在运行中每个一段长的空闲时间就出现异常:
java 代码
org.hibernate.exception.jdbcconnectionexception: could not execute queryat org.hibernate.exception.sqlstateconverter.convert(sqlstateconverter.java:74)at org.hibernate.exception.jdbcexceptionhelper.convert(jdbcexceptionhelper.java:43).......caused by: com.mysql.jdbc.exceptions.mysqlnontransientconnectionexception: no operations allowed after connection closed.connection was implicitly closed due to underlying exception/error:** begin nested exception **com.mysql.jdbc.communicationsexceptionmessage: communications link failure due to underlying exception:** begin nested exception **java.net.socketexceptionmessage: broken pipestacktrace:java.net.socketexception: broken pipeat java.net.socketoutputstream.socketwrite0(native method)......** end nested exception **查看了mysql的文档，以及connector/j的文档以及在线说明发现，出现这种异常的原因是：
mysql服务器默认的“wait_timeout”是8小时，也就是说一个connection空闲超过8个小时，mysql将自动断开该connection。这就是问题的所在，在c3p0 pools中的connections如果空闲超过8小时，mysql将其断开，而c3p0并不知道该connection已经失效，如果这时有client请求connection，c3p0将该失效的connection提供给client，将会造成上面的异常。
解决的方法有3种：
增加wait_timeout的时间。减少connection pools中connection的lifetime。测试connection pools中connection的有效性。当然最好的办法是同时综合使用上述3种方法，下面就dbcp和c3p0分别做一说明，假设wait_timeout为默认的8小时
dbcp增加以下配置信息:
//set to 'select 1'validationquery = select 1//set to 'true'testwhileidle = true//some positive integertimebetweenevictionrunsmillis = 3600000//set to something smaller than 'wait_timeout'minevictableidletimemillis = 18000000//if you don't mind a hit for every getconnection(), set to truetestonborrow = truec3p0增加以下配置信息:
//获取connnection时测试是否有效testconnectiononcheckin = true//自动测试的table名称
automatictesttable=c3p0testtable
 //set to something much less than wait_timeout, prevents connections from going staleidleconnectiontestperiod = 18000//set to something slightly less than wait_timeout, preventing 'stale' connections from being handed outmaxidletime = 25000//if you can take the performance 'hit', set to truetestconnectiononcheckout = true更多的配置信息大家可以查看c3p0文档，connector/j文档，以及dbcp的文档。