resultSet not open, works in one case another case not

Question

Why if i query the database from the db class using queryFromDb() i get "ResultSet not open" when it try to do rs.next() for the second time and instead if i try to use queryFromMain everything works?

public class Db {

private String protocol = "jdbc:derby:";
private ResultSet resultSet = null;
private Connection connection = null;
private Statement statement;

public Db(){

    try{
        Properties props = new Properties(); 
        props.put("user", "user");
        props.put("password", "password");

        String dbName = "database";
        connection = DriverManager.getConnection(protocol + dbName  , props);

    }
    catch(SQLException sqle){
        printSQLException(sqle);
    }
}

public ResultSet returnValue(String query){

    try{
        statement = connection.createStatement();

        resultSet = statement.executeQuery(query);  
    }
    catch(SQLException sqle){
        printSQLException(sqle);
    }

    return resultSet;   
}


public void queryFromDb(){
         try {
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery("SELECT * FROM clsbck ORDER BY id");  
            while(rs.next()){
                System.out.println(rs.getString(2));
                String str = "INSERT INTO cls rck VALUES 2";
                [...]           
                statement.execute(str); 
            }
         } catch (SQLException e) {
            printSQLException(e);
         }  
    }

   }
}

public class Main {
    private static Db db;

    public static void main(String[] args){
        db = new Db();
    }

    public static void queryFromMain(){
        ResultSet rs = db.returnValue("SELECT * FROM clsbck ORDER BY id");
        try {   
           while(rs.next()){
               String str = "INSERT INTO cls rck VALUES 2";     
               [...]
               db.addValue(str);
           }
        } catch (SQLException e) {
            printSQLException(e);
        }   
    }
}

Answer 1

You should only have one ResultSet open per Statement; in queryFromDb you are opening one in statement.executeQuery and (implicitly) another one inside the loop in statement.execute(str). From the documentation:

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

You could fix the method by doing something like this:

public void queryFromDb() {

    Statement queryStatement = null;
    PreparedStatement insertStatement = null;

    try {
        queryStatement = connection.createStatement();
        insertStatement = connection.prepareStatement(
                             "INSERT INTO cls rck VALUES 2");

        ResultSet rs = queryStatement
                .executeQuery("SELECT * FROM clsbck ORDER BY id");

        while (rs.next()) {
            System.out.println(rs.getString(2));
            // [...]
            insertStatement.executeUpdate();
        }
        rs.close();
    } catch (SQLException e) {
        printSQLException(e);
    } finally {
        if (queryStatement != null) {
            queryStatement.close();
        }
        if (insertStatement != null) {
            insertStatement.close();
        }
    }
}

You shouldn't use global (method-wide) Statement objects in your code; instead, declare them locally. For the SQL INSERT that happens within the loop, you should use a PreparedStatement, which is prepared outside of the loop and executed inside (replace the value in the query with a bind placeholder (?) if you want to change the value to be inserted on each iteration).

Finally, remember to close your resources (ResultSets and Statements) after use. If you're on Java 7 or above, the most fluent way to achieve this is by using the try-with-resources statement.

Answer 2

It is possible that because you are reusing the object statement within the while loop recordset gets reset.