[TUTORIAL] Using MySQL in your plugin (NO LIBRARIES!)

Discussion in 'Resources' started by GreatBlitz, May 13, 2014.

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

    GreatBlitz

    Readme
    This is my first tutorial so please pardon me if I make any mistakes. Constructive criticism is always appreciated.
    Introduction
    *Skip this if you are in urgent need. However, I recommend reading this. You may not want to use a MySQL database, I've mentioned why in here*
    I was making a minigame plugin (for learning purposes, yes I'm 13) and soon came to the point where I wanted to store a player's kills and deaths. I considered my options:
    1. Vault - Of course a nice option, you can use this too.
    2. MySQL databases - Which I'll be teaching you now.
    I was about to go for Vault, but before that I wanted to take the advice of a friend. He had made a plugin too, and he said that he recommended a database. I asked why, and he said - it is essentially the same but, you can create a PHP front-end and make a cool website where someone enters their name and gets their kills/deaths!
    It did sound like a cool feature, so I switched to MySQL at the end-moment.
    However, keep this in mind: if your needs aren't many, and you don't want a front end of sorts, you can always go for some sort of economy plugin or some plugin that will suit your needs.
    You'll save yourself the effort of making a database and hosting it.
    Onto the real deal now :)
    Prerequisites
    1. Eclipse
    2. A MySQL database and a place to host it (if you want to use it for development purposes, you can use XAMPP (google it))
    Connecting to your database

    Firstly in your main file you want to declare some instance variables as such:-
    Code:java
    1. static final String DB_NAME = "jdbc:mysql://ip:port/database";
    2. static final String USER = "user";
    3. static final String PASS = "pass";


    DB_NAME can be broken up as such:
    ip = The ip on which you are hosting your database, localhost if you are hosting on your own machine.
    port = The port, 3306 by default
    database = Name of your database. Example - aliens
    So from the above info, DB_NAME would be
    Code:java
    1. "jdbc:mysql://localhost:3306/aliens"



    User and pass are your username and password respectively.

    Connection and Statement are explained in the upcoming block of code.
    Now, in your onEnable() method you want to place the following:
    Code:java
    1. try {
    2. Class.forName("com.mysql.jdbc.Driver"); //Gets the driver class
    3. getLogger().info("About to connect to database"); //These are just for debugging purposes.
    4.  
    5. conn = DriverManager.getConnection(DB_NAME, USER, PASS); //Gets a connection to the database using the details you provided.
    6.  
    7. getLogger().info("Successfully connected.");
    8.  
    9. getLogger().info("About to create a statement");
    10.  
    11. s = conn.createStatement(); //Creates a statement. You can execute queries on this.
    12.  
    13. getLogger().info("Successfully created statement.");
    14. }
    15. catch(Exception ex) {
    16. ex.printStackTrace();
    17. }


    You could run this, and it would work just fine, but we aren't doing anything with the database at all! So onto the next section now-

    Placing data in the database (adding rows)
    We will now add a row into our database. Add this into your try {} block.
    Code:java
    1. getLogger().info("About to add new row");
    2.  
    3. String sql = "INSERT INTO bukkitpoints (player_name, kills, deaths) VALUES ('GreatBlitz', 1, 1)"; //This line is completely MySQL. Also, it assumes you have a table called bukkitpoints, and it has the columns specified.
    4. s.executeUpdate(sql); //Execute the mysql statement. Remember the s variable we had made earlier? This line uses that.
    5.  
    6. getLogger().info("Successfully created row.");
    7.  
    8. getLogger().info("About to close everything");


    Now use any MySQL tool such as phpMyAdmin or the console to check the database and.. voila!
    You just added in a row!

    Getting data from the database

    I also quickly want to explain something - ResultSets. These are basically like your table itself. It contains methods for moving to the next row, obtaining a value from a column and such. Keep these in mind, for you'll need these later.

    Firstly you want to create a Statement. Statements have mainly three methods (for purposes of getting data):-
    1. execute(String sql) :-
    Parameter: The string containing the SQL you want to execute.
    Return: Boolean. If true, a ResultSet can be obtained. If false, a ResultSet cannot be obtained.
    Usage: Not too sure. I think it is for queries such as CREATE, ALTER, DROP and such. (Requesting clarification)

    2. executeUpdate(String sql):-
    Parameter: The string containing the SQL you want to execute.
    Return: Integer. This integer is the number of rows affected.
    Usage: Mainly for queries where you don't expect a ResultSet like UPDATE, INSERT and DELETE. Is used quite often. We used this to set the data in the previous example.

    3. executeQuery(String sql):-
    Parameter: The string containing the SQL you want to execute.
    Return: A ResultSet. Contains the ResultSet you asked for.
    Usage: Mainly for SELECT and other queries where you would expect a ResultSet. Is used quite often.

    With that being said, we can now proceed with actual code.

    Busy right now, will continue when possible.
    I hope you this helped you so far! :)

    Reserved for future info.

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

    1Rogue Retired Staff

    "No libraries" but, you used one...

    That and it's unnecessary, bukkit already loads the drivers for mysql.
     
    GreatBlitz and Garris0n like this.
  3. Offline

    GreatBlitz

    1Rogue
    Are you trying to say Connector/J isn't even required?

    Removed Connector/J.
    Thanks 1Rogue

    EDIT by Moderator: merged posts, please use the edit button instead of double posting.
     
    Last edited by a moderator: Jun 8, 2016
  4. Offline

    1Rogue Retired Staff


    nor the Class.forName();
     
    Konkz likes this.
  5. Offline

    Konkz

    Wrong place to post it too - should be under resources. :p
     
    ZodiacTheories likes this.
  6. Offline

    ZodiacTheories

    + 1 Move to Resoures
     
    Konkz likes this.
  7. Offline

    GreatBlitz

  8. Offline

    hawkfalcon

    Nope! DBO staff, not forum. Hit the 'report' button :)
     
    -_Husky_- likes this.
  9. Offline

    GreatBlitz

    hawkfalcon
    Yep, I reported the original post with the reason "Please move this to the Resources section" :)
     
  10. Offline

    Iroh Retired Staff

    Moved to resources.
     
  11. Offline

    GreatBlitz

  12. Offline

    MOMOTHEREAL

    Nice thread, waiting for the 'Getting Data' part :)
     
  13. Offline

    GreatBlitz

    MOMOTHEREAL
    Glad you liked it. Adding in the Getting Data part ASAP :)
     
  14. Offline

    TheE

    First, you are using JDBC witch is a library (that comes bundled with bukkit witch is, again, a library). Second, you absolutely want to use a PreparedStatement instead of the Statement. A PreparedStatement is send to the database right away and gets pre-compiled, so when you execute it with the corresponding values, the MySQL server just executed the pre-compiled statement. When you execute the same statement again with other values, MySQL can use the cached and pre-compiled PreparedStatement which reduces load on the database-server.

    That said, there is a decent tutorial on JDBC usage in the official Java tutorials which contains all these informations. And there are java-docs for JDBC that clarify any questions one might have about a method's usage. Sorry, but it might have been better if you had read these instead of writing this incomplete tutorial.
     
  15. Offline

    GreatBlitz


    JDBC may be a library, but in this context I mean no hassle to install any libraries such as what -_Husky_- or PatPeter created.

    PreparedStatements. Yes of course I do plan on including how to use them. Can I not start with something basic first? Show them basic functionality?

    Also, regarding your thoughts about tutorials already being present - if people wanted to use them (official tutorials), why would people like Husky or PatPeter even make libraries? Is there not a clear understanding that people need some helping? Sorry, but if you believe that you can manage without this tutorial, go ahead. I am making this tutorial for people to just start off.
     
  16. Offline

    TheE

    The problem I saw is that you introduced the Statement in a way where you (normally) want to use a PreparedStatement. However you did not give any information about PreparedStatments, neither did you inform learners about the existence and benefits of PreparedStatements. The official Tutorial I linked introduces all three statements types on the first page, so learners know that there are multiple possibilities available and, given that they are rational thinkers, would read about all to decide witch one they should use.

    Libraries are not tutorial so there is no valid point in comparing the existence of one with the other. Libraries are designed to remove certain, repetitive and specific code from projects by bundling it in another central project. Tutorials are meant for learners - and it is definitely great to have multiple tutorials since people used to have different ways of learning stuff, but at least these tutorials should be complete and correct. If you add PreparedStatments and include the missing information which you marked with 'Requesting clarification' from the java-docs than that is all I asked for.

    It might be another questions why there is a need for bukkit-specific libraries for standard java-tasks when there are business solutions available (e.g. looking at JOOQ to connect rational-databases with object-orientated logic). But that might be a question of personal taste or experience (these libraries are harder to implement because they allow much more) and even if not there is no point in discussing it here.
     
  17. Offline

    ccrama

    GreatBlitz
    How exactly were you planning on using Vault to store data? Vault is a connector between economy and other plugins, not really usable for stats because you can only store one value. You should also have considered using flatfile and other localized databases, as those can be more reliable and faster :). The only time you would be using PHP is with a dedicated machine, as you have to have tons of programs (like Apache and Lampp) with dedicated ips and ports, and data systems to probably cache the data (you don't want to call a mysql database every pageload, especially if there are lots of people using your site). That being said, there are tons of datasystems like MongoDB or SqLite which would probably fulfil your needs, and are much better at storing only the two values you wanted to store. If you were saving 30 stats per player, mysql is probably the better choice, but with two stats, you might as well be using YML. I commend you for the tutorial, I would probably not have even dreamed of coding Java at 13 haha. Next time, though, you should do some more research into other database systems, as there are many which suit your needs.

    Cheers,
    ccrama
     
  18. Offline

    Build_and_Break

    I really do hate to grave dig, and the hate replies on this post are not nice, this is an amazing tutorial (yet short.) tyvm!
     
    Aphex124, ski23 and ChipDev like this.
Thread Status:
Not open for further replies.

Share This Page