Reading and Updating from a MySQL database

Discussion in 'Plugin Development' started by Royal_Soda, Aug 5, 2012.

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

    Royal_Soda

    Hey,

    I'm writing a plugin and I need some help for when I'm connecting into a MySQL database. I'd like the plugin to read from a specific table, and be able to update variables on it. I'd also like to pull some of the data to compare it with local variables. Can someone please give me some examples of code that can do this?

    PS: I've already set up the database, have the username and password. Also, I've already got the table set up.

    Regards,

    Harry J. (Royal_Soda)

    ^^

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: May 27, 2016
  2. Offline

    digga

    So you got the connection from java to the database established?
     
  3. Offline

    wirher

  4. Offline

    Royal_Soda

    Nope, I'm wondering how I should go about doing this. (Information is already being written by a PHP script I have on a different server.)

    Thanks!

    wirher
    Would you mind posting some examples of code on how to utilize it?

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: May 27, 2016
  5. Offline

    wirher

    Add this lib as additional package in your project so you will have two packages in src folder (if you are using eclipse)
    - you.yourrandompackagename.anotherstuff
    - lib.PatPeter.SQLibrary

    To create mysql connection you use in your class file:
    Code:
    public MySQL  mysql = new MySQL(log,"hostname","hostport","database","user","password");
    log is your logger object.

    Opening connection:
    Code:
            try{
            mysql.open();
            }catch (Exception e){
                log.info(e.getMessage());
            }
    And queries like that:
    Code:
    String query = "CREATE TABLE `users` (`id` integer NOT NULL AUTO_INCREMENT, `nick` varchar(30) NOT NULL, `password` varchar(16) NOT NULL, PRIMARY KEY (`id`), UNIQUE(`nick`)) ENGINE = MYISAM";
    mysql.query(query);//I suggest using "try" and "catch there"
    When you are not using connection for a specified amount of time it will lose connection so I always make "keep alive task" to do simple query - database will not drop connection (why? because opening new connection is long, doing query is really short
    Code:
    getServer().getScheduler().scheduleAsyncRepeatingTask(this, new Runnable() {
     
                      public void run() {
                          mysql.query("SELECT 1");
                      }
                    }, 10L, 1200L);
    To get data from database you use ResultSet:
    Code:
    String query = "SELECT `password` FROM `users` WHERE `nick` LIKE 'wirher'";
    ResultSet rs = mysql.query(query);
    rs.next(); //sets pointer to first record in result set
    pass = rs.getString(1);
    If you've never worked with ResultSets you can find a lot of tutorials in the internet.
     
  6. Offline

    Royal_Soda

    Thanks!

    wirher
    hmm.. Eclipse is telling me that mysql shouldn't be public, but instead it should be final. Also, log should be getLogger()?

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: May 27, 2016
  7. Offline

    rjVapes

  8. Offline

    wirher

    Why do you ask me about it?
    I've just given you simple examples and encapsulation or hermetization is not my problem. You code that and you should keep the code clean.
    About the logger. You should know how does Java works and what is class, method and object. You can use there "getLogger()" but probably you will use logger more than once so executing getLogger() every time would affect efficiency.
     
Thread Status:
Not open for further replies.

Share This Page