我有一个程序向数据库发出多个请求,打开和关闭连接以执行它需要做的事情 . 对于每个连接,它执行一个返回50个结果和更新的选择;它每次连接大约做10次 . 之后,关闭连接,并采取新的连接 . 但是最近我们遇到了一些随机问题,其中出现了这个SQL异常:
java.sql.SQLException:您无法对已关闭的Statement进行操作!
此错误随机出现 . 它首先出现在执行中期,并且在我设法重现它的唯一时刻,它发生在程序的启动时(之后我再次启动它,没有做任何改变,它工作得非常好) . 我查看了代码,但在使用它之前连接没有关闭(在预准备语句中插入参数时发生错误) . 我已经在使用c3p0来管理连接池了,所以我不知道还能在哪里看 .
有人之前遇到过这个错误吗?关于在哪里寻找或如何重现它的任何建议,以便我可以正确测试它?
编辑:这是一段有问题的代码
try{
//send row to producer
producer.processItem(fields);
if (stmtLasProcessedTransaction == null) {
stmtLasProcessedTransaction = getDbConnection().prepareStatement("UPDATE JTICKET_SUBSCRIBER SET LAST_PROCESSED_ROW = ? WHERE NAME = ? ");
logger.trace("creating statement");
}
//update last processed transaction
logger.trace("Setting the primary key to the prepared statement");
stmtLasProcessedTransaction.setString(1, primaryKey);
logger.trace("Setting the name to the prepared statement");
stmtLasProcessedTransaction.setString(2, name);
logger.trace("Attempting to execute the update on JTICKET_SUBSCRIBER in consumer {}",this.name);
stmtLasProcessedTransaction.executeUpdate();
logger.trace("Commiting execution");
getDbConnection().commit();
logger.trace("Update on JTICKET_SUBSCRIBER in consumer {} executed successfully",this.name);
if (processedRows % 500 == 0) {
logger.trace("resetting prepared statement");
stmtLasProcessedTransaction.close();
logger.trace("statement closed");
stmtLasProcessedTransaction = null;
}
processedRows++;
}catch(SQLException sqlException){
logger.error("An SQL error ocurred while processing consumed item. Closing database connection and statement",sqlException);
try{
stmtLasProcessedTransaction.close();
logger.info("Previous prepared statement of db consumer {} closed",this.name);
}catch(Throwable throwable){
logger.info("Couldn't properly close the prepared statement of db consumer {}",this.name);
}finally{
stmtLasProcessedTransaction=null;
}
try{
databaseConnection.rollback();
logger.info("Rollback of db connection of consumer {} done successfully",this.name);
databaseConnection.close();
logger.info("Previous connection of db consumer {} closed",this.name);
}catch(Throwable throwable){
logger.info("Couldn't rollback and/or close the connection of db consumer {}",this.name);
}finally{
databaseConnection=null;
}
throw sqlException;
}catch(Exception exception){
logger.error("An error ocurred while processing consumed item.", exception);
throw exception;
}
准备好的语句是一个局部变量,而不是函数一,所以它可以在每个循环中重复使用(这个函数是一个函数的一部分,对于查询中的每个结果调用一次,该函数使用预先准备好的语句来完成相同的数据库连接) . 当我尝试处理预准备语句时,会发生错误 .
最新的堆栈:
18/06/15 15:54:34.841 [ecbcbcmt] TRACE DatabaseConsumer - 创建声明18/06/15 15:54:34.850 [ecbcbcmt]错误DatabaseConsumer - 处理消耗的项目时出错 . java.sql.SQLException:您无法对已关闭的Statement进行操作! at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)〜[mchange-commons-java-0.2.9.jar:0.2.9] at com.mchange.v2.sql.SqlUtils.toSQLException( SqlUtils.java:77)〜[mchange-commons-java-0.2.9.jar:0.2.9] at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setString(NewProxyPreparedStatement.java:488)~ [c3p0-0.9 .5.jar:0.9.5] at us.inswitch.jticket.subscriber.consumer.database.DatabaseConsumer.processRow(DatabaseConsumer.java:152)[bin /:na] at us.inswitch.jticket.subscriber.consumer.database .DatabaseNumberConsumer.processRows(DatabaseNumberConsumer.java:73)[bin /:na] at us.inswitch.jticket.subscriber.consumer.database.DatabaseConsumer.start(DatabaseConsumer.java:65)[bin /:na] at us.inswitch .jticket.subscriber.consumer.Consumer.run(Consumer.java:35)[bin /:na] at java.lang.Thread.run(Thread.java:662)[na:1.6.0_38-ea]引起: java.lang.NullPointerException:null at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setString(NewProxyPreparedStatement.java:482)~ [c3p0-0.9.5.jar:0.9 .5] ...省略了5个常见帧18/06/15 15:54:34.852 [ecbcbcmt] WARN DatabaseConsumer - 消耗表数据时出错 . DatabaseConsumer将重新启动 . java.sql.SQLException:您无法对已关闭的Statement进行操作! at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:118)〜[mchange-commons-java-0.2.9.jar:0.2.9] at com.mchange.v2.sql.SqlUtils.toSQLException( SqlUtils.java:77)〜[mchange-commons-java-0.2.9.jar:0.2.9] at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setString(NewProxyPreparedStatement.java:488)~ [c3p0-0.9 .5.jar:0.9.5] at us.inswitch.jticket.subscriber.consumer.database.DatabaseConsumer.processRow(DatabaseConsumer.java:152)〜[bin /:na] at us.inswitch.jticket.subscriber.consumer . database.DatabaseNumberConsumer.processRows(DatabaseNumberConsumer.java:73)〜[bin /:na] at us.inswitch.jticket.subscriber.consumer.database.DatabaseConsumer.start(DatabaseConsumer.java:65)〜[bin /:na] at us.inswitch.jticket.subscriber.consumer.Consumer.run(Consumer.java:35)[bin /:na] at java.lang.Thread.run(Thread.java:662)[na:1.6.0_38-ea]引起:java.lang.NullPointerException:null at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.setString(NewProxyPreparedStatement.java:482)~ [c3p0-0.9.5.jar: 0.9.5] ...省略了5个常用帧
这很奇怪,因为它完全是随机的 . 我们已经启动并运行了一段时间的程序,所有连接都运行得很好,我们遇到了这种问题 .
2 回答
使用局部变量,或者仔细使用PreparedStatements . 这避免了在Web应用程序中的并发使用 .
尝试资源也会在关闭时关闭抛出异常 .
语句stmtLasProcessedTransaction.close();出现在两个可疑的地方!
1.在条件中的try块内如果
2.在拦截区内
更好的编程实践是只有一个紧密的声明,这也是最后一个块
PS:你也可以在finally块中尝试catch-finally