java.sql.SQLException: 关闭的连接 解决办法

本文详细介绍了在使用C3P0连接Oracle数据库时遇到的问题,即长时间未进行数据库操作导致连接断开引发的Spring事务异常。通过配置C3P0参数,特别是idleConnectionTestPeriod、testConnectionOnCheckin和automaticTestTable,解决了异常问题。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

程序如果长时间不进行数据库操作,那么数据源中的 Connection 很可能已经断开。其原因有可能是防火墙,或者连接的数据库设置的超时时间。这里使用的是 C3P0 连接 oracle 数据库,引起的异常信息为:

 

 

org.springframework.transaction.TransactionSystemException: Could not roll back Hibernate transaction; nested exception is org.hibernate.TransactionException: rollback failed

at org.springframework.orm.hibernate4.HibernateTransactionManager.doRollback(HibernateTransactionManager.java:503)

at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:845)

at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:822)

at org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.java:411)

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:114)

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

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

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

at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:622)

at com.platform.dao.TasksDAO$$EnhancerByCGLIB$$abcb28f5.getTaskById(<generated>)

 

at com.platform.manager.TasksManager.getTaskById(TasksManager.java:287)

at com.platform.manager.TasksManager.checkResult(TasksManager.java:300)

at com.platform.timer.TimerBase.tasksListener(TimerBase.java:149)

at com.platform.timer.TimerBase.run(TimerBase.java:81)

at java.util.TimerThread.mainLoop(Timer.java:555)

at java.util.TimerThread.run(Timer.java:505)

Caused by: org.hibernate.TransactionException: rollback failed

at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.rollback(AbstractTransactionImpl.java:215)

at org.springframework.orm.hibernate4.HibernateTransactionManager.doRollback(HibernateTransactionManager.java:500)

... 15 more

Caused by: org.hibernate.TransactionException: unable to rollback against JDBC connection

at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doRollback(JdbcTransaction.java:167)

at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.rollback(AbstractTransactionImpl.java:209)

... 16 more

Caused by: java.sql.SQLException: 关闭的连接

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:207)

at oracle.jdbc.driver.PhysicalConnection.rollback(PhysicalConnection.java:1160)

at com.alibaba.china.jdbc.proxy.simple.ConnectionProxy.rollback(ConnectionProxy.java:228)

at com.mchange.v2.c3p0.impl.NewProxyConnection.rollback(NewProxyConnection.java:855)

at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doRollback(JdbcTransaction.java:163)

... 17 more

 

异常抛出的地方为 Spring 的事务管理 transactionManager,而并非程序代码。即便捕捉到异常,使用 sessionFactory.openSession 也会抛出如下异常:

 

 

org.springframework.transaction.TransactionSystemException: Could not roll back Hibernate transaction; nested exception is org.hibernate.TransactionException: rollback failed

at org.springframework.orm.hibernate4.HibernateTransactionManager.doRollback(HibernateTransactionManager.java:503)

at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:845)

at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:822)

at org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.java:411)

 

at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:114)

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

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

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

at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:622)

 

at com.platform.dao.TasksDAO$$EnhancerByCGLIB$$abcb28f5.getTaskByNewSession(<generated>)

at com.platform.manager.TasksManager.checkResult(TasksManager.java:303)

at com.platform.timer.TimerBase.tasksListener(TimerBase.java:149)

at com.platform.timer.TimerBase.run(TimerBase.java:81)

at java.util.TimerThread.mainLoop(Timer.java:555)

at java.util.TimerThread.run(Timer.java:505)

Caused by: org.hibernate.TransactionException: rollback failed

at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.rollback(AbstractTransactionImpl.java:215)

 

at org.springframework.orm.hibernate4.HibernateTransactionManager.doRollback(HibernateTransactionManager.java:500)

... 14 more

Caused by: org.hibernate.TransactionException: unable to rollback against JDBC connection

at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doRollback(JdbcTransaction.java:167)

at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.rollback(AbstractTransactionImpl.java:209)

... 15 more

Caused by: java.sql.SQLException: Io 异常: Software caused connection abort: recv failed

 

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:254)

at oracle.jdbc.driver.T4CConnection.doRollback(T4CConnection.java:577)

at oracle.jdbc.driver.PhysicalConnection.rollback(PhysicalConnection.java:1163)

at com.alibaba.china.jdbc.proxy.simple.ConnectionProxy.rollback(ConnectionProxy.java:228)

at com.mchange.v2.c3p0.impl.NewProxyConnection.rollback(NewProxyConnection.java:855)

at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doRollback(JdbcTransaction.java:163)

... 16 more

 

解决的办法是:

 

c3p0 自定义配置:

 

 

 

