CODEWITHSUNDEEP
javamysqlsql
ThatGuy343
ThatGuy343

Reputation: 2424

Java SQL query, most efficient check for matching column value?

I have a table which will potentially have 200 to 300 thousand rows.

I need to check if a primary key exists in a table.

Here is what i have so far:

public boolean checkIfKeyExists(String userkey) {
    try {
        connection = getSQLConnection();
        PreparedStatement ps = connection.prepareStatement("SELECT userkey FROM " + keytable + " WHERE userkey = ?");
        ps.setString(1, userkey);
        ResultSet rs = ps.executeQuery();
        boolean set = false;
        while(rs.next()){
            set = true;
        }
        close(ps,rs);
        return set;
    } catch (SQLException ex) {
        Error.execute(plugin, ex);
    }
    return false;
}

Having to use a result set and setting a boolean off that just seems too lengthy for a simple key check you know? What is the simplest way of doing this?

Upvotes: 1

Views: 512

Answers (2)

PM 77-1
PM 77-1

Reputation: 13344

Using your variables, you can have

set = rs.next();

Alsi in SQL part you can have SELECT 1 ... which will work a tad faster.

Upvotes: 1

Robby Cornelissen
Robby Cornelissen

Reputation: 97331

I don't see a way around using the result set, but you could get around having to set the boolean by just doing

return ps.executeQuery().next();

and then close your statement and resultset in the finally block.

Upvotes: 1

Related Questions