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

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() returns 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 that involve calling the 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 databases like the 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 the Complete JDBC Programming course on Udemy., 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 developers 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 the 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 then the 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 the basics of JDBC. One of the better books to learn from JDBC.

The right way to check if ResultSet is empty

There are a couple of solutions to the 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 databases like 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 a do-while loop, which checks the condition at the end of the loop as opposed to starting 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 records when it really has. See these best core Java courses to learn more about getting and setting data from ResultSet.

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 a result of the 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 a message if the resultset is empty or a 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 databases e.g. HSQL DB doesn't support it. Instead of using traditional do-while loop approach is guaranteed to work in all database like 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 the 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)

Thanks for reading this tutorial so far. If you found this example helpful please share it with your friends and colleagues. I would really appreciate it.