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 .

Please show the Exception message/trace. Please tell if your code has ever connected to that specific database. — Javier, Aug 05 '12 at 08:39

score 6 · Accepted Answer · answered Aug 05 '12 at 08:39

6

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.

answered Aug 05 '12 at 08:39

JB Nizet

678,734
91
1,224
1,255

thanks it sufficient :) so until I don't do "rs.next()" to the ResultSet I can't use it ? – URL87 Aug 05 '12 at 08:45
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). – JB Nizet Aug 05 '12 at 08:48

sql query execute with PreparedStatement

1 Answers1