java - Spring JDBCTemplate 除了 apache commons 之外还有其他 MySQL 数据源吗？

Question

我正在使用 Spring JDBCTemplate 在 apache 公共(public)数据源 (org.apache.commons.dbcp.BasicDataSource) 上执行 SQL 操作，当服务启动并运行很长时间时，我最终得到这个异常:

org.springframework.dao.RecoverableDataAccessException: StatementCallback; SQL [SELECT * FROM vendor ORDER BY name]; The last packet successfully received from the server was 64,206,061 milliseconds ago.  The last packet sent successfully to the server was 64,206,062 milliseconds ago. 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.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 64,206,061 milliseconds ago.  The last packet sent successfully to the server was 64,206,062 milliseconds ago. 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.
    at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:98)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:406)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:455)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:463)
    at com.cable.comcast.neto.nse.taac.dao.VendorDao.getAllVendor(VendorDao.java:25)
    at com.cable.comcast.neto.nse.taac.controller.RemoteVendorAccessController.requestAccess(RemoteVendorAccessController.java:78)

我已尝试将“autoReconnect=true”添加到连接字符串中，但问题仍然存在。是否可以使用另一个数据源来为我管理重新连接？

Answer 1

BasicDataSource 可以为您管理使连接保持 Activity 状态。您需要设置以下属性:

minEvictableIdleTimeMillis = 120000 // Two minutes
testOnBorrow = true
timeBetweenEvictionRunsMillis = 120000 // Two minutes
minIdle = (some acceptable number of idle connections for your server)

这些将配置数据源以持续测试您的连接，并在它们变得陈旧时过期并删除它们。您可能还需要考虑检查基本数据源上的许多其他属性，以调整您的连接池性能。我过去遇到过一些奇怪的问题，我的数据库访问出现问题，而这一切都归结为连接池的配置方式。