连接mysql解决网络抖动_网络抖动时候，获取数据库连接等待15分钟后报错。

我们的服务器和数据库分布在不同的机房，现象就是每隔一小时左右，获取数据库连接就会等待15分钟后报异常，然后继续获取连接，执行SQL正常。服务器用的是druid1.0.17版本。

有没有可以设置连接超时的参数？我找到了一个validationQueryTimeout参数，这个是statement级别的控制，不是Connection级别的控制。

2016-05-31 11:13:00 DEBUG [http-bio-8081-exec-21] org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:110): Fetching JDBC Connection from DataSource

2016-05-31 11:28:30 DEBUG [http-bio-8081-exec-21] com.alibaba.druid.util.JdbcUtils.close(JdbcUtils.java:96): close statement error

java.sql.SQLRecoverableException: Closed Connection

at oracle.jdbc.driver.PhysicalConnection.needLine(PhysicalConnection.java:5389)

at oracle.jdbc.driver.OracleStatement.closeOrCache(OracleStatement.java:1578)

at oracle.jdbc.driver.OracleStatement.close(OracleStatement.java:1563)

at oracle.jdbc.driver.OracleStatementWrapper.close(OracleStatementWrapper.java:94)

at com.alibaba.druid.filter.FilterChainImpl.statement_close(FilterChainImpl.java:2386)

at com.alibaba.druid.filter.stat.StatFilter.statement_close(StatFilter.java:311)

at com.alibaba.druid.filter.FilterChainImpl.statement_close(FilterChainImpl.java:2383)

at com.alibaba.druid.proxy.jdbc.StatementProxyImpl.close(StatementProxyImpl.java:124)

at com.alibaba.druid.util.JdbcUtils.close(JdbcUtils.java:94)

at com.alibaba.druid.pool.DruidAbstractDataSource.testConnectionInternal(DruidAbstractDataSource.java:1285)

at com.alibaba.druid.pool.DruidDataSource.getConnectionDirect(DruidDataSource.java:990)

at com.alibaba.druid.filter.FilterChainImpl.dataSource_connect(FilterChainImpl.java:4544)

at com.alibaba.druid.filter.stat.StatFilter.dataSource_getConnection(StatFilter.java:662)

at com.alibaba.druid.filter.FilterChainImpl.dataSource_connect(FilterChainImpl.java:4540)

at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:955)

at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:947)

at com.alibaba.druid.pool.DruidDataSource.getConnection(DruidDataSource.java:102)

at org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource.getConnection(AbstractRoutingDataSource.java:164)

at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)

at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)

at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:82)

at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:68)

at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:315)

at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:75)

at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:61)

at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:303)

at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:154)

at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:102)

at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:82)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:120)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:113)

at sun.reflect.GeneratedMethodAccessor45.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:497)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:386)

at com.sun.proxy.$Proxy23.selectList(Unknown Source)

at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:205)

at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:122)

at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:64)

at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)

at com.sun.proxy.$Proxy38.QueryBargNoByCustid(Unknown Source)

at sun.reflect.GeneratedMethodAccessor58.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:497)

at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)

at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)

at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:85)

at com.homelink.dao.datasource.multi.SQLExecuterInterceptor.executeSZSQL(SQLExecuterInterceptor.java:57)

at sun.reflect.GeneratedMethodAccessor35.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:497)

at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:621)

at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:610)

at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:68)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)

at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)

at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)

at com.sun.proxy.$Proxy39.QueryBargNoByCustid(Unknown Source)

at com.homelink.service.portal.impl.AgreementQueryServiceImpl.QueryBargNoByCustid(AgreementQueryServiceImpl.java:61)

at com.homelink.controller.app.QueryFkAndDjSketchController.businesslogic(QueryFkAndDjSketchController.java:114)

at com.homelink.controller.app.QueryFkAndDjSketchController.protocoldescription(QueryFkAndDjSketchController.java:91)

at sun.reflect.GeneratedMethodAccessor75.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:497)

at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:178)

at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:444)

at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:432)

at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)

at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)

at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:969)

at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:871)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)

at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:845)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at com.homelink.filter.UserOnlineFilter.doFilter(UserOnlineFilter.java:101)

at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)

at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:121)

at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)

at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:503)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)

at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)

at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1070)

at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)

at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)

at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)

at java.lang.Thread.run(Thread.java:745)

吐槽下：druid测试链接的testOnBorrow、testWhileIdle只是拿到连接时候测试，如果这时候Connection(物理网络)不稳定，光测试这一下就耗时很久。

希望testWhileIdle是在守护线程上来做。