You can't operate on a closed Connection!!!

最新推荐文章于 2022-02-16 13:57:53 发布

DTDX002

最新推荐文章于 2022-02-16 13:57:53 发布

阅读量2.8k

点赞数

分类专栏： java web tomcat deployment

本文链接：https://blog.csdn.net/DTDX002/article/details/45209603

版权

java web tomcat deployment 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

c3p0 连接后报错。

still having You can't operate on a closed ResultSet error after using connection pool

up vote 0 down vote favorite

I have many threads accessing MYSQL database, at first I didn't use connection pool so I had this error "You can't operate on a closed ResultSet"

I searched on Google and found out that I should used connection pool so I tried c3p0 API for implementation, but I still have the same problem and nothing changed. so should I Synchronize getAllcountries method or there's another better solution.

public class DataSource {

private static DataSource datasource;
private ComboPooledDataSource cpds ; 

private DataSource() throws IOException, SQLException, PropertyVetoException {
    cpds = new ComboPooledDataSource();
    cpds.setDriverClass("com.mysql.jdbc.Driver"); //loads the jdbc driver
    cpds.setJdbcUrl("jdbc:mysql://localhost/question_game");
    cpds.setUser("root");
    cpds.setPassword("");
    cpds.setMaxPoolSize(500);
    }

public static DataSource getInstance() throws IOException, SQLException, PropertyVetoException {
    if (datasource == null) {
        datasource = new DataSource();
        return datasource;
    } else {
        return datasource;
    }
}

public  Connection getConnection() throws SQLException {
    return this.cpds.getConnection();
}


public  List<Country> getAllCountries() {
    String query = "SELECT * FROM country order by name ";
    List<Country> list = new ArrayList<Country>();
    Country country = null;
    ResultSet rs = null;
    try {
        try {
            connection = DataSource.getInstance().getConnection();
            } catch (IOException e) {
            e.printStackTrace();
        } catch (PropertyVetoException e) {
            e.printStackTrace();
        }
        statement = connection.createStatement();
        rs = statement.executeQuery(query);
        while (rs.next()) {
              //getting countries
        }
    } catch (SQLException ex) {
        ex.printStackTrace();
    } finally {
        //DbUtil used to check if null 
        DbUtil.close(rs);
        DbUtil.close(statement);
        DbUtil.close(connection);
    }
    return list;

}

原因：

In addition to @stephen-c 's observation, you basically have two options: either synchronize getAllCountries method, or make the database connection local to that method instead of having it as a class member.

As you have it now, 'connection' is a class member (available to all invocations of getAllCountries(), on all threads), so the connection is probably being overwritten by a second thread. Move it to a variable in the method, and then each invocation of the method will have its own connection.

原文：http://stackoverflow.com/questions/26198654/still-having-you-cant-operate-on-a-closed-resultset-error-after-using-connectio