Java ResultSet how to check if there are any results

Question

Resultset has no method for hasNext. I want to check if the resultSet has any value

is this the correct way

if (!resultSet.next() ) {
    System.out.println("no data");
} 

Answer 1

Assuming you are working with a newly returned ResultSet whose cursor is pointing before the first row, an easier way to check this is to just call isBeforeFirst(). This avoids having to back-track if the data is to be read.

As explained in the documentation, this returns false if the cursor is not before the first record or if there are no rows in the ResultSet.

if (!resultSet.isBeforeFirst() ) {    
    System.out.println("No data"); 
} 

 

Answer 2

That's correct, initially the ResultSet's cursor is pointing to before the first row, if the first call to next() returns false then there was no data in the ResultSet.

If you use this method, you may have to call beforeFirst() immediately after to reset it, since it has positioned itself past the first row now.

It should be noted however, that Seifer's answer below is a more elegant solution to this question.

Answer 3

you could always do the next up front, and just do a post loop check

if (!resultSet.next() ) {
    System.out.println("no data");
} else {

    do {
     //statement(s)
    } while (resultSet.next());
}

Answer 4

To be totally sure of rather the resultset is empty or not regardless of cursor position, I would do something like this:

public static boolean isMyResultSetEmpty(ResultSet rs) throws SQLException {
    return (!rs.isBeforeFirst() && rs.getRow() == 0);
}

This function will return true if ResultSet is empty, false if not or throw an SQLException if that ResultSet is closed/uninitialized.

Answer 5

You would usually do something like this:

while ( resultSet.next() ) { 
   // Read the next item
   resultSet.getString("columnName");
}

If you want to report an empty set, add a variable counting the items read. If you only need to read a single item, then your code is adequate.

Answer 6

Best to use ResultSet.next() along with the do {...} while() syntax for this.

The "check for any results" call ResultSet.next() moves the cursor to the first row, so use the do {...} while() syntax to process that row while continuing to process remaining rows returned by the loop.

This way you get to check for any results, while at the same time also processing any results returned.

if(resultSet.next()) { // Checks for any results and moves cursor to first row,
    do { // Use 'do...while' to process the first row, while continuing to process remaining rows

    } while (resultSet.next());
}

Answer 7

According to the most viable answer the suggestion is to use "isBeforeFirst()". That's not the best solution if you don't have a "forward only type".

There's a method called ".first()". It's less overkill to get the exact same result. You check whether there is something in your "resultset" and don't advance your cursor.

The documentation states: "(...) false if there are no rows in the result set".

if(rs.first()){
    //do stuff      
}

You can also just call isBeforeFirst() to test if there are any rows returned without advancing the cursor, then proceed normally. – SnakeDoc Sep 2 '14 at 19:00

However, there's a difference between "isBeforeFirst()" and "first()". First generates an exception if done on a resultset from type "forward only".

Compare the two throw sections: http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#isBeforeFirst() http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#first()

Okay, basically this means that you should use "isBeforeFirst" as long as you have a "forward only" type. Otherwise it's less overkill to use "first()".

Answer 8

That would work if you want to see if there are any rows in the result set yes.

Note that next() always moves to the next row, so if you are planning on doing any reading from the result set you need to take that into account.

Usual usage with ResultSet (when simply reading) is:

while (resultSet.next())
{
   ... read from the row here ...
}

Which obviously won't work correctly if you invoked next() once already to check if the result set was empty, so watch out for that. Although there are methods for "backing up", they are not supported for all types of result sets.

Answer 9

This is a practical and easy read piece I believe.

        if (res.next()) {
            do {

                // successfully in. do the right things.

            } while (res.next());
        } else {
           // no results back. warn the user.
        }

Answer 10

Why not use rs.getRow()?

int getRow()
           throws SQLException
Retrieves the current row number. The first row is number 1, the second number 2, and so on.
Note:Support for the getRow method is optional for ResultSets with a result set type of TYPE_FORWARD_ONLY

Returns:
the current row number; 0 if there is no current row
Throws:
SQLException - if a database access error occurs or this method is called on a closed result set
SQLFeatureNotSupportedException - if the JDBC driver does not support this method
Since:
1.2

For me check "if (rs.getRow() != 0)" seems to work just fine.

Answer 11

if (!resultSet.isAfterLast() ) {    
System.out.println("No data"); 
} 

isAfterLast() also returns false for empty result set but since cursor is before first row anyways, this method seems more clear.

Answer 12

if(resultSet.first) {

} else { 
    system.out.println("No raw or resultSet is empty");
}

Because if ResultSet has no raw then resultSet.first returns false.

Answer 13

The best thing for to do is to check the first row so that when you intend to get the data you can avoid the mistake of skipping a row. Something like: if (!resultSet.first() ) { System.out.println("no data"); }

Answer 14

By using resultSet.next() you can easily get the result, whether resultSet containing any value or not

ResultSet resultSet = preparedStatement.executeQuery();
if(resultSet.next())
 //resultSet contain some values
else
 // empty resultSet

Answer 15

ResultSet result = stmt.executeQuery(sqlQuery);
if (!result.next())
    status = "ERROR";
else
    status = "SUCCESS";

Answer 16

I've been attempting to set the current row to the first index (dealing with primary keys). I would suggest

if(rs.absolute(1)){
    System.out.println("We have data");
} else {
    System.out.println("No data");
}

When the ResultSet is populated, it points to before the first row. When setting it to the first row (indicated by rs.absolute(1)) it will return true denoting it was successfully placed at row 1, or false if the row does not exist. We can extrapolate this to

for(int i=1; rs.absolute(i); i++){
    //Code
}

which sets the current row to position i and will fail if the row doesn't exist. This is just an alternative method to

while(rs.next()){
    //Code
}

Answer 17

I created the following method to check if a ResultSet is empty.

public static boolean resultSetIsEmpty(ResultSet rs){        
    try {
        // We point the last row
        rs.last();
        int rsRows=rs.getRow(); // get last row number

        if (rsRows == 0) {
            return true;
        }

        // It is necessary to back to top the pointer, so we can see all rows in our ResultSet object.
        rs.beforeFirst();
        return false;
    }catch(SQLException ex){            
        return true;
    }
}

It is very important to have the following considerations:

CallableStatement object must be setted to let to ResultSet object go at the end and go back to top.

TYPE_SCROLL_SENSITIVE: ResultSet object can shift at the end and go back to top. Further can catch last changes.

CONCUR_READ_ONLY: We can read the ResultSet object data, but can not updated.

CallableStatement proc = dbconex.prepareCall(select, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

Answer 18

I think the easiest way for checking result set is via CollectionUtils under package org.apache.commons.collections.CollectionUtils

if(CollectionUtils.isNotEmpty(resultList)){
  /**
  * do some stuff
  */
}

This will check for null as well as empty result set condition.

For more detail information you can refer to the following doc. CollectionUtils