java recv failed_错误：Caused by: java.net.SocketException: Software caused connection abort: recv fail...

使用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代码  

select * from dual

这样客户端在使用一个无效的连接时会先对该连接进行测试，如果发现该连接已经无效，则重新从连接池获取有效数据库连接来使用。

Oracle 的设置是select * from dual  ；

mysql 的值是select 1 ；

解决方法来源：https://blog.csdn.net/qq_23412263/article/details/70257063

扩展：

DBCP针对不同数据库的validationQuery

DBCP针对不同数据库的validationQuery

当你使用DBCP连接池时，你可以通过设置testOnBorrow和testOnReturn属性测试这个连接是否可用。不幸的是你还需要设置validationQuery才能起作用。那么问题来了如何设置validationQuery这个值呢？

什么是validationQuery？

validationQuery是用来验证数据库连接的查询语句，这个查询语句必须是至少返回一条数据的SELECT语句。每种数据库都有各自的验证语句，下表中收集了几种常见数据库的validationQuery。

DataBasevalidationQuery

hsqldb

select 1 from INFORMATION_SCHEMA.SYSTEM_USERS

Oracle

select 1 from dual

DB2

select 1 from sysibm.sysdummy1

MySql

select 1

Microsoft SqlServer

select1

postgresql

select version()

ingres

select 1

derby

values 1

H2

select 1

根据JDBC驱动获取validationQuery

如果你想支持多种数据库，你可以根据JDBC驱动来获取validationQuery，这里有个简单的类，根据JDBC驱动名称来获取validationQuery

import java.io.IOException;

import java.io.InputStream;

import java.util.Properties;

public class ValidationQuery {

public String getValidationQuery(String driver) {

Properties properties = loadProperties();

return properties.getProperty(driver, "");

}

private Properties loadProperties() {

String propertyFilename = "db.validation.properties";

try {

Properties props = new Properties();

InputStream resourceAsStream = this.getClass().

getClassLoader().getResourceAsStream(propertyFilename);

props.load(resourceAsStream);

resourceAsStream.close();

return props;

} catch (IOException e) {

throw new RuntimeException("Cannot load properties file '" + propertyFilename + "'.", e);

}

}

//Example: Get validationQuery for hsqldb

public static void main(String[] args) {

System.out.println(new ValidationQuery().getValidationQuery("org.hsqldb.jdbcDriver"));

}

}

创建“db.validation.properties”文件，并放置在classpath目录下

#hsqldb

org.hsqldb.jdbcDriver=select 1 from INFORMATION_SCHEMA.SYSTEM_USERS

#Oracle

oracle.jdbc.driver.OracleDriver=select 1 from dual

#DB2

com.ibm.db2.jcc.DB2Driver=select 1 from sysibm.sysdummy1

#mysql

com.mysql.jdbc.Driver=select 1

org.gjt.mm.mysql.Driver=select 1

#microsoft sql

com.microsoft.sqlserver.jdbc.SQLServerDriver=select 1

#postgresql

org.postgresql.Driver=select version();

#ingres

com.ingres.jdbc.IngresDriver=select 1

#derby

org.apache.derby.jdbc.ClientDriver=values 1

#H2

org.h2.Driver=select 1