Tutorial In Depth Guide to Using MySQL

Discussion in 'Resources' started by bcohen9685, Jul 11, 2016.

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

    bcohen9685

    -------------------------------------------------------------------------------
    Quick Resoures:
    DELETE:

    To delete a player from a database, do:

    Code:
     statement.executeUpdate("DELETE FROM tablename WHERE + "playername/uuid" + "';");
    ADD:
    To add a player to a database, do:

    Code:
    \statement.executeUpdate("INSERT INTO (columnname) VALUES ('" + "playername/uuid"+ "')");
    -------------------------------------------------------------------------------

    So, I've been researching for the past week how to use MySQL in Bukkit plugins using JDBC.

    I tried following Husky's guide, but I didn't find a lot of resources on how to actually use MySQL, I just found how to link it. In this tutorial I will be going through and showing you the basics of MySQL

    If you don't know what MySQL is, look here.

    To start, we need to link MySQL into your plugin and connect to the database.
    1. Open up your Main class.

    2. Put these in the top of your class:
    Code:
            public Connection connection;
            public String host;
            public String database;
            public String username;
            public String password;
            public Statement stmt = null;
            public int port;
    2. Create a connect method like so:
    Code:
    public void connect() throws SQLException, ClassNotFoundException {
    
         }
    
    3. Run the connect method in your onEnable().

    4. Surround the connect method (in the onEnable) with try/catch by hovering over it in your IDE.

    So as of now, your class should look like this:
    Code:
    package com.braden.tut;
    
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    import org.bukkit.plugin.java.JavaPlugin;
    
    public class Main extends JavaPlugin {
        public Connection connection;
        public String host;
        public String database;
        public String username;
        public String password;
        public Statement stmt = null;
        public int port;
    
    
        public void onEnable(){
            try {
                connect();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        public void connect() throws SQLException, ClassNotFoundException {
    
    
        }
    }
    Now it's time to give the plugin your database info, like so:

    1. Label the strings/ints in your connect method that you created earlier, like so:
    Code:
    host = "Your database IP"; //EX: 127.0.0.1
              port = 3306; //YOUR DATABASE PORT, TYPICALLY 3306.
              username = "root"; //DATABASE USERNAME
              password = "password"; //DATABASE PASSWORD
              database = "database"; //DATABASE NAME
    Now that we have all the information stored, it's time to actually connect to the server (in the connect method), using these lines of code:
    Code:
    Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://" + this.host+ ":" + this.port + "/" + this.database, this.username, this.password);
    
    And for future usage, we're going to create a statement (right under where you define connection):
    Code:
    stmt = connection.createStatement();
    Now that we've connected to the database, it's time to run the proper checks in our connect method, like so:
    Code:
    if (connection != null && !connection.isClosed()) {
                return;
            }
    
            synchronized (this) {
                if (connection != null && !connection.isClosed()) {
    
                    return;
    
                } 
    Now that we've come this far, your class should look something like this:
    Code:
    package com.braden.tut;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    import org.bukkit.plugin.java.JavaPlugin;
    
    public class Main extends JavaPlugin {
        public Connection connection;
        public String host;
        public String database;
        public String username;
        public String password;
        public Statement stmt = null;
        public int port;
    
    
        public void onEnable(){
            try {
                connect();
            } catch (ClassNotFoundException e) {
      
                e.printStackTrace();
            } catch (SQLException e) {
      
                e.printStackTrace();
            }
        }
        public void connect() throws SQLException, ClassNotFoundException {
            host = "Your database IP"; //EX: 127.0.0.1
            port = 3306; //YOUR DATABASE PORT, TYPICALLY 3306.
            username = "root"; //DATABASE USERNAME
            password = "password"; //DATABASE PASSWORD
            database = "database"; //DATABASE NAME
    
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://" + this.host+ ":" + this.port + "/" + this.database, this.username, this.password);
    
        }
    }
    
    Next, to create a table, create a method like so:

    Code:
    public void createTables(){
    public void createTables(){
            String players = "CREATE TABLE IF NOT EXISTS players("
                    + "PlayerUUID LONGTEXT)";
            try {
                stmt.executeUpdate(players);
            } catch (SQLException e) {
        
                e.printStackTrace();
            }
        }
    }
    This will create a table named players with a column named PlayerUUID.
    Initiate this in your onEnable method, by putting 'createTables();' under your connect method.
    If you want, to show in Console that you've connected, feel free to put this in the "try" section of your onEnable:
    Code:
    System.out.println("Successfully connected to " + this.database);
    Now, if you inserted the information correctly, you should be able to connect to MySQL on your server startup!

    Now that we're connected, it's time to get into the juicy stuff. I personally use separate classes for my commands to keep things organized, so I'm going to go ahead and create a class named "CMDStaffSet", and let it implement CommandExecutor. Go ahead and setup your command class.

    You're going to want to link your Command class to your Main class, so put this near the top:
    Code:
        Main plugin;
        public CMDStaffSet(Main instance){
            plugin = instance;
        }
    Now, this is when the Statement you created earlier comes into play. In your onCommand, go ahead and call on your Statement. Your class should look like this:
    Code:
    package com.braden.tut;
    
    import java.sql.Statement;
    
    import org.bukkit.command.Command;
    import org.bukkit.command.CommandExecutor;
    import org.bukkit.command.CommandSender;
    
    public class CMDStaffSet implements CommandExecutor {
        Main plugin;
        public CMDStaffSet(Main instance){
            plugin = instance;
        }
    
        public boolean onCommand(CommandSender sender, Command cmd, String label, String[] args){
            Statement stmt = plugin.stmt;
    
    
            return true;
        }
    }
    
    Next, I'll be showing you how to add/remove players from a database using a command.
    1. Go ahead and create the command admin (this is very basic, use cmd.getName).
    2. Create a target variable like so:
    Code:
    Player target = sender.getServer().getPlayer(args[0]);
    1. Name the command add or whatever you like, and make sure the argument contains a player. (check below for example usage)
    2. Check if the command is being executed from Console or a Player, using ConsoleCommandSender.
    Code:
    package com.braden.tut;
    
    import java.sql.Statement;
    
    import org.bukkit.command.Command;
    import org.bukkit.command.CommandExecutor;
    import org.bukkit.command.CommandSender;
    import org.bukkit.command.ConsoleCommandSender;
    import org.bukkit.entity.Player;
    
    public class CMDAdd implements CommandExecutor {
        Main plugin;
        public CMDAdd(Main instance){
            plugin = instance;
        }
    
        public boolean onCommand(CommandSender sender, Command cmd, String label, String[] args){
            Statement stmt = plugin.stmt;
            if(cmd.getName().equalsIgnoreCase("add")){
                 if(Bukkit.getPlayerExact(args[0]) != null){
                if(sender instanceof ConsoleCommandSender && args.length == 1){
           
                } else if (sender instanceof Player && args.length == 1){
           
                }
       }
            }
    
            return true;
        }
    }
    
    Your class should look something like this.

    Next, add a player's UUID to the database like so:
    Code:
    plugin.stmt.executeUpdate("INSERT INTO players (PlayerUUID) VALUES ('" + target.getUniqueId().toString() + "')");
    Surround that with try/catch once you put it in.

    Your class should end up looking something like this:
    Code:
    package com.braden.tut;
    
    import java.sql.SQLException;
    import java.sql.Statement;
    
    import org.bukkit.Bukkit;
    import org.bukkit.command.Command;
    import org.bukkit.command.CommandExecutor;
    import org.bukkit.command.CommandSender;
    import org.bukkit.command.ConsoleCommandSender;
    import org.bukkit.entity.Player;
    
    public class CMDAdd implements CommandExecutor {
        Main plugin;
        public CMDAdd(Main instance){
            plugin = instance;
        }
    
        public boolean onCommand(CommandSender sender, Command cmd, String label, String[] args){
            Statement stmt = plugin.stmt;
            Player target = sender.getServer().getPlayer(args[0]);
            if(cmd.getName().equalsIgnoreCase("add")){
                if(Bukkit.getPlayerExact(args[0]) != null){
                if(sender instanceof ConsoleCommandSender && args.length == 1){
                    try {
                        stmt.executeUpdate("INSERT INTO players(PlayerUUID) VALUES ('" + target.getUniqueId().toString() + "')");
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                } else if (sender instanceof Player && args.length == 1){
                    try {
                        stmt.executeUpdate("INSERT INTO players(PlayerUUID) VALUES ('" + target.getUniqueId().toString() + "')");
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                }
                }
            }
    
            return true;
        }
    }
    
    That's how you add a player's UUID to a table.

    To check if a user is in a table (or multiple tables) follow the following steps:
    COMING SOON
     
    Last edited: Jul 11, 2016
  2. Offline

    FrostedSnowman

    Few problems:
    • You should be using PreparedStatements to avoid SQL injection attacks
    • You need to run any tasks involving MySQL data manipulation asynchronously or in a new thread, to prevent blockage of the main thread involving Bukkit's tasks. You'll basically freeze the server.
    • You should be closing the connection once finished doing all tasks necessary in a block
    • You should be closing the statements once finished doing all tasks necessary in a block
    • You're parsing index-0 in the command as a player twice, try to avoid repetitive code
    • Again with repeating code, you should combine both checking if the commandsender is a player or a console with an or operator, instead of repeating the code twice.

    Of all, I'd advise not using this until the above is corrected.

    I would suggest using this A-OK tutorial on using MySQL

    https://www.spigotmc.org/threads/tutorial-implement-mysql-in-your-plugin-with-pooling.61678/
     
    Last edited: Jul 23, 2016
Thread Status:
Not open for further replies.

Share This Page