How can I check (java) if an username already exists in my Data Base(postgres)?

Question

I have a client, a server, a RMI server and a PostgreSQL Data base. I am creating a new user, so I naturally want to check if that username already exists in my DB. So this is what I am doing:

• Make a SELECT query for username I want to create
• If my RequestSet is null, that means that the table that the DB returns is empty, so there isn't any user with that username and I can create a new user.
• It it isn't empty, I can't create a new user.

However, whenever I try to insert a new user, whether it exists or not in the DB, it always says that the user already exists...

This is the code I use in the RMI Server:

String[] userInfo = (String[]) clrqst.getRequest()[1];

    clrqst.setStage(2);
    myRequests.add(clrqst);

    try
    {
        query = "SELECT * FROM utilizador WHERE username= '"+userInfo[2]+"'";
        request = connection.createStatement();
        rs = request.executeQuery(query);

        if (rs == null)
        {
            try {
                query = "INSERT INTO utilizador (nome, apelido, username, pass, saldo) VALUES (?,?,?,?,?)";
                preparedstatement = connection.prepareStatement(query);
                preparedstatement.setString(1, userInfo[0]);
                preparedstatement.setString(2, userInfo[1]);
                preparedstatement.setString(3, userInfo[2]);
                preparedstatement.setString(4, userInfo[3]);
                preparedstatement.setInt(5, 100);
                preparedstatement.executeUpdate();

            } catch (SQLException e) {
                System.err.println("SQLException:" + e);
            }

            try {
                query = "SELECT id FROM utilizador WHERE username='" + userInfo[0] + "'";
                request = connection.createStatement();
                rs = request.executeQuery(query);

                rs.next();
                resposta[0] = "infosave";
                resposta[1] = rs.getInt(1);

                clrqst.setResponse(resposta);
                clrqst.setStage(3);

                updateRequest(clrqst);

            } catch (SQLException ex) {
                System.err.println("Erro:" + ex);
            } finally {
                if (request != null) {
                    try {
                        request.close();
                    } catch (SQLException ex) {
                        Logger.getLogger(RMIServer.class.getName()).log(Level.SEVERE, null, ex);
                    }
                }
            }
        }

        else{
            resposta[0] = "user_already_exists";
            resposta[1] = userInfo[2];
            clrqst.setResponse(resposta);
            clrqst.setStage(3);
        }

    }  catch (SQLException e) {
        System.err.println("SQLException:" + e);
    }


    return clrqst;

I can't figure out what am I doing wrong. Can someone point me to the right direction? Thank you!

Answer 1

A query returns a result set. A result set is just that... a set. Even an empty set is still a set. What you want to know is "did a row return?". Assuming that the username column is unique (a fair assumption), your query will return a result set with either 0 or 1 rows.

The rs.next() method advances to the next row (the starting position is before the first row). It returns true if there was a row to advance to, false if it ran out of rows in the result set.

Since you only need to know if 1 row came back or not, your solution can be as simple as calling rs.next() and checking the result set.

rs = request.executeQuery(query);
if (!rs.next())
{
    // Insert the new user.