Hey, I've been having some trouble with MySQL and databases. I've got a command that adds gold (it's basically a master plugin) to a player, with a command of /add <user> <amount>. I did it to myself, and after fixing some bugs, ended up with an error that I can't seem to solve. Error: Code: [11:07:34] [Server thread/INFO]: Xp10d3 issued server command: /add Xp10d3 10 [11:07:34] [Server thread/WARN]: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SET GOLD='9eaf2e85-9784-48b9-bdb5-4593de0db4d0' WHERE UUID='Xp10d3'' at line 1 [11:07:34] [Server thread/WARN]: at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) [11:07:34] [Server thread/WARN]: at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) [11:07:34] [Server thread/WARN]: at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) [11:07:34] [Server thread/WARN]: at java.lang.reflect.Constructor.newInstance(Unknown Source) [11:07:34] [Server thread/WARN]: at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) [11:07:34] [Server thread/WARN]: at com.mysql.jdbc.Util.getInstance(Util.java:408) [11:07:34] [Server thread/WARN]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944) [11:07:34] [Server thread/WARN]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3933) [11:07:34] [Server thread/WARN]: at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3869) [11:07:34] [Server thread/WARN]: at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524) [11:07:34] [Server thread/WARN]: at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2675) [11:07:34] [Server thread/WARN]: at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2465) [11:07:34] [Server thread/WARN]: at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1912) [11:07:34] [Server thread/WARN]: at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2020) [11:07:34] [Server thread/WARN]: at corelia.koc.main.Commands.onCommand(Commands.java:67) [11:07:34] [Server thread/WARN]: at org.bukkit.command.PluginCommand.execute(PluginCommand.java:45) [11:07:34] [Server thread/WARN]: at org.bukkit.command.SimpleCommandMap.dispatch(SimpleCommandMap.java:149) [11:07:34] [Server thread/WARN]: at org.bukkit.craftbukkit.v1_15_R1.CraftServer.dispatchCommand(CraftServer.java:711) [11:07:34] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.PlayerConnection.handleCommand(PlayerConnection.java:1657) [11:07:34] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.PlayerConnection.a(PlayerConnection.java:1497) [11:07:34] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.PacketPlayInChat.a(PacketPlayInChat.java:47) [11:07:34] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.PacketPlayInChat.a(PacketPlayInChat.java:1) [11:07:34] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.PlayerConnectionUtils.lambda$0(PlayerConnectionUtils.java:19) [11:07:34] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.TickTask.run(SourceFile:18) [11:07:34] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.IAsyncTaskHandler.executeTask(SourceFile:144) [11:07:34] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.IAsyncTaskHandlerReentrant.executeTask(SourceFile:23) [11:07:34] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.IAsyncTaskHandler.executeNext(SourceFile:118) [11:07:34] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.MinecraftServer.aZ(MinecraftServer.java:917) [11:07:34] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.MinecraftServer.executeNext(MinecraftServer.java:910) [11:07:34] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.IAsyncTaskHandler.awaitTasks(SourceFile:127) [11:07:34] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.MinecraftServer.sleepForTick(MinecraftServer.java:894) [11:07:34] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.MinecraftServer.run(MinecraftServer.java:827) [11:07:34] [Server thread/WARN]: at java.lang.Thread.run(Unknown Source) I don't know much about MySQL and since I'm probably not going to use it in the future, I don't really have the time to learn it. I've seen a few tutorials and researched a bit on how to use it, but the most confusing part for me is this line: Code: PreparedStatement statement1 = core.getConnection().prepareStatement("SELECT " + core.table + " SET GOLD=? WHERE UUID=?"); Based on what I know, I see that it selects a line using plugin.table (which gets the table) and then sets the gold value and checks the UUID of the player. But what I don't get is why I get the error of a MySQL syntax. I also am having trouble updating the value of an already inserted table line, because I get an error when changing SELECT to UPDATE. I don't want to insert a table line, rather I want to UPDATE the value. Below is my Commands class, where the problem originates. My Core/Main class is fine and so is my MySQL class. It's just the Commands class that I am having trouble with. Thanks for the help! -Xp10d3 Commands.java: Code: package corelia.koc.main; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.HashMap; import java.util.UUID; import org.bukkit.Bukkit; import org.bukkit.ChatColor; import org.bukkit.command.Command; import org.bukkit.command.CommandExecutor; import org.bukkit.command.CommandSender; import org.bukkit.entity.Player; public class Commands implements CommandExecutor { public Commands(Core core) { this.core = core; Bukkit.getPluginCommand("add").setExecutor(this); } final HashMap<UUID, Integer> coins = new HashMap<>(); private Core core; // For entering data use: https://bukkit.org/threads/mysql-update-column.243601/ @Override public boolean onCommand(CommandSender sender, Command cmd, String lable, String[] args) { Player player = (Player) sender; if (!(sender instanceof Player)) { sender.sendMessage("You must be a player to access this command!"); } if (cmd.getName().equalsIgnoreCase("add")) { if (args.length == 2) { Player target = Bukkit.getServer().getPlayer(args[0]); if (target == null) { player.sendMessage(ChatColor.RED + "That player isn't online!"); } else { if (isInt(args[1]) == true) { /* try { PreparedStatement statement = core.getConnection().prepareStatement("SELECT * FROM " + core.table + " WHERE UUID=?"); statement.setString(1, uuid.toString()); ResultSet results = statement.executeQuery(); results.next(); int argumentTwo = Integer.parseInt(args[1]); //coins.put(target.getUniqueId(), results.getInt("GOLD") + argumentTwo); results.next(); player.sendMessage("Gave " + target + argumentTwo +" gold."); target.sendMessage(ChatColor.GREEN + "You have recieved " + argumentTwo + " gold."); } catch (SQLException e) { e.printStackTrace(); } */ // Note: Set ResultSet to 0 NOT 1. That is likely the error. int argumentTwo = Integer.parseInt(args[1]); try { PreparedStatement statement1 = core.getConnection() .prepareStatement("SELECT " + core.table + " SET GOLD=? WHERE UUID=?"); statement1.setString(1, target.getUniqueId().toString()); statement1.setString(2, target.getName()); ResultSet results = statement1.executeQuery(); results.next(); statement1.setInt(3, results.getInt("GOLD") + argumentTwo); target.sendMessage(ChatColor.GREEN + "You have recieved " + argumentTwo + " gold!"); player.sendMessage("Gave " + target + argumentTwo + " gold."); statement1.executeUpdate(); Bukkit.getLogger().info(target + " has recieved " + argumentTwo + " gold."); } catch (SQLException e) { e.printStackTrace(); } } else { player.sendMessage(ChatColor.RED + "Please use an integer. Example: /pay Xp10d3 50"); } } } else { player.sendMessage(ChatColor.RED + "Too few arguments! Correct usage: /pay <player> <amount>"); } } return false; } public boolean isInt(String s) { try { Integer.parseInt(s); return true; } catch (NumberFormatException e) { return false; } } }
Alright. Thanks for the quick reply. I've tried doing "UPDATE" instead of "SELECT" but I get this error: Code: [11:46:46] [Server thread/WARN]: java.sql.SQLException: Can not issue data manipulation statements with executeQuery(). [11:46:46] [Server thread/WARN]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) [11:46:46] [Server thread/WARN]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898) [11:46:46] [Server thread/WARN]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887) [11:46:46] [Server thread/WARN]: at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861) [11:46:46] [Server thread/WARN]: at com.mysql.jdbc.StatementImpl.checkForDml(StatementImpl.java:453) [11:46:46] [Server thread/WARN]: at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1977) [11:46:46] [Server thread/WARN]: at corelia.koc.main.Commands.onCommand(Commands.java:67) [11:46:46] [Server thread/WARN]: at org.bukkit.command.PluginCommand.execute(PluginCommand.java:45) [11:46:46] [Server thread/WARN]: at org.bukkit.command.SimpleCommandMap.dispatch(SimpleCommandMap.java:149) [11:46:46] [Server thread/WARN]: at org.bukkit.craftbukkit.v1_15_R1.CraftServer.dispatchCommand(CraftServer.java:711) [11:46:46] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.PlayerConnection.handleCommand(PlayerConnection.java:1657) [11:46:46] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.PlayerConnection.a(PlayerConnection.java:1497) [11:46:46] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.PacketPlayInChat.a(PacketPlayInChat.java:47) [11:46:46] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.PacketPlayInChat.a(PacketPlayInChat.java:1) [11:46:46] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.PlayerConnectionUtils.lambda$0(PlayerConnectionUtils.java:19) [11:46:46] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.TickTask.run(SourceFile:18) [11:46:46] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.IAsyncTaskHandler.executeTask(SourceFile:144) [11:46:46] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.IAsyncTaskHandlerReentrant.executeTask(SourceFile:23) [11:46:46] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.IAsyncTaskHandler.executeNext(SourceFile:118) [11:46:46] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.MinecraftServer.aZ(MinecraftServer.java:917) [11:46:46] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.MinecraftServer.executeNext(MinecraftServer.java:910) [11:46:46] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.IAsyncTaskHandler.awaitTasks(SourceFile:127) [11:46:46] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.MinecraftServer.sleepForTick(MinecraftServer.java:894) [11:46:46] [Server thread/WARN]: at net.minecraft.server.v1_15_R1.MinecraftServer.run(MinecraftServer.java:827) [11:46:46] [Server thread/WARN]: at java.lang.Thread.run(Unknown Source) I change it back to "SELECT" and then I get the error in my first post.
Yeah its executeUpdate not executeQuery. But then I get an error saying to change: Code: ResultSet results = statement1.executeUpdate(); to: Code: int reuslts = statement1.executeUpdate(); which then causes a bunch more errors with results.next(); and results.getInt("GOLD") saying that it can't invoke getInt(String) on the primitive type int.
Not to sound like a dick, but your life would be a lot easier if you went and watched/read a tutorial on using MySQL in Java. It would cover such basic things as are tripping you up. Also I assume somewhere you are inserting "default" records that set a zero balance for new players, right? Cuz you know, this will fail if the user is not already in the DB. I'd suggest reading about REPLACE INTO so you can avoid unnecessary trips to the SQL server.
Alright. Thanks, Tim and Strahan. Also to answer your question, I insert the player into the database when they first join the server if they aren't already in it. Are there any forums on REPLACE INTO? I can't seem to find any atm.
REPLACE INTO means if the index you are updating exists, update it else insert a new record. This saves you from having to create new records when a new player joins, as if they have no data then the update will set the initial data. So like if you have this table: Code: table playerassets uuid, gold fe5303f8071642cbade30a6023be87d9, 12 If I were to do REPLACE INTO playerassets (uuid, gold) VALUES ('fe5303f8071642cbade30a6023be87d9', 25) my table is now: Code: uuid, gold fe5303f8071642cbade30a6023be87d9, 25 If I do REPLACE INTO playerassets (uuid, gold) VALUES ('069a79f444e94726a5befca90e38aaf5', 1000) my table is now: Code: uuid, gold fe5303f8071642cbade30a6023be87d9, 25 069a79f444e94726a5befca90e38aaf5, 1000 No worries about having to create an entry for 069a79f444e94726a5befca90e38aaf5 prior to an update. PS: on second thought, I had totally forgotton about the insert/on duplicate option. I think that would be slightly more efficient as it will hunt down the record with the duplicate index and replace it, whereas replace into will (IIRC) remove the duplicate then insert fresh. Reduces one operation doing the insert/duplicate update and is likely safer if you are using foreign key constraints.
Thank you so much that was really helpful Finally got one of my problems working! Now I got another problem with my /pay command. Sorry but apparently when I'm trying to sense a results.getInt variable (in this case int coinsAmount = results.getInt("GOLD")) it thinks I have 0 gold when I really have, lets say, a 100 gold. I tested this and it worked only once, yet afterwards, it no longer worked. I don't know why this is (this is another plugin by the way with nearly the same code. I'm making two; a master (which is working now!) and a regular plugin). Commands.java: Code: package xp10d3.corelia.main; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.UUID; import org.bukkit.Bukkit; import org.bukkit.ChatColor; import org.bukkit.Material; import org.bukkit.command.Command; import org.bukkit.command.CommandExecutor; import org.bukkit.command.CommandSender; import org.bukkit.entity.Player; import org.bukkit.inventory.Inventory; import org.bukkit.inventory.ItemStack; import org.bukkit.inventory.meta.ItemMeta; public class Commands implements CommandExecutor { public Commands(Core core) { this.core = core; Bukkit.getPluginCommand("eshop").setExecutor(this); Bukkit.getPluginCommand("pay").setExecutor(this); Bukkit.getPluginCommand("gold").setExecutor(this); } private Core core; public Inventory shopGUI(Player player, Core core) { // Gets the config/material values for items one through twenty four. Material itemZero = Material.getMaterial(core.config.getString("shop.to.item.itemZero")); Material itemOne = Material.getMaterial(core.config.getString("shop.to.item.itemOne")); Material itemTwo = Material.getMaterial(core.config.getString("shop.to.item.itemTwo")); Material itemThree = Material.getMaterial(core.config.getString("shop.to.item.itemThree")); Material itemFour = Material.getMaterial(core.config.getString("shop.to.item.itemFour")); Material itemFive = Material.getMaterial(core.config.getString("shop.to.item.itemFive")); Material itemSix = Material.getMaterial(core.config.getString("shop.to.item.itemSix")); Material itemSeven = Material.getMaterial(core.config.getString("shop.to.item.itemSeven")); Material itemEight = Material.getMaterial(core.config.getString("shop.to.item.itemEight")); Material itemNine = Material.getMaterial(core.config.getString("shop.to.item.itemNine")); Material itemTen = Material.getMaterial(core.config.getString("shop.to.item.itemTen")); Material itemEleven = Material.getMaterial(core.config.getString("shop.to.item.itemEleven")); Material itemTwelve = Material.getMaterial(core.config.getString("shop.to.item.itemTwelve")); Material itemThirteen = Material.getMaterial(core.config.getString("shop.to.item.itemThirteen")); Material itemFourteen = Material.getMaterial(core.config.getString("shop.to.item.itemFourteen")); Material itemFifteen = Material.getMaterial(core.config.getString("shop.to.item.itemFifteen")); Material itemSixteen = Material.getMaterial(core.config.getString("shop.to.item.itemSixteen")); Material itemSeventeen = Material.getMaterial(core.config.getString("shop.to.item.itemSeventeen")); // Creates the inventory. final Inventory inventory = Bukkit.createInventory(null, 18, ChatColor.BLACK + "Shop"); // Gets the material in each GUI from 1-24. final ItemStack item0 = new ItemStack(itemZero); final ItemMeta item0Meta = item0.getItemMeta(); final ItemStack item1 = new ItemStack(itemOne); final ItemMeta item1Meta = item1.getItemMeta(); final ItemStack item2 = new ItemStack(itemTwo); final ItemMeta item2Meta = item2.getItemMeta(); final ItemStack item3 = new ItemStack(itemThree); final ItemMeta item3Meta = item3.getItemMeta(); final ItemStack item4 = new ItemStack(itemFour); final ItemMeta item4Meta = item4.getItemMeta(); final ItemStack item5 = new ItemStack(itemFive); final ItemMeta item5Meta = item5.getItemMeta(); final ItemStack item6 = new ItemStack(itemSix); final ItemMeta item6Meta = item1.getItemMeta(); final ItemStack item7 = new ItemStack(itemSeven); final ItemMeta item7Meta = item7.getItemMeta(); final ItemStack item8 = new ItemStack(itemEight); final ItemMeta item8Meta = item8.getItemMeta(); final ItemStack item9 = new ItemStack(itemNine); final ItemMeta item9Meta = item9.getItemMeta(); final ItemStack item10 = new ItemStack(itemTen); final ItemMeta item10Meta = item10.getItemMeta(); final ItemStack item11 = new ItemStack(itemEleven); final ItemMeta item11Meta = item11.getItemMeta(); final ItemStack item12 = new ItemStack(itemTwelve); final ItemMeta item12Meta = item12.getItemMeta(); final ItemStack item13 = new ItemStack(itemThirteen); final ItemMeta item13Meta = item13.getItemMeta(); final ItemStack item14 = new ItemStack(itemFourteen); final ItemMeta item14Meta = item14.getItemMeta(); final ItemStack item15 = new ItemStack(itemFifteen); final ItemMeta item15Meta = item15.getItemMeta(); final ItemStack item16 = new ItemStack(itemSixteen); final ItemMeta item16Meta = item16.getItemMeta(); final ItemStack item17 = new ItemStack(itemSeventeen); final ItemMeta item17Meta = item17.getItemMeta(); // Sets the display name for each item and sets the ItemMeta for it. item0Meta.setDisplayName(ChatColor.WHITE + "Item"); item0.setItemMeta(item0Meta); item1Meta.setDisplayName(ChatColor.WHITE + "Item"); item1.setItemMeta(item1Meta); item2Meta.setDisplayName(ChatColor.WHITE + "Item"); item3.setItemMeta(item3Meta); item4Meta.setDisplayName(ChatColor.WHITE + "Item"); item4.setItemMeta(item4Meta); item5Meta.setDisplayName(ChatColor.WHITE + "Item"); item5.setItemMeta(item5Meta); item6Meta.setDisplayName(ChatColor.WHITE + "Item"); item6.setItemMeta(item6Meta); item6Meta.setDisplayName(ChatColor.WHITE + "Item"); item7.setItemMeta(item7Meta); item8Meta.setDisplayName(ChatColor.WHITE + "Item"); item8.setItemMeta(item8Meta); item9Meta.setDisplayName(ChatColor.WHITE + "Item"); item9.setItemMeta(item9Meta); item10Meta.setDisplayName(ChatColor.WHITE + "Item"); item10.setItemMeta(item10Meta); item11Meta.setDisplayName(ChatColor.WHITE + "Item"); item11.setItemMeta(item11Meta); item12Meta.setDisplayName(ChatColor.WHITE + "Item"); item12.setItemMeta(item12Meta); item13Meta.setDisplayName(ChatColor.WHITE + "Item"); item13.setItemMeta(item13Meta); item14Meta.setDisplayName(ChatColor.WHITE + "Item"); item14.setItemMeta(item14Meta); item15Meta.setDisplayName(ChatColor.WHITE + "Item"); item15.setItemMeta(item15Meta); item16Meta.setDisplayName(ChatColor.WHITE + "Item"); item16.setItemMeta(item16Meta); item17Meta.setDisplayName(ChatColor.WHITE + "Item"); item17.setItemMeta(item17Meta); // Sets the item for each GUI slot. inventory.setItem(0, item0); inventory.setItem(1, item1); inventory.setItem(2, item2); inventory.setItem(3, item3); inventory.setItem(4, item4); inventory.setItem(5, item5); inventory.setItem(6, item6); inventory.setItem(7, item7); inventory.setItem(8, item8); inventory.setItem(9, item9); inventory.setItem(10, item10); inventory.setItem(11, item11); inventory.setItem(12, item12); inventory.setItem(13, item13); inventory.setItem(14, item14); inventory.setItem(15, item15); inventory.setItem(16, item16); inventory.setItem(17, item17); return inventory; } @Override public boolean onCommand(CommandSender sender, Command cmd, String lable, String[] args) { Player player = (Player) sender; UUID uuid = player.getUniqueId(); if (!(sender instanceof Player)) { sender.sendMessage("You must be a player to access this command!"); } if (cmd.getName().equalsIgnoreCase("eshop")) { if (args.length == 0) { player.openInventory(shopGUI(player, core)); return true; } else { player.sendMessage("Too many arguments! Correct usage: /eshop"); } } if (cmd.getName().equalsIgnoreCase("gold")) { if (args.length == 0) { try { PreparedStatement statement = core.getConnection().prepareStatement("SELECT * FROM " + core.table + " WHERE UUID=?"); statement.setString(1, uuid.toString()); ResultSet results = statement.executeQuery(); results.next(); player.sendMessage("You have " + results.getInt("GOLD") + " gold."); } catch (SQLException e) { e.printStackTrace(); } return true; } else { player.sendMessage("Too many arguments! Correct usage: /gold"); } } if (cmd.getName().equalsIgnoreCase("pay")) { if (args.length == 2) { Player target = Bukkit.getServer().getPlayer(args[0]); if (target == null) { player.sendMessage(ChatColor.RED + "That player isn't online!"); } else { if (isInt(args[1]) == true) { int argumentTwo = Integer.parseInt(args[1]); try { PreparedStatement statement = core.getConnection() .prepareStatement("SELECT * FROM " + core.table + " WHERE UUID=?"); statement.setString(1, uuid.toString()); ResultSet results = statement.executeQuery(); results.next(); int coinsAmount = (results.getInt("GOLD")); if (argumentTwo < 1) { player.sendMessage(ChatColor.RED + "You can't pay another person a negative amount!"); return false; } else if (coinsAmount < 1 || argumentTwo < coinsAmount) { player.sendMessage(ChatColor.RED + "You don't have enough gold!"); return false; } else { /* coins.put(target.getUniqueId(), results.getInt("GOLD") + argumentTwo); coins.put(player.getUniqueId(), results.getInt("GOLD") - argumentTwo); player.sendMessage("Gave " + target + argumentTwo + " gold."); target.sendMessage(ChatColor.GREEN + "You have recieved " + argumentTwo + " gold."); */ PreparedStatement statement1 = core.getConnection().prepareStatement("UPDATE " + core.table + " SET GOLD = GOLD + ? WHERE UUID=?"); statement1.setInt(1, argumentTwo); statement1.setString(2, target.getUniqueId().toString()); statement1.executeUpdate(); PreparedStatement statement2 = core.getConnection().prepareStatement("UPDATE " + core.table + " SET GOLD = GOLD + ? WHERE UUID=?"); statement2.setInt(1, -argumentTwo); statement2.setString(2, player.getUniqueId().toString()); statement2.executeUpdate(); player.sendMessage("Gave " + target.getName() + " " + argumentTwo + " gold."); target.sendMessage(ChatColor.GREEN + "You have recieved " + argumentTwo + " gold."); } } catch (SQLException e) { e.printStackTrace(); } } else { player.sendMessage(ChatColor.RED + "Please use an integer. Example: /pay Xp10d3 50"); } } } else { player.sendMessage(ChatColor.RED + "Too few arguments! Correct usage: /pay <player> <amount>"); } } return false; } public boolean isInt(String s) { try { Integer.parseInt(s); return true; } catch (NumberFormatException e) { return false; } } public void getCoins(UUID uuid) { try { PreparedStatement statement = core.getConnection() .prepareStatement("SELECT * FROM " + core.table + " WHERE UUID=?"); statement.setString(1, uuid.toString()); ResultSet results = statement.executeQuery(); results.next(); //System.out.print("Player has " + results.getInt("GOLD") + " gold."); } catch (SQLException e) { e.printStackTrace(); } } }
Hmm. Add this in your pay command so it looks thus: Code: if (cmd.getName().equalsIgnoreCase("pay")) { if (args.length == 2) { Player target = Bukkit.getServer().getPlayer(args[0]); if (target == null) { player.sendMessage(ChatColor.RED + "That player isn't online!"); } else { if (isInt(args[1]) == true) { int argumentTwo = Integer.parseInt(args[1]); try { PreparedStatement statement = core.getConnection() .prepareStatement("SELECT * FROM " + core.table + " WHERE UUID=?"); statement.setString(1, uuid.toString()); core.getLogger().info("** SQL Query: SELECT * FROM " + core.table + " WHERE UUID='" + uuid.toString() + "';"); The last line is the new addition. I assume "core" is your class instance that extends JavaPlugin. Then run pay, go to the console and copy the SQL statement then paste it into whatever SQL management program you use and verify you are getting the data you expect. Also you are checking the variable type for sender after casting it. That is backwards. Plus your check has it throwing an error if they are the console, but there is no break in the flow of the function so it would continue on and fail as soon as you tried to access any player methods, assuming some glitch in the matrix magically allowed the creation of the player variable to succeed heh. I'd also recommend for readabilities sake that you partition your commands into their own classes. Not necessary, but it's "good form" as they say.
Okay. Thank you! I'll test that when I get back home from school. And yes, core is the instance that extends JavaPlugin. Refers to my main class. EDIT: Sorry, one more question. By pasting into my SQL management program I don't have one, so should I just compare it to my database? Like the table and see if it matches up. Or should I just compare it to the SQL of the database?
Well, whatever tool you are using to look at the database contents is the SQL management program. Like phpMyAdmin, MySQL Workbench, SQLyog, etc. Whatever interface you have that lets you query the DB.
Alright. I’m using PHPMyAdmin so I guess I’ll test it out and see how it compares. Thanks again EDIT: Alright, I tested it and it seems to work now. For some reason, it thinks I don't have enough gold. No errors, but after setting my gold to 0 then later adding 100 and paying myself 10, it doesn't work anymore. I added some permissions but even OPing myself doesn't work. Code: package xp10d3.corelia.main; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.UUID; import org.bukkit.Bukkit; import org.bukkit.ChatColor; import org.bukkit.Material; import org.bukkit.command.Command; import org.bukkit.command.CommandExecutor; import org.bukkit.command.CommandSender; import org.bukkit.configuration.file.FileConfiguration; import org.bukkit.entity.Player; import org.bukkit.inventory.Inventory; import org.bukkit.inventory.ItemStack; import org.bukkit.inventory.meta.ItemMeta; public class Commands implements CommandExecutor { public Commands(Core core) { this.core = core; Bukkit.getPluginCommand("eshop").setExecutor(this); Bukkit.getPluginCommand("epay").setExecutor(this); Bukkit.getPluginCommand("gold").setExecutor(this); } private Core core; public Inventory shopGUI() { final FileConfiguration config = core.getConfig(); // Creates the inventory. final Inventory inventory = Bukkit.createInventory(null, 18, ChatColor.BLACK + "Shop"); int i = 0; for(String key : config.getConfigurationSection("shop.items").getKeys(false)) { Material material = Material.getMaterial(key.toUpperCase()); String name = config.getString("shop.items." + key + ".name"); if (name == null) { Bukkit.getConsoleSender().sendMessage(ChatColor.RED + "Name is null! Please fill in the appropriate name."); return inventory; } else if (material == null) { Bukkit.getConsoleSender().sendMessage(ChatColor.RED + "Material is null! Please fill in the appropriate material."); return inventory; } System.out.println(key.toUpperCase()); final ItemStack item = new ItemStack(material); final ItemMeta itemMeta = item.getItemMeta(); itemMeta.setUnbreakable(true); itemMeta.setDisplayName(ChatColor.WHITE + name); item.setItemMeta(itemMeta); inventory.setItem(i, item); i++; } return inventory; } @Override public boolean onCommand(CommandSender sender, Command cmd, String lable, String[] args) { if (!(sender instanceof Player)) { sender.sendMessage("You must be a player to access this command!"); } Player player = (Player) sender; UUID uuid = player.getUniqueId(); if (cmd.getName().equalsIgnoreCase("eshop") && player.hasPermission("shop.eshop")) { if (args.length == 0) { player.openInventory(shopGUI()); return true; } else { player.sendMessage("Too many arguments! Correct usage: /eshop"); } } else if (!player.hasPermission("shop.eshop")) { player.sendMessage(ChatColor.RED + "You don't have permission to execute this command!"); return false; } if (cmd.getName().equalsIgnoreCase("gold")) { if (args.length == 0) { try { PreparedStatement statement = core.getConnection().prepareStatement("SELECT * FROM " + core.table + " WHERE UUID=?"); statement.setString(1, uuid.toString()); ResultSet results = statement.executeQuery(); results.next(); player.sendMessage("You have " + results.getInt("GOLD") + " gold."); } catch (SQLException e) { e.printStackTrace(); } return true; } else { player.sendMessage("Too many arguments! Correct usage: /gold"); } } if (cmd.getName().equalsIgnoreCase("epay") && player.hasPermission("shop.epay")) { if (args.length == 2) { Player target = Bukkit.getServer().getPlayer(args[0]); if (target == null) { player.sendMessage(ChatColor.RED + "That player isn't online!"); } else { if (isInt(args[1]) == true) { int argumentTwo = Integer.parseInt(args[1]); try { PreparedStatement statement = core.getConnection() .prepareStatement("SELECT * FROM " + core.table + " WHERE UUID=?"); statement.setString(1, uuid.toString()); ResultSet results = statement.executeQuery(); results.next(); int coinsAmount = (results.getInt("GOLD")); if (argumentTwo < 1) { player.sendMessage(ChatColor.RED + "You can't pay another person a negative amount!"); return false; } else if (coinsAmount < 1 || argumentTwo < coinsAmount) { player.sendMessage(ChatColor.RED + "You don't have enough gold!"); return false; } else { PreparedStatement statement1 = core.getConnection().prepareStatement("UPDATE " + core.table + " SET GOLD = GOLD + ? WHERE UUID=?"); statement1.setInt(1, argumentTwo); statement1.setString(2, target.getUniqueId().toString()); statement1.executeUpdate(); PreparedStatement statement2 = core.getConnection().prepareStatement("UPDATE " + core.table + " SET GOLD = GOLD + ? WHERE UUID=?"); statement2.setInt(1, -argumentTwo); statement2.setString(2, player.getUniqueId().toString()); statement2.executeUpdate(); player.sendMessage("Gave " + target.getName() + " " + argumentTwo + " gold."); target.sendMessage(ChatColor.GREEN + "You have recieved " + argumentTwo + " gold."); } } catch (SQLException e) { e.printStackTrace(); } } else { player.sendMessage(ChatColor.RED + "Please use an integer. Example: /epay Xp10d3 50"); } } } else { player.sendMessage(ChatColor.RED + "Too few arguments! Correct usage: /epay <player> <amount>"); } } else if (!player.hasPermission("shop.epay")) { player.sendMessage(ChatColor.RED + "You don't have permission to execute this command!"); return false; } return false; } public boolean isInt(String s) { try { Integer.parseInt(s); return true; } catch (NumberFormatException e) { return false; } } public void getCoins(UUID uuid) { try { PreparedStatement statement = core.getConnection() .prepareStatement("SELECT * FROM " + core.table + " WHERE UUID=?"); statement.setString(1, uuid.toString()); ResultSet results = statement.executeQuery(); results.next(); } catch (SQLException e) { e.printStackTrace(); } } }
@Xp10d3 Unless you will be syncing data between multiple minecraft servers or want to change SQL data remotely without being on the server, I would advise looking into using SQLite instead of MySQL. Here's why (Move your mouse to reveal the content) Here's why (open) Here's why (close) MySQL is usually used with a network connection. The problem with that is every time you make a SQL statement to pass on, you have to wait for the server to send the data back to you over internet connection. If there's even so much as a 50 millisecond delay, doing SQL statements in the main server thread could cause minecraft's logic to slow down and entire ticks to be skipped. You would want to be aware of this and likely run the server request in an asynchronous runnable, separate from the main thread, and then once the data is gotten schedule a synchronous runnable to do the effects ingame. Also if you don't have a solid understanding of concurrency, you might run into some bugs and glitches. tl;dr If you don't understand some of that or don't want to deal with async, try SQLite. It uses a single file on your server and needs no ip/port configuration. There will be a delay in certain situations (such as having a big request to handle), but no network delay like with SQLite. Also prettymuch all Minecraft servers have SQLite already set up and installed; you just have to get it from the database driver like with MySQL.
Ok. True. I have fixed my problem but am trying to sync data from multiple severs. Ex. player is on two servers and has data from both, I want to sync both of those together. But I see the benefit of SQLite. Thanks for the tip!
