How to handle if a sql query finds nothing? Using resultset in java

Question

I have found a few answers to this question on this site, but haven't been able to apply it correctly. I'm trying to take a user's input for user name and password and check it against a database to see if its correct. Everything works fine unless the username entered is not in the database. In that case the query doesn't find any rows. I'm doing this with derby in netbeans. So I think I'm just having trouble detecting when the query is sent with an unknown username. Any Suggestions?

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet(name = "MyServlet", urlPatterns ={"/echo"})
public class LoginAction extends HttpServlet {
private ResultSet resultSet;
private Connection con;

@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    response.setContentType("text/html; charset=UTF-8");
    PrintWriter out = response.getWriter();
    try {
    out.println("<!DOCTYPE html>");
    out.println("<html><head>");
    out.println("<meta http-equiv='Content-Type' content='text/html; charset=UTF-8'>");
    String userName = request.getParameter("username");
    String password = request.getParameter("password");
    if (userName.length()>0 & password.length()>0){
        try{
            String url = "jdbc:derby:c:/users/project/.netbeans-derby/";
            String db = "loginpass";
            String driver = "org.apache.derby.jdbc.EmbeddedDriver";
            String user = "user";
            String pass = "pass";
            Class.forName(driver).newInstance();
            con = DriverManager.getConnection(url+db, user, pass);
            String statement = "SELECT PASSWORD FROM LOGINPASS WHERE LOGIN = '"+userName+"'";
            PreparedStatement prepStatement = con.prepareStatement(statement);
            resultSet = prepStatement.executeQuery();

            while (resultSet.next()){
               if (resultSet.getString("password").equals(password)){
                   out.println("Login attempt successful!");
                   out.println("<br><a href='index.jsp'>Back to Login Page</a>");
               } else {
                   out.println("Password incorrect");
                   out.println("<br><a href='index.jsp'>Back to Login Page</a>");
               }
            }
            con.close();
        }catch(Exception e){
            out.println("caught...");
            e.printStackTrace();
        }
     } else {
        out.println("Login attempt failed");
        out.println("<a href='index.jsp'>Back to Login Page</a>");
     }
     } finally {
        out.close();  
     }
}

@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    doGet(request, response);
}

http://stackoverflow.com/questions/867194/java-resultset-how-to-check-if-there-are-any-results/6813771#6813771 — Salih Erikci, Jul 06 '13 at 20:26

score 3 · Accepted Answer · answered Jul 06 '13 at 20:31

As you expect only one row, you may write:

if (resultSet.next()) {
    if (resultSet.getString("password").equals(password)){
        out.println("Login attempt successful!");
        out.println("<br><a href='index.jsp'>Back to Login Page</a>");
    } else {
        out.println("Password incorrect");
        out.println("<br><a href='index.jsp'>Back to Login Page</a>");
    }
} else {
    out.println("User doesn't exist");
    out.println("<br><a href='index.jsp'>Back to Login Page</a>");
}

And you'd better use parameters to avoid sql injections:

String statement = "SELECT PASSWORD FROM LOGINPASS WHERE LOGIN = ?";
PreparedStatement prepStatement = con.prepareStatement(statement);
prepStatement.setString(1, userName);
resultSet = prepStatement.executeQuery();

Thanks this did it, I'm not used to thinking about just one row, so this worked for what I was trying to do. Thanks! — dghalbr, Jul 06 '13 at 21:01

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

You can check that from the ResultSet object itself . ResultSet#isBeforeFirst() :

Retrieves whether the cursor is before the first row in this ResultSet object.

Note: Support for the isBeforeFirst method is optional for ResultSets with a result set type of TYPE_FORWARD_ONLY

Returns:

true if the cursor is before the first row; false if the cursor is at any other position or the result set contains no rows.

if (!resultSet.isBeforeFirst()) {
  out.println("no rows fetched");
  out.println("User with this password not found");
  out.println("<br><a href='index.jsp'>Back to Login Page</a>");
}

I will keep this in mind I've never tried it this way before. Thanks! — dghalbr, Jul 06 '13 at 21:02

score 1 · Answer 3 · answered Jul 06 '13 at 20:28

1

Before While(resultSet.next()), type if(resultSet!=null).If the query has no result, the resultSet will be null.

answered Jul 06 '13 at 20:28

zari

1,709
1
12
18

If the resulSet is null the if condition will be false and while condition never been checked. So the cursor never moves! – zari Jul 06 '13 at 20:34
if there are no records returned by sql query, it does not mean that resultset will be null. resultSet null does not imply result is empty. I didnt read your post properly, ignore my first comment. i thought you were doing a resultset.next to find if resultset is empty. – ajay.patel Jul 06 '13 at 20:42
I had tried it in the beginning and this didn't work for me. Thanks though! – dghalbr Jul 06 '13 at 21:02
executeQuery() never returns null! http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html#executeQuery() – Alex Skalozub Jul 06 '13 at 21:38
Yeah. Never returns null, but when it didn't find any record, it returns nothing! and the resultSet will remain null. – zari Jul 07 '13 at 11:25

How to handle if a sql query finds nothing? Using resultset in java

3 Answers3