sql query execute with PreparedStatement

Question

I have the follow code in a servlet -

            String loginID = request.getParameter("loginId").toString();
            String loginPassword = request.getParameter("loginPassword").toString();
            String strSQLcount = "SELECT COUNT(*) as 'Number Of Match' "
                + "FROM persons " + "WHERE (password =? AND  id =?);";
            PreparedStatement prepareSQL = connection
                    .prepareStatement(strSQLcount);
            prepareSQL.setString(1, loginPassword);
            prepareSQL.setString(2, loginID);
            ResultSet numOfMatchResult = prepareSQL.executeQuery();
            // now we know number of matchs ...
            int numOfMatch = numOfMatchResult.getInt(1);

When on running and reach to the line int numOfMatch = numOfMatchResult.getInt(1); it throws the exception - java.sql.SQLException . I checked it and seen that it because the executeQuery() retrieved no one . it occur although I have in persons table ,created with MySQL, the 2 fields - id (text) with value "300" and password (text) with value "500" . and of course I check it when loginID and loginPassword with same 2 values . I checked all the other things about the connection to the DB and it was OK .. so I think the problem is in the SQL syntax in strSQLcount .

Nehru

Please show the Exception message/trace. Please tell if your code has ever connected to that specific database. – Attorney

Answer 1

You forgot to call next() on the result set:

ResultSet numOfMatchResult = prepareSQL.executeQuery();
int numOfMatch = 0;
if (rs.next() {    
    numOfMatch = numOfMatchResult.getInt(1);
}

If that is not sufficient to solve the problem, paste the whole stack trace of the exception: it contains meaningful information.

Entreaty

thanks it sufficient :) so until I don't do "rs.next()" to the ResultSet I can't use it ? – Nehru

Each time you want to retrieve info from a result set, you need to make the cursor point to a given row of data. rs.next() places it on the next row (the first one the first time it's called). – Entreaty