CODEWITHSUNDEEP
javamysqlsqlminecraftbukkit
SAM_XPS
SAM_XPS

Reputation: 11

How do I use this if else in a MySql query?

So, I have this code below (in Java), that uses 1 query to check if the table contains or not a register with the player UUID and ONLY after the mysql has sent the result it can decide to use UPDATE or INSERT in ANOTHER query.

// Assuming that I have variables like these:
// String uid = "ffffffff-ffff-ffff-ffff-ffffffffffff"; // This is a Minecraft UUID.
// int languageID = 1; // This is a simple Integer
ResultSet set = stm.executeQuery("SELECT language FROM players WHERE UUID = '" + uid + "';");
set.beforeFirst();
while (set.next()){
    // This will run if in the table already contains the UUID = the uid.
    stm.execute("UPDATE players SET language = '" + languageID + "' WHERE UUID = '" + uid +"';");
    return;
}
// This will just run if the resultSet is empty.
stm.execute("INSERT INTO players(UUID, language) values('" + uid + "','" + languageID +"')");

I need to turn this code into just one mysql query to avoid LAG in the server, because this code will be executed every time that someone joins in my BungeeCord server. Although this code is in an asynchronous task, It still can cause some problems.

The fact is, I don't know how to use the IF function in sql to check if the table contains a register with the Player's UUID:

IF (/* HERE IS THE PROBLEM */) THEN {
   UPDATE players SET ... WHERE ... // update the result
}
ELSE {
  INSERT INTO players ... // insert a new register in the table
}

Thanks.

Upvotes: 0

Views: 511

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You want insert . . . on duplicate key update. Something like this:

INSERT INTO players(UUID, language)
     VALUES (uid, languageID)
     ON DUPLICATE KEY UPDATE language = VALUES(language);

Note that even moving the IF into the database (in a stored procedure) does not eliminate the race condition. The only way to do that is to lock the table for updating. But, you can do all this in one statement, so that is the best approach.

Also, this assumes that you have a unique index or primary key on the table to identify duplicates.

Upvotes: 2

Related Questions