<!-- 数据库连接声明 -->

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"

destroy-method="close">

<property name="driverClass" value="com.alibaba.china.jdbc.SimpleDriver" />

<!-- <property name="driverClass" value="oracle.jdbc.OracleDriver" /> -->

<property name="jdbcUrl" value="jdbc:oracle:thin:@202.116.70.180:61166:orcl" />

<!-- 每60秒检查所有连接池中的空闲连接。Default: 0 -->

<property name="idleConnectionTestPeriod" value="60" />

  <!--如果设为true那么在取得连接的同时将校验连接的有效性。Default: false -->

<property name="testConnectionOnCheckin" value="true" />

<!--c3p0将建一张名为Test的空表,并使用其自带的查询语句进行测试。如果定义了这个参数那么 属性preferredTestQuery将被忽略。你不能在这张Test表上进行任何操作,它将只供c3p0测试 

使用。Default: null -->

<property name="automaticTestTable" value="Test" />

 

<property name="properties">

<props>

<prop key="clientEncoding">GBK</prop>

<prop key="serverEncoding">CP1252</prop>

<prop key="user">WDS</prop>

 <prop key="password">HelloOracle</prop> 

</props>

</property>

</bean>

 

配置项一定要作为 <bean> 的子元素,而不能做为 <properties> 的子元素,否则不起作用。

 

 

参考:

http://my.oschina.net/tianzimensheng/blog/65225

http://www.cnblogs.com/younes/archive/2012/06/01/2529483.html

http://www.cnblogs.com/safeking/archive/2007/02/01/637067.html

### 解决方案分析 `java.sql.SQLException: No operations allowed after statement closed` 错误通常发生在数据库操作语句(Statement)被关闭后,程序仍然试图对其进行操作。以下是导致该问题的常见原因及解决方案: #### 1. **确保 Statement 在使用后正确关闭** 如果在执行完 SQL 操作后没有显式地关闭 `Statement` 对象,可能会导致资源泄漏或状态混乱。因此,在完成所有数据库操作后,应确保调用 `statement.close()` 方法以释放资源[^1]。 ```java try { Statement stmt = connection.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM users"); // 处理结果集 } catch (SQLException e) { e.printStackTrace(); } finally { if (stmt != null) { try { stmt.close(); // 显式关闭 Statement } catch (SQLException e) { e.printStackTrace(); } } } ``` #### 2. **避免重复使用已关闭的 Statement** 程序中可能存在逻辑错误,导致尝试对已经关闭的 `Statement` 对象进行操作。为防止这种情况,可以在每次操作前检查 `Statement` 是否为 `null` 或重新创建一个新的 `Statement` 实例[^1]。 ```java if (stmt == null || stmt.isClosed()) { stmt = connection.createStatement(); } ``` #### 3. **调整数据库连接池配置** 如果使用了数据库连接池(如 Druid、C3P0),需要确保连接池的配置与数据库的超时时间一致。例如,如果 MySQL 的 `wait_timeout` 设置为 7200 秒,则连接池的 `minEvictableIdleTimeMillis` 和 `timeBetweenEvictionRunsMillis` 参数应适当调整以避免连接因长时间闲置而被关闭[^5]。 ```xml <property name="minEvictableIdleTimeMillis" value="600000" /> <property name="timeBetweenEvictionRunsMillis" value="300000" /> <property name="validationQuery" value="SELECT 1" /> <property name="testWhileIdle" value="true" /> ``` #### 4. **捕获异常并重新初始化连接** 在某些情况下,数据库连接可能因为网络问题或其他原因被意外关闭。可以通过捕获 `SQLException` 并根据异常状态码重新初始化连接来解决此类问题[^3]。 ```java try { stmt.executeUpdate("UPDATE users SET status = 1 WHERE id = 1"); } catch (SQLException sqlEx) { String sqlState = sqlEx.getSQLState(); if ("08S01".equals(sqlState)) { // 表示连接中断 // 重新建立数据库连接 connection = DriverManager.getConnection(DB_URL, USER, PASS); stmt = connection.createStatement(); stmt.executeUpdate("UPDATE users SET status = 1 WHERE id = 1"); } } ``` #### 5. **合理管理数据库连接的生命周期** 如果在代码中手动管理数据库连接,需确保在不再需要连接时调用 `connection.close()` 方法。此外,应避免在关闭连接后继续使用该连接对象[^2]。 ```java Connection conn = null; try { conn = DBConnect.getConnect(); // 执行数据库操作 } catch (SQLException e) { e.printStackTrace(); } finally { if (conn != null) { try { conn.close(); // 关闭连接 } catch (SQLException e) { e.printStackTrace(); } } } ``` --- ###
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值