ResultSet prematurely closing

Question

I'm compiling a list of Page variables (which has a list of books in it) from a running MYSQL Database. When attempting the second iteration in the while(rs.next()) loop, I receive an SQL Exception saying that the ResultSet was already closed. I see nowhere that this code closes the rs object.

try {
    stmt = con.createStatement();
    ResultSet rs =
            stmt.executeQuery("SELECT pageURL," +
                    "pageName FROM pages GROUP BY pageName;");
    ResultSet rs2;
    while(rs.next()) { // Where the error occurs on the second pass
        Page tempP = new Page(rs.getString(1),rs.getString(2));
        rs2 = stmt.executeQuery("SELECT `books`.`itemID`,cost," +
                "title,author,shortD,longD FROM " +
                "books INNER JOIN pages ON " +
                "books.itemID=pages.itemID WHERE " +
                "pageName='" + rs.getString(2) + "';");
        while(rs2.next()) {
            tempP.addBook(new Book(rs2.getInt(1),
                    rs2.getFloat(2),rs2.getString(3),
                    rs2.getString(4),rs2.getString(5),
                    rs2.getString(6)));
        }
        pages.addPage(tempP);
    }
} catch(SQLException e) {
    System.err.print("SQLException: ");
    System.err.println(e.getMessage());
}

Here is the contents of the pages table:

|pageName  |pageURL    |itemID|
-------------------------------
|Tech Books|./techbooks|1     |
-------------------------------
|Tech Books|./techbooks|2     |
-------------------------------
|Kids Books|./kidsbooks|3     |
-------------------------------
|Kids Books|./kidsbooks|4     |
-------------------------------
|Kids Books|./kidsbooks|5     |
-------------------------------

EDIT:

Okay, it looks like that ResultSet becomes invalid when I use the Statement again. Does anyone have any suggestions to remedy this situation?

Answer 1

To quote the javadocs for Statement:

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.

Create two statements, or better yet use PreparedStatements with bound variables.

Answer 2

See the documentation of ResultSet:

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.