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

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

    -_Husky_-

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

    OptimalBread

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

    -_Husky_-

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


    Thanks, Husky.
     
  7. Offline

    -_Husky_-

    Simo389

    All looks good!

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

    Thanks for your kind words!
     
  8. Offline

    candyfloss20

  9. Offline

    agjm1

    -_Husky_-
    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?
    Code:java
    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;
    11.  
    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. }
    23.  
    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

    agjm1

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

    SirFaizdat

    Brilliant!
     
    -_Husky_- likes this.
  12. Offline

    -_Husky_-

    Post your new code, please.

    SirFaizdat
    Thanks :)!
     
  13. Offline

    woutwoot

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

    jusjus112

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

    NovaGamingBrian

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

    heu-craft

  17. Offline

    MinecraftMart

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

    Agentleader1

    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.
    Code:
    PreparedStatement name = connection.prepareStatement("INSERT INTO `player_data`(username,blah,blah) VALUES (?,0,0);");
    name.setString(1, player.getName());
    name.executeUpdate();
    name.executeQuery();
    name.execute();
    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

    Agentleader1

    So no help yet? :/
     
  21. Offline

    RingOfStorms

    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

    Agentleader1

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

    metmad22

    @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?

    Code:
    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
                db.executeUpdate();
    
            } catch (Exception e) { errorHandler(e); }
        }
    ERROR while loading the plugin:
    Code:
    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

    RingOfStorms

    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

    Agentleader1

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

    pie_flavor

    do explain. on the thread.
     
  27. Offline

    mythbusterma

    @pie_flavor

    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

    InfiniteIV

    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.
    Code:
           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;
           @Override
           public void onEnable(){
               try{
                   Class.forName("com.mysql.jdbc.Driver");
                   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:
    Code:
            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

    RingOfStorms

    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