Executing query with condition in java (response = Illegal operation on empty result set)

Question

I have a problem, I want to execute query based on the condition. The concept is if the result is null, then execute another query. But when I try it, the result is an error

Illegal operation on empty result set`

My code:

String strSQL = "SELECT * FROM user WHERE userid=? AND password=? LIMIT 1;";

PreparedStatement pst=null;
pst = con.prepareStatement(strSQL);
pst.setString(1, userid);
pst.setString(2, password);

rs = pst.executeQuery();

if(rs.getString(1) == null && rs.getString(2) == null) {
   String strSQL = "SELECT userid FROM user WHERE userid=?;";
}

if(rs.next())
{   
    yoyo yo = new yoyo(); 
    yo.SetUserid(rs.getString(1));
    yo.SetUsername(rs.getString(2));
    yo.SetPosisi(rs.getString(5));
    lstLogin = yo;
}

If no result is found, the query will not return a resultset containing one row with null values. It will return an empty resultset. — Robby Cornelissen, Dec 08 '17 at 02:36
A `ResultSet` is pointing basicly to the index -1 at first, you need to check if there is a `ResultSet.next()` first to be on the first cell (index 0). — AxelH, Dec 08 '17 at 09:08

Arun Sudhakaran · Answer 1 · 2017-12-11T05:08:00.850

1

At this line you will get the result set

rs = pst.executeQuery();

Now we want know that the rs is not null and also not empty. So we will give condition like this

if (rs != null && !rs.isBeforeFirst() ) {    
    String strSQL = "SELECT userid FROM user WHERE userid=?;"; 
}

Check this link for reference Check result set is empty or not

In your code it is not checking whether the ResultSet is empty or not.

edited Dec 11 '17 at 05:08

answered Dec 08 '17 at 09:23

Arun Sudhakaran

2,167
4
27
52

cse · Answer 2 · 2017-12-08T09:10:58.283

0

You have not executed the query when if(rs.getString(1) == null && rs.getString(2) == null) condition is true.
To solve this, change the if condition as following:

if(!rs.next()){
    strSQL = "SELECT userid FROM user WHERE userid=?;";
    pst = con.prepareStatement(strSQL);
    pst.setString(1, userid);
    rs = pst.executeQuery();
}

Note: You have re-declared strSQL variable in if statement.

edited Dec 08 '17 at 09:10

answered Dec 08 '17 at 09:05

cse

4,066
2
20
37

Executing query with condition in java (response = Illegal operation on empty result set)

2 Answers2