dbcp解决mysql的wait_timeout

正常异常:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was52,587,242 milliseconds ago.  The last packet sent successfully to the server was 52,587,242 milliseconds ago. is longer thanthe 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.

意思是当前的connection所进行过的最新请求是在52,587秒之前，这个时间是大于服务所配置的wait_timeout时间的。

mysql的wait_timeout时间，默认为28800秒，查询命令如下:

mysql> show global variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+

dbcp是连接池模式，其默认没配置的情况下，对于已经创建起来的连接是不会主动去请求的数据库的。

由上面这些原因显然是会导致长时间不适用connection，会导致抛出"wati_timeout"异常的。

解决方案:

在mysql wait_timeout的时间内，至少保证每个连接有一次查询请求。

dbcp可配置如下:

timeBetweenEvictionRunsMillis=20000 //每timeBetweenEvictionRunsMillis毫秒检查一次连接池中空闲的连接
testWhileIdle=true //也就是连接池中的连接空闭下来时,进行检验
validationQuery=SELECT 1 FROM dual //查询语句