How to check if ResultSet is empty in JDBC - Java Example

The JDBC ResultSet doesn't provide any isEmpty(), length() or size() method to check if its empty or not. Hence, when a Java programmer needs to determine if ResultSet is empty or not, it just calls the next() method and if next() return false it means ResultSet is empty. This is correct but the main problem with this approach is if the ResultSet is not empty then you may miss the first row if you follow the standard idiom to iterate over ResultSet and get the rows which involve calling next() method of ResultSet in a while loop. The key thing, which you need to remember is that initially the ResultSet's cursor points to before the first row when you call the next() method it points to the first row and if you don't get this data and calls the next() method again then you will lose the first row.

Alternatively, you need to call the beforeFirst() method to reset the cursor where it was before i.e. before the first row. Since beforeFirst() is not guaranteed to be supported in all database e.g. HSQL database doesn't support it, you should rather use a do-while loop to iterate over ResultSet. This way you won't lose the first row as shown in our sample program.

If you want to know more about how to work effectively with a database in Java application I suggest reading Practical Database Programming with Java By Ying Bai, he has explained many subtle details of JDBC and Java, which are essential for any experienced Java programmer.




Wrong way to check if ResultSet is empty

When you ask a Java developer to print something when ResultSet is empty and display the result otherwise, this is how most of them write at first :

    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * from Employee");

    // checking if ResultSet is empty
    if (rs.next() == false) {
      System.out.println("ResultSet in empty in Java");
    }

    // Iterating over ResultSet, since you are calling
    // the next method again, it will now point to
    // the second row and you have missed the first row
    while (rs.next()) {
      String data = rs.getString("MIC");
      System.out.println(data);
    }

This seems pretty straightforward and many Java developer will not find any problem or bug unless someone reports to them that some data is missing. Since this code doesn't throw any compile time error or runtime exception but introduces a subtle logical bug, which is very hard to trace especially if you are not a Java veteran working in JDBC for years.


The code will work fine if ResultSet is empty but if ResultSet is not empty it introduces a hard to find bug, where the first row of the result is missed. When you call the next() method first time, the ResultSet cursor moves to the first row but this time if you don't call getXXX() method to retrieve data from the row and call the next() method again than ResultSet cursor will point to the second row and you have lost the first row, at least your application will think that only N-1 records are returned.

If you are not familiar with retrieving data from ResultSet, you can read Core Java, Volume II--Advanced Features by Cay S. Horstmann to start learning basics of JDBC. One of the better book to learn JDBC.

How to check if ResultSet is empty in JDBC - Java


Right way to check if ResultSet is empty

There are couple of solution of above problem e.g. calling the first() or beforeFirst() to reset the ResultSet cursor back to the first row, but these methods are not guaranteed to be supported in all database e.g. HSQL doesn't support first() and beforeFirst().

A better way to solve this problem is to get the data from the first row before calling the next() method again and you can do that by using do while loop, which checks the condition at the end of the loop as oppose to start of the loop in case of a while loop. This way, you will not lose the first row from ResultSet.

 if (rs.next() == false) {
        System.out.println("ResultSet in empty in Java");
      } else {

        do {
          String data = rs.getString("emp_name");
          System.out.println(data);
        } while (rs.next());
      }

You can see that we are first checking if rs.next() and then immediately printing the result using a do-while loop to avoid losing the first row. This is the right way to check if ResultSet is empty and only process record when it really has. See Java How to Program by Dietel to learn more about getting and setting data from ResultSet.

How to check if ResultSet is empty in Java


Java example to check if ResultSet is empty or not

Here is a sample Java program to demonstrate how to check if a ResultSet returned as result of executeQuery() method is empty or not. This method assumes that you have an Employee table and you are running an SQL Server instance in your machine i.e. localhost. The query fetches all employees for a department and displays message if the resultset is empty or list of employees otherwise.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

public class DBTest {

  public static void main(String args[]) {

    Connection con = null;
    try {

      String url = "jdbc:sqlserver://localhost:42588;";

      // no need to call Class.forName(driver) from JDK 6 onward
      con = DriverManager.getConnection(url, "admin", "root");

      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("SELECT * from Employee");

      if (rs.next() == false) {
        System.out.println("ResultSet in empty in Java");
      } else {

        do {
          String data = rs.getString("emp_name");
          System.out.println(data);
        } while (rs.next());
      }

    } catch (Exception e) {
      e.printStackTrace();
    }

  }

}


That's all about how to check if JDBC ResultSet is empty in Java or not. Though you can use the isBeforeFirst() method to check if ResultSet contains any row or not, it's not guaranteed to be supported in all database e.g. HSQL DB doesn't support it. Instead of using traditional do-while loop approach is guaranteed to work in all database e.g. Oracle, SQL Server, MySQL, and PostgreSQL. It's simple, elegant and readable.


Other JDBC tutorials you may like
  • 10 JDBC Best Practices Every Java Programmer should follow (read)
  • Difference between Type 1 and Type 4 JDBC drivers (answers)
  • JDBC Batch Insert and Update example using PreparedStatement (tutorial)
  • Top 10 JDBC Interview Question for Java programmers (read)
  • How to connect to MySQL database from Java Program? (tutorial)
  • Difference between java.sql.Date, java.sql.Timestamp, and java.util.Date in JDBC? (answer)
  • Java Program to connect Oracle database using JDBC (tutorial)
  • 6 Essential JDBC Performance tips for Java Programmers (tips)
  • How to connect Microsoft SQL Server from Eclipse? (tutorial)
  • How to convert java.util.Date to java.sql.Date in Java? (example)
  • Why use PreparedStatement in Java? (answer)


References
Javadoc of ResulstSet



3 comments :

Fredy Mosquera Lemus said...
This comment has been removed by the author.
Fredy Mosquera Lemus said...

Hi, It is a good post about how to check if a resulset is empty or not, thank so much I personaly learn alot about this topic.

There are a thing that I dont understand in the last part of the code:
ResultSet rs = stmt.executeQuery("SELECT * from Employee"); ResultSet rs = cs.executeQuery();

I think that this is a typing error, checkout if that is true, if it is no true excuseme.

Thank you again

Javin Paul said...

Hello @Fredy, yes, ResultSet rs = cs.executeQuery(); is not needed, I'll remove it. Thanks for pointing it out

Post a Comment