Solved Economy via MySQL [DEVELOPMENT]

Discussion in 'Plugin Development' started by Fight_Or_Die, Apr 6, 2014.

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

    Fight_Or_Die

    Hey there.
    I've been coding plugins for quite some time and just freelance develop. Basically, I study forum posts and Java Docs, however I never made a plugin with MySQL.

    This plugin that I'm working on is a Token System, that needs to operate across servers and have basic editing (Set,Take,Give) operations. I made the plugin work locally, storing the files to a .yml .

    What I came to the forums for, today, is help with implementing MySQL and possibly adding a /tokens (name) - command - which checks the amount of tokens a player, or oneself, has. The MySQL table storage also should update itself periodically.

    Before you ask - Yes, I've looked over various forum posts explaining MySQL operations ; YouTube videos, etc. but I can't get it to work.


    Thank you in advance for your time and help.
    I will be monitoring this post and will respond as fast as possible to provide any additional information.

    Best Regards,
    Fight_Or_Die
    ----

    Classes:

    PlayerJoin.java

    Code:java
    1. package me.fightordie.boltsoft.tokens;
    2.  
    3.  
    4.  
    5. import org.bukkit.event.EventHandler;
    6.  
    7. import org.bukkit.event.Listener;
    8.  
    9. import org.bukkit.event.player.PlayerJoinEvent;
    10.  
    11.  
    12.  
    13. public class PlayerJoin implements Listener {
    14.  
    15.  
    16.  
    17. @EventHandler
    18.  
    19. public void onJoin(PlayerJoinEvent e) {
    20.  
    21. if(TokenManager.hasAccount(e.getPlayer().getName())) return;
    22.  
    23.  
    24.  
    25. TokenManager.setBalance(e.getPlayer().getName(), 50D);
    26.  
    27. }
    28.  
    29. }




    SaveLoad.java

    Code:java
    1. package me.fightordie.boltsoft.tokens;
    2.  
    3.  
    4.  
    5. public class SaveLoad {
    6.  
    7.  
    8.  
    9. private static Token plugin = TokenManager.getPlugin();
    10.  
    11.  
    12.  
    13. public static void saveBalances() {
    14.  
    15. for(String p : TokenManager.getBalanceMap().keySet()) {
    16.  
    17.  
    18.  
    19. plugin.getConfig().set("tokens."+p, TokenManager.getBalanceMap().get(p));
    20.  
    21. }
    22.  
    23.  
    24.  
    25. plugin.saveConfig();
    26.  
    27. }
    28.  
    29.  
    30.  
    31. public static void loadBalances() {
    32.  
    33. if(!plugin.getConfig().contains("tokens")) return;
    34.  
    35.  
    36.  
    37. for(String s : plugin.getConfig().getConfigurationSection("tokens").getKeys(false)) {
    38.  
    39.  
    40.  
    41. TokenManager.setBalance(s, plugin.getConfig().getDouble("tokens."+s));
    42.  
    43. }
    44.  
    45. }
    46.  
    47. }



    Token.java

    Code:java
    1. package me.fightordie.boltsoft.tokens;
    2.  
    3.  
    4.  
    5. import org.bukkit.Bukkit;
    6.  
    7. import org.bukkit.plugin.java.JavaPlugin;
    8.  
    9.  
    10.  
    11. public class Token extends JavaPlugin {
    12.  
    13.  
    14.  
    15. public void onEnable() {
    16.  
    17.  
    18.  
    19. getCommand("token").setExecutor(new TokenCommand());
    20.  
    21.  
    22.  
    23. new TokenManager(this);
    24.  
    25.  
    26.  
    27. SaveLoad.loadBalances();
    28.  
    29.  
    30.  
    31. Bukkit.getPluginManager().registerEvents(new PlayerJoin(), this);
    32.  
    33.  
    34.  
    35. System.out.println("Enabling token system");
    36.  
    37.  
    38.  
    39. }
    40.  
    41.  
    42.  
    43. public void onDisable() {
    44.  
    45.  
    46.  
    47. SaveLoad.saveBalances();
    48.  
    49.  
    50.  
    51. System.out.println("Disabling token system");
    52.  
    53.  
    54.  
    55.  
    56.  
    57. }
    58.  
    59.  
    60.  
    61.  
    62.  
    63.  
    64.  
    65. }
    66.  
    67.  



    TokenCommand,java

    Code:java
    1. package me.fightordie.boltsoft.tokens;
    2.  
    3.  
    4.  
    5. import org.bukkit.ChatColor;
    6.  
    7. import org.bukkit.command.Command;
    8.  
    9. import org.bukkit.command.CommandExecutor;
    10.  
    11. import org.bukkit.command.CommandSender;
    12.  
    13.  
    14.  
    15. public class TokenCommand implements CommandExecutor {
    16.  
    17.  
    18.  
    19. @Override
    20.  
    21. public boolean onCommand(CommandSender cs, Command command, String s, String[] args) {
    22.  
    23.  
    24.  
    25. if(args.length != 3) {
    26.  
    27. cs.sendMessage(ChatColor.GREEN + "[BST] " + ChatColor.RED + "Invalid usage.");
    28.  
    29. cs.sendMessage(ChatColor.GREEN + "[BST] " + ChatColor.RED + "Usage: /token <give/take/set> <player> <amount>");
    30.  
    31.  
    32.  
    33. return true;
    34.  
    35.  
    36.  
    37. }
    38.  
    39.  
    40.  
    41.  
    42.  
    43. if(cs.hasPermission("token.edit")) {
    44.  
    45.  
    46.  
    47. if(args[0].equalsIgnoreCase("give")) {
    48.  
    49.  
    50.  
    51. if(!TokenManager.hasAccount(args[1])) {
    52.  
    53. cs.sendMessage(ChatColor.GREEN + "[BST] " + ChatColor.RED + "Player does NOT have an account!");
    54.  
    55. return true;
    56.  
    57. }
    58.  
    59.  
    60.  
    61. double amount = 0;
    62.  
    63.  
    64.  
    65. try {
    66.  
    67.  
    68.  
    69. amount = Double.parseDouble(args[2]);
    70.  
    71.  
    72.  
    73. } catch (Exception e) {
    74.  
    75.  
    76.  
    77. cs.sendMessage(ChatColor.GREEN + "[BST] " + ChatColor.RED + "Arguments must be a number!");
    78.  
    79.  
    80.  
    81. return true;
    82.  
    83. }
    84.  
    85.  
    86.  
    87. TokenManager.setBalance(args[1], TokenManager.getBalance(args[1]) + amount);
    88.  
    89. cs.sendMessage(ChatColor.GREEN + "[BST] " + ChatColor.GRAY + "Transaction (give) successful!");
    90.  
    91.  
    92.  
    93. } else if (args[0].equalsIgnoreCase("take")) {
    94.  
    95. if(!TokenManager.hasAccount(args[1])) {
    96.  
    97. cs.sendMessage(ChatColor.GREEN + "[BST] " + ChatColor.RED + "Player does NOT have an account!");
    98.  
    99. return true;
    100.  
    101. }
    102.  
    103.  
    104.  
    105. double amount = 0;
    106.  
    107.  
    108.  
    109. try {
    110.  
    111.  
    112.  
    113. amount = Double.parseDouble(args[2]);
    114.  
    115.  
    116.  
    117. } catch (Exception e) {
    118.  
    119.  
    120.  
    121. cs.sendMessage(ChatColor.GREEN + "[BST] " + ChatColor.RED + "Arguments must be a number!");
    122.  
    123.  
    124.  
    125. return true;
    126.  
    127. }
    128.  
    129.  
    130.  
    131. TokenManager.setBalance(args[1], TokenManager.getBalance(args[1]) - amount);
    132.  
    133. cs.sendMessage(ChatColor.GREEN + "[BST] " + ChatColor.GRAY + "Transaction (take) successful!");
    134.  
    135.  
    136.  
    137. } else if (args[0].equalsIgnoreCase("set")) {
    138.  
    139. if(!TokenManager.hasAccount(args[1])) {
    140.  
    141. cs.sendMessage(ChatColor.GREEN + "[BST] " + ChatColor.RED + "Player does NOT have an account!");
    142.  
    143. return true;
    144.  
    145. }
    146.  
    147.  
    148.  
    149. double amount = 0;
    150.  
    151.  
    152.  
    153. try {
    154.  
    155.  
    156.  
    157. amount = Double.parseDouble(args[2]);
    158.  
    159.  
    160.  
    161. } catch (Exception e) {
    162.  
    163.  
    164.  
    165. cs.sendMessage(ChatColor.GREEN + "[BST] " + ChatColor.RED + "Arguments must be a number!");
    166.  
    167.  
    168.  
    169. return true;
    170.  
    171. }
    172.  
    173.  
    174.  
    175. TokenManager.setBalance(args[1], amount);
    176.  
    177. cs.sendMessage(ChatColor.GREEN + "[BST] " + ChatColor.GRAY + "Transaction (set) successful!");
    178.  
    179.  
    180.  
    181. }
    182.  
    183.  
    184.  
    185. } else {
    186.  
    187. cs.sendMessage(ChatColor.GREEN + "[BST] " + ChatColor.RED + "No permission!");
    188.  
    189. }
    190.  
    191.  
    192.  
    193. return true;
    194.  
    195. }
    196.  
    197.  
    198.  
    199. }
    200.  
    201.  



    TokenManager.java

    Code:java
    1. package me.fightordie.boltsoft.tokens;
    2.  
    3.  
    4.  
    5. import java.util.HashMap;
    6.  
    7.  
    8.  
    9. public class TokenManager {
    10.  
    11.  
    12.  
    13. private static Token plugin;
    14.  
    15.  
    16.  
    17. public TokenManager(Token instance) {
    18.  
    19.  
    20.  
    21. plugin = instance;
    22.  
    23.  
    24.  
    25. }
    26.  
    27.  
    28.  
    29. public static HashMap<String, Double> bal = new HashMap<>();
    30.  
    31.  
    32.  
    33. public static void setBalance (String player, double amount) {
    34.  
    35.  
    36.  
    37. bal.put(player, amount);
    38.  
    39. }
    40.  
    41.  
    42.  
    43. public static Double getBalance(String player) {
    44.  
    45.  
    46.  
    47. return bal.get(player);
    48.  
    49. }
    50.  
    51.  
    52.  
    53. public static boolean hasAccount(String player) {
    54.  
    55.  
    56.  
    57. return bal.containsKey(player);
    58.  
    59. }
    60.  
    61.  
    62.  
    63. public static HashMap<String, Double> getBalanceMap() {
    64.  
    65.  
    66.  
    67. return bal;
    68.  
    69.  
    70.  
    71. }
    72.  
    73.  
    74.  
    75. public static Token getPlugin() {
    76.  
    77.  
    78.  
    79. return plugin;
    80.  
    81.  
    82.  
    83. }
    84.  
    85.  
    86.  
    87. }
    88.  
    89.  



    -Edited with classes-

    Anyone know how to make it write and edit to the mySQL tables?

    Still not solved.

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

    WoodMKII

    If you are using husky and tip's MySQL library from here (I found this thread from your post there) then you should just be able to make up a small class that replaces your TokenManager, with a few changes to your Token class.

    First you need to connect to your database, you can do this in your Token class to avoid reconnecting each time you want to access it:

    Token.java
    Code:java
    1. package me.fightordie.boltsoft.tokens;
    2.  
    3. import java.sql.Connection;
    4. import java.sql.SQLException;
    5.  
    6. import org.bukkit.Bukkit;
    7. import org.bukkit.plugin.java.JavaPlugin;
    8.  
    9. import code.husky.mysql.MySQL;
    10.  
    11. public class Token extends JavaPlugin
    12. {
    13. public MySQL mySQL = null;
    14. public Connection connection = null;
    15.  
    16. public TokenManager tokenManager;
    17.  
    18. public void onEnable()
    19. {
    20. getLogger().info("Enabling token system");
    21.  
    22. Bukkit.getPluginManager().registerEvents(new PlayerJoin(), this);
    23. getCommand("token").setExecutor(new TokenCommand());
    24.  
    25. getLogger().info("Connecting to database...");
    26.  
    27. mySQL = new MySQL(this, "hostname", "port", "db", "user", "pass");
    28.  
    29. if(checkConnection())
    30. {
    31. //Failed to connect to MySQL Database ( Actual error is printed in MySQL.openConnection() )
    32. getLogger().severe("Connection to MySQL failed!");
    33.  
    34. //You may want to do something else here, ie. disable the plugin
    35. }
    36. else
    37. {
    38. getLogger().info("Connected to MySQL.");
    39.  
    40. checkTables();
    41.  
    42. tokenManager = new TokenManager(this);
    43.  
    44. getLogger().info("Token system enabled.");
    45. }
    46. }
    47.  
    48. public void onDisable()
    49. {
    50. getLogger().info("Disabling token system");
    51.  
    52. getLogger().info("Disconnecting database...");
    53.  
    54. try
    55. {
    56. if(mySQL.checkConnection())
    57. {
    58. connection.close();
    59. }
    60.  
    61. getLogger().info("Disconnected.");
    62. }
    63. catch (SQLException e)
    64. {
    65. getLogger().severe("Failed to disconnect MySQL!");
    66. e.printStackTrace();
    67. }
    68.  
    69. getLogger().info("Token system disabled.");
    70. }
    71.  
    72. public boolean checkConnection()
    73. {
    74. if(connection != null && mySQL.checkConnection())
    75. {
    76. return true;
    77. }
    78.  
    79. connection = mySQL.openConnection();
    80. return mySQL.checkConnection();
    81. }
    82.  
    83. private boolean checkTables()
    84. {
    85. /*
    86.   You could place a checkConnection() here, but as this is only
    87.   used in the onEnable method, you don't really need to
    88.   */
    89.  
    90. try
    91. {
    92. String query = "CREATE TABLE IF NOT EXISTS tokens (Username VARCHAR(255), Tokens DOUBLE);";
    93. connection.prepareStatement(query).executeUpdate();
    94.  
    95. return true;
    96. }
    97. catch (SQLException e)
    98. {
    99. e.printStackTrace();
    100. return false;
    101. }
    102. }
    103. }


    And the new TokenManager:

    TokenManager.java
    Code:java
    1. package me.fightordie.boltsoft.tokens;
    2.  
    3. import java.sql.PreparedStatement;
    4. import java.sql.ResultSet;
    5. import java.sql.SQLException;
    6.  
    7. public class TokenManager {
    8.  
    9. private static Token plugin;
    10.  
    11. public TokenManager(Token token)
    12. {
    13. plugin = token;
    14. }
    15.  
    16. public static boolean hasAccount(String name)
    17. {
    18. //Checks to see if the MySQL connection is open
    19. if(!plugin.checkConnection())
    20. {
    21. //The connection is closed, and could not be reopened
    22. return false;
    23. }
    24.  
    25. try
    26. {
    27. /*
    28.   By using prepared statements, you can protect from MySQL Injection.
    29.   While most usernames wont cause an issue from placing it directly into
    30.   a query, you never know xD
    31.   */
    32. String query = "SELECT * FROM tokens WHERE Username=?;";
    33. PreparedStatement statement = plugin.connection.prepareStatement(query);
    34.  
    35. statement.setString(1, name);
    36.  
    37. ResultSet result = statement.executeQuery();
    38.  
    39. //If the query returned a row, then the player has an account and next() will be true
    40. return result.next();
    41. }
    42. catch (SQLException e)
    43. {
    44. /*
    45.   An error occurred trying to check the table,
    46.   you may want to handle this differently.
    47.   */
    48.  
    49. e.printStackTrace();
    50. }
    51.  
    52. return false;
    53. }
    54.  
    55. public static void setBalance(String name, double d)
    56. {
    57. //Checks to see if the MySQL connection is open
    58. if(!plugin.checkConnection())
    59. {
    60. //The connection is closed, and could not be reopened
    61. return;
    62. }
    63.  
    64. try
    65. {
    66. //Check to see if they already have an account
    67. if(hasAccount(name))
    68. {
    69. //Update the pre-existing row
    70. String query = "UPDATE tokens SET Tokens=? WHERE Username=?;";
    71. PreparedStatement statement = plugin.connection.prepareStatement(query);
    72.  
    73. statement.setDouble(1, d);
    74. statement.setString(2, name);
    75.  
    76. statement.executeUpdate();
    77. }
    78. else
    79. {
    80. //Insert a new row
    81. String query = "INSERT INTO tokens (Username, Tokens) VALUES (?, ?);";
    82. PreparedStatement statement = plugin.connection.prepareStatement(query);
    83.  
    84. statement.setString(1, name);
    85. statement.setDouble(2, d);
    86.  
    87. statement.executeUpdate();
    88. }
    89. }
    90. catch (SQLException e)
    91. {
    92. /*
    93.   An error occurred trying to check the table,
    94.   you may want to handle this differently.
    95.   */
    96.  
    97. e.printStackTrace();
    98. }
    99.  
    100. }
    101.  
    102. public static double getBalance(String name)
    103. {
    104. //Checks to see if the MySQL connection is open
    105. if(!plugin.checkConnection())
    106. {
    107. //The connection is closed, and could not be reopened
    108. return 0;
    109. }
    110.  
    111. try
    112. {
    113. String query = "SELECT Tokens FROM tokens WHERE Username=?;";
    114. PreparedStatement statement = plugin.connection.prepareStatement(query);
    115.  
    116. statement.setString(1, name);
    117.  
    118. ResultSet result = statement.executeQuery();
    119.  
    120. if(!result.next())
    121. {
    122. //The player does not exist
    123. return 0;
    124. }
    125.  
    126. return result.getDouble("Tokens");
    127. }
    128. catch (SQLException e)
    129. {
    130. /*
    131.   An error occurred trying to check the table,
    132.   you may want to handle this differently.
    133.   */
    134.  
    135. e.printStackTrace();
    136. }
    137. return 0;
    138. }
    139. }




    Instead of using the static methods of TokenManager in TokenCommand, you can use the instance of TokenManager from Token, but that's up to you. You can also make up a wrapper class, to handle all the SQL queries, that way you don't have to write the lengthy query chunk of code more than once.

    --- I haven't tested any of this code, if there are any errors let me know xD
     
  3. Offline

    Fight_Or_Die

    Edit:
    Snipped to protect code.
     
  4. Offline

    WoodMKII

    I can't look too closely until I come back from class, but what are the MySQL errors? Can you pinpoint where the error is coming from? ie. From connecting to the database, creating the table, creating players, updating players... etc. Also, just from a quick glance, I've noticed that in the query you are surrounding the value for money with single quotes, integer values don't need them, otherwise MySQL interprets it as a string, and should give you an error. It's up to you if you would prefer to use PreparedStatements or put data directly into the query, but you should sanitise any input from a user, and since users can choose their own name, their usernames should be sanitised too.
     
  5. Offline

    Fight_Or_Die

  6. Offline

    WoodMKII

    hmm, It looks like you might be having issues with your config file, what port are you trying to use? (The second error was caused because CoinMethods.createTable() is called after the try statement, with no check to see if it actually succeeded and so tries to reconnect with the same URL that failed)
     
  7. Offline

    Fight_Or_Die

    The DB has the default port (3306). Any ideas how to fix?
     
  8. Offline

    WoodMKII

    Right, but what do you have in your config file for port?
    Lets take an example server, hosted at localhost, with the database minecraft, username Steve and password Creeper hosted on the default port; your config.yml would look something like this:

    config.yml
    Code:
    ip: localhost
    db: minecraft
    user: Steve
    pass: Creeper
    port: 3306
    

    Also, have you created the database you are trying to access?

    You should check to see whether or not the values retrieved from the config file are valid, you could try checking to see if the port is actually a number, and if it is null, or not a number then default to 3306

    For example:
    Code:java
    1. String port = getConfig().getString("port");
    2. try
    3. {
    4. Integer.parseInt(port);
    5. }
    6. catch(Exception e)
    7. {
    8. getLogger().info(port + " is not a valid port");
    9. port = "3306";
    10. }
    11.  
     
  9. Offline

    Fight_Or_Die

    WoodMKII
    Thank you, I fixed it. - I'm working on a scoreboard now, but I'm going to make a new post on that.
    Appreciate it!
     
Thread Status:
Not open for further replies.

Share This Page