MySQL Tokens

Discussion in 'Spigot Plugin Development' started by MangoCodes, May 25, 2016.

  1. Code (Text):

    public int getPlayerTokens(Player p) throws SQLException {
        Connection con = getMySQLConnection();
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery("SELECT * FROM Tokens WHERE UUID='" + p.getPlayer().getUniqueId().toString().toLowerCase() + "'");
        if(rs.next()) {
            return rs.getInt("TOKENS");
        }else{
            return 0;
        }
    }

    public void addTokens(Player p, Integer tokens) throws SQLException {
        Connection con = getMySQLConnection();
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery("SELECT * FROM Tokens WHERE UUID='" + p.getPlayer().getUniqueId().toString().toLowerCase() + "'");
        if(rs.next()) {
            int playerTokens = getPlayerTokens(p);
        }else{
            //PLAYER DOESN'T EXIST SO RIP
        }
    }
     
    I made a method to get the players tokens but I am having issues making a method to add tokens can someone help me?
     
  2. First, you might want to make a method to see if the player exists in the database, like this:

    Code (Text):
    public static boolean playerExists(String Name) {
            try {
                ResultSet rs = query("SELECT * FROM `TABLENAME` WHERE UUID= '" + Name + "'");
                if (rs.next()) {
                    return rs.getString("Name") != null;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return false;
        }
    That way it's easier for future methods to see if the player exists.
    Next you're going to obviously query the database to get the current token count a certain player has, then set that to a variable.
    And If I am understanding you correctly, you will want to add that variable you just made with the Integer tokens amount.

    From here all you have to do is something like this:

    Code (Text):
    update("UPDATE `TABLENAME` SET someColumn='" + someInteger + "' WHERE UUID= '" + p.getUniqueId().toString() + "';");
     
    Hope that makes sense, kind of hard to explain without spoonfeeding haha
     
  3. Sick database tip here: make the smallest amount of queries to the database possible. If you want to add X amount of credits to the table, it would look like this:
    Code (Text):
    UPDATE TableName
    SET Tokens = (Tokens + X)
    WHERE UUID=PlayerUUID
    That way you avoid making an unnecessary select to get the value.
     
    • Agree Agree x 1
  4. Or you could just cache all the data locally, this way you only have to update your database when the player leaves ;)
     
  5. MiniDigger

    Supporter

    this will not work if you have multiple instances of your plugin accessing the db ;)
     
  6. What @Ferdz said is fine. You should also close your resources in a finally block or by using try-with-resources.

    Also if you plan to use more instances of your plugin accessing the same database or table at the same time (or if you plan to execute them asynchronously) and you want to execute read-modify-write operations you have to:
    • do everything in one statement (that's the case in @Ferdz solution)
    • or use transactions with SELECT FOR UPDATE statements
    • or use transections with (UN)LOCK TABLE statements
    • or use transections and increase the isolation level
    to avoid race conditions.

    Note that it is not thread-safe to use one connection object in multiple threads (if you plan to execute statements asynchronously). In that case I recommend using a connection pool like HikariCP.
     
    #6 stonar96, May 25, 2016
    Last edited: May 25, 2016
  7. Your server will lag if you're running many MySQL commands on the main-server thread. Also, make sure to create one connection only. And turn that off on your onDisable method. Otherwise, it is horrible.
     
  8.  
  9. MiniDigger

    Supporter

    NEVER run any mysql operation on the main thread. PLEASE.
     
  10. But you have to know what you are doing if you just run them asynchronously. As I have already said it's not thread-safe. You would need one connection object per thread or use a lock or synchronization or a connection pool. And then you would still have to make sure to avoid race conditions on the database.
     
    #10 stonar96, May 25, 2016
    Last edited: May 25, 2016
  11. Huh? Why would you have one player on two servers?
     
  12. MiniDigger

    Supporter

    you don't need one player on another server to run in such problems. Think of a webshop where you can spend money. Or maybe you have a tax system on your citybuild server.
    Also caching could cause data loss if your server crashes.
    (I am not a friend of caching, as you can notice ^^)
     
  13. True, certain things like currencies and punishments are probably better of not being cached. But when you're talking about statistics that could increase by a couple / s (Mob kills in combination with farms, blocks mined, and all those things) caching is vital, especially on networks.
     
  14. MiniDigger

    Supporter

    shure, for stats (which can only increase and only on the server you are currently on) I would use caching too. But I don't cache very agressivly, invalidating the cache every 15 minutes.
     
  15. If you're worried about data loss then use a writeback cache ;), it still saves on the data fetch.
     
  16. You don't need one player on different servers and you also don't need different servers for a race condition. Even if you use differnent connection objects a race condition is possible if you execute the statements asynchronously. Imagine a player kills two others at almost the same time and gets tokens for that two times. If both statements are executed on different threads it is possible that both threads read the same value from the db, add to the same value and write the same value.

    In my first answer above I have listed some options how to avoid that.

    I can't understand why people always think it's that easy. "Just put it inside an async task and problem is solved" - That's not true.
     
    #16 stonar96, May 25, 2016
    Last edited: May 25, 2016
    • Agree Agree x 1
  17. Huh? Thats not what i meant. My point was that storing stats locally (optimally thread safe) will work too because "usually" you wont have one player on two servers in a bungee network, you just have to delay the data fetching a bit when a player joins to give the other server some time to upload it's data.