Tutorial Using MySQL In your Plugins!

Discussion in 'Resources' started by -_Husky_-, Mar 1, 2013.

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

    hawkfalcon

    This would be great, but shading in a connection pool increases your plugins size by a lot.
     
  2. Offline

    frogawesome

    I am very new to using MySQL inside a plugin and i need it to add something to a plugin i am developing atm. this is my code atm: (i don't know what the java code tags are so sorry about that...)
    i'm making a token or coin system.
    Code:
    package me.frogawesome.DvZPoints;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import org.bukkit.ChatColor;
    import org.bukkit.command.Command;
    import org.bukkit.command.CommandSender;
    import org.bukkit.entity.Player;
    import org.bukkit.plugin.java.JavaPlugin;
     
    public class DvZPointsMain extends JavaPlugin {
    public static DvZPointsMain plugin;
    MySQL sql = new MySQL(this, "localhost", "3306", "mcd_76", "mcd_76", "3d4bb4102f");
    int tokens = 0;
     
    public void addToken(int Tokens, String name){
      if(sql.checkConnection() == true){
      try {
        Statement s = sql.openConnection().createStatement();
        ResultSet res = s.executeQuery("SELECT * FROM tokens WHERE PlayerName = '" + name + "';");
                res.next();
                if(res.getString("PlayerName") == null) {
                  Tokens = 0;
                  } else {
                  Tokens = res.getInt("coins")+Tokens;
                  }
                s.executeUpdate("INSERT INTO tokens (`PlayerName`, `coins`) VALUES ('" + name + "','" + Tokens + "');");
                System.out.println("Inserted info");
     
     
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
      }else {
      System.out.println("No Connection Available With Database!");
      }
    }
     
    public void removeToken(int Tokens, String name){
      if(sql.checkConnection() == true){
      try {
        Statement s = sql.openConnection().createStatement();
        ResultSet res = s.executeQuery("SELECT * FROM tokens WHERE PlayerName = '" + name + "';");
                res.next();
                if(res.getString("PlayerName") == null) {
                  tokens = 0;
                  } else {
                  tokens = res.getInt("coins")-Tokens;
                  }
                s.executeUpdate("INSERT INTO tokens (`PlayerName`, `coins`) VALUES ('" + name + "','" + Tokens + "');");
                System.out.println("Inserted info");
     
     
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
      }else {
      System.out.println("No Connection Available With Database!");
      }
     
    }
     
    public void checkTokens(Player player){
      if(sql.checkConnection() == true){
      try {
        Statement s = sql.openConnection().createStatement();
        ResultSet res = s.executeQuery("SELECT * FROM tokens WHERE PlayerName = '" + player.getName() + "';");
                res.next();
                if(res.getString("PlayerName") == null) {
                  player.sendMessage(ChatColor.RED + "You Do Not Have A Account Yet! The System Is Creating One For You please Wait....");
                  tokens = 0;
                    s.executeUpdate("INSERT INTO tokens (`PlayerName`, `coins`) VALUES ('" + player.getName() + "','" + tokens + "');");
                    System.out.println("Inserted info");
                  } else {
                  tokens = res.getInt("coins");
                  player.sendMessage(ChatColor.GREEN + "You Have: "+tokens+" Tokens!");
                  }
                System.out.println("Inserted info");
     
     
      } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
      }else {
      System.out.println("No Connection Available With Database!");
      }
    }
     
     
    @Override
    public void onEnable() {
      sql.openConnection();
          System.out.println("Enabled");
    }
     
    @Override
    public void onDisable() {
      // TODO Auto-generated method stub
      super.onDisable();
    }
     
        public boolean onCommand(CommandSender sender, Command cmd, String commandLabel, String[] args){
            if(cmd.getName().equalsIgnoreCase("frogcoins")){
                Player player = (Player) sender;
                if(sql.checkConnection() == true){
          try {
            Statement s = sql.openConnection().createStatement();
                    s.executeUpdate("CREATE TABLE IF NOT EXISTS `tokens` (mcd_76 VARCHAR(20), `coins` TEXT(100), `PlayerName` TEXT(100), tokens TEXT(100))");
                      System.out.println("Database Table Has Been Created");
           --> line 133 ResultSet res = s.executeQuery("SELECT * FROM `tokens` WHERE `PlayerName` = " + player.getName());
                              if(res.getString("PlayerName") == null) {
                              tokens = 0;
                              s.executeUpdate("INSERT INTO tokens (`PlayerName`, `coins`) VALUES (" + player.getName() + "," + tokens + ");");
                              System.out.println("Inserted info");
                              }else {
                player.sendMessage(ChatColor.GREEN +""+ res.getInt("coins"));
                              }
            PreparedStatement stmt = sql.openConnection().prepareStatement("SELECT * FROM `tokens` WHERE `PlayerName` = ? AND `coins` = ?");
            stmt.setString(1, player.getName());
            stmt.executeUpdate();
                              res.next();
                              PreparedStatement thing = sql.openConnection().prepareStatement("INSERT INTO `tokens` (`PlayerName`, `coins`) VALUES (?, ?)", Statement.RETURN_GENERATED_KEYS);
                                    thing.setString(1, player.getName());
                                    thing.setInt(2, res.getInt("SELECT `coins` FROM `tokens` WHERE `PlayerName` = ?"));
                                    thing.executeUpdate();
                              System.out.println("Inserted info");
     
     
          } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
          }
          player.sendMessage(ChatColor.GREEN + "IT WORKS!!!!!");
                }else {
                System.out.println("No Connection Available!");
                }
            }else if(commandLabel.equalsIgnoreCase("tokens")){
            if(sender instanceof Player){
              Player player = (Player) sender;
            checkTokens(player);
            }
            }
                return false;
            }
        }
    
    it is mainly about the command "frogcoins" every time i use something it says
    Code:
    2013-08-30 12:09:05 [SEVERE] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'frogawesome' in 'where clause'[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at java.lang.reflect.Constructor.newInstance(Unknown Source)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at com.mysql.jdbc.Util.handleNewInstance(Util.java:407)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at com.mysql.jdbc.Util.getInstance(Util.java:382)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2570)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1474)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at me.frogawesome.DvZPoints.DvZPointsMain.onCommand(DvZPointsMain.java:133)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at org.bukkit.command.PluginCommand.execute(PluginCommand.java:44)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at org.bukkit.command.SimpleCommandMap.dispatch(SimpleCommandMap.java:189)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at org.bukkit.craftbukkit.v1_6_R2.CraftServer.dispatchCommand(CraftServer.java:523)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.PlayerConnection.handleCommand(PlayerConnection.java:964)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.PlayerConnection.chat(PlayerConnection.java:882)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.PlayerConnection.a(PlayerConnection.java:839)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.Packet3Chat.handle(SourceFile:49)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.NetworkManager.b(NetworkManager.java:296)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.PlayerConnection.e(PlayerConnection.java:118)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.ServerConnection.b(SourceFile:37)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.DedicatedServerConnection.b(SourceFile:30)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.MinecraftServer.t(MinecraftServer.java:590)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.DedicatedServer.t(DedicatedServer.java:226)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.MinecraftServer.s(MinecraftServer.java:486)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.MinecraftServer.run(MinecraftServer.java:419)[/SIZE][/SIZE][/SIZE]
    [SIZE=2][SIZE=2][SIZE=2]2013-08-30 12:09:05 [SEVERE]  at net.minecraft.server.v1_6_R2.ThreadServerApplication.run(SourceFile:582)
    [/SIZE][/CODE][/SIZE][/CODE][/SIZE][/CODE][/SIZE]
     
  3. Offline

    EdenCampo

    nvm, fixed
     
  4. Offline

    Mang0eZPvP

    How can i check if a row exists or not? my code so far

    Code:java
    1. try {
    2. if (mc.getString("permissions", p.getName(), "permissions", "stomper") == "stomper") {
    3. p.sendMessage("Stomper Kit Availible!");
    4. PermissionAttachment attachment = p.addAttachment(EnableMain.getInstance());
    5. attachment.setPermission("stomper", true);
    6. } else {
    7. p.sendMessage("Stomper Kit Not Availible!");
    8. }
    9. } catch (Exception e) {
    10. e.printStackTrace();
    11. }


    Code:java
    1. public String getString(String item, String table, String where, String whereClause) throws Exception {
    2. Statement s = EnableMain.getInstance().MySQL.open().createStatement();
    3. ResultSet rs = s.executeQuery("SELECT " + item + " FROM " + table + " WHERE " + where + "=('" + whereClause + "');");
    4. rs.next();
    5. return rs.getString(1);
    6. }


    Thank You For Your help :)



    EDIT: mc is just so i can access the getString() easier

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Dec 20, 2015
  5. Offline

    -_Husky_-

    Updated tutorial, now using tips48's awesome overhaul. Thanks again tips48! :)
     
    hawkfalcon likes this.
  6. Offline

    Mang0eZPvP

    Pls help me???

    See it works if there is a row called stomper but if not... Lots of errors basicly saying no row found. I wan away so it would only do it if the row ixisted

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Dec 20, 2015
  7. Offline

    -_Husky_-

    I'll respond when I get on my computer in the morning, Mang0eZPvP
     
  8. Offline

    Mang0eZPvP

    Yay. Wit time is it for you???
     
  9. Offline

    -_Husky_-

    10:35PM (GMT + 10)

    Mang0eZPvP here's some code my friend uses..

    Code:java
    1. public boolean checkTable(String table) {
    2. try {
    3. Statement statement = connection.createStatement();
    4.  
    5. ResultSet result = statement.executeQuery("SELECT * FROM " + table);
    6.  
    7. if (result == null)
    8. return false;
    9. if (result != null)
    10. return true;
    11. } catch (SQLException e) {
    12. if (e.getMessage().contains("exist")) {
    13. return false;
    14. } else {
    15. this.writeError("Error in SQL query: " + e.getMessage(), false);
    16. }
    17. }
    18. }
     
  10. Offline

    CeramicTitan Retired Staff

    -_Husky_-

    Maybe you could explain to me statements, or maybe even make a tutorial.
     
  11. Offline

    -_Husky_-

  12. Offline

    CeramicTitan Retired Staff

  13. Offline

    -_Husky_-

  14. Offline

    CeramicTitan Retired Staff

  15. Offline

    Mang0eZPvP

    IT will always return true for some reason? here is what i have changed.

    Code:java
    1. @SuppressWarnings("unused")
    2. public boolean exists(String table, String item, String i) throws SQLException {
    3. Statement statement = EnableMain.getInstance().MySQL.open().createStatement();
    4. ResultSet result = statement.executeQuery("SELECT " + item + " FROM " + table + " WHERE " + item + " = '" + i + "';");
    5. if (result == null)
    6. return false;
    7. if (result != null)
    8. return true;
    9.  
    10. return false;
    11. }
     
  16. Offline

    CeramicTitan Retired Staff

    Change this: ResultSet result = statement.executeQuery("SELECT " + item + " FROM " + table + " WHERE " + item + " = '" + i + "';");
    To this; ResultSet result = statement.executeQuery("SELECT * FROM " + table + " WHERE " + item + " = '" + i + "';");
     
  17. Offline

    Mang0eZPvP

    still no work
     
  18. Offline

    Mang0eZPvP

    Any help will be nice. All i need is a way see if a row exist and if not dont spill a missive error in the cobsol
     
  19. Offline

    sbl03

    Hi there, thank you very much for this guide. I've been encountering a time out error which I've solved by opening a connection every time I query or update (similar to your querySQL and updateSQL functions). Is this the "best practice" for using MySQL with Bukkit?
    Originally, I only had the Connection variable as a field which I would initialize once when the plugin was loaded. This is a register plugin, so the period between commands may be very long. After some time, I would receive this error.
    Code:
    The last packet sent successfully to the server was XXXXX seconds ago, which is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
    I've tried using the suggested autoReconnect=true to no avail.
    Thanks!
     
  20. Offline

    Mang0eZPvP

    setup a auto restart on the server. as that is best method. else u will be leaving ur mysql server vulnable.
     
  21. Offline

    -_Husky_-

    Open and close connections as you need them, best to not leave them open if you're not using them. :)
     
  22. Offline

    bf2sniper2

    Great! I am working on a cool new mini-game, this simplifies things a good bit.
     
  23. Offline

    GaaTavares

    Why the MySQL causes lag? i tried to do it on a TaskAsynchronously for stop the lag but i cant return values.. Can anyone help me
     
  24. Offline

    Mang0eZPvP

    were is the help?
     
  25. Offline

    Kyle FYI

    Just wondering could you use this class for SQLite?
     
  26. Offline

    1Achmed1

    Code:java
    1. public void onDisable() {
    2. c = closeConnection();
    3. }
    4.  

    Doesn't work for me:oops:
     
  27. Offline

    1Achmed1

    Is there a class called "SQLite"? Yes. So is SQLite available with this? Yes. :)
     
  28. Offline

    Mang0eZPvP

    -_Husky_- said:
    10:35PM (GMT + 10)

    @Mang0eZPvP here's some code my friend uses..

    Code:java
    1. public boolean checkTable(String table) {
    2. try {
    3. Statement statement = connection.createStatement();
    4. ResultSet result = statement.executeQuery("SELECT * FROM " + table);
    5. if (result == null)
    6. return false;
    7. if (result != null)
    8. return true;
    9. } catch (SQLException e) {
    10. if (e.getMessage().contains("exist")) {
    11. return false;
    12. } else {
    13. this.writeError("Error in SQL query: " + e.getMessage(), false);
    14. }
    15. }
    16. }
    IT will always return true for some reason? here is what i have changed.

    Code:java
    1. @SuppressWarnings("unused")
    2. public boolean exists(String table, String item, String i) throws SQLException {
    3. Statement statement = EnableMain.getInstance().MySQL.open().createStatement();
    4. ResultSet result = statement.executeQuery("SELECT " + item + " FROM " + table + " WHERE " + item + " = '" + i + "';");
    5. if (result == null)
    6. return false;
    7. if (result != null)
    8. return true;
    9. return false;
    10. }

     
  29. Offline

    -_Husky_-

    Mang0eZPvP

    If you're using SQLite I can't help, tips48 -may- be able to, though.
     
  30. Offline

    Mang0eZPvP

    no it is mysql...
     
Thread Status:
Not open for further replies.

Share This Page