Calling a method which returns Resultset

Question

I am building a 3-tier application which uses java classes for retrieving data from db and jsp pages for the user interface. When I try to call a method in a jsp page which returns resultset, I get this error message:

Operation not allowed after ResultSet closed.

For example, I have this method:

public ResultSet getName(String country) throws SQLException {
    String name;
    try {
        open();
        stmt1 = con.prepareStatement(fetchPanepistimio);
        stmt1.setString(1,country);
        rs1 = stmt1.executeQuery();
        stmt1.close();
        close();
    }catch(Exception e2) {
    }

    return rs1;

}

and when I write in a jsp page the following:

<%

ResultSet rs = panepistimio.getName(country);

while(rs.next()) { %>

...........
<% } %>

I get the exception which I described. Have you got any idea about what is going wrong? Thank you in advance

What you exactly want to return, the ResultSet or the Query result? — Bahramdun Adil, Dec 17 '15 at 08:39
the contents of the data base. In this case, I want the names of the universities which are on the database — nikiforosb, Dec 17 '15 at 08:41
Then you first get the content from ResultSet then return it in String, but you close the ResultSet then return, it definitely not working. — Bahramdun Adil, Dec 17 '15 at 08:44

score 2 · Answer 1 · answered Dec 17 '15 at 08:48

You are trying to access the ResultSet after closing the database connection stmt1.close(); and close();), which doesn't work that way. (see Oracle's tutorial for a longer explanation).

Instead of leaving the connection open, you should retrieve the content of the ResultSet and then use it in your JSP page. That way, you decouple the life cycle of the DB query from your view life cycle.

PA001 · Answer 2 · 2015-12-17T10:18:12.413

In your example, the ResultSet is closed when you call stmt1.close(). Perform your database access within the single method, add the results to an appropriate Collection and return it.

For example:

public Collection<String> getName(String country) {
    String sql = "...";
    Collection<String> names = new ArrayList<>();
    try(Statement statement = ...; ResultSet resultSet = statement.executeQuery(sql)){
        while(resultSet.next()) {
            names.add(resultSet.getString(...));
        }
    } catch (SQLException e) {
        // LOG
    }
    return names;
}

score 1 · Answer 3 · edited Jun 20 '20 at 09:12

This is because you're closing the PreparedStatement object that created the ResultSet immediately after executing the query (and potentially also closing the Connection).

From the documentation for the Statement#close() method (PreparedStatement is a child of Statement):

Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed. It is generally good practice to release resources as soon as you are finished with them to avoid tying up database resources.

Calling the method close on a Statement object that is already closed has no effect.

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

Emphasis mine.

Ideally you should materialise the contents of your ResultSet into a formal collection and send that collection to the JSP instead of the ResultSet.

score 0 · Answer 4 · edited May 23 '17 at 11:52

0

You call close on the statement, which you should not.

Second, you should not swallow exeptions as in catch(Exception e2) {}. This makes it hard or impossible to trace errors.

Third, it is not good practise to iterate over a ResultSet in a JSP. This should be done in the backend e.g. (in getName). see How to avoid Java code in JSP files?

edited May 23 '17 at 11:52

Community

1
1

answered Dec 17 '15 at 08:49

user140547

7,750
3
28
80

Calling a method which returns Resultset

4 Answers4