Help with SQL Connections

Discussion in 'Plugin Development' started by Airbornz, Jul 26, 2015.

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

    Airbornz

    Hello,

    So I have decided to switch to SQL for managing my player data as it is running on a bungeecord network. What I'm writing is the backend and API for such network so all data pulling needs to be static in some way. So i made a skyplayer class which will hold the players data, when the Asyncprelogin is fired I tell my SQL code to pull that players data hosted in a new thread. Before while leaving it in the bukkit thread it lagged a lot. While doing it in this new thread though I'm getting timed out when trying to connect. I'm fairly new to SQL and creating new threads, so I was wondering if there was a better way to do SQL pulling while still keeping in mind most of it needs to be static and somehow returnable (My method for returning being the sky player class).

    SQL Pull Code (open)

    Code:
    public void run(){
            if (pull.size() >= 1){
                for (UUID uuid : pull){
                    try{
                        Connection connection = DriverManager.getConnection("jdbc:mysql://airbornz.com:3306/airbornz_skylar", "airbornz_skylar", "DrEUbdk5SCXFKeVs");
                      
                        PreparedStatement sql;
                        {
                            sql = connection.prepareStatement("SELECT * FROM `playerdata` WHERE UUID=?;");
                            sql.setString(1, uuid.toString());
                            ResultSet result = sql.executeQuery();
                            boolean contains = result.next();
                            sql.close();
                            result.close();
                          
                            if (!contains){
                                sql = connection.prepareStatement("INSERT INTO `playerdata` VALUES(NULL, ?, ?, 'None', 'No', 'None', 'None', 'None', 0, ?, 'IP', 'Default', '', '', '', '', '', '');");
                                sql.setString(1, uuid.toString());
                                sql.setString(2, Bukkit.getPlayer(uuid).getName());
                                sql.setString(3, uuid.toString());
                                sql.execute();
                                sql.close();
                            }
                          
                            String name = null;
                            sql = connection.prepareStatement("SELECT Name FROM `playerdata` WHERE UUID=?;");
                            sql.setString(1, uuid.toString());
                            result = sql.executeQuery();
                            result.next();
                            name = result.getString("Name");
                            result.close();
                          
                            StaffLevel level = null;
                            sql = connection.prepareStatement("SELECT StaffLevel FROM `playerdata` WHERE UUID=?;");
                            sql.setString(1, uuid.toString());
                            result = sql.executeQuery();
                            result.next();
                            level = convertLevel(result.getString("StaffLevel"));
                            result.close();
                          
                            boolean banned = false;
                            sql = connection.prepareStatement("SELECT Banned FROM `playerdata` WHERE UUID=?;");
                            sql.setString(1, uuid.toString());
                            result = sql.executeQuery();
                            result.next();
                            banned = result.getBoolean("Banned");
                            result.close();
                          
                            BanType type = null;
                            sql = connection.prepareStatement("SELECT BanType FROM `playerdata` WHERE UUID=?;");
                            sql.setString(1, uuid.toString());
                            result = sql.executeQuery();
                            result.next();
                            String temp;
                            temp = result.getString("BanType");
                            if (temp.equalsIgnoreCase("0")){
                                type = BanType.Perm;
                            }
                            else if (temp.equals("1")){
                                type = BanType.Temp;
                            }
                            else{
                                type = BanType.Error;
                            }
                            result.close();
                          
                            long time = 0;
                            sql = connection.prepareStatement("SELECT BanTime FROM `playerdata` WHERE UUID=?;");
                            sql.setString(1, uuid.toString());
                            result = sql.executeQuery();
                            result.next();
                            time = result.getLong("BanTime");
                            result.close();
                          
                            String banner = "Console";
                            sql = connection.prepareStatement("SELECT BannedBy FROM `playerdata` WHERE UUID=?;");
                            sql.setString(1, uuid.toString());
                            result = sql.executeQuery();
                            result.next();
                            banner = result.getString("BannedBy");
                            result.close();
                          
                            String reason = "None provided";
                            sql = connection.prepareStatement("SELECT BanReason FROM `playerdata` WHERE UUID=?;");
                            sql.setString(1, uuid.toString());
                            result = sql.executeQuery();
                            result.next();
                            reason = result.getString("BanReason");
                            result.close();
                          
                            BanInfo info = new BanInfo(uuid, banned, reason, time, banner, type);
                          
                            List<String> friends = new ArrayList<String>();
                            sql = connection.prepareStatement("SELECT Friends FROM `playerdata` WHERE UUID=?;");
                            sql.setString(1, uuid.toString());
                            result = sql.executeQuery();
                            result.next();
                            String fri = result.getString("Friends");
                            result.close();
                            String[] fraw = fri.split(",");
                            List<String> f = new ArrayList<String>();
                            for (int i = 0, n = fraw.length; i < n; i++){
                                f.add(fraw[i]);
                            }
                            friends = f;
                          
                            List<String> groups = new ArrayList<String>();
                            sql = connection.prepareStatement("SELECT Groups FROM `playerdata` WHERE UUID=?;");
                            sql.setString(1, uuid.toString());
                            result = sql.executeQuery();
                            result.next();
                            String gr = result.getString("Groups");
                            result.close();
                            String[] graw = gr.split(",");
                            List<String> g = new ArrayList<String>();
                            for (int i = 0, n = graw.length; i < n; i++){
                                g.add(graw[i]);
                            }
                            groups = g;
                          
                            List<String> perms = new ArrayList<String>();
                            sql = connection.prepareStatement("SELECT Permissions FROM `playerdata` WHERE UUID=?;");
                            sql.setString(1, uuid.toString());
                            result = sql.executeQuery();
                            result.next();
                            String pr = result.getString("Permissions");
                            result.close();
                            String[] praw = pr.split(",");
                            List<String> p = new ArrayList<String>();
                            for (int i = 0, n = praw.length; i < n; i++){
                                p.add(graw[i]);
                            }
                            perms = p;
                          
                            List<String> high = new ArrayList<String>();
                            sql = connection.prepareStatement("SELECT NotHigh FROM `playerdata` WHERE UUID=?;");
                            sql.setString(1, uuid.toString());
                            result = sql.executeQuery();
                            result.next();
                            pr = result.getString("NotHigh");
                            result.close();
                            praw = pr.split(",");
                            p = new ArrayList<String>();
                            for (int i = 0, n = praw.length; i < n; i++){
                                p.add(graw[i]);
                            }
                            high = p;
                          
                            List<String> mid = new ArrayList<String>();
                            sql = connection.prepareStatement("SELECT NotMid FROM `playerdata` WHERE UUID=?;");
                            sql.setString(1, uuid.toString());
                            result = sql.executeQuery();
                            result.next();
                            pr = result.getString("NotMid");
                            result.close();
                            praw = pr.split(",");
                            p = new ArrayList<String>();
                            for (int i = 0, n = praw.length; i < n; i++){
                                p.add(graw[i]);
                            }
                            mid = p;
                          
                            List<String> low = new ArrayList<String>();
                            sql = connection.prepareStatement("SELECT NotLow FROM `playerdata` WHERE UUID=?;");
                            sql.setString(1, uuid.toString());
                            result = sql.executeQuery();
                            result.next();
                            pr = result.getString("NotLow");
                            result.close();
                            praw = pr.split(",");
                            p = new ArrayList<String>();
                            for (int i = 0, n = praw.length; i < n; i++){
                                p.add(graw[i]);
                            }
                            low = p;
                          
                            List<String> none = new ArrayList<String>();
                            sql = connection.prepareStatement("SELECT NotNone FROM `playerdata` WHERE UUID=?;");
                            sql.setString(1, uuid.toString());
                            result = sql.executeQuery();
                            result.next();
                            pr = result.getString("NotNone");
                            result.close();
                            praw = pr.split(",");
                            p = new ArrayList<String>();
                            for (int i = 0, n = praw.length; i < n; i++){
                                p.add(graw[i]);
                            }
                            none = p;
                          
                            List<String> reports = new ArrayList<String>();
                            sql = connection.prepareStatement("SELECT Reports FROM `playerdata` WHERE UUID=?;");
                            sql.setString(1, uuid.toString());
                            result = sql.executeQuery();
                            result.next();
                            pr = result.getString("Reports");
                            result.close();
                            praw = pr.split(",");
                            p = new ArrayList<String>();
                            for (int i = 0, n = praw.length; i < n; i++){
                                p.add(graw[i]);
                            }
                            reports = p;
                          
                            new SkyPlayer(Bukkit.getPlayer(uuid), uuid, name, level, groups, perms, friends, info, high, mid, low, none, reports);
                        }
                      
                        if (connection != null && !connection.isClosed()){
                            connection.close();
                        }
                    }
                    catch(Exception e){
                        e.printStackTrace();
                    }
                }
                pull.clear();
            }
        }


    SQL Runnable for Updating (open)

    Code:
    public void run(){
            List<PreparedStatement> done = new ArrayList<PreparedStatement>();
            if (updates.size() >= 1){
                for (PreparedStatement s : updates.keySet()){
                    try {
                        Connection connection = DriverManager.getConnection("jdbc:mysql://airbornz.com:3306/airbornz_skylar", "airbornz_skylar", "DrEUbdk5SCXFKeVs");
                        UUID uuid = updates.get(s);
                        PreparedStatement sql;
                      
                        {
                            sql = connection.prepareStatement("SELECT * FROM `playerdata` WHERE UUID=?;");
                            sql.setString(1, uuid.toString());
                            ResultSet result = sql.executeQuery();
                            boolean contains = result.next();
                            sql.close();
                            result.close();
                          
                            if (!contains){
                                sql = connection.prepareStatement("INSERT INTO `playerdata` VALUES(NULL, ?, ?, 'None', 'No', 'None', 'None', 'None', 0, ?, 'IP', 'Default', '', '', '', '', '', '');");
                                sql.setString(1, uuid.toString());
                                sql.setString(2, Bukkit.getPlayer(uuid).getName());
                                sql.setString(3, uuid.toString());
                                sql.execute();
                                sql.close();
                            }
                          
                            s.executeUpdate();
                            s.close();
                            done.add(s);
                        }
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                }
                for (PreparedStatement p : done){
                    if (updates.containsKey(p)){
                        updates.remove(p);
                    }
                }
                done.clear();
            }
            if (createProfile.size() >= 1){
                List<UUID> done2 = new ArrayList<UUID>();
                for (UUID uuid : createProfile){
                    Connection connection;
                    try {
                        connection = DriverManager.getConnection("jdbc:mysql://airbornz.com:3306/airbornz_skylar", "airbornz_skylar", "DrEUbdk5SCXFKeVs");
                        PreparedStatement sql;
                        sql = connection.prepareStatement("INSERT INTO `playerdata` VALUES(NULL, ?, ?, 'None', 'No', 'None', 'None', 'None', 0, ?, 'IP', 'Default', '', '', '', '', '', '');");
                        sql.setString(1, uuid.toString());
                        sql.setString(2, Bukkit.getPlayer(uuid).getName());
                        sql.setString(3, uuid.toString());
                        sql.execute();
                        sql.close();
                        connection.close();
                        done2.add(uuid);
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                  
                }
                for (UUID uuid : done2){
                    if (createProfile.contains(uuid)){
                        createProfile.remove(uuid);
                    }
                }
                done2.clear();
            }
          
        }


    What I'm also doing is waiting for the pull to finish by doing something like
    Code:
    while (SQLDownloader.pull.contains(uuid){}
    Don't think that is bad, correct me if I'm wrong.

    If you need anymore code just let me know, I can gladly provide it.
     
  2. Offline

    mythbusterma

    @Airbornz

    While we're technically not supposed to help you here (you run offline mode servers with unsupported software), I'm going to give you the benefit of the doubt here.


    No, it doesn't. If it does "need to be static" then you've designed everything wrong and probably need to go back and redo it.

    The point being that this is asynchronous programming, and waiting for a return value is inherently a synchronous operation, if you think about it.


    There's quite a lot wrong with this statement, actually:

    • This will halt the thread it's running on until the cache containing the bucket that contains the UUID is cleared (which, if you're lucky, actually happens)
    • You're using Collections that aren't thread safe across multiple threads, you're going to cause yourself quite a few issues here
    • You're using public, static Objects when it is painfully obvious that they are not only stateful (i.e. should not belong to the class) and shouldn't be exposed to other classes
    • You're burning the CPU of whatever thread this is running because you have a wait loop that has no sleep in it
    In short, you probably need to work on some more basic programming skills before you try multithreaded programming.
     
  3. Offline

    Airbornz

    Yeah now that I read that, I really didn't mean "has to be static" I just really needed an API call which can access the data from other plugins, which I have. And yes I have no experience with multi-threaded applications, I tried off the top of my head for the first time and was just trying to go by it. Do you mind expanding on the "Sleep" function for the wait loop? Or better can you explain how you do your wait loops so I can get an idea?

    Thanks!

    @mythbusterma
    Just read up on multi-threading and have a couple of questions they didn't answer.

    Can I sleep? I mean this is running on the Bukkit thread so wouldn't that pause Bukkit?

    What substitution collections could I use then?

    Are you talking about the collection? Like the ArrayList of UUIDs?

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jun 11, 2016
  4. Offline

    mythbusterma

    @Airbornz

    The idea is that you if you wait for a call, you should do something like:

    Code:
    while (!item.isReady()) {
        Thread.sleep(5);
    }
    That way you can avoid burning the CPU (which has significant consequences in other applications, not so much Bukkit), however this is not a very good design pattern for Bukkit because, as you said, this would halt the main thread.

    The point being, the way you're doing it right now also halts the main thread (assuming that code is being run on the main thread), unless it's in an AsyncPlayerPreLoginEvent Listener, in which case, it is, as the name suggests, being run asynchronously to the main thread.

    A few thread-safe Collections in the standard library that I know of are: ConcurrentHashMap (and Sets that are backed using this Map), CopyOnWriteArrayList (pretty damn slow), ConcurrentLinkedBlockingQueue, LinkedBlockingDeque, and a few other Queues.

    Yes, that is the Collection I'm referring to, specifically, the Collection and the connection to the server should be stateful, as it doesn't make sense for them to be associated with the class that contains them, but rather the instance.

    An API should be self-contained to either another class or another package, you shouldn't allow an API direct access to your SQL statements. Instead, you should interact with other plugins by exposing a single, well documented API class (or package, should the API be large enough) and only use internal classes where absolutely necessary. Also, there's no reason API methods have to be static, it's perfectly possible for you to either register a service that they can request a provider for or for them to get the instance of your plugin and request an API object. That being said, there's nothing particularly wrong with making your API methods static, if that's what fits. But that shouldn't affect the rest of your plugin, as you can "escape" a static call with a singleton (which, in this case, your main class should be).

    The design pattern you should probably use for this is a callback. After the database query is finished on another thread, run a task on the main thread that uses the results of the database call and somehow notifies the main thread.
     
  5. Online

    timtower Administrator Administrator Moderator

    Locked.
    Offline mode is not supported by Bukkit
     
Thread Status:
Not open for further replies.

Share This Page