Tutorial Using MySQL In your Plugins!

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

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

    masj2


    Code:java
    1. package me.masj2.Basics;
    2. import code.husky.*;
    3.  
    4. import java.sql.Connection;
    5. import java.sql.ResultSet;
    6. import java.sql.SQLException;
    7. import java.sql.Statement;
    8. import java.util.logging.Logger;
    9.  
    10. import org.bukkit.ChatColor;
    11. import org.bukkit.Location;
    12. import org.bukkit.command.Command;
    13. import org.bukkit.command.CommandSender;
    14. import org.bukkit.entity.Player;
    15. import org.bukkit.plugin.PluginDescriptionFile;
    16. import org.bukkit.plugin.java.JavaPlugin;
    17.  
    18.  
    19. public class Basics extends JavaPlugin{
    20.  
    21. public final Logger logger = Logger.getLogger("Minecraft");
    22. public static Basics plugin;
    23. MySQL MySQL = new MySQL(plugin, "192.168.0.101", "3306", "minecraft","root","password");
    24. Connection c =null;
    25.  
    26. @Override
    27. public void onDisable(){
    28. PluginDescriptionFile pdfFile = this.getDescription();
    29. this.logger.info(pdfFile.getName() + " Has Been Disabled.");
    30. }
    31. @Override
    32. public void onEnable(){
    33. PluginDescriptionFile pdfFile = this.getDescription();
    34. c = MySQL.openConnection();
    35. this.logger.info(pdfFile.getName() + " Vesion " + pdfFile.getVersion() + " Has Been Enabled.");
    36. }
    37.  
    38. public boolean onCommand(CommandSender sender, Command cmd, String commandLable, String[] args){
    39. Player player = (Player) sender;
    40. if(commandLable.equalsIgnoreCase("sethome")){
    41. Location loc = player.getLocation();
    42. double x = loc.getX();
    43. double y = loc.getY();
    44. double z = loc.getZ();
    45. Statement statement;
    46. try {
    47. statement = c.createStatement();
    48. statement.executeQuery("INSERT INTO users (`ign`, `x`, `y`, `z`) VALUES ('" + player + "', '" + x + "', '" + y + "', '" + z + "');");
    49. player.sendMessage(ChatColor.GREEN + "Your home has been set to this place.");
    50. } catch (SQLException e) {
    51. // TODO Auto-generated catch block
    52. e.printStackTrace();
    53. }
    54. return false;
    55. }
    56. if(commandLable.equalsIgnoreCase("home")){
    57. Statement statement;
    58. try {
    59. statement = c.createStatement();
    60. ResultSet res = statement.executeQuery("SELECT * FROM users WHERE ign = '" + player + "';");
    61. res.next();
    62. if(res.getString("ign") == null) {
    63. player.sendMessage(ChatColor.RED + "You have not set any home yet. Use /sethome");
    64. } else {
    65. double x = res.getInt("x");
    66. double y = res.getInt("y");
    67. double z = res.getInt("z");
    68. Location loc = player.getLocation();
    69. loc.setX(x);
    70. loc.setY(y);
    71. loc.setZ(z);
    72. player.teleport(loc);
    73. }
    74. } catch (SQLException e) {
    75. // TODO Auto-generated catch block
    76. e.printStackTrace();
    77. }
    78. return false;
    79. }
    80. return false;
    81. }
    82. }
     
  2. Offline

    -_Husky_-

  3. Offline

    masj2

    -_Husky_-
    Thanks but its not working, i tried to remove c = MySQL.openConnection(); in onEnable() and then it successfully load, however none of the commands work. I also added .getName() after player as in case that was the problem but no success.
     
  4. Offline

    -_Husky_-

  5. Offline

    masj2

  6. Offline

    -_Husky_-

    Try now.
     
  7. Offline

    masj2

    Still get error while loading:
    java.lang.NullPointerException
    at code.husky.MySQL.openConnection<MySQL.java:59>
    at me.masj2.Basics.Basics.onEnable<Basics.java:35>
     
  8. Offline

    -_Husky_-

    masj2

    Hmm, this is odd, can I see the MySQL class you're using?
     
  9. Offline

    masj2


    Code:
    package code.husky;
     
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.util.logging.Level;
     
    import org.bukkit.plugin.Plugin;
     
    /**
    * Connects to and uses a MySQL database
    *
    * @author -_Husky_-
    * @author tips48
    */
    public class MySQL extends Database {
        private final String user;
        private final String database;
        private final String password;
        private final String port;
        private final String hostname;
     
        private Connection connection;
     
        /**
        * Creates a new MySQL instance
        *
        * @param plugin
        *            Plugin instance
        * @param hostname
        *            Name of the host
        * @param portnmbr
        *            Port number
        * @param database
        *            Database name
        * @param username
        *            Username
        * @param password
        *            Password
        */
        public MySQL(Plugin plugin, String hostname, String port, String database, String username, String password) {
            super(plugin);
            this.hostname = hostname;
            this.port = port;
            this.database = database;
            this.user = username;
            this.password = password;
            this.connection = null;
        }
     
        @Override
        public Connection openConnection() {
            try {
                Class.forName("com.mysql.JDBC.Driver");
                connection = DriverManager.getConnection("jdbc:mysql://" + this.hostname + ":" + this.port + "/" + this.database, this.user, this.password);
            } catch (SQLException e) {
                plugin.getLogger().log(Level.SEVERE, "Could not connect to MySQL server! because: " + e.getMessage());
            } catch (ClassNotFoundException e) {
                plugin.getLogger().log(Level.SEVERE, "JDBC Driver not found!");
            }
            return connection;
        }
     
        @Override
        public boolean checkConnection() {
            return connection != null;
        }
     
        @Override
        public Connection getConnection() {
            return connection;
        }
     
        @Override
        public void closeConnection() {
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    plugin.getLogger().log(Level.SEVERE, "Error closing the MySQL Connection!");
                    e.printStackTrace();
                }
            }
        }
    }
    
     
  10. Offline

    -_Husky_-

    masj2

    I've been taking the wrong approach to this error, I apologise.

    I'll see if I can try fix the code from my iPad.

    Will edit this post when done,

    EDIT -- Done, think I analysed my error. https://gist.github.com/Huskehhh/7955980

    Thanks, Husky.
     
  11. Offline

    masj2

    -_Husky_-
    Thanks its loading but i get a warning that jdbc driver not found and a crash on command
     
  12. Offline

    -_Husky_-

  13. Offline

    masj2

  14. Offline

    HungerCraftNL

    How can you get objects from the mysql database?

    Code:java
    1. Statement statement;
    2. try {
    3. statement = mysql.openConnection().createStatement();
    4. statement.executeQuery("INSERT INTO users (`ign`, `warns`) VALUES ('" + target.getName() + "', '" + 2 +"');");
    5. } catch (SQLException e) {
    6. e.printStackTrace();
    7. }
     
  15. Offline

    WinterTactic

    Husky's classes include functions to execute.. Besides, it'd be easier in my opinion to use a prepared statement:
    Code:java
    1.  
    2. try {
    3. PreparedStatement statement = conn.prepareStatement("INSERT INTO `users`(ign, warns) VALUES (?, ?);");
    4. statement.setString(1, target.getName());
    5. statement.setLong(2, 2);
    6. statement.executeUpdate();
    7. } catch (SQLException e) {
    8. e.printStackTrace();
    9. }

    If you're trying to retrieve results from your database, use:
    Code:java
    1.  
    2. try {
    3. ResultSet res = statement.executeQuery("SELECT * FROM `users` WHERE ign= '" + target.getName() + "';");
    4. while (res.next()) {
    5. // do stuff, probably add a counter or something
    6. }
    7. } catch (SQLException e) {
    8. e.printStackTrace();
    9. }
     
  16. Offline

    AstramG

    I'm trying to incorporate SQL as a back end to my server. I watched almost EVERY one of TheNewBostons SQL tutorials and not one mentioned the grave accent. When do I use it and what is it for? Also, regarding ResultSet.next(); is that for going the next row in the table?
     
  17. Offline

    gomeow

    it isn't really necessary, unless you have a table/column named 'select' or another keyword, where you would need to put `select` so it knows it's a name, not a keyword, however, many people choose to use it for all their table names.

    ResultSet#next() will bring it down to the next row, yes
     
    AstramG likes this.
  18. Offline

    ryr11

    It says that MySQL can not be resolved to a type. (fixed that)
    I have a new error now ;(
    plugin can not be resolved to a variable
    on the line
    MySQL MySQL = new MySQL(plugin, "host.name", "port", "database", "user", "pass");
     
    tstom0 likes this.
  19. Offline

    xCyanide

    -_Husky_-
    How can I save a list to mysql? For example I want to save List<EAchievement> to the database and retrieve that list how can I do that?
    I want the table to look like this playername | List<EAchievement>
     
  20. Offline

    tstom0

    ryr11 I have the same error :/
     
  21. Offline

    viper_monster

    tstom0 "plugin" is the instance of your Main class that extends JavaPlugin
     
  22. Offline

    tstom0

    spoljo666 Would you be able to give me an example please.
    private static MySQL playerDB = new MySQL(plugin, host, port, database, user, pass);
    My class name is db_registration, which when put in says cannot be resolved to a variable and when I use:
    com.github.tstom0.db_registration.db_registration it says the same thing. I tried "this" but that cannot be used in a static context.
     
  23. Offline

    viper_monster

  24. Offline

    HIFiGamer

    Hi! My code wont work but i don't know how to fix it! Here is my Code!

    Main Class:
    Code:
    package net.vanxiety.vrpg;
    
    import java.sql.Connection;
    
    import code.husky.*;
    import net.milkbowl.vault.economy.Economy;
    import net.vanxiety.vrpg.functions.SQL.*;
    import net.vanxiety.vrpg.functions.Colors.*;
    import net.vanxiety.vrpg.functions.Commands.*;
    import net.vanxiety.vrpg.functions.Entity.*;
    import net.vanxiety.vrpg.functions.Mana.ManaBar;
    import net.vanxiety.vrpg.functions.Menu.*;
    import net.vanxiety.vrpg.functions.Scheduler.*;
    import net.vanxiety.vrpg.functions.Scoreboard.*;
    import net.vanxiety.vrpg.functions.Vote.*;
    
    import org.bukkit.Bukkit;
    import org.bukkit.event.Listener;
    import org.bukkit.plugin.Plugin;
    import org.bukkit.plugin.PluginManager;
    import org.bukkit.plugin.RegisteredServiceProvider;
    import org.bukkit.plugin.java.JavaPlugin;
    
    public class Main extends JavaPlugin implements Listener {
        SettingsManager settings = SettingsManager.getInstance();
        public static Economy econ = null;
        public static Main instance;
        public static Main inst;
        private static Plugin plugin;
        public MySQL sql = new MySQL(plugin, "host", "port", "database", "user", "pass");
        public Connection c = null;
    
        public void onEnable() {
            plugin = this;
            sql = new MySQL(plugin, "host", "port", "database", "user", "pass");
            c = sql.openConnection();
    
    Second Class:
    Code:
    package net.vanxiety.vrpg.functions.SQL;
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    import net.vanxiety.vrpg.Main;
    
    import org.bukkit.ChatColor;
    import org.bukkit.command.Command;
    import org.bukkit.command.CommandExecutor;
    import org.bukkit.command.CommandSender;
    import org.bukkit.entity.Player;
    import org.bukkit.plugin.java.JavaPlugin;
    
    public class SQLCommands extends JavaPlugin implements CommandExecutor {
        int votes;
        public static Main plugin;
    
        public boolean onCommand(CommandSender sender, Command cmd,
                String commandLabel, String[] args) {
            Player player = (Player) sender;
            if (commandLabel.equalsIgnoreCase("votes")) {
                if (player.hasPermission("vrpg.admin")) {
                    Statement s;
                    try {
                        s = plugin.sql.openConnection().createStatement();
                        ResultSet res = s.executeQuery("SELECT * FROM vrpg WHERE Player = '" + player + "';");
                        res.next();
                        if (res.getString("Votes") == null) {
                            player.sendMessage("0 Votes.");
                        } else {
                            votes = res.getInt("Votes");
                            player.sendMessage(ChatColor.RED + "- " + votes + ChatColor.GRAY + " Vote(s)!");
                        }
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                }
            }
            return true;
        }
    }
    
    So yeah i don't know where it goes wrong! I know that i need to put my shizzle in the Main for the Database connection! But it is private info so i removed it in here!
     
  25. Offline

    bigteddy98

    Do you have any errors in your console?
     
  26. Offline

    ryr11

  27. Offline

    Wizehh

  28. Offline

    calebbfmv

    We have a major error here:
    Post last updated: 10:59PM 17 September 2013
    This post has been edited 9 times. It was last edited by -_Husky_- Sep 16, 2013.
    -_Husky_- explain this lie....
     
    -_Husky_- likes this.
  29. Offline

    Desle

    Thanks for this, although;
    How do i check if a player already has it's name in a table?
    this gives me an error;
    Code:
            ResultSet res = statement.executeQuery("SELECT * FROM Players WHERE Name = '" + e.getPlayer().getName() + "';");
            res.next();
            if(res.getString("Name") == null) {
     
  30. Offline

    calebbfmv

    Desle
    Use:
    if(res.next()){
    sql.UPDATE
    } else {
    sql.INSERT
     
Thread Status:
Not open for further replies.

Share This Page