Resultset is empty when I close preparedStatement

Question

I have such a method:

private static ResultSet select (Connection connection, String query) {
        PreparedStatement selectQuery = null;
        ResultSet resultSet = null;
        try {
            selectQuery = connection.prepareStatement(query);
            resultSet = selectQuery.executeQuery();
            selectQuery.close();
        } catch (SQLException e) {
            System.out.println(e);
        }
        return resultSet;
    }

The thing is that the resultSet is always empty when I close the preparedStatement. If I comment out the line with clothing preparedStatement //selectQuery.close(); everything is fine. I close it after assigning value to the resultSet. So why it's empty?

probably because it gets closed by the query close. You should not return ResultSet, use them "in place" and dispose of them — , May 02 '17 at 16:54
Related: https://stackoverflow.com/questions/1039419/when-to-close-connection-statement-preparedstatement-and-resultset-in-jdbc?rq=1 — , May 02 '17 at 16:54

score 1 · Accepted Answer · answered May 02 '17 at 16:54

1

A ResultSet is associated with an executed Statement. Close the statement and the resultset, with any data in it is cleared.

You need to process the resultset before you close the statement, so your approach will not work.

answered May 02 '17 at 16:54

Kayaman

72,141
5
83
121

Stephen C · Answer 2 · 2017-05-02T17:03:16.490

Because the javadoc says so:

Note: When a Statement object is closed, its current ResultSet object, if one exists, is also closed.

Rationale: The stated behavior of Statement.close() is to release all resources. One of those resources is the server-side cursor for reading the results. But if you release that, then the ResultSet has nothing to pull data from.

I'm curious how you are determining that the (closed) ResultSet is "empty". It looks like all operations on a closed ResultSet (apart from close()) ought to throw an exception.

davidxxx · Answer 3 · 2017-05-02T17:01:45.047

You have not to close the statement before having retrieved the data of the resultset otherwise these may be not accessible.
When you call this method, its ResultSet objects are closed.

So, only when you are finished using a Statement, call the Statement.close() method.

The close should be performed in a finally statement.
In this way you are ensure that you don't worry to wonder when close it.

With your actual code :

private static ResultSet select (Connection connection, String query) {
        PreparedStatement selectQuery = null;
        ResultSet resultSet = null;
        try {
            selectQuery = connection.prepareStatement(query);
            resultSet = selectQuery.executeQuery();
        } catch (SQLException e) {
            System.out.println(e);
        }
        finally {
           if (selectQuery != null) { selectQuery.close(); }
        }
        return resultSet;
    }    
}

A better alternative is using a try-with-resources statement :

try (Statement stmt = con.createStatement()) {
    // ...
}

score 1 · Answer 4 · answered May 02 '17 at 16:57

You have to iterate through the ResultSet. You have an high level example right here:

try{ 
  // execute the query
  ResultSet rs = st.executeQuery(query);

  // iterate through the result set
  while (rs.next())
  {
    // Replace with your data
    int id = rs.getInt("id");
    String name = rs.getString("name");

    // do stuff with the result set, add to a List of objects (for example)
  }
  selectQuery.close();
}catch(SQLException e) {
        System.out.println(e);
}

Resultset is empty when I close preparedStatement

4 Answers4