Stats with MySQL causing LAG

I'm creating a PvP Plugin with stats on scoreboard using MySQL, and it is causing Lag spikes.

My Stats Class:

Code:

public static int getKills(String nome){
       
        try{
           
            Statement st = Main.c.createStatement();
           
            ResultSet rs = st.executeQuery("SELECT * FROM stats WHERE player = '" + nome + "'");
           
            rs.next();
           
            return rs.getInt("kills");
        }
       
        catch(SQLException ex){
           
            ex.printStackTrace();
        }
       
        return 0;
    }
   
    public static int getMortes(String nome){
       
        try{
           
            Statement st = Main.c.createStatement();
           
            ResultSet rs = st.executeQuery("SELECT * FROM stats WHERE player = '" + nome + "'");
           
            rs.next();
           
            return rs.getInt("mortes");
        }
       
        catch(SQLException ex){
           
            ex.printStackTrace();
        }
       
        return 0;
    }
   
    public static void addKills(String nome, int kills){
       
        try{
       
            Statement st = Main.c.createStatement();
           
            st.executeUpdate("UPDATE stats SET kills = " + (getKills(nome) + kills) + " WHERE player = '" + nome + "'");
           
            st.close();
        }
       
        catch(SQLException ex){
           
            ex.printStackTrace();
        }
    }
   
    public static void addMortes(String nome, int mortes){
       
        try{
       
            Statement st = Main.c.createStatement();
           
            st.executeUpdate("UPDATE stats SET mortes = " + (getMortes(nome) + mortes) + " WHERE player = '" + nome + "'");
           
            st.close();
        }
       
        catch(SQLException ex){
           
            ex.printStackTrace();
        }
    }
   
    public static void removeKills(String nome, int kills){
       
        try{
       
            Statement st = Main.c.createStatement();
           
            if(getMortes(nome) >= kills){
           
                st.executeUpdate("UPDATE stats SET kills = " + (getKills(nome) - kills) + " WHERE player = '" + nome + "'");
            }
           
            else{
               
                st.executeUpdate("UPDATE stats SET kills = 0 WHERE player = '" + nome + "'");
            }
           
            st.close();
        }
       
        catch(SQLException ex){
           
            ex.printStackTrace();
        }
    }
   
    public static void removeMortes(String nome, int mortes){
       
        try{
       
            Statement st = Main.c.createStatement();
           
            if(getMortes(nome) >= mortes){
           
                st.executeUpdate("UPDATE stats SET mortes = " + (getMortes(nome) - mortes) + " WHERE player = '" + nome + "'");
            }
           
            else{
               
                st.executeUpdate("UPDATE stats SET mortes = 0 WHERE player = '" + nome + "'");
            }
           
            st.close();
        }
       
        catch(SQLException ex){
           
            ex.printStackTrace();
        }
    }
   
    public static void setKills(String nome, int kills){
       
        try{
       
            Statement st = Main.c.createStatement();
           
            st.executeUpdate("UPDATE stats SET kills = " + kills + " WHERE player = '" + nome + "'");
           
            st.close();
        }
       
        catch(SQLException ex){
           
            ex.printStackTrace();
        }
    }
   
    public static void setMortes(String nome, int mortes){
       
        try{
           
            Statement st = Main.c.createStatement();
           
            st.executeUpdate("UPDATE stats SET mortes = " + mortes + " WHERE player = '" + nome + "'");
           
            st.close();
        }
       
        catch(SQLException ex){
           
            ex.printStackTrace();
        }
    }

Death Listener:

Code:

Bukkit.getScheduler().scheduleAsyncDelayedTask(Death.main, new Runnable(){
                   
                    public void run(){
                       
                        Principal.addKills(d.getName(), 1);
                    }
                });

Bukkit.getScheduler().scheduleSyncDelayedTask(Death.main, new Runnable(){
                       
                        public void run(){
                           
                            Scoreboard.set(d);
                        }
                    }, 1L);

Scoreboard Class:

Code:

