Why isn't the ResultSet null when there are no results?

Question

Why is my else{ } not working? No output. My if{ } is working perfectly fine. Help appreciated!

String s2 = "SELECT CCA FROM generatedchoices WHERE category='" + category + "' AND intensiveness='"+ intensiveness + "'";
        rs2 = st.executeQuery(s2);

        if(rs2!=null){
            while (rs2.next()) {
                ccalist.add(rs2.getString("CCA"));
            }
        }
        else{
            System.out.println("No combination!");
            //JOptionPane.showMessageDialog(null, "No combination found!");
        }

Why would the `else` block be entered, if the `if` block "is working perfectly fine"? — Elliott Frisch, Feb 03 '17 at 04:31
rs2 will not be null if there is no record fetched. `rs2.next()` will be false — Gurwinder Singh, Feb 03 '17 at 04:32
if your `if` block is working fine why would `else` block work ? — coderpc, Feb 03 '17 at 04:32

Scary Wombat · Answer 1 · 2017-02-03T04:41:17.270

1

You are doing

rs2 = st.executeQuery(s2);

so rs2 will never be null.

Maybe what you want ti if (rs2.isBeforeFirst()) {...} else {...}

see http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#isBeforeFirst()

edited Feb 03 '17 at 04:41

answered Feb 03 '17 at 04:32

Scary Wombat

44,617
6
35
64

score 0 · Accepted Answer · edited May 23 '17 at 10:29

From the docs for Statement#executeQuery (emphasis mine):

Returns: a ResultSet object that contains the data produced by the given query; never null

If the query returns no rows you will not get null. You will get a ResultSet that is empty. An easy trick to check for this is to use isBeforeFirst() as given in this answer, rather than checking for null:

if (resultSet.isBeforeFirst() ) {    
    // loop over results
} else {
    // no results returned
}

If you don't feel comfortable with that solution or if for some reason it causes problems for you, another straightforward way is to simply check if you processed results or not, e.g.:

rs2 = st.executeQuery(s2);

boolean hadResults = false;

while (rs2.next()) {
    ccalist.add(rs2.getString("CCA"));
    hadResults = true;
}

if (!hadResults) {
    System.out.println("No combination!");
    //JOptionPane.showMessageDialog(null, "No combination found!");
}

Or any other various ways you can come up with to skin that particular cat.

@ScaryWombat That works too, although you have to be careful not to make any assumptions about the contents of `ccalist` prior to this code snippet. With the [lack of] info given, you'd want to compare its size before and after the loop. Also this implies the assumption that `ccalist` is actually a list of some sort, and that `add` always increases the size, rather than a confusingly named variable for e.g. some type of set. But if you know more about `ccalist`, you can narrow down your options a bit. — Jason C, Feb 03 '17 at 05:11

Why isn't the ResultSet null when there are no results?

2 Answers2