Tutorial Using MySQL In your Plugins!

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

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

    Zacky1

    Okay so I'm a complete nooob at MySQL (ill admit it :p) but I was wondering, is it possible to order strings based on how many times it was entered (from most to lowest)?
     
  2. Offline

    gomeow

    iWareWolf
    Example of how I did it:
    Code:java
    1. DatabaseMetaData dm = this.connection.getMetaData();
    2. ResultSet tables = dm.getTables(null, null, "Players", null);
    3. if(!tables.next()) {
    4. Statement s = this.connection.createStatement();
    5. s.executeUpdate("CREATE TABLE `Players` (`Name` varchar(32), `Total` int, `Players` int, `Entities` int)");
    6. }
     
  3. Offline

    iWareWolf

    gomeow
    Would this work to constantly keep your database updated?

    @EventHandler
    public void onChange(ReputationChangeEvent e) throws SQLException {
    Statement statement = c.createStatement();
    DatabaseMetaData dm = this.c.getMetaData();
    ResultSet tables = dm.getTables(null, null, "Username", null);
    if (!tables.next()) {
    Statement s = this.c.createStatement();
    s.executeUpdate("CREATE TABLE `Reputation` (`Username` varchar(32), `Rep` int)");
    }
    statement.executeUpdate("INSERT INTO 'Reputation' ('" + e.getPlayer().getName() + "', " + reputation.getInstance().getRep(e.getPlayer()) + "')");
    }
     
  4. Offline

    gomeow

    iWareWolf
    Personally, I'd do it onEnable
     
  5. Offline

    iWareWolf

    gomeow
    So this in onEnable?

    Code:
            try {
                dm = this.c.getMetaData();
                ResultSet tables = dm.getTables(null, null, "Username", null);
                if (!tables.next()) {
                    Statement s = this.c.createStatement();
                    s.executeUpdate("CREATE TABLE `Reputation` (`Username` varchar(16), `Rep` int)");
                }
            } catch (SQLException ex) {
                Logger.getLogger(ReputationSQL.class.getName()).log(Level.SEVERE, null, ex);
            }
     
  6. Offline

    gomeow

    iWareWolf
    Except for the logging, you can just do getLogger().severe(String)
     
  7. Offline

    iWareWolf

    gomeow
    How do you check if a row exists
     
  8. Offline

    gomeow

    use a WHERE statement then check row count
     
  9. Offline

    iWareWolf

    gomeow
    I mean using this api not on MySQL
     
  10. Offline

    -_Husky_-

    iWareWolf

    This is a library for connecting to the database, you must execute your own checks/updates to the database.

    Use gomeow 's MySQL syntax to do this.
     
  11. Hi i currently have an issue with my plugin where the MySQL connection drops after it has been idle for more than 8 hours, i know there is a way around this but i'm unsure on how it's done, any help is appreciated. :)
     
  12. Offline

    gomeow

    -_Husky_- likes this.
  13. I could do that but the user would have to enter the command twice because the connection would be reestablished after an exception has occured. Other plugins that use MySQL don't have this problem i'm just wondering what i have to enter in the code to avoid this aswell. :(
     
  14. Offline

    -_Husky_-

    Before the command try re-open the connection?

    May not be the best performance wise, but it gets the job done.
     
  15. So before each command i should check the connection using your MySQL.checkconnection() function and then reopen it if it is closed?, k will do thanks. Will post results here if it works :)
     
  16. Offline

    gomeow

    Wizardo367
    How often do you do things with your database?
    eg. onBlockBreak
     
  17. My plugin is a command based server shop but somebody with a startup server that is empty overnight says the mysql connection drops, i've tried this myself by closing wamp, running a command and then opening wamp. It simply doesn't reconnect. I've tried closing the mysql and reopening it with a connection but it didn't work :( .

    UPDATE

    Fixed the problem by calling a function which closes and reopens the connection every time a command is run, i'll find a more efficient way to do this later. :p

    Code:
    MySQL.closeConnection(C);
    C = MySQL.open();
     
  18. Offline

    Shad

    Hello, when I update something to the database with this particular line of code, it causes the server to hang up for a split-second, though noticeable.

    Here is that bit of code:
    Code:
    //Updating the database
                    Statement statement = null;
                    try {
                        statement = MySQL.open().createStatement();
                    } catch (SQLException e1) {
                        e1.printStackTrace();
                    }
                    try { 
                        statement.executeUpdate("UPDATE MMOSkills_mining "
                                + "SET EXP=EXP+50 WHERE Player='"+player+"';");
                     
                    } catch (SQLException e1) {
                        e1.printStackTrace();
                    }
    Is it because of how I have
    Code:
    statement = MySQL.open().createStatement();
    ?

    I'm not really sure. Any help pointing out the problem would be greatly appreciated!
     
  19. Offline

    -_Husky_-

    Shad

    Yes, it is because of that, multithreading the MySQL side of your plugin would prove very useful, at the moment it does it all on the servers thread, that is why the server lags for a second or so.
     
  20. Offline

    Shad

    -_Husky_-
    How can I change it so it doesn't hang up?

    Code:
    Statement statement = c.createStatement();
    Causes a NullPointer.
     
  21. Offline

    -_Husky_-

    I will see if I can work on a fix tonight. :)

    Shad
     
  22. Offline

    Shad

    -_Husky_-
    Thanks! I'll be waiting in anticipation.
     
  23. Offline

    -_Husky_-

    Shad

    Had a chat to a few mates about what I could do,

    Our solution was to just close the connection when finished and then re-open it, it would be silly for me to multi-thread the library, if you want to multi-thread MySQL handling, go for it, but I'll leave that out of the library.

    Hope this helps, Husky.
     
  24. Offline

    Shad

    -_Husky_-
    Hmm, alright. How do I make it not multi-thread the MySQL side of things? I'm a bit lost on this aspect and why it's even happening.
     
  25. Offline

    -_Husky_-

    The best option would be to multi-thread it, it is not originally multi-threaded.

    Thanks, Husky
     
  26. Offline

    ritipinheiro

    is this legit to use?
     
  27. Offline

    gomeow

    -_Husky_- and hawkfalcon like this.
  28. -_Husky_-
    gomeow

    Hey guys i'm going to start using MySQL on my Plugin i've been learning SQL, but acctually never used it so i got here a few questions if don't mind me asking:

    How can i check if a table exits ?
    Incrementing a value on the Table, do i just have to set it again or update it ?
    Checking for a value on the database equals to something, for example i want to check for player money is greater to 50.

    For now that's my Question if you can anwer them i'll be much thankfull.
     
  29. Offline

    gomeow

    This is how I'd check if your tables exist:
    Code:java
    1. try {
    2. DatabaseMetaData dm = this.connection.getMetaData();
    3. ResultSet tables = dm.getTables(null, null, "Global", null);
    4. if(!tables.next()) {
    5. Statement s = this.connection.createStatement();
    6. s.executeUpdate("CREATE TABLE `Global` (`Total` int, `Entities` int, `Players` int)");
    7. s.executeUpdate("INSERT INTO `Global` (`Total`, `Entities`, `Players`) VALUES ('0', '0', '0')");
    8. }
    9. tables = dm.getTables(null, null, "Players", null);
    10. if(!tables.next()) {
    11. Statement s = this.connection.createStatement();
    12. s.executeUpdate("CREATE TABLE `Players` (`Name` varchar(32), `Total` int, `Players` int, `Entities` int)");
    13. }
    14. } catch(Exception e) {
    15. e.printStackTrace();
    16. System.out.println(ChatColor.RED + "KillCounter has experienced a fatal error. Please check your SQL setup and/or config.yml");
    17. getLogger().severe("KillCounter has experienced a fatal error. Please check your SQL setup and/or config.yml");
    18. setEnabled(false);
    19. }

    As for incrementing, you will need to get the value, then update it everytime
     
    BugsyFTW and -_Husky_- like this.
  30. gomeow Thanks, but how would i got to check if for example the Table Total is >= 5, if it is do something else something else ?
     
Thread Status:
Not open for further replies.

Share This Page