Tutorial Using MySQL In your Plugins!

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

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

    -_Husky_-

    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
    Code:java
    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
    Code:java
    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.

    [java.sql.Statement]


    Statements can be declared like this,

    Code:java
    1. Statement statement = c.createStatement();


    'c' being the Connection.

    Now we get to retrieving values.



    Code:java
    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

    Code:java
    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;

    Code:java
    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!

    Code:java
    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
    Code:java
    1. Statement statement = c.createStatement();

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

    Enjoy!

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

    jorisk322

    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

    -_Husky_-

    Fixed. :)
     
    jorisk322 likes this.
  4. Offline

    gomeow

    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

    chasechocolate

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

    vYN

    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

    gomeow

  8. Offline

    vYN

    gomeow
    The whole class:
    Code:
    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){
            if(cmd.getName().equalsIgnoreCase("homiecraft")){
                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)){
                        if(args[0].equalsIgnoreCase("register")){
                            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 + "';");
                            res.next();
                         
                            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);
                            }else{
                                player.sendMessage("[HomieCraft] You have allready registered! You can login at http://homiecraft.pro");
                            }
                        }else{
                            player.sendMessage("[HomieCraft] Usage:");
                            player.sendMessage("/homiecraft register <UserName> <Password> <email>");
                        }
                    }else{
                        player.sendMessage("[HomieCraft] Usage:");
                        player.sendMessage("/homiecraft register <UserName> <Password> <email>");
                    }
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            return false;
        }
    }
    
     
  9. Offline

    -_Husky_-

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

    gomeow

    -_Husky_-
    I don't see how that would do anything.

    vYN
    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

    vYN

    -_Husky_- likes this.
  12. Offline

    evilmidget38 Retired Staff

    -_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

    gomeow

  14. Offline

    evilmidget38 Retired Staff

    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 :
    Code:
    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.");
    ps.executeUpdate();
     
  15. Offline

    gomeow

  16. Offline

    Debels

    This will be really helpful for my plugins.

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

    gomeow

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

    Debels

    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

    -_Husky_-

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

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

    Debels

    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

    CarPet

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

    Port throws and error
     
  22. Offline

    gomeow

    -_Husky_- likes this.
  23. Offline

    CarPet

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

    Debels

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

    gomeow

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

    Debels

    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

    gomeow

    -_Husky_- likes this.
  28. Offline

    NoLiver92

    -_Husky_-
    How would i go about creating a table using your code? this is what i have done:
    Code:
    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

    gomeow

    NoLiver92
    CREATE TABLE IF NOT EXISTS `XInven`...
     
  30. Offline

    NoLiver92

    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