SQL queries in plugin

Hello!
First of all, I'm fairly new to these forums and MySQL development, so I apologize if I make any mistakes.

I own a beginning network, currently existing of around 10 servers. I made a plugin that enables donators and staff to use /color <color code> to change their displayname. It worked, but after switching to another server it reverts to white again.

Now, I'm trying to get the plugin to get the color upon joining a server and storing the color upon changing it.
I've already established the connection class, called MySQL.java.

I'm aware of the attempt to use the database in the first subcommand being wrong. Could someone please give me a little help with this? Thanks in advance!

Main class (open) Main class (close)

Code:

package nl.live.partyblaze.pbcolor;

import java.sql.*;
import org.bukkit.ChatColor;
import org.bukkit.command.Command;
import org.bukkit.command.CommandSender;
import org.bukkit.entity.Player;
import org.bukkit.plugin.java.JavaPlugin;
import nl.live.partyblaze.pbcolor.MySQL;

public final class PBColor extends JavaPlugin
{
    @Override
    public void onEnable()
    {
        MySQL.connect();
        getLogger().info("[PBColor] Plugin has been enabled!");
    }
    @Override
    public void onDisable()
    {
        MySQL.disconnect();
        getLogger().info("[PBColor] Plugin has been disabled!");
    }
   
    @Override
    public boolean onCommand(CommandSender sender, Command cmd, String label, String[] args)
    {
        if (cmd.getName().equalsIgnoreCase("color"))
        {
            if (args.length == 1)
            {
                if (args[0].equalsIgnoreCase("a"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.GREEN + displ);
                    sender.sendMessage("§aYour name has been set to green!");
                    PreparedStatement ps = MySQL.getConnection().prepareStatement("INSERT IGNORE INTO PBColor (UUID, Color) VALUES (?,?)");
                    ps.setString(1, p.getUniqueId());
                    ps.setString(2, "a");
                    ps.executeUpdate();
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("green"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.GREEN + displ);
                    sender.sendMessage("§aYour name has been set to green!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("b"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.AQUA + displ);
                    sender.sendMessage("§bYour name has been set to aqua!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("aqua"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.AQUA + displ);
                    sender.sendMessage("§bYour name has been set to aqua!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("c"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.RED + displ);
                    sender.sendMessage("§cYour name has been set to red!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("red"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.RED + displ);
                    sender.sendMessage("§cYour name has been set to red!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("d"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.LIGHT_PURPLE + displ);
                    sender.sendMessage("§dYour name has been set to light purple!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("lightpurple"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.LIGHT_PURPLE + displ);
                    sender.sendMessage("§dYour name has been set to light purple!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("e"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.YELLOW + displ);
                    sender.sendMessage("§eYour name has been set to yellow!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("yellow"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.YELLOW + displ);
                    sender.sendMessage("§eYour name has been set to yellow!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("f"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.WHITE + displ);
                    sender.sendMessage("§fYour name has been set to white!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("white"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.WHITE + displ);
                    sender.sendMessage("§fYour name has been set to white!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("1"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.DARK_BLUE + displ);
                    sender.sendMessage("§1Your name has been set to dark blue!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("darkblue"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.DARK_BLUE + displ);
                    sender.sendMessage("§1Your name has been set to dark blue!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("2"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.DARK_GREEN + displ);
                    sender.sendMessage("§2Your name has been set to dark green!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("darkgreen"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.DARK_GREEN + displ);
                    sender.sendMessage("§2Your name has been set to dark green!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("3"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.DARK_AQUA + displ);
                    sender.sendMessage("§3Your name has been set to dark aqua!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("darkaqua"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.DARK_AQUA + displ);
                    sender.sendMessage("§3Your name has been set to dark aqua!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("4"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.DARK_RED + displ);
                    sender.sendMessage("§4Your name has been set to dark red!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("darkred"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.DARK_RED + displ);
                    sender.sendMessage("§4Your name has been set to dark red!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("5"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.DARK_PURPLE + displ);
                    sender.sendMessage("§5Your name has been set to dark purple!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("darkpurple"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.DARK_PURPLE + displ);
                    sender.sendMessage("§5Your name has been set to dark purple!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("6"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.GOLD + displ);
                    sender.sendMessage("§6Your name has been set to gold!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("gold"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.GOLD + displ);
                    sender.sendMessage("§6Your name has been set to gold!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("7"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.GRAY + displ);
                    sender.sendMessage("§7Your name has been set to gray!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("gray"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.GRAY + displ);
                    sender.sendMessage("§7Your name has been set to gray!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("8"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.DARK_GRAY + displ);
                    sender.sendMessage("§8Your name has been set to dark gray!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("darkgray"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.DARK_GRAY + displ);
                    sender.sendMessage("§8Your name has been set to dark gray!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("9"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.BLUE + displ);
                    sender.sendMessage("§9Your name has been set to blue!");
                    return true;
                }
               
                if (args[0].equalsIgnoreCase("blue"))
                {
                    String displ = sender.getName();
                    Player p = (Player) sender;
                    p.setDisplayName(ChatColor.BLUE + displ);
                    sender.sendMessage("§9Your name has been set to blue!");
                    return true;
                }
            }
        }
        return false;
    }
}

 

Well, I haven't been working with MySQL for a long time but I don't think you can just set a string like that.
I think it is:

Code:

statement.executeUpdate("INSERT IGNORE INTO PBColor (UUID, Color) VALUES ('" + player.getUniqueId().toString() + "', 'c');");

 

@PartyBlaze Also SQL queries will lag your server. Run them on a separate thread.

 

@teej107 Might be a stupid question, but what exactly do you mean?

 

@PartyBlaze SQL queries can take a while. If you run that on Bukkit's main thread, it will most likely lag the server. Run your queries in a separate thread to keep the main thread free from long tasks.

 

@teej107 Thanks! One last thing, how do I get it to get the colour upon joining one of the servers?

 

@PartyBlaze With a SELECT statement, you should also use PreparedStatements instead of concatenting strings.

I also suggest using BINARY(16) for the UUIDs to improve select performance http://stackoverflow.com/questions/10950202/how-to-store-uuid-as-number

 

@Konato_K I really appreciate your help, but I'm not experienced enough to fully understand your post. I'm just seeking for a way to have a color network-wide which isn't extremely resource-using.

 

On command: parse args[0] to find a ChatColor. Since that's an enum, you can just use its toString() and valueOf() functions. toString() would be used for storing in the DB, valueOf would be used to get the ChatColor again when you SELECT it in the database.
On join, run SELECT Color FROM myTable WHERE uuid=?
If there are any results, you know he has a color and you can set it.

Last note: Run SQL queries in an ASync thread as to not create a heavy load on the main thread.