Result set always returns empty, should I not use executeQuery() on my prepared statement?

Question

I've got users trying to register to a site. before they can register their username of choice is searched for in an SQL database to make sure it doesn't already exist. the problem is the names are never searched because the ResultSet always returns empty. I think it's the prepared statement.

I think my prepared statement isn't executing. I'm using executeQuery() to execute my statement because that's how I've been inserting the usernames without any problem. I got the search ResultsSet part if (rs.next())... from the method that inserts the usernames. Same with the String SQL and the prepared statement stuff.

String SQL = "SELECT * FROM users WHERE username='" + getUsername() + "'";

    System.out.println(SQL);
    // prints out SELECT * FROM users WHERE username='whatever I searched'
    // so this String is valid
    if (db.getConn() != null){
        System.out.println("connected to database");
        // always prints
    }
    PreparedStatement preparedStatement = db.getConn().prepareStatement(SQL);
   // preparedStatement.setString(1, getUsername());
    ResultSet rs = preparedStatement.executeQuery();
//    userNameCounter = rs.getString("username");
    // putting this here returns an sqlexception. empty set
if (preparedStatement != null){
        System.out.println("ps != null");
        // prints this
    }
    if (rs != null){
        System.out.println("rs != null");
        // prints this
    }
    if (!rs.next()){
        System.out.println("!rs.next");
        // prints this
    }
    if (rs.next()) {
        userNameCounter = rs.getString("username");
        System.out.println("rs.next()");
        // doesn't print
        // so the resultset is empty

        if (!userNameCounter.equals(getUsername())) {
            System.out.println("that username is unique");
            return true;
        }
    }
    preparedStatement.close();
    incorrectLabels.setText("That username is already taken");
    incorrectLabels.setVisible(true);
    System.out.println("that username is already there");
    // this always prints. it shouldn't
    return false;

So executeUpdate() requires an int but I'm not sure what I would put there. And doing just execute() throws an error Requires ResultSet found boolean. I don't think there are any syntax errors since the table is called users. Everything I try just leads me back to an error resulting from an empty set. let me know if you need more code but this is where the error is happening.

Thanks!

Answer 1

You are issuing a query to the database when using the SELECT statement therefore you use the executeQuery() method.

What looks confusing is the userNameCounter variable you're using. Where is it declared and what is it declared as? It looks like it may be a Integer variable which would bring me to ask....what do you think the rs.getString("username") method returns? As a matter of fact...what's with all the rs.next() conditions for all those if statements?

The whole thing is rather confusing. If you want to see if a User Name already exists within a database table then you might do it something like this:

if (db.getConn() == null){
    throw new RuntimeException("Method Error! You Are NOT Connected To Database!");
}

String suppliedUserName = getUsername();
String dbUserName = "";
String SQL = "SELECT username FROM users WHERE username=?";

PreparedStatement preparedStatement = db.getConn().prepareStatement(SQL);
preparedStatement.setString(1, suppliedUserName);
ResultSet rs = preparedStatement.executeQuery();

while (rs.next()) {
    dbUserName = rs.getString("username");
}
rs.close()
preparedStatement.close()

/* Below we use the equalsIgnoreCase() method. You 
   don't want a supplied User Name to be that close 
   or that similar to another User Name already in 
   Database. If you do then just use equals() method.  */
if (dbUserName.equalsIgnoreCase(suppliedUserName)) {
    System.out.println("The User name (" + suppliedUserName + 
                ") is already in use. Try another User Name.");
    return false;
}
else {
    System.out.println("The User name (" + suppliedUserName + ") is Unique.");
    return true;
}

Of course this code isn't tested and I assume you have your try/catch in place to handle any SQLException. I merely provide this code to give you an idea of how it can be accomplished.