Connecting to databases - MySQL

May 27, 2017
Connecting to databases - MySQL
  • Connecting to databases - MySQL

    How to work with MySQL databases





    What are databases?(top)


    As you delve into the depths of programming, you will come across times when you realize that it would be a lot more convenient for both the user and the developer to store data in tables like you see in spreadsheets instead of creating all sorts of wrapper objects, hashmaps of hashmaps of hashmaps and so on. This is where databases come in handy. Now, I'm sure most of us have heard of the term "database". It just seems to be that. A base full of data. But how are they stored? They must be stored in some efficient manner for them to even be considered an option over regular files. Data in MySQL servers are stored in tables similar to this one:

    [​IMG]

    Looking at the image, I think you might be seeing how this comes in handy. Data is stored neatly as entries or rows of data. Each kind of data is classified under each column. In this tutorial, I will be showing you how to connect your plugin to a MySQL database. Note, however, that you will still have to learn how to program using the MySQL programming syntax. Here is a good tutorial which I used to learn the basics.
    Now to clear up, what exactly are databases? Well, a database in MySQL is a single table such as that one up there. It is a table with columns and rows possessing data. Each column has an expected type of data such as a Date, Int, etc. The general SQL data types are listed here.

    Setting up a Connection(top)


    First of all, you will need to ensure that you have:
    • Hostname - the ip address where the database is being stored
    • Port - the port on the ip address on which the database is being hosted
    • Database - which database to use because a server can have multiple databases
    • Username - the username with which to use in connecting to the database
    • Password - similar to the username, it is used for authentication purposes
    Ensure that you have those 5 and we can get started with setting up a connection.

    Code (Java):
    public class Test extends JavaPlugin {
     
        private Connection connection;
        private String host, database, username, password;
        private int port;
     
        @Override
        public void onEnable() {  
            host = "localhost";
            port = 3306;
            database = "BukkitCoding";
            username = "root";
            password = "123";    
        }
     
        @Override
        public void onDisable() {
        }
     
    }
    This is what your main plugin class should generally have at this point. Note that for the host, port, database, username, and password, those are just examples. You should change it to whatever is the actual information needed to connect to your MySQL server. It is a good idea to make this configurable so you do not have to re-compile the plugin every time the database information changes. If you plan on releasing the plugin having this configurable is needed.

    You may notice this variable I declared:
    Code (Java):
    private Connection connection;
    Now what this is, is an instance of the 'java.sql.Connection' class. This is what we will be using to connect. Before we get on to that, we will first ensure that we have the necessary requirements to connect to a MySQL server. Here is a method that we can use to return a "safe" Connection instance:

    Code (Java):
    public void openConnection() throws SQLException, ClassNotFoundException {
        if (connection != null && !connection.isClosed()) {
            return;
        }
     
        synchronized (this) {
            if (connection != null && !connection.isClosed()) {
                return;
            }
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://" + this.host+ ":" + this.port + "/" + this.database, this.username, this.password);
        }
    }
    What this essentially does is check if the system that it is running on has installed the jdbc driver for MySQL. After it performs the check, it will then attempt to get the Connection using the DriverManager.getConnection method which is using the 'java.sql.DriverManager' class to return a connection with the given information.
    This method can be modified as necessary in order to make things easier such as making a separate class to handle all your MySQL methods.
    Here is what your main class should now have :

    Code (Java):

    public class Test extends JavaPlugin {
     
        private Connection connection;
        private String host, database, username, password;
        private int port;
     
        @Override
        public void onEnable() {
            host = "localhost";
            port = 3306;
            database = "TestDatabase";
            username = "user";
            password = "pass";    
            try {    
                openConnection();
                Statement statement = connection.createStatement();          
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
     
        @Override
        public void onDisable() {
        }
     
        public void openConnection() throws SQLException, ClassNotFoundException {
        if (connection != null && !connection.isClosed()) {
            return;
        }
     
        synchronized (this) {
            if (connection != null && !connection.isClosed()) {
                return;
            }
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://" + this.host + ":" + this.port + "/" + this.database, this.username, this.password);
        }
    }
     
    Now what we did here was successfully setup our connection to the MySQL server. Now what can we do to send commands to the server to get or set data? This is where the 'java.sql.Statement' instance comes in handy. As you see, there is a Statement variable that I created using connection.createStatement(). This returns a statement which you can use to send commands. Note that at this point, you should already know how to program in SQL such as through the tutorial I mentioned earlier.

    Statements - Getting and Setting Data(top)


    Using statements, you can execute commands to the server to perform a query in order to retrieve data stored in the database or you can set data such as adding new columns, creating new entries, or editing existing ones.

    Getting Data(top)


    Supposing we had a table with two columns, 'PLAYERNAME' and 'BALANCE', we will be retrieving all entries of players with empty balances.

    Code (Java):
    ResultSet result = statement.executeQuery("SELECT * FROM PlayerData WHERE BALANCE = 0;");
    List<String> bankruptPlayers = new ArrayList<String>();
    while (result.next()) {
        String name = result.getString("PLAYERNAME");
        bankruptPlayers.add(name);
    }
    Now you will see that I have created a ResultSet object from executing a query from the statement variable. When executing queries, it accepts a single String as a parameter which should contain your entire query. This will then return a ResultSet. A ResultSet is basically a special object which acts as a set containing all the data returned from that query if any at all. I created a while loop with the method result.next() as its condition. What this method does is move the set of values that you are viewing one step forward. If there is an available entry, it returns true. Note that when you create the ResultSet directly from a query, you should call result.next() to make it move to the first entry. Inside the loop, I called resultset.getString("PLAYERNAME"). This gets a String value from that entry under the column named 'PLAYERNAME'. Note that there are several different get methods for ResultSet which should be used in coordination with the expected data type of the specified column. This is basically all that you need to do to get data from MySQL tables.

    Setting Data(top)


    Using the same example table as in the Getting Data tutorial, we will be setting an entry of a player. Unlike the getters, this is pretty much a simple one-liner. We need the String name of the player and the int balance.
    Code (Java):
    statement.executeUpdate("INSERT INTO PlayerData (PLAYERNAME, BALANCE) VALUES ('Playername', 100);");
    Using this, we are setting the data of a player named 'Playername' with a balance of 100. This is basically all there is to setting data for MySQL servers.


    Asynchronous Database Interaction(top)


    Asynchronous database interaction is vital for an efficient plugin. Making connections to the database and retrieving/updating information in the database takes time. This is because it has to transfer the data through the internet from the server to the database. Doing this on the main thread will block it up and will cause large amounts of lag.

    So, what is this Asynchronous thing?
    Asynchronous essentially means off the main thread. The main thread is where all the game logic is ran, updating entities, block breaking, block placing. By doing IO (Input/Output) on the main thread it blocks it until the IO is finished before it can continue. This means that while you are waiting for the mysql server to send back some data, or for the mysql server to receive your data, nothing else can be happening on the server, everything is paused. This is a common cause of lag spikes!
    Running things asynchronously is running them off of this thread. This means that it doesn't block the main thread and everything can keep running while you are waiting to recieve some data.

    So, how do i do it?
    Running things asynchronously is easy, all you have to do is make a new runnable.
    Code (Java):
    BukkitRunnable r = new BukkitRunnable() {
        @Override
        public void run() {
            //This is where you should do your database interaction
        }
    }
    What we are doing here is creating a new instance of BukkitRunnable. BukkitRunnable is a uility class made by bukkit to make scheduling tasks easier. We can now run this Runnable asynchronously easily, just call the BukkitRunnable#runTaskAsynchronously(Plugin) method.
    Code (Java):
    r.runTaskAsynchronously(<Instance of your plugin>);
    You should replace <Instance of your plugin> with an instance of your plugins main class. This could be stored in a variable or if you are doing the database interaction in your main class you can use the java keyword "this".

    So, now all you have to do is place your database interaction code into the run() method. You should now end up with something like this:
    Code (Java):
    BukkitRunnable r = new BukkitRunnable() {
       @Override
       public void run() {
          try {
             openConnection();
             Statement statement = connection.createStatement();
          } catch(ClassNotFoundException e) {
             e.printStackTrace();
          } catch(SQLException e) {
             e.printStackTrace();
          }
       }
    };
     
    r.runTaskAsynchronously(this);
    not that difficult, you should make
    Running database interaction asynchronously is very important and is sure to do it whenever you use databases.

    MySQL servers are very useful when you know how to use them. What you learn here can help kickstart your career into working with databases. :)

    Useful Tips(top)


    • If you are saving a lot of minor changes e.g. economy updates, use a cache on your server. Avoid sending too many updates to the server as this may cause lag especially if you are running the process on the main server thread.
  • Loading...
  • Loading...