how to handle java sql exception:exhausted result set

Question

i have problem with the below code. help me!!

    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:xe","system","**********");
    Statement st=conn.createStatement();
    String sql="select password from db where username='user'";
    ResultSet rs=st.executeQuery(sql);
    rs.next();
    String password=rs.getString("password");
    if(password.equals(pass))
    {       
        RequestDispatcher rd=req.getRequestDispatcher("/home.jsp");
        rd.forward(req,res);
    }
    else
    {
        out.println("invalid username and password");
    }

when i execute this code i am getting an java sql exception : exhausted result set. thanks in advance...

You're looking for a row with a username of `user` - you're never using the `user` variable. Therefore no rows are matching, therefore `rs.next()` returns false, but you're ignoring that and reading from the row anyway. (You should use parameterized SQL - but you also shouldn't be storing passwords in a database like this...) — Jon Skeet, Jul 26 '15 at 13:52

Somnath Musib · Accepted Answer · 2015-07-26T13:55:53.803

Instead of using rs.next();, use it with while e.g while(rs.next()). Once you got the resultset, the pointer will point to the first record. Each time you do a rs.next(), the pointer will advances to the next record. If you use with while, once you reach to the end of your resultset, rs.next() will return false once all records are iterated. In your case, since you are not checking whether resultset has exhausted and trying to advanced the pointer, you are getting the exception.

SatyaTNV · Answer 2 · 2015-07-26T14:08:26.753

That is correct, because if you enter wrong username or password, NO record will be returned. So, when you use the rs.next(); in this case, it is trying to access the first row of the empty result set

 String password=rs.getString("password");//error, if rs is empty

in the where clause you are not using user variable

where clause should be where username='"+user+"'";

and

instead of

rs.next();

use

if(rs.next())
{
     String password=rs.getString("password");
     ........
}

how to handle java sql exception:exhausted result set

2 Answers2