Operation not allowed after ResultSet closed

Question

Allright been trying to figure this out the last 2 days.

Statement statement = con.createStatement();
                        String query = "SELECT * FROM sell";
                        ResultSet rs = query(query);
                        while (rs.next()){//<--- I get there operation error here

This is the query method.

    public static ResultSet query(String s) throws SQLException {
        try {
            if (s.toLowerCase().startsWith("select")) {
                if(stm == null) {
                    createConnection();
                }
                ResultSet rs = stm.executeQuery(s);
                return rs;
            } else {
                if(stm == null) {
                    createConnection();
                }
                stm.executeUpdate(s);
            }
            return null;
        } catch (Exception e) {
            e.printStackTrace();
            con = null;
            stm = null;
        }
        return null;
    }

How can I fix this error?

Answer 1

there are few things you need to fix. Opening a connection, running a query to get the rs, closing it, and closing the connection all should be done in the same function scope as far as possible. from your code, you seem to use the "con" variable as a global variable, which could potentially cause a problem. you are not closing the stm object. or the rs object. this code does not run for too long, even if it has no errors. Your code should be like this:

if (stringUtils.isBlank(sql)){
     throw new IllegalArgumentsException ("SQL statement is required");
}
Connection con = null;
PreparedStatement ps =null;
Resultset rs = null;
try{
         con = getConnection();
         ps = con.preparestatement(sql);
         rs = ps.executeQuery();
         processResults(rs);
         close(rs);
         close(ps);
         close(con);
}catch (Execption e){
        log.Exception ("Error in: {}", sql, e);
        throw new RuntimeException (e);
}finally{
        close(rs);
        close(ps);
        close(con);
}

Answer 2

IMHO, you should do everything you need with your ResultSet before you close your connection.

Answer 3

I know this is a few years late, but I've found that synchronizing the db methods usually get rid of this problem.

Answer 4

use another Statement object in inner loop Like

Statement st,st1;

st=con.createStatement();
st1=con.createStatement();

//in Inner loop
while(<<your code>>)
{
   st1.executeQuery(<<your query>>);
}

Answer 5

It's hard to be sure just from the code you've posted, but I suspect that the ResultSet is inadvertently getting closed (or stm is getting reused) inside the body of the while loop. This would trigger the exception at the start of the following iteration.

Additionally, you need to make sure there are no other threads in your application that could potentially be using the same DB connection or stm object.