select count(*) returns true regardless of the existing account

Question

The AccountCheck method is returning false always and saying "Account exists!" always. Idea is to check if there's an existing account in the database and create one if it doesn't. Please help. I am not doing any validations in the code.

private boolean AccountCheck(String username, String password) {

    try {

        String sql = "select count(*) from user where username=? and password=?";

        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, username);
        pst.setString(2, password);
        ResultSet rs = pst.executeQuery();

        if (rs.next()) {

            rs.close();
            System.out.println("Account exists!");
            return false;

        } else {

            rs.close();
            System.out.println("Accout does not exist!");
            return true;
        }

    } catch (SQLException e) {

        e.printStackTrace();
        return false;
    }

}

Answer 1

Even if record doesnt exist count will return "0" as output. So rs.next() will return true always.

Solution :

Replace select count(*) from user where username=? and password=? with

select * from user where username=? and password=?

If you use * instead of count(*) it will not return any record

Answer 2

As you are querying for count, it will always return one record (with value 0 if no account exists), in this case, it will always go inside if. We can make wither of the below changes to make it work:

1. Change count(*) to *, e.g.: select * from user where username=? and password=?

2. Get the count from resultset and check the value:

   String sql = "select count(*) as count from user where username=? and password=?";
PreparedStatement pst = conn.prepareStatement(sql); pst.setString(1, username); pst.setString(2, password); ResultSet rs = pst.executeQuery(); if (rs.next() && rs.getInt("count") > 0) { rs.close(); System.out.println("Account exists!"); return false; } else { rs.close(); System.out.println("Accout does not exist!"); return true; }

Also, you may want to have a look at this SO answer regarding how to close PreparedStatement and ResultSet objects.