Running SQL Queries in separate threads

Discussion in 'Plugin Development' started by Lilret123, Apr 30, 2015.

Thread Status:
Not open for further replies.
  1. Offline

    Lilret123

    Hey guys, I've ran into a little problem(maybe i big one idk) with SQL in my plugin.

    When a player logs on to the server I call a bunch of SQL queries to get the data for their scoreboard..

    Code:
    public static void updateStatsSQL(Player p){
            if(new Statistics().containsUser(p.getUniqueId())){
                new Statistics().updatePlayer(p.getUniqueId());
                return;
            }
            new Statistics().addPlayer(p.getUniqueId());
        }
    
        public static void updateEconSQL(Player p){
            if(new Economy().containsUser(p.getUniqueId())){
                double balance = new Economy().getBalance(p.getUniqueId());
                new Economy().setBalance(p.getUniqueId(), balance);
                return;
            }
            new Economy().addBalance(p.getUniqueId(), 0);
        }
    In those queries it gets their balance, rank, and kills score..
    (Example is the getter method for their kills score)

    Code:
    public int getHubKills(UUID uuid){
            Connection conn = null;
            PreparedStatement ps = null;
            ResultSet res = null;
            try {
                conn = new MySQL(Main.getPlugin(Main.class), "omitted", "omitted", "omitted", "omitted", "omitted").openConnection();
                ps = conn.prepareStatement("SELECT hubkills FROM TheTrialsStats WHERE uuid=?");
                ps.setString(1, uuid.toString());
                res = ps.executeQuery();
                int toreturn = 0;
                if(res.next()){
                    toreturn = res.getInt("hubkills");
                }
                res.close();
                ps.close();
                conn.close();
                return toreturn;
            }
            catch (ClassNotFoundException | SQLException e) {
                Global.printException(e, "get", cname);
            } finally {
                if(res != null) {try {res.close();} catch (SQLException e){Global.printException(e, "get", cname);}}
                if(ps != null) {try{ps.close();} catch (SQLException e){Global.printException(e, "get", cname);}}
                if (conn != null) {try {conn.close();} catch (SQLException | NullPointerException e) {Global.printException(e, "get", cname);}}
            }
            return 0;
        }
    These queries work and the correct data is returned, the only problem is that they lag the server because their running on the main thread, and I don't know how i would run them on a separate thread...
    Any help would be greatly appreciated
    :)

    ps: the "Global.printException" is just something i made so i could format the stacks and make them easier to debug.
     
  2. Offline

    I Al Istannen

  3. Offline

    1Rogue

    don't create a new class instance for every single method call, that literally will not work at all.
     
  4. Offline

    mazentheamazin

    For the love of all that is fuck, learn Java before playing with it in anyway (regardless of what you're doing, however especially Bukkit). As @1Rogue said, creating a new instance for every method call will not work unless the methods are accessing static fields which I really hope you're not doing.
     
  5. Offline

    Lilret123

    @mazentheamazin
    Yo... chill.. lol, its what i always have done and it always has worked. so excuse my apparent ignorance and actually be helpful next time. KThkxBai


    @1Rogue
    And should i be doing something like this?

    Code:
    public static void updateStatsSQL(Player p){
            Statistics statistics = new Statistics();
            if(statistics.containsUser(p.getUniqueId())){
                statistics.updatePlayer(p.getUniqueId());
                return;
            }
            statistics.addPlayer(p.getUniqueId());
        }
     
  6. Offline

    1Rogue

    You shouldn't be making new references at all.
     
  7. Offline

    Lilret123

    @1Rogue
    How should i be doing this then, at this point im lost....
    the class "Statistics" is in another plugin of mine that runs on all of my servers. Its meant to be a "Core" plugin that all of my other plugins build off of...
     
  8. Offline

    1Rogue

    Then you need to get the reference from that plugin, not create another one.
     
  9. Offline

    Lilret123

    @1Rogue

    This is a broken down version of the join event on the hub server..
    Code:
    package me.Lilret123.tthub.Events;
    
    public class JoinEvent implements Listener {
    
    @EventHandler
        public void onJoin(PlayerJoinEvent e) {
    updateStatsSQL(p);
    }
    
    public static void updateStatsSQL(Player p){
            Statistics statistics = new Statistics();
            if(statistics.containsUser(p.getUniqueId())){
                statistics.updatePlayer(p.getUniqueId());
                return;
            }
            statistics.addPlayer(p.getUniqueId());
        }
    }
    And this is a broken down version of the "Statistics" class in my other plugin..

    Code:
    package net.Lilret123.core.MySqlDatabase.TheTrialsSQL;
    
    public class Statistics {
    String cname = Statistics.class.getSimpleName();
    
    public void addPlayer(UUID uuid){
            Connection conn = null;
            PreparedStatement ps = null;
            if(containsUser(uuid)){
                updatePlayer(uuid);
                return;
            }
            try {
                conn = new MySQL(Main.getPlugin(Main.class), "omitted", "omitted", "omitted", "omitted", "omitted").openConnection();
                ps = conn.prepareStatement("INSERT INTO TheTrialsStats(uuid, name, hubkills) VALUES(?,?,?)");
                ps.setString(1, uuid.toString());
                ps.setString(2, UserData.getOfflinePlayerName(uuid.toString()));
                ps.setInt(3, 0);
                ps.execute();
                ps.close();
                conn.close();
            }
            catch (ClassNotFoundException | SQLException e) {
                Global.printException(e, "add", cname);
            }
            finally {
                if(ps != null) {try{ps.close();} catch (SQLException e){Global.printException(e, "add", cname);}}
                if (conn != null) {try {conn.close();} catch (SQLException | NullPointerException e) {Global.printException(e, "add", cname);}}
            }
        }
    }
    
     
  10. Offline

    1Rogue

    when you create a new class instance, you are no longer referencing the same fields. Each instance is a new copy of fields, a list in one instance won't be the same in another.
     
  11. Offline

    Lilret123

    @1Rogue
    Ok... maybe im just being stupid..
    but how would i go about calling these methods then?
     
  12. Offline

    1Rogue

    You need to retrieve the main class (JavaPlugin#getPlugin(Class)) and then use accessor methods for going to other references.
     
Thread Status:
Not open for further replies.

Share This Page