使用Spring JDBC时遇到的Software caused connection abort: recv failed问题

在使用Spring jdbc连接数据库时遇到一个头疼的问题：程序启动以后，如果长时间不访问调用，当再次调用时会报错：

引用

org.springframework.dao.DataAccessResourceFailureException: ConnectionCallback; SQL []; IO 错误: Software caused connection abort: recv failed; nested exception is java.sql.SQLRecoverableException: IO 错误: Software caused connection abort: recv failed
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:253)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:349)
at services.dao.DBUtil.subRptInfo(DBUtil.java:208)
at services.impl.ServiceImpl.subRptInfo(ServiceImpl.java:170)
at sun.reflect.GeneratedMethodAccessor65.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
Caused by: java.sql.SQLRecoverableException: IO 错误: Software caused connection abort: recv failed
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1062)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1329)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3685)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4714)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1376)
at org.apache.commons.dbcp.DelegatingCallableStatement.execute(DelegatingCallableStatement.java:221)
at services.dao.DBUtil$2.doInConnection(DBUtil.java:260)
at services.dao.DBUtil$2.doInConnection(DBUtil.java:1)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:342)
... 39 more
Caused by: java.net.SocketException: Software caused connection abort: recv failed
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream.java:147)
at oracle.net.ns.Packet.receive(Packet.java:300)
at oracle.net.ns.DataPacket.receive(DataPacket.java:106)
at oracle.net.ns.NetInputStream.getNextPacket(NetInputStream.java:315)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:260)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:185)
at oracle.net.ns.NetInputStream.read(NetInputStream.java:102)
at oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket(T4CSocketInputStreamWrapper.java:124)
at oracle.jdbc.driver.T4CSocketInputStreamWrapper.read(T4CSocketInputStreamWrapper.java:80)
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1137)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:290)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:204)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1041)
... 48 more

到处寻找资料后，发现是由于当数据库连接池中的连接被创建而长时间不使用的情况下，该连接会自动回收并失效，但客户端并不知道，在进行数据库操作时仍然使用的是无效的数据库连接造成的。解决方法是，在jdbc配置中加上：

<property name="validationQuery">
    <value>select * from dual</value>
</property>

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

评论

3 楼 hzy252 2016-07-28  

请问你说的这段配置应该加在哪个地方，我现在的配置如下：
<?xml version='1.0' encoding='UTF-8'?>
<jdbc-data-source xmlns="http://xmlns.oracle.com/weblogic/jdbc-data-source" xmlns:sec="http://xmlns.oracle.com/weblogic/security" xmlns:wls="http://xmlns.oracle.com/weblogic/security/wls" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/weblogic/jdbc-data-source http://xmlns.oracle.com/weblogic/jdbc-data-source/1.0/jdbc-data-source.xsd">
  <name>TxLife</name>
  <jdbc-driver-params>
    <url>jdbc:oracle:thin:@172.25.14.19:1521:g19u1</url>
    <driver-name>oracle.jdbc.OracleDriver</driver-name>
    <properties>
      <property>
        <name>user</name>
        <value>dahsing_p2_mt_dev_app</value>
      </property>
    </properties>
    <password-encrypted>{AES}6+zVwIoXV64NOH2w71LPokzaS+8aMFVhSvIWAqKQcP1jWAFCYjs4LFpwaz+D1JCI</password-encrypted>
  </jdbc-driver-params>
  <jdbc-connection-pool-params>
    <initial-capacity>1</initial-capacity>
    <max-capacity>15</max-capacity>
    <capacity-increment>1</capacity-increment>
    <test-table-name>SQL SELECT 1 FROM DUAL</test-table-name>
    <statement-cache-size>10</statement-cache-size>
    <statement-cache-type>LRU</statement-cache-type>
  </jdbc-connection-pool-params>
  <jdbc-data-source-params>
    <jndi-name>TxLife</jndi-name>
    <global-transactions-protocol>OnePhaseCommit</global-transactions-protocol>
  </jdbc-data-source-params>
</jdbc-data-source>

2 楼 godandghost 2013-04-11  

这个是spring jdbc默认调用的，和我们自己的程序没有关系。

1 楼 zqj15011 2013-04-09  

<property name="validationQuery"> 
    <value>select * from dual</value> 
</property> 
配上这个程序是如何调用的？