MySQL database integration with your plugin.

Apr 7, 2017
MySQL database integration with your plugin.
  • Most of this tutorial is completed, if you find anything missing please feel free to add it but there is no need to attach your name at the end because the editors section already does that.

    Tutorial status: Semi-Finished (Updates may be required)
    Last update: 7th April 2017.


    Prerequisites

    You need to install JDBC first, it's available from: http://dev.mysql.com/downloads/connector/j/5.1.html#downloads

    Basic explanation on JDBC
    JDBC is Java Database Connectivity API. We use the J connector which let's you connect to mySQL databases and run mySQL queries in the Java language.

    Setup
    Select .zip if you run windows, select .tar if you have a mac/linux.
    Extract it with a program like Winrar or 7Zip.

    Once that's done, we're going to want to add it to our project.
    [​IMG]

    You add it the same way you added Craftbukkit.jar into your Java Build path. So first select Java build path and then click on libraries, and then click on add external jars. Locate to the place you downloaded the JDBC connector and click OK.

    For you maven users
    It is available on maven-central, so you just need to add the dependency to your pom (no need for repo):
    Code (Text):
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.41</version>
    </dependency>
    Coding
    My favorite part, the coding!

    We must create a few variables that can let us connect to the database. This is what we need to create. (Note, these are field variables not local variables).

    Code (Java):
    //DataBase vars.
    final String username="YOUR DB USERNAME"; //Enter in your db username
    final String password="YOUR DB PASSWORD"; //Enter your password for the db
    final String url = "jdbc:mysql://db4free.net:3306/DataBaseName"; //Enter URL w/db name

    //Connection vars
    static Connection connection; //This is the variable we will use to connect to database
    Now we need to connect to the database using the Connection variable we made that is provided by Java. We do this by assigning properties to our connection variable in the onEnable and then trying to connect. This is the code we need to use:

    Again, put this code in the onEnable(); method because we're trying to connect as soon as we start the server.
    Code (Java):
    try { //We use a try catch to avoid errors, hopefully we don't get any.
        Class.forName("com.mysql.jdbc.Driver"); //this accesses Driver in jdbc.
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
        System.err.println("jdbc driver unavailable!");
        return;
    }
    try { //Another try catch to get any SQL errors (for example connections errors)
        connection = DriverManager.getConnection(url,username,password);
        //with the method getConnection() from DriverManager, we're trying to set
        //the connection's url, username, password to the variables we made earlier and
        //trying to get a connection at the same time. JDBC allows us to do this.
    } catch (SQLException e) { //catching errors)
        e.printStackTrace(); //prints out SQLException errors to the console (if any)
    }
    Now it's time to let our plugin close the connection, since we don't want useless connections still lingering around. We're going to do this by closing the connection variable in the onDisable() method. So when the server closes, we don't need the connection to still be there anymore.

    Code (Java):
    public void onDisable() {
        // invoke on disable.
        try { //using a try catch to catch connection errors (like wrong sql password...)
            if (connection!=null && !connection.isClosed()){ //checking if connection isn't null to
                //avoid receiving a nullpointer
                connection.close(); //closing the connection field variable.
            }
        } catch(Exception e) {
            e.printStackTrace();
        }
    }
    If you can seem to get a connection established, then you can execute queries and updates. Here's a quick tip, whenever you want to modify the database, use stmt#executeUpdate(), but if you just want to grab information from the database, simply do stmt#executeQuery().

    First, we must create a table.

    Code (Java):
    String sql = "CREATE TABLE IF NOT EXISTS myTable(Something varchar(64));";
    // prepare the statement to be executed
    try {
        PreparedStatement stmt = connection.prepareStatement(sql);
        // I use executeUpdate() to update the databases table.
        stmt.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    After that, we are all set to update and query from the database.

    Code (Java):
    String sql = "SELECT * FROM myTable WHERE Something='Something'";
    PreparedStatement stmt = connection.prepareStatement(sql);
    ResultSet results = stmt.executeQuery();
    if (!results.next()) {
        System.out.println("Failed");
    } else {
        System.out.println("Success");
    }
    This code would print out "Failed" because we have no values to select. Lets add some now.

    Code (Java):
    String sql = "INSERT INTO myTable(Something) VALUES ('?');";
    PreparedStatement stmt = connection.prepareStatement(sql);
    stmt.setString(1, "Something"); //I set the "?" to "Something"
    stmt.executeUpdate();
    Now if we where to rerun this code right here:

    Code (Java):
    String sql = "SELECT * FROM myTable WHERE Something='Something'";
    PreparedStatement stmt = connection.prepareStatement(sql);
    ResultSet results = stmt.executeQuery();
    if (!results.next()) {
        System.out.println("Failed");
    } else {
        System.out.println("Success");
    }
    We would get "Success" because a value has been inserted and selected for us to view.
  • Loading...
  • Loading...
(27844 Views)
Last Modified: Apr 7, 2017 at 4:08 PM
XenCarta PRO © Jason Axelrod from 8WAYRUN.COM