sql query execute with PreparedStatement

URL87 picture URL87 · Aug 5, 2012 · Viewed 12.2k times · Source

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 .

Answer

JB Nizet picture JB Nizet · Aug 5, 2012

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.