×

Spring Boot 修改数据库连接池

96
尽心上
2016.07.11 17:36* 字数 223
[ WARN ] [2016-07-11 09:52:45] org.hibernate.engine.jdbc.spi.SqlExceptionHelper [144] - SQL Error: 0, SQLState: 08S01
[ ERROR] [2016-07-11 09:52:45] org.hibernate.engine.jdbc.spi.SqlExceptionHelper [146] - The last packet successfully received from the server was 229,451,844 milliseconds ago.  The last packet sent successfully to the server was 229,451,844 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.
[ ERROR] [2016-07-11 09:52:45] org.springframework.boot.context.web.ErrorPageFilter [178] - Forwarding to error page from request [/getPeopleNearbyList] due to exception [could not extract ResultSet; nested exception is org.hibernate.exception.JDBCConnectionException: could not extract ResultSet]
org.springframework.dao.DataAccessResourceFailureException: could not extract ResultSet; nested exception is org.hibernate.exception.JDBCConnectionException: could not extract ResultSet

应用跑了两天,报了这样一个错误。

MySQL连接时,服务器默认的“wait_timeout”是8小时,也就是说一个connection空闲超过8个小时,Mysql将自动断开该connection。connections如果空闲超过8小时,Mysql将其断开,而DBCP并不知道该connection已经失效,如果这时有Client请求connection,DBCP将该失效的Connection提供给Client,将会造成异常

Spring Boot中,默认使用的连接池为JDBC Connection Pool

那样,在JDBC Connection Pool中我们需要处理好以下问题:
1、对每个连接进行检查
2、对一次操作数据库超过多少时间的连接进行移除
3、每隔多少时间检测一次连接
4、一个连接在连接多少时间后,就必须删除

#这里可以制定自己要使用的连接池
#Fully qualified name of the connection pool implementation to use. By default, it is auto-detected from the classpath.
spring.datasource.type=这里可以不用添加此项
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf-8
spring.datasource.username=数据库用户名
spring.datasource.password=数据库密码
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
# 初始化大小,最小,最大
spring.datasource.initial-size=5
spring.datasource.min-idle=5
spring.datasource.max-idle=100
spring.datasource.max-wait=10000
spring.datasource.validation-query=SELECT 1
#没次使用连接时进行校验,会影响系统性能。默认为false
#spring.datasource.test-on-borrow=true
spring.datasource.test-while-idle=true
spring.datasource.time-between-eviction-runs-millis=27800
#spring.datasource.jdbc-interceptors=ConnectionState;SlowQueryReport(threshold=0)
Mybatis
Web note ad 1