[Tutorial] [Advanced] Creating a Lag Free MySQL Query

Discussion in 'Resources' started by SuperOmegaCow, Feb 9, 2014.

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

    SuperOmegaCow

    I will be showing you how to create a lag free way of making a mysql query. Most of this code has come to be due to very helpful people from the spigot and bukkit irc. Before we start, I just want to say that I will not be covering the basics of MySQL and will expect that you know how to make prepared statements, result sets etc.

    For this tutorial we will be using connection pooling. Connection pooling is required due to the fact that multiple threads can't use the same connection at the same time. To accomplish this connection pooling we will be C3p0 which can be found here.

    Now to actually connect to the database just simply load the drivers for MySQL and then do this:

    Code:java
    1. DataSource pool = DataSources.unpooledDataSource("jdbc:mysql://" + hostname + "/" + database, user, password);
    2.  
    3. connection = pool.getConnection();


    Just to restate this, THE DRIVERS MUST BE LOADED BEFORE DOING THIS.

    Now lets actually get started on that query. First we shall be creating a class called ThreadDown which implements Runnable:

    Code:java
    1. public class ThreadDown implements Runnable {
    2.  
    3. public ThreadDown() {
    4.  
    5.  
    6.  
    7. }
    8.  
    9. }


    Now to actually start the thread we need to implement a runnable and in the constructor a thread. We also need to give the callback data that is should look for in the database.

    Code:java
    1. public class ThreadDown implements Runnable {
    2.  
    3. private String uuid;
    4.  
    5. private PreparedStatement sql = null;
    6.  
    7. private ResultSet res = null;
    8.  
    9. private boolean running = false;
    10.  
    11. private Object[] returnedData;
    12.  
    13. public ThreadDown(String playerUUID) {
    14.  
    15. this.uuid = playerUUID;
    16.  
    17. Thread thread = new Thread(this);
    18.  
    19. thread.start();
    20.  
    21. }
    22.  
    23. @Override
    24.  
    25. public void run() {
    26.  
    27. }
    28.  
    29. }


    Now lets make it so when the process starts and stops, we call an event that we can listen to. For this I will not be explaining it as I assume you know how to do this:

    Code:java
    1. public class ThreadDownEvent extends Event {
    2.  
    3. private static HandlerList handlers = new HandlerList();
    4.  
    5. private boolean running;
    6.  
    7. private ThreadDown thread;
    8.  
    9. private Object[] data;
    10.  
    11. public ThreadDownEvent(boolean running, ThreadDown thread, Object[] data) {
    12.  
    13. this.running = running;
    14.  
    15. this.thread = thread;
    16.  
    17. this.data = data;
    18.  
    19. }
    20.  
    21. public HandlerList getHandlers() {
    22.  
    23. return handlers;
    24.  
    25. }
    26.  
    27. public static HandlerList getHandlerList() {
    28.  
    29. return handlers;
    30.  
    31. }
    32.  
    33. public boolean getRunning() {
    34.  
    35. return this.running;
    36.  
    37. }
    38.  
    39. public ThreadDown getThread() {
    40.  
    41. return this.thread;
    42.  
    43. }
    44.  
    45. public Object[] getDataProcessed() { return this.data; }
    46.  
    47. }


    Now we will call the event when the thread starts and stops:

    Code:java
    1. @Override
    2.  
    3. public void run() {
    4.  
    5. this.running = true;
    6.  
    7. Bukkit.getPluginManager().callEvent(new ThreadDownEvent(this.running, this, this.returnedData));
    8.  
    9.  
    10.  
    11. this.running = false;
    12.  
    13. Bukkit.getPluginManager().callEvent(new ThreadDownEvent(this.running, this, this.returnedData));
    14.  
    15. }


    Now lets first check if the player is in the database:

    Code:java
    1. sql = Main.getInstance().c.prepareStatement("SELECT * FROM `yourdatabase` WHERE user_id=? LIMIT 1;");
    2.  
    3. sql.setString(1, this.uuid);
    4.  
    5. res = sql.executeQuery();
    6.  
    7. if (res.next()) {
    8.  
    9. contains = true;
    10.  
    11. }


    Now that we know that the player is in the database we can get their data and return an object array. In my case my database contains the player's uuid, their kills and their deaths.

    In the case that the database contains the player:

    Code:java
    1. if (contains) {
    2.  
    3. sql = Main.getInstance().c.prepareStatement("SELECT * FROM `yourdatabase` WHERE user_id=?;");
    4.  
    5. sql.setString(1, this.uuid);
    6.  
    7. res = sql.executeQuery();
    8.  
    9. res.next();
    10.  
    11. Object[] s = {res.getString("user_id"), res.getInt("kills"), res.getInt("deaths")};
    12.  
    13. this.returnedData = s;
    14.  
    15. }


    The code simply gets their uuid, kills and deaths and puts it into an object array.

    Now if the player is not in the database we will return their uuid with 0 kills and 0 deaths:

    Code:java
    1. } else {
    2.  
    3. Object[] k = {this.returnedData, 0, 0};
    4.  
    5. this.returnedData = k;
    6.  
    7. }


    Now to finish this off we should always close our PreparedStatements and ResultSets by doing this:

    Code:java
    1. if(sql != null) {
    2.  
    3. sql.close();
    4.  
    5. }
    6.  
    7. if(res != null) {
    8.  
    9. res.close();
    10.  
    11. }


    Now that we have finished the actual sql stuff ,we need to actually do something with the data that we got from the database. In my case, I save this data to a config:

    Code:java
    1. @EventHandler
    2.  
    3. public void onThreadDown(ThreadDownEvent event) {
    4.  
    5. if(!event.getRunning()) {
    6.  
    7. Object[] data = event.getDataProcessed();
    8.  
    9. FileConfiguration config = this.getConfig();
    10.  
    11. if(config.getString("players." + data[0]) != null) {
    12.  
    13. config.set("players." + data[0] + ".kills", data[1]);
    14.  
    15. config.set("players." + data[0] + ".deaths", data[2]);
    16.  
    17. }
    18.  
    19. }
    20.  
    21. }


    Now simply to start this thread of ours we can call this class when a player join:

    Code:java
    1. new ThreadDown(p.getUniqueId().toString());


    Full ThreadDown class with imports:

    Code:java
    1. import com.SuperOmegaCow.TheCube.Events.ThreadDownEvent;
    2. import com.SuperOmegaCow.TheCube.Main;
    3. import org.bukkit.Bukkit;
    4.  
    5. import java.sql.PreparedStatement;
    6. import java.sql.ResultSet;
    7.  
    8. public class ThreadDown implements Runnable {
    9.  
    10. private String uuid;
    11. private PreparedStatement sql = null;
    12. private ResultSet res = null;
    13. private boolean running = false;
    14. private Object[] returnedData;
    15.  
    16.  
    17. public ThreadDown(String playerUUID) {
    18.  
    19. this.uuid = playerUUID;
    20. Thread thread = new Thread(this);
    21. thread.start();
    22. }
    23.  
    24. @Override
    25. public void run() {
    26. this.running = true;
    27. Bukkit.getPluginManager().callEvent(new ThreadDownEvent(this.running, this, this.returnedData));
    28. Boolean contains = false;
    29.  
    30. try {
    31. sql = Main.getInstance().c.prepareStatement("SELECT * FROM `yourdatabase` WHERE user_id=? LIMIT 1;");
    32. sql.setString(1, this.uuid);
    33. res = sql.executeQuery();
    34. if (res.next()) {
    35. contains = true;
    36. }
    37.  
    38. if (contains) {
    39. sql = Main.getInstance().c.prepareStatement("SELECT * FROM `yourdatabase` WHERE user_id=?;");
    40. sql.setString(1, this.uuid);
    41. res = sql.executeQuery();
    42. res.next();
    43. Object[] s = {res.getString("user_id"), res.getInt("kills"), res.getInt("deaths")};
    44. this.returnedData = s;
    45. } else {
    46. Object[] k = {this.uuid, 0, 0};
    47. this.returnedData = k;
    48. }
    49. } catch (Exception e) {
    50. e.printStackTrace();
    51. } finally {
    52. try {
    53. if (sql != null) {
    54. sql.close();
    55. }
    56. if (res != null) {
    57. res.close();
    58. }
    59. } catch (Exception e) {
    60. e.printStackTrace();
    61. }
    62. }
    63.  
    64.  
    65. this.running = false;
    66. Bukkit.getPluginManager().callEvent(new ThreadDownEvent(this.running, this, this.returnedData));
    67.  
    68. }
    69.  
    70. }


    Reserved for a thread up tutorial.

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

    KraZ__

    THANK YOU SO MUCH! :D
     
  3. Offline

    RawCode

    entire "lagless" mysql implementation is:

    1) Request data
    2) Process in async
    3) Return to this at later time

    you dont need large amount of support classes or additional libraries for this.
     
    Stoux and L33m4n123 like this.
  4. Offline

    SuperOmegaCow

    RawCode well this is async... And when it is done it calls an event... No lag... And the fact that a connection can't be accessed by multiple threads at the same time...
     
  5. Offline

    RawCode

    1. You create singletron database connection inside separate thread with something like while(true) inside. (ONLY SINGLE THREAD AT TIME)
    2. dont dos your mysql, request only when needed and add some thread sleep into loop. (Thread sleep can be variable, elso you can put thread to complete sleep and interrupt when needed)
    3. your async thread backed by IO pump (blockless hashtable or atomic list or something similar) (google implementation or code your own)
    4. dont use sync blocks, they will lock main thread for some time, this will cause lags ever if async thread used (if implementer properly, no race conditions will be possible ever if multiple threads IO same object at same time)
    5. inside your real code you request data or sent data to mysql via adding command to atomic list or reading from "atomic" hashtable
    6. as many threads as you want can IO at same time without conflicts, but not directly - via IO pump wrapper.
     
  6. Offline

    calebbfmv

    I simply create a new Thread for running my SQL on it. Don't worry, I keep it clean.
     
  7. SuperOmegaCow I've got a question: how can I use/reference the c3p0 jar, without putting it in my plugins folder/how are you getting the classes from the c3p0 jar
     
  8. Offline

    IDragonfire

  9. Offline

    CeramicTitan

    Maven!
     
Thread Status:
Not open for further replies.

Share This Page