使用Spring JDBC时遇到的Software caused connection abort: recv failed问题
org.springframework.dao.DataAccessResourceFailureException: PreparedStatementCallback; SQL [select t.f_role_id from db_sys_user_role t where t.f_user_id=?]; Communications link failure
Last packet sent to the server was 1 ms ago.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
Last packet sent to the server was 1 ms ago.
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:113) ~[spring_spring.jar:2.0.6]
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:276) ~[spring_spring.jar:2.0.6]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:553) ~[spring_spring.jar:2.0.6]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:587) ~[spring_spring.jar:2.0.6]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:616) ~[spring_spring.jar:2.0.6]
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:624) ~[spring_spring.jar:2.0.6]
at com.pf.plugin.db.spring.DbEngineSpringImpl.query(DbEngineSpringImpl.java:142) ~[pf-utils.jar:na]
at com.pf.plugin.db.DbEngine.query(DbEngine.java:101) ~[pf-utils.jar:na]
at com.pf.plugin.system.security.user.SysUserBuffer.interReload(SysUserBuffer.java:56) [classes/:na]
at com.pf.plugin.system.security.user.SysUserBuffer.interReload(SysUserBuffer.java:20) [classes/:na]
at com.pf.framework.cache.BaseJcsCache.reload(BaseJcsCache.java:288) [classes/:na]
at com.pf.framework.cache.BaseJcsCache.get(BaseJcsCache.java:192) [classes/:na]
at com.pf.plugin.system.security.user.SysUserBuffer.get(SysUserBuffer.java:361) [classes/:na]
at com.pf.plugin.system.security.user.SysUserBuffer.get(SysUserBuffer.java:20) [classes/:na]
at com.pf.framework.cache.BaseJcsCache.refresh(BaseJcsCache.java:252) [classes/:na]
at com.pf.framework.security.login.BaseLoginValid.loginValidate(BaseLoginValid.java:45) [classes/:na]
at com.pf.framework.security.login.LoginHelper.loginValidate(LoginHelper.java:38) [classes/:na]
at com.pf.framework.security.login.LoginAction.userLogin(LoginAction.java:153) [classes/:na]
at sun.reflect.GeneratedMethodAccessor3063.invoke(Unknown Source) ~[na:na]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_131]
at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.8.0_131]
at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:452) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:291) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:254) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:176) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:263) [xwork-core-2.2.1.jar:2.2.1]
at org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:68) [struts2-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorInterceptor.java:133) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:207) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:207) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:190) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248) [xwork-core-2.2.1.jar:2.2.1]
at org.apache.struts2.interceptor.MultiselectInterceptor.intercept(MultiselectInterceptor.java:75) [struts2-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248) [xwork-core-2.2.1.jar:2.2.1]
at org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:94) [struts2-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248) [xwork-core-2.2.1.jar:2.2.1]
at org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:243) [struts2-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:100) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.interceptor.ScopedModelDrivenInterceptor.intercept(ScopedModelDrivenInterceptor.java:141) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248) [xwork-core-2.2.1.jar:2.2.1]
at org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:267) [struts2-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:142) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:166) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:176) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248) [xwork-core-2.2.1.jar:2.2.1]
at org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:164) [struts2-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:190) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:187) [xwork-core-2.2.1.jar:2.2.1]
at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:248) [xwork-core-2.2.1.jar:2.2.1]
at org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:52) [struts2-core-2.2.1.jar:2.2.1]
at org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:574) [struts2-core-2.2.1.jar:2.2.1]
at org.apache.struts2.dispatcher.ng.ExecuteOperations.executeAction(ExecuteOperations.java:77) [struts2-core-2.2.1.jar:2.2.1]
at org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter.doFilter(StrutsPrepareAndExecuteFilter.java:91) [struts2-core-2.2.1.jar:2.2.1]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:8.5.15]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:8.5.15]
at com.pf.framework.init.CacheFilter.doFilter(CacheFilter.java:41) [classes/:na]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:8.5.15]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:8.5.15]
at com.pf.framework.init.VpFilter.doFilter(VpFilter.java:55) [classes/:na]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:8.5.15]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:8.5.15]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198) [catalina.jar:8.5.15]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [catalina.jar:8.5.15]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478) [catalina.jar:8.5.15]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) [catalina.jar:8.5.15]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80) [catalina.jar:8.5.15]
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624) [catalina.jar:8.5.15]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) [catalina.jar:8.5.15]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) [catalina.jar:8.5.15]
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:799) [tomcat-coyote.jar:8.5.15]
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-coyote.jar:8.5.15]
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:861) [tomcat-coyote.jar:8.5.15]
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1455) [tomcat-coyote.jar:8.5.15]
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-coyote.jar:8.5.15]
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [na:1.8.0_131]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [na:1.8.0_131]
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-util.jar:8.5.15]
at java.lang.Thread.run(Unknown Source) [na:1.8.0_131]
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
Last packet sent to the server was 1 ms ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_131]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) ~[na:1.8.0_131]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) ~[na:1.8.0_131]
at java.lang.reflect.Constructor.newInstance(Unknown Source) ~[na:1.8.0_131]
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) ~[mysql-connector-java-5.1.6-bin.jar:na]
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074) ~[mysql-connector-java-5.1.6-bin.jar:na]
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2985) ~[mysql-connector-java-5.1.6-bin.jar:na]
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2871) ~[mysql-connector-java-5.1.6-bin.jar:na]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3414) ~[mysql-connector-java-5.1.6-bin.jar:na]
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936) ~[mysql-connector-java-5.1.6-bin.jar:na]
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060) ~[mysql-connector-java-5.1.6-bin.jar:na]
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542) ~[mysql-connector-java-5.1.6-bin.jar:na]
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734) ~[mysql-connector-java-5.1.6-bin.jar:na]
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1885) ~[mysql-connector-java-5.1.6-bin.jar:na]
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93) ~[common_commons-dbcp.jar:1.2.2]
at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:594) ~[spring_spring.jar:2.0.6]
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:537) ~[spring_spring.jar:2.0.6]
... 92 common frames omitted
Caused by: java.net.SocketException: Software caused connection abort: recv failed
at java.net.SocketInputStream.socketRead0(Native Method) ~[na:1.8.0_131]
at java.net.SocketInputStream.socketRead(Unknown Source) ~[na:1.8.0_131]
at java.net.SocketInputStream.read(Unknown Source) ~[na:1.8.0_131]
at java.net.SocketInputStream.read(Unknown Source) ~[na:1.8.0_131]
at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:113) ~[mysql-connector-java-5.1.6-bin.jar:na]
at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:160) ~[mysql-connector-java-5.1.6-bin.jar:na]
at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:188) ~[mysql-connector-java-5.1.6-bin.jar:na]
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2428) ~[mysql-connector-java-5.1.6-bin.jar:na]
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2882) ~[mysql-connector-java-5.1.6-bin.jar:na]
... 102 common frames omitted
发现是由于当数据库连接池中的连接被创建而长时间不使用的情况下，该连接会自动回收并失效，但客户端并不知道，在进行数据库操作时仍然使用的是无效的数据库连接造成的。解决方法是，在jdbc配置中加上：
发现是由于当数据库连接池中的连接被创建而长时间不使用的情况下，该连接会自动回收并失效，但客户端并不知道，在进行数据库操作时仍然使用的是无效的数据库连接造成的。解决方法是，在jdbc配置中加上：
Xml代码- <property name="validationQuery">
- <value>select * from dual</value>
- </property>
这样客户端在使用一个无效的连接时会先对该连接进行测试，如果发现该连接已经无效，则重新从连接池获取有效数据库连接来使用。
解决方法来源：https://blog.csdn.net/qq_23412263/article/details/70257063