CODEWITHSUNDEEP
javajspexceptionjdbcresultset
nikiforosb
nikiforosb

Reputation: 23

Calling a method which returns Resultset

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

Upvotes: 0

Views: 969

Answers (4)

PA001
PA001

Reputation: 451

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;
}

Upvotes: 1

JonK
JonK

Reputation: 2108

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.

Upvotes: 1

user140547
user140547

Reputation: 8200

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?

Upvotes: 0

neurotic-d
neurotic-d

Reputation: 76

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.

Upvotes: 2

Related Questions