@SuppressWarnings("deprecation")
    public static void set(final Player p){
        
        final org.bukkit.scoreboard.Scoreboard score = Bukkit.getScoreboardManager().getNewScoreboard();
       
        final Objective stats = score.registerNewObjective("stats", "dummy");
       
        stats.setDisplaySlot(DisplaySlot.SIDEBAR);
       
        stats.setDisplayName(p.getDisplayName());
       
        Bukkit.getServer().getScheduler().scheduleAsyncDelayedTask(Arenas.main, new Runnable(){
           
            public void run(){
               
                    Score kills = stats.getScore(Bukkit.getOfflinePlayer("§6Kills"));
                   
                    kills.setScore(dudubs123.Stats.Principal.getKills(p.getName()));
                   
                    Score mortes = stats.getScore(Bukkit.getOfflinePlayer("§cMortes"));
                   
                    mortes.setScore(dudubs123.Stats.Principal.getMortes(p.getName()));
        });
       
        Bukkit.getServer().getScheduler().scheduleSyncDelayedTask(Arenas.main, new Runnable(){
           
            public void run(){
               
                p.setScoreboard(score);
            }
        }, 5L);
    }

 

@timtower but the stats change every time anyone dies...

 

@dudubs123 Save them in a local object, then you can save them after a certain amount or time or on disable.

You may want to use a thread pool or something else, also, you should use prepared statements instead of concatenating strings like that

 

@Konato_K You mean save stats in HashMaps and use runTaskAsynchronously instead of scheduleAsyncDelayedTask?

 

@dudubs123

No, he didn't say "violate the thread safety of the server." Your code completely disregards any semblance of thread safety. Perhaps you should learn the concept of thread safety before you attempt to use MySQL.

Also, none of your code is object oriented, it looks awful and your constant abuse of static is an absolute eyesore. You don't handle any errors properly.

Perhaps you should use the Configuration API, it seems much more on par with your skill level.

 

Save on a HashMap for local caching, update on hashmap, use hashmap to get the info, when server shutdown, save hashmap info on mysql.

 

@mythbusterma I've never used MySQL, I'm starting now, so I came to ask for help, I've researched in many places of how to use MySQL in plugins, but most plugins are posted on Bukkit and few people know how to use MySQL if a plugin need it. My plugin is very complete and I had never made Scoreboard in my plugins, when I made, I used YAML files, which caused much lag, I just came to ask for help because I am newbie in MySQL.

 

@dudubs123

Alright, well first of all, are you writing this plugin as a way to learn SQL (and integrate it into Java), or are you creating this plugin for another reason? Because if it's the latter, then you really should be using FileConfiguration, it's a lot easier to use and faster.

 

@mythbusterma I'm creating for a server, and FileConfiguration causes more lag than SQL.

 

Oh man, that was funny. Like, legit, I fell out of my chair laughing at that post. I haven't laughed that hard in at least a week. Show me those benchmarks, please. Protip: you can't, because in almost any usage case applicable to a plugin, FileConfiguration is much, much, much faster than SQL.

I could understand if you wanted to learn how to use SQL, this is a decent way to do so. But if you're seriously thinking about putting this on a server, you would find it not only much easier but much faster to just use FileConfiguration.

 

When server shutdown, I lose the hashmap's values...

 

@dudubs123 onDisable is called right before the server shuts down

 

But, if I close the window console, not call onDisable...

 

@dudubs123 That is why it is clearly stated to not close the window console in just about every tutorial on how to setup a Bukkit server.
EDIT: Ninjad

 

Ok, thx, but there is a problem in my code? would help me with this?

 

@dudubs123

Did you even bother to read my post? Or did you completely ignore it because you can't accept what I'm saying?

Just use FileConfiguration, it's faster and easier to use.

 

I prefer to use MySQL, there are some things that are much easier to be made than in FileConfiguration. But thx for the help!

 

If your problem is when the server is shut down, make so it saves every data every X time plus on shutdown.

 

I thought about that too, but I can not have anything lag on the server ...

 

Ok, just to notice that i'm brazilian too.
Here's my scoreboard class for example: ( No Lags )

// Create my STATIC hashmap ( because i need to acess it from another class, so shut up STATIC-Haters )

Code:

private static HashMap<Player, Integer> CoinsCache = new HashMap<Player, Integer>();

//On join method, add the player to make a "local cache version of his value"

Code:

int cois = coinsAPI.get(e.getPlayer()); // MySQL Connection here.
if(CoinsCache.contains(e.getPlayer()) == false) {
CoinsCache.put(e.getPlayer(), coins);
} else {
CoinsCache.remove(e.getPlayer());
CoinsCacheput(e.getPlayer(), coins);
}

//onDisable method
- just save it with a for loop.

Easy right? i was talking about this, just this.

 

Hahahaha

Like you can't access another class without static.

 

@Fabricio20

No, I won't "shut up" because you're too thick to understand the most fundamental concept of Java, Object-Oriented Programming. Encouraging the use of non-OOP code (in Java) is stupid on a whole new level, frankly.


@dudubs123

You keep saying "I don't want it to lag" (or things of that nature), and we've already explained quite clearly in this thread that if you don't want it to lag, you should use the FileConfiguration API.

If you want to continue to ignore our advice to the contrary, that is your decision. In that case, you should stop querying the database on the main thread, and actually instantiate an object to manage your SQL connection, cache the connection and either access it asynchronously or access it only when the server starts and shuts down, avoiding the issue.

 

Have your own player object for each player, loading their stats from MySQL to the object on player join/server start(reload),
and save them TO the MySQL on player quit/server disable(reload)

 

And what using static or not using static changes in the code? Just the way you acess it? I've been using this for a year and never got any problems with it.

 

Statics cause memory leaks. DON'T USE STATICS!!!! Pass an instance instead.

 

and @mythbusterma i'm not here to discuss, i just hate how people say about using static, it's nice-looking and i don't see any difference between using or not using it.

@dudubs123 as i know the thread is solved, so mark it as solved.

I never got memory leaks, NEVER, so for me, as I sayed it's ok to use static. ^^

EDIT by Moderator: merged posts, please use the edit button instead of double posting.

 

It is NOT ok to use static. I really hope you're testing Cunningham's Law.