ResultSet closing immediately after executing query

Question

I'm using the following method to show information from a database in a JavaFX Application

public void showFirstSix() {
    PreparedStatement takeFields = null;
    ResultSet rs = null;
    String sqlTakeFields = "SELECT * FROM VirtualProductTable WHERE VirtualProductTable MATCH name=? ORDER BY rank DESC";
    try {
        takeFields = this.newSearchModel.connection.prepareStatement(sqlTakeFields);
        takeFields.setString(1, nameSearch.getText());
        rs = takeFields.executeQuery();
    } catch (SQLException e1) {
        e1.printStackTrace();
    }

    // gets a sorted list of matching products, next we just need to print them out entry by entry //
    // we'll traverse every row in the result set, and print all needed values before moving to the next row //

    try {

        // first entry
        String id = rs.getString(2);
        String name = rs.getString(3);
        String description = rs.getString(4);
        String price = rs.getString(5);
        String length = rs.getString(6);
        String width = rs.getString(7);
        String height = rs.getString(8);
        String dateAdded = rs.getString(9);
        Blob image = rs.getBlob(14);

        id1.setText(id);
        name1.setText(name);
        description1.setText(description);
        dateAdded1.setText(dateAdded);
        price1.setText(price);
        length1.setText(length);
        width1.setText(width);
        height1.setText(height);
        image1.setImage((Image) image);

        // second entry
        rs.next();

        id = rs.getString(2);
        name = rs.getString(3);
        description = rs.getString(4);
        price = rs.getString(5);
        length = rs.getString(6);
        width = rs.getString(7);
        height = rs.getString(8);
        dateAdded = rs.getString(9);
        image = rs.getBlob(14);

        id2.setText(id);
        name2.setText(name);
        description2.setText(description);
        dateAdded2.setText(dateAdded);
        price2.setText(price);
        length2.setText(length);
        width2.setText(width);
        height2.setText(height);
        image2.setImage((Image) image);

        ...

    } catch (SQLException e1) {
        e1.printStackTrace();
    } 

}

As soon as this method is executed I get an error pointing to the first rs.getString() line in the second try block, claiming that the ResultSet is closed

It seems my true issue occurs in the first try block, immediately after/during rs = takeFields.executeQuery()- I had tested it with some System.out.println(rs.isClosed()) and System.out.println(takeFields.isClosed()) right after the aforementioned query, and confirmed that the Prepared Statement takeFields is actually still open, but the Result Set rs is immediately closed after the query execution.

I was looking through similar questions and I've confirmed that this particular Prepared Statement is never used or referenced anywhere else, and it's never closed, so I'm trying to figure out why this Result Set keeps immediately closing and how to make it cooperate.

Please refer to the [JDBC Tutorials](https://docs.oracle.com/javase/tutorial/jdbc/basics/processingsqlstatements.html), after `PreparedStatement#executeQuery` has been executed, the `Cursor` is in the "before start", you MUST call `ResultSet#next` to move the `Cursor` to the next row - also, you should be checking the return result of `next` to make sure that the `Cursor` was moved to a valid row — MadProgrammer, May 05 '18 at 04:09

score 2 · Accepted Answer · answered May 05 '18 at 04:07

Typical processing resultset iterator uses next method call, like the following:

  ResultSet rs = stmt.executeQuery(query);
  while (rs.next()) {

    //read record
  }

In your case make sure you are calling next before reading first record:

  ResultSet rs = stmt.executeQuery(query);
  if(! rs.next()) {
    return;
  }
  //read first reacord

   if(! rs.next()) {
    return;
  }
  //read second reacord

ResultSet closing immediately after executing query

1 Answers1