CODEWITHSUNDEEP
javamysqlmultithreadingjdbcc3p0
Marah
Marah

Reputation: 565

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

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;

}

Upvotes: 0

Views: 3691

Answers (2)

Stephen C
Stephen C

Reputation: 718826

I see a method called getConnection, I don't see where you are calling it. Instead, I see your getAllCountries method using a static DataSource object that is created like this:

new DataSource();

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 change

Yea ...

It looks like you just cut-and-pasted the code you found into your application without really thinking about it. Obviously ... you need to understand what the code is doing AND figure out how to use it in your application.

Upvotes: 0

GreyBeardedGeek
GreyBeardedGeek

Reputation: 30088

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.

Upvotes: 2

Related Questions