Tutorial Using MySQL In your Plugins!

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

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


    Neilnet likes this.
  2. What's the reasoning behind creating helper methods in MySQL that aren't in SQLite?
  3. Offline


    Simply because I don't do SQLite, never looked at it, either. :p
  4. Check my pull request then
  5. Offline


    How would I initialize this in another class? I am trying to get a value the exact way you did -_Husky_- but when I try to do:
    1. Statement statement = c.createStatement();

    c is not recognized should i create an instance for the other classes that will be using mysql?
  6. Offline


    Insert this code above it:
    1. Connection c = MySQL.openConnection();

    Thanks, Husky.
  7. Offline



    All looks good!

    (There is a small typo however: "issueing" -> "issuing", Line 12) :p

    Thanks for your kind words!
  8. Offline


  9. Offline


    Soo, i'm doing this and when I hover over c = MySQL.openConnection(); it says: Unhandled Exceptions: java.sql.SQLException, java.lang.ClassNotFoundException

    How can I fix this?
    1. public class EventCraftTracker extends JavaPlugin implements Listener
    2. {
    3. public Plugin plugin;
    4. MySQL MySQL = new MySQL(plugin,
    5. getConfig().getString(getConfig().getString(UserCredentials.HOSTNAME)),
    6. getConfig().getString(getConfig().getString(UserCredentials.HOSTNAME)),
    7. getConfig().getString(getConfig().getString(UserCredentials.HOSTNAME)),
    8. getConfig().getString(getConfig().getString(UserCredentials.HOSTNAME)),
    9. getConfig().getString(getConfig().getString(UserCredentials.HOSTNAME)));
    10. Connection c = null;
    12. public void onEnable()
    13. {
    14. loadConfiguration();
    15. getLogger().info("Hostname set to : " + getConfig().getString(UserCredentials.HOSTNAME));
    16. getLogger().info("Port set to : " + getConfig().getString(UserCredentials.PORT));
    17. getLogger().info("Database set to : " + getConfig().getString(UserCredentials.DATABASE));
    18. getLogger().info("Username set to : " + getConfig().getString(UserCredentials.USERNAME));
    19. getLogger().info("Password set to : " + getConfig().getString(UserCredentials.PASSWORD));
    20. Bukkit.getServer().getPluginManager().registerEvents(this, this);
    21. c = MySQL.openConnection();
    22. }
    24. void loadConfiguration()
    25. {
    26. getConfig().addDefault(UserCredentials.HOSTNAME, "");
    27. getConfig().addDefault(UserCredentials.PORT, "");
    28. getConfig().addDefault(UserCredentials.DATABASE, "");
    29. getConfig().addDefault(UserCredentials.USERNAME, "");
    30. getConfig().addDefault(UserCredentials.PASSWORD, "");
    31. getConfig().options().copyDefaults(true);
    32. saveConfig();
    33. }
    34. }
  10. Offline


    Okay, fixed. Now getting an error that Connection c, can't be null;
  11. Offline


    -_Husky_- likes this.
  12. Offline


    Post your new code, please.

    Thanks :)!
  13. Offline


    Don't you mean column and not table? :)
    -_Husky_- likes this.
  14. Offline


    How can i add values to an existed table?
    When i use executeUpdate im getting 2 of the same in the table!
    I want to override their values and for example i want their coins from 0 to 20
    I am using this:
    1. try {
    2. java.sql.Statement statement = c.createStatement();
    3. statement.executeUpdate("CREATE TABLE IF NOT EXISTS coins (PlayerName VARCHAR(20), Coins INT(20));");
    4. System.out.println("Table created");
    5. statement.executeUpdate("INSERT INTO coins (`PlayerName`, `Coins`) VALUES ('" + name + "', '0');");
    6. System.out.println("Inserted info");
    7. } catch (SQLException e) {
    8. // TODO Auto-generated catch block
    9. e.printStackTrace();
    10. }
    11. }
  15. Offline


    @-_Husky_- How would i acomplish to update stuff whitout getting duplicates
  16. Offline


  17. Offline


    So, how does someone download those files? Im pretty bad with github..
  18. -_Husky_- likes this.
  19. Offline


    Tag my username when replying to me.

    I'm using PreparedStatements and it doesn't seem to work when I run an INSERT INTO command.
    PreparedStatement name = connection.prepareStatement("INSERT INTO `player_data`(username,blah,blah) VALUES (?,0,0);");
    name.setString(1, player.getName());
    When I ran the plugin, it said the error was on the line that declared the QUERY (preparedstatement name), and I can't seem to fix it, I have tried a lot to.
  20. Offline


    So no help yet? :/
  21. Offline


    Maybe you should post the actual error rather than just saying some error happened on "this query." It doesn't look particularly wrong and without the actual error there is no way to help you.
  22. Offline


    07.04 19:06:19 [Server] ERROR com.mysql.jdbc.Util.handleNewInstance(Util.java:407)
    07.04 19:06:19 [Server] ERROR java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    07.04 19:06:19 [Server] ERROR sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    07.04 19:06:19 [Server] ERROR sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    07.04 19:06:19 [Server] ERROR sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    07.04 19:06:19 [Server] ERROR com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    07.04 19:06:19 [Server] ERROR com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:794)
    07.04 19:06:19 [Server] ERROR com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2163)
    07.04 19:06:19 [Server] ERROR com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2371)
    07.04 19:06:19 [Server] ERROR com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2338)
    07.04 19:06:19 [Server] ERROR com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1090)
    07.04 19:06:19 [Server] ERROR com.mysql.jdbc.MysqlIO.readPacket(MysqlIO.java:611)
    07.04 19:06:19 [Server] ERROR com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:2526)
    07.04 19:06:19 [Server] ERROR com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:189)
    07.04 19:06:19 [Server] ERROR com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:161)
    07.04 19:06:19 [Server] ERROR com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:114)
    07.04 19:06:19 [Server] ERROR java.net.SocketInputStream.read(SocketInputStream.java:141)
    07.04 19:06:19 [Server] ERROR java.net.SocketInputStream.read(SocketInputStream.java:170)
    07.04 19:06:19 [Server] ERROR java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
    07.04 19:06:19 [Server] ERROR java.net.SocketInputStream.socketRead0(Native Method)
    @RingOfStorms Error log is up there. ^
    I fixed a bit of the code, but now when I join the server, it lags, crashes the server, and kicks me for "Timed Out." Also, the above error log is upside down, meaning the first error message in console is really the last one pasted there.
  23. Offline


    @gomeow @-_Husky_- When I try to connect to the host, it's replacing the host with the server's localhost ip I believe. How is that possible and how can I fix this?

    Main plugin;
        MySQL sql = new MySQL(plugin, "remotemysqlserver.com", "3306", "mydatabase", "username", "pass");
        Connection c = null;
       public void onEnable()
        {        try {
                c = sql.openConnection();
            } catch (Exception e) { errorHandler(e); }
    public void mysqlMethod(Player player, String data, Integer type, String xyz)
            try {
                //we could use mysql's timestamp, but it is good to keep things tied with the server
                Date date = new Date();
                SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                //custom location not provided, grab players current location
                if (xyz == "")
                    xyz = (int) player.getLocation().getX() + ";" + (int) player.getLocation().getY() + ";" + (int) player.getLocation().getZ();
                //filter/filter incomming info for later transmitting via mysql
                String theDate      = dateFormat.format(date);
                String p_name       = player.getName();
                String p_uuid       = String.valueOf(player.getUniqueId());
                String p_ip         = player.getAddress().getAddress().getHostAddress();
                String p_server     = player.getServer().getIp() + ":" + getServer().getPort();
                String p_world      = player.getLocation().getWorld().getName();
                String[] p_xyz  = xyz.split(";");
                //prepare and upload data to mysql database
                String query = "INSERT INTO `mc_records` (date, uuid, player, ip, pserver, type, data, world, x, y, z) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
                PreparedStatement db = sql.openConnection().prepareStatement(query);
                db.setString(1, theDate); //date
                db.setString(2, p_uuid); //uuid
                db.setString(3, p_name); //player
                db.setString(4, p_ip); //ip
                db.setString(5, p_server); //server
                db.setInt(6, type); //type
                db.setString(7, data); //data
                db.setString(8, p_world); //world
                db.setString(9, p_xyz[0]); //y
                db.setString(10, p_xyz[1]); //y
                db.setString(11, p_xyz[2]); //z
            } catch (Exception e) { errorHandler(e); }
    ERROR while loading the plugin:
    07.04 23:11:03 [Server] WARN at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593)
    07.04 23:11:03 [Server] WARN at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)
    07.04 23:11:03 [Server] WARN java.sql.SQLException: Access denied for user 'MYDBUSERNAME'@'ns501591.ip-192-99-72.net' (using password: YES)
  24. Offline


    That error looks incomplete. All you pasted was everything after the real exception line. What is the actual error that goes with the rest of that trace?
  25. Offline


    @RingOfStorms It was a crash error. The server crashed, then it dumped this code.
  26. Offline


    do explain. on the thread.
  27. Offline



    Well, I''m not going to bother reading this ridiculously long thread, so sorry if this has been said before.

    1. Doesn't even bother mentioning the fact that you should run SQL queries on a separate thread to avoid hanging the server for indeterminate periods of time
    2. Doesn't help anyone learn SQL or database design, nor does it provide links to do so, so you'll probably wind up with ugly, sprawling, broken tables that are slapped together with no forethought
    3. Shows how to use a prepared statement but doesn't explain what it is or why they are necessary
    Should I continue?
  28. Offline


    I used these classes but I found them to not be very effective so I use this way, it's a bit harder but anyone who is using MySQL in their plugins should think of this as simple.
           static final String URL = "jdbc:mysql://" + getConfig().get("MySQL.URL") +":"+ getConfig().get("MySQL.Port")+"/" +getConfig().get("MySQL.Database");
           static final String USER = getConfig().get("MySQL.Username");
           static final String PASS = getConfig().get("MySQL.Password");
           Connection c = null;
           public void onEnable(){
                   c = DriverManager.getConnection(URL,USER,PASS);
               }catch(SQLException|ClassNotFoundException e){
                   System.out.println("[MySQL] Couldn't connect to MySQL cuz : "+ e.getMessage());
    (I mainly used this instead because the classes also didn't work)
  29. Guys, I have a little issue with this...
    Whenever I try to retrieve values from the database a get the "ResultSet closed" exception. Here's the part of my code that produces it:
            ResultSet set = l.statement.executeQuery("SELECT Location FROM LaunchPlate WHERE Location = '"+ serializeLoc(loc.getBlock().getLocation()) +"';");
            if(player.hasPermission(l.p) && set.getString("Location").equals(serializeLoc(loc.getBlock().getLocation()))){
    //do stuff
    This code is part of a class for event handling, so 'l' represents the instance of the main class, where I created the connection and the statement.
    Can you help me?
  30. Offline


    Your query doesn't really make sense. Why would you be retrieving your Location column when you already know what it is? Your query doesn't actually DO anything does it?
Thread Status:
Not open for further replies.

Share This Page