Tutorial Using MySQL In your Plugins!

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

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


    Hey guys, I haven't seen a working tutorial on this, and I use MySQL in most of my plugins, I decided to share.

    Huge thanks to tips48 for his contributions. ;)

    Put -this- into your project in your workspace.

    Next, in your main class, add
    1. MySQL MySQL = new MySQL(plugin, "host.name", "port", "database", "user", "pass");
    2. Connection c = null;

    Import the connection from java.sql.Connection

    Now all you need to do is open the connection,

    in onEnable(), add
    1. c = MySQL.openConnection();

    And you're done connecting to the Database.

    --Getting values--

    To get values from the database, you need to set up a few more things. Statements.


    Statements can be declared like this,

    1. Statement statement = c.createStatement();

    'c' being the Connection.

    Now we get to retrieving values.

    1. ResultSet res = statement.executeQuery("SELECT * FROM tokens WHERE PlayerName = '" + name + "';");
    2. res.next();

    This is an example I have used from one of my other plugins, It retrieves information from the db tokens, where the Table 'PlayerName' == my 'name' variable.

    Then I can get the information using

    1. if(res.getString("PlayerName") == null) {
    2. tokens = 0;
    3. } else {
    4. tokens = res.getInt("tokens");
    5. }

    --Setting information--

    To set information in the database, we use Statements again, but we use a different method within the Statement.

    Some example code;

    1. statement.executeUpdate("INSERT INTO tokens (`PlayerName`, `tokens`) VALUES ('" + name + "', '0');");
    2. System.out.println("Inserted info");

    Which will insert into the table.

    That's all from my tutorials, if you need more help, Please leave a comment, I'm happy to help.

    --Prepared Statements--

    To handle prepared statements, Do something like this!

    1. PreparedStatement ps = connection.prepareStatement("INSERT INTO `yourTable`(X, Y, Z) VALUES (?, ?, ?);");
    2. ps.setString(1, "This is the first question mark");
    3. ps.setString(2, "second question mark.");
    4. ps.setString(3, " I think you get it now.");
    5. ps.executeUpdate();

    Thanks to evilmidget38 for supplying the code for this!

    P.S : If you're having problems getting a NullPointer when calling
    1. Statement statement = c.createStatement();

    Try putting
    1. Statement s = MySQL.open().createStatement();
    before trying to make the statement.


    Post last updated: 10:59PM 17 September 2013
  2. Offline


    Thanks for this. Might be useful some day.
    Your example code-blocks are broken though (there are three, and I think you meant to put it in one).
    -_Husky_- likes this.
  3. Offline


    Fixed. :)
    jorisk322 likes this.
  4. Offline


    Nice! I am familiar with MySQL and have been doing a lot with them with PDOs in PHP, however, I had been having a hard time doing it in Java. This simplifies things just by looking at it
    -_Husky_- likes this.
  5. Offline


    Like it!
    -_Husky_- likes this.
  6. Offline


    I'm having a little issue with that NPE...
    And I put "c = MySql.open();" right before it... But it still gives me an NPE on "Statement statement = c.createStatement();"
  7. Offline


  8. Offline


    The whole class:
    package pro.homiecraft;
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    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 boolean onCommand(CommandSender sender, Command cmd, String commandLabel, String[] args){
                Player player = (Player) sender;
                String pName = player.getName();
                Connection c = null;
                try {
                    String sqlHost = Homiecraft.pluginST.getConfig().getString("HomieCraft.mysql.settings.host");
                    String sqlPort = Homiecraft.pluginST.getConfig().getString("HomieCraft.mysql.settings.port");
                    String sqlDb = Homiecraft.pluginST.getConfig().getString("HomieCraft.mysql.settings.db");
                    String sqlUser = Homiecraft.pluginST.getConfig().getString("HomieCraft.mysql.settings.user");
                    String sqlPw = Homiecraft.pluginST.getConfig().getString("HomieCraft.mysql.settings.pw");
                    if(!(args.length == 0)){
                            pro.homiecraft.MySql MySql = new pro.homiecraft.MySql(sqlHost, sqlPort, sqlDb, sqlUser, sqlPw);
                            c = MySql.open();
                            Statement statement = c.createStatement();
                            ResultSet res = statement.executeQuery("SELECT * FROM users WHERE minecraft = '" + pName + "';");
                            if(res.getString("minecraft") == null){
                                String UserName = args[1];
                                String Pw = args[2];
                                String EMail = args[3];
                                statement.executeUpdate("INSERT INTO users ('minecraft', 'username', 'password', 'email') VALUES('" + pName + "', '" + UserName + "', '" + Pw + "', '" + EMail + "');");
                                //player.sendMessage("[HomieCraft] You have now registered as: " + UserName + " Password: " + Pw + " Email: " + EMail);
                                player.sendMessage("[HomieCraft] You have allready registered! You can login at http://homiecraft.pro");
                            player.sendMessage("[HomieCraft] Usage:");
                            player.sendMessage("/homiecraft register <UserName> <Password> <email>");
                        player.sendMessage("[HomieCraft] Usage:");
                        player.sendMessage("/homiecraft register <UserName> <Password> <email>");
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
            return false;
  9. Offline


    vYN Try
    Statement statement = MySql.open().createStatement();
  10. Offline


    I don't see how that would do anything.

    Was the plugin able to connect to the MySQL server successfully? Any other stack traces than the NPE?

    If it can't connect in my plugin (they have an option between sql and yaml), I use setEnabled(false)
  11. Offline


    -_Husky_- likes this.
  12. Offline


    -_Husky_- Could you change your examples to use a PreparedStatements? I think it's a far better idea for people being introduced to MySQL to learn about and use PreparedStatements, especially for queries. Not only does it prevent SQL injection(not necessarily an issue with MC, but better safe than sorry), but for most servers it's a performance increase.
  13. Offline


  14. Offline


    gomeow It's really easy. My current class is almost over, so I'll make this quick, but if you want to find me later you can catch me on irc.

    Pretty much comes down to :
    PreparedStatement ps = connection.prepareStatement("INSERT INTO `yourTable`(X, Y, Z) VALUES (?, ?, ?);");
    ps.setString(1, "This is the first question mark");
    ps.setString(2, "second question mark.");
    ps.setString(3, " I think you get it now.");
  15. Offline


  16. Offline


    This will be really helpful for my plugins.

    edit: How would I check if a table exists?
  17. Offline


    I can show you a method I made in an hour or so
  18. Offline


    That would help a lot.

    Btw: Is there a way to eliminate the warning some commands give such as "UnHandled function" since its rather annoying to put it on all the methods that use MySQL

    Edit: How would I get all the results of a specific column as a List?
  19. Offline


    Been away camping for a week, sorry for no response.

    I will add PreparedStatements soon. evilmidget38 gomeow
    gomeow likes this.
  20. Offline


    So... can someone tell me how to check if a table exists?

    Note: to generate a table i just do:

    statement.executeQuery("create table something ( something varchar(35), som int");?
  21. Offline


    MySQL MySQL = new MySQL("host.name", port, "user", "pass");

    Port throws and error
  22. Offline


    -_Husky_- likes this.
  23. Offline


    gomeow Yeah I figured it out as soon as I posted like an idiot XD Thanks anyways
    -_Husky_- likes this.
  24. Offline


    How can I check if a table exists? and If it doesn't exist then create it?
  25. Offline


    sql = "CREATE TABLE IF NOT EXISTS `Gomeow` ..."
    Lubenica007 and Debels like this.
  26. Offline


    Quite simple, thanks :)

    do I make this as executeQuery or executeUpdate?, since both are sending me nullpointerexception.

    or maybe its my generation code?

    "CREATE TABLE IF NOT EXISTS `tm_towns` (id int PRIMARY KEY AUTO_INCREMENT, town varchar(33), owner varchar(16), bank int);"

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


    -_Husky_- likes this.
  28. Offline


    How would i go about creating a table using your code? this is what i have done:
    if(statement.executeQuery("SELECT count(*) FROM information_schema.TABLES WHERE (TABLE_SCHEMA = 'test') AND (TABLE_NAME = 'XInven');").getFetchSize() == 0)
                    statement.executeQuery("CREATE TABLE XInven (Username VARCHAR(20), Slot1ID int, Slot1Data int);");
    This connects to the database and returns 0 if there isnt a table and 1 if there is (in if statement) but i get an error when i do the create table query. the error is:

    what am i doing wrong?
  29. Offline


  30. Offline


    that isnt the error. i just found the problem you have to use executeupdate() not executequery().

    Thanks Anyway
Thread Status:
Not open for further replies.

Share This Page