java.sql.SQLException: Exhausted Resultset

Some of the possible causes of java.sql.SQLException: Exhausted Resultset

1) Opening the resultset in a loop and not closing them correctly. 

For e.g.
while(i >10)
{
     ResultSet rs = null;
     rs = ps.executeQuery("Query");
     while(rs.next())
      {
        // some code
       }
}

2) When underlying statement/connection is closed before accessing the result set.

For e.g.
if(statement != null)
statement.close();
while(rs.next())
      {
        // some code
       }

3) ResultSet is out of length.

4) ResultSet is empty and you can calling the next().

5) When you forget to call ResultSet.next(). The ResultSet initially points to the on-before-first element. You need to call next() before you use it to make it point to the first element. 

6) Trying to call next() on ResultSet when already next() was called and the pointer is out of the last record  and again trying to fetch some result then it throws the exception.

if (rs! = null) {
  while (rs.next()) {
    result = rs.getInt(1);
  }
  result = rs.getInt(1); //this will throw Exhausted resultset

}


Solution

If there is an need to do the same then can try the below snippet
if (rs! = null) {
  while (rs.next()) {
    result = rs.getInt(1);
  }
rs.absolute(1);// or rs.first()
  result = rs.getInt(1); //this will throw Exhausted resultset


}
rs.absolute(1);// or rs.first() this will reset the ResultSet pointer back to first element.


8) The data may not be available in the table.
 Here without checking the return value of next() or not checking whether the query returns some value of the ResultSet has some query output data

rs.next();
result = rs.getString("ColumnName"); this will throw Exhausted resultset

and If no records are returned by the query then it will throw exhausted exception.

Solution
Always check it there is some result in ResultSet 
If only one row is expected
if(rs.next()) 
{
   result = rs.getString("ColumnName"); 

}
For multiple rows use below
while(rs.next())
{
    result = rs.getString("ColumnName"); 

}

9) While fetching the ResultSet data ensure that you always call rs.next() as it initially points to the on-before-first element. As it is not pointing to any row and if rs.getString(1); is called then it will throw exhausted exception.

10) ResultSet is closed and after that trying to access the record.
for e.g.
rs.close();
rs.getString(1);//this will throw Exhausted resultset