36

How can I find that the ResultSet, that I have got by querying a database, is empty or not?

Vaishali
  • 431
  • 1
  • 4
  • 17
Amit
  • 33,847
  • 91
  • 226
  • 299

6 Answers6

36

Immediately after your execute statement you can have an if statement. For example

ResultSet rs = statement.execute();
if (!rs.next()){
//ResultSet is empty
}
Paul
  • 4,812
  • 3
  • 27
  • 38
  • 18
    however, if the result set is not empty, you will now have moved the cursor, effectively skipping the first row. – monopoint Dec 04 '14 at 17:45
  • 2
    @Paul I believe you should update your answer to make it complete by resetting the cursor just before the first row in the else-block if it is not empty i.e. `else { rs.beforeFirst(); }` – sactiw Jun 15 '15 at 15:34
  • That doesn't work with SQLite, @sactiw. It should be stated that if you want to use this approach, SQL driver needs to support other types than `TYPE_FORWARD_ONLY`. SQLite doesn't support it. That means for SQLite, you need different approach. – Polda18 Dec 16 '22 at 17:02
32

Definitely this gives good solution,

ResultSet rs = stmt.execute("SQL QUERY");
// With the above statement you will not have a null ResultSet 'rs'.
// In case, if any exception occurs then next line of code won't execute.
// So, no problem if I won't check rs as null.

if (rs.next()) {
    do {
      // Logic to retrieve the data from the resultset.
      // eg: rs.getString("abc");
    } while(rs.next());
} else {
    // No data
}
Community
  • 1
  • 1
purush
  • 349
  • 3
  • 5
11

Do this using rs.next():

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

If the result set is empty, the code inside the loop won't execute.

Scott Smith
  • 3,900
  • 2
  • 31
  • 63
  • In case there is something to do if the result set is empty, then this doesn't do what you need. You'll need something similar to `while-else` statement, and closest match you can do is with @purush's answer with a `do-while` statement inside the `if-else` statement, where the `else` statement runs code in case the set is empty. – Polda18 Dec 16 '22 at 16:59
9

If you use rs.next() you will move the cursor, than you should to move first() why don't check using first() directly?

    public void fetchData(ResultSet res, JTable table) throws SQLException{     
    ResultSetMetaData metaData = res.getMetaData();
    int fieldsCount = metaData.getColumnCount();
    for (int i = 1; i <= fieldsCount; i++)
        ((DefaultTableModel) table.getModel()).addColumn(metaData.getColumnLabel(i));
    if (!res.first())
        JOptionPane.showMessageDialog(rootPane, "no data!");
    else
        do {
            Vector<Object> v = new Vector<Object>();
            for (int i = 1; i <= fieldsCount; i++)              
                v.addElement(res.getObject(i));         
            ((DefaultTableModel) table.getModel()).addRow(v);
        } while (res.next());
        res.close();
}
Kachwahed
  • 542
  • 7
  • 17
5
if (rs == null || !rs.first()) {
    //empty
} else {
    //not empty
}

Note that after this method call, if the resultset is not empty, it is at the beginning.

Santosh Tiwari
  • 1,167
  • 2
  • 14
  • 26
4

Calculates the size of the java.sql.ResultSet:

int size = 0;
if (rs != null) {
    rs.beforeFirst();
    rs.last();
    size = rs.getRow();
}

(Source)

Dolph
  • 49,714
  • 13
  • 63
  • 88
  • As far as I know, that is a bad idea... first of all, you need to ensure that the result can move backwards, second of all, you take a performance hit when doing that. Much quicker to just use a forward only result set, and use a while loop (like has already been suggested by others here) – user85116 Apr 19 '10 at 14:21
  • Agree... I voted up the accepted answer, which answers the question much more directly. – Dolph Apr 20 '10 at 01:49
  • Dolph - NICE!!!!! – Michael Sims Nov 19 '17 at 03:18