Tomcat JDBC连接池问题:“语句已关闭" [英] Tomcat JDBC connection pool issue: "Statement is closed"

问题描述

我有一个使用Tomcat JDBC连接池的服务器应用程序.

I have a server application that uses the Tomcat JDBC connection pool.

这是我用来创建数据源的代码:

This is the code I use to create the DataSource:

PoolProperties connProperties = new PoolProperties();
connProperties.setUrl(resources.getProperty("db.url"));
connProperties.setDriverClassName(resources.getProperty("db.driver"));
connProperties.setUsername(resources.getProperty("db.user"));
connProperties.setPassword(resources.getProperty("db.password"));
connProperties.setJmxEnabled(true);
connProperties.setTestWhileIdle(false);
connProperties.setValidationQuery("SELECT 1");
connProperties.setTestOnReturn(false);
connProperties.setValidationInterval(30000);
connProperties.setTimeBetweenEvictionRunsMillis(30000);
connProperties.setMaxActive(500);
connProperties.setInitialSize(50);
connProperties.setMaxWait(10000);
connProperties.setRemoveAbandonedTimeout(60);
connProperties.setMinEvictableIdleTimeMillis(60000);
connProperties.setSuspectTimeout(60);
connProperties.setMaxIdle(50);
connProperties.setMinIdle(10);
connProperties.setLogAbandoned(false);
connProperties.setRemoveAbandoned(true);
connProperties.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
"org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");

dataSource = new DataSource();
dataSource.setPoolProperties(connProperties); 

然后我有一种方法可以从池中获取连接

Then I have a method to get a connection from the pool

protected Connection getDbConnection() throws Exception
{
    dbConn = dataSource.getConnection();
    return dbConn;
}

每次我想执行一条语句时，我都会调用此代码:

And every time I want to execute a statement I call this code:

protected CallableStatement executeCSqlQuery(String sql) throws Exception
{
    CallableStatement cstmt;
    ResultSet rs = null;

    try {
        cstmt = getDbConnection().prepareCall(sql);     
        cstmt.execute();            
    } catch (SQLException e) {
        throw e;
    }

    return cstmt;
}

这是对先前代码的调用示例:

And this is an example of a call to the previous code:

try {
    cstmt = dbConnection.executeCSqlQuery(query);
    rs = cstmt.getResultSet();
} catch (Exception e) {
    // do smething
} finally {
    try {
        if (cstmt != null) {
            cstmt.close();
        }
        dbConnection.shutdown();
    } catch (Exception e) {
        // do something
    }
}

public void shutdown() {
    if (this.dbConn != null) 
        this.dbConn.close();
}

我面临的问题是，每隔X秒在线程中执行一次调用时，就会不时地收到语句已关闭"的异常. 我不确定为什么会这样.我在想这可能是驱动程序错误或与数据库(在不同服务器上运行)的连接失败.

The problem I'm facing is that every now and then, I'm getting an exception "Statement is closed" when I execute a call in a Thread every X seconds. I'm not sure why this happens. I'm thinking that it could be a driver bug or something failing with the connection to the database (that runs in a different server).

我没主意了.我想念什么?

I'm out of ideas. What am I missing?

我应该改用 c3p0 连接池吗?

推荐答案

我创建了赏金计划来帮助Reznik，但最终通过查看他的代码来找出问题所在.

I created the bounty to help out Reznik but I ended up figuring out what the problem was by looking at his code.

问题是每次从池中获取新连接时

The problem is that every time a new connection is fetched from the pool in

protected Connection getDbConnection() throws Exception
{
    dbConn = dataSource.getConnection();
    return dbConn;
}

对象dbConn已更新为新连接.

示例:

T1调用getDbConnection()

T2调用getDbConnection()

T1执行查询，处理resultSet并调用shutdown()

T1 executes the query, processes the resultSet and calls shutdown()

public void shutdown() {
    if (this.dbConn != null) 
        this.dbConn.close();
}

因为T2更新了对象，所以T1

Because T2 updated the object, the connection being used by T2 will be shutdown by T1

T2尝试使用该连接，但该连接已关闭.

T2 tries to use the connection but it is already closed.

通过这种方式，而不是总是更新连接，只需返回它，然后添加额外的逻辑来关闭从池中获取的连接.

This way, instead of always updating the connection, just return it and then add the extra logic to close the connection that was fetched from the pool.

这篇关于Tomcat JDBC连接池问题:“语句已关闭"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！