CODEWITHSUNDEEP
javamysqlsql
Daan Meijer
Daan Meijer

Reputation: 143

MySQL - Object not null?

I am trying to check if a player is already is in the database with this code:

Statement sql = mySql.getConnection().createStatement();
ResultSet check = sql.executeQuery("SELECT * FROM `playerinfo` WHERE Username='" + player.getName() + "';");
System.out.println(check.toString());
if(check != null) {
    System.out.println("2");
    Bukkit.getConsoleSender().sendMessage(ChatColor.RED + "Player already in database");
    check.close();
    sql.close();
    return;
}

I checked but nothing is in the database and it says that the player already contains in the database Sorry for bad english

Upvotes: 0

Views: 175

Answers (2)

Mick Mnemonic
Mick Mnemonic

Reputation: 7956

Some considerations:

When checking whether the database contains a certain value, it's good practise to do this using a query that returns a single value (and not SELECT * which returns all columns of all rows that match the WHERE condition). You can do this e.g. by selecting a single check flag (SELECT 1) with a row-limiting clause (LIMIT 1):

SELECT 1 FROM playerinfo WHERE Username = ? LIMIT 1

This query is guaranteed to return only one row (with a single column, '1') if a player with the given name exists, or no rows if there are no players with the given name.

As others have pointed out, when you're inputting parameters into the query, you should use a PreparedStatement instead of a simple statement with concatenated inputs. This way, you can avoid SQL injection and the database is also able to reuse/cache the query (or cursor) internally.

Finally, you should close the resources you use, even if an Exception gets thrown during the execution. This is best done in the finally clause, or if you're on Java 7 or later, using the try-with-resources statement.

With these things in mind, a re-write of your code could look like this:

PreparedStatement ps = null;
try {
    ps = mySQL.getConnection()        
        .prepareStatement("SELECT 1 FROM playerinfo WHERE Username = ? LIMIT 1");
    ps.setString(1, player.getName()); 
    ResultSet rs = ps.executeQuery();

    // the first invocation of rs.next() returns true if 
    // there are rows in the result set, or false if no rows were found
    if (rs.next()) {
        System.out.println("2");
        Bukkit.getConsoleSender().sendMessage(ChatColor.RED 
            + "Player already in database");
    }        
    rs.close();
} finally {
    if (ps != null) {
        ps.close();
    }
}

Upvotes: 1

ptntialunrlsd
ptntialunrlsd

Reputation: 794

I think instead of checking if the ResultSet is null or not, you should check if the ResultSet contains any row or not.

Apart from that, use PreparedStatements.

Upvotes: 0

Related Questions