ResultSet showing empty but there is at least 1 record in the table

Question

ISSUE My ResultSet is empty but I know there is at least 1 record in the table ("tblStudents")?

CODE :

package smartdatabase;

/**
 *
 * @author travi
 */
import java.sql.*;

public class Conn {

    private Connection connect = null;
    private Statement stmt = null;

    public Conn() {
    }

    public void getStudents() {
        try {

            Class.forName("org.apache.derby.jdbc.ClientDriver");
            connect = DriverManager.getConnection("jdbc:derby://localhost:1527/SmartData", "root", 
"password");
            String sql;
            sql = "SELECT FirstName, Surname FROM tblStudents";
            stmt = connect.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

            if (rs.next()) {
                String Firstname = rs.getString("Firstname");
                String Surname = rs.getString("surname");
                System.out.println(Firstname + "    " + Surname);
            }else{
                System.out.println("Empty ResultSet");
            }
        } catch (SQLException se) {
            se.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}

I have tried checking the case sensitivity and it doesn't seem to be the issue.

I also tried ResultSet rs = stmt.executeQuery(sql); and it didn't change anything. Any ideas?

Answer 1

The rs.next() method will move the pointer of the current object to the next row.

So when you place the condition if (rs.next()) the pointer is pointing to the next row only which is null as you have said you have one row entry in the DB.

On calling the next() method for the first time the result set pointer/cursor will be moved to the 1st row. Again calling the next() method for the second time the result set cursor will be moved to the 2nd row. By this way it goes.

You can call the first() or beforeFirst() methods to reset the ResultSet cursor back to the first row.

You can find more explanation at : ResultSet---isBeforeFirst()---AND--first()

Now for checking empty you need to implement in the below way, move the cursor to the first position, it will return false if the result set is empty:

Option 1 :

  if (!rs.first()) {
      System.out.println("Empty ResultSet");
  } else {
      do {
          String Firstname = rs.getString("Firstname");
          String Surname = rs.getString("surname");
          System.out.println(Firstname + "    " + Surname);
      } while (rs.next());
  }

UPDATE :

Option 2 :

if (!rs.isBeforeFirst()) {    
    System.out.println("Empty ResultSet"); 
} else {
      do {
          String Firstname = rs.getString("Firstname");
          String Surname = rs.getString("surname");
          System.out.println(Firstname + "    " + Surname);
      } while (rs.next());
}

Option 3 :

if (!rs.isBeforeFirst() && rs.getRow() == 0) {    
    System.out.println("Empty ResultSet"); 
} else {
      do {
          String Firstname = rs.getString("Firstname");
          String Surname = rs.getString("surname");
          System.out.println(Firstname + "    " + Surname);
      } while (rs.next());
}

UPDATE 2 :

For DERBY database use the below parameters in createStatement:

// Replace : stmt = connect.createStatement();
stmt = connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                               ResultSet.CONCUR_READ_ONLY);

ResultSet rs = stmt.executeQuery(sql);

Then use the above option 1 mentioned.