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

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

 DataSource pool = DataSources.unpooledDataSource("jdbc:mysql://" + hostname + "/" + database, user, password);
 
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

public class ThreadDown implements Runnable {
 
public ThreadDown() {
 
 
 
}
 
}

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

public class ThreadDown implements Runnable {
 
private String uuid;
 
private PreparedStatement sql = null;
 
private ResultSet res = null;
 
private boolean running = false;
 
private Object[] returnedData;
 
public ThreadDown(String playerUUID) {
 
this.uuid = playerUUID;
 
Thread thread = new Thread(this);
 
thread.start();
 
}
 
@Override
 
public void run() {
 
}
 
}

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

public class ThreadDownEvent extends Event {
 
private static HandlerList handlers = new HandlerList();
 
private boolean running;
 
private ThreadDown thread;
 
private Object[] data;
 
public ThreadDownEvent(boolean running, ThreadDown thread, Object[] data) {
 
this.running = running;
 
this.thread = thread;
 
this.data = data;
 
}
 
public HandlerList getHandlers() {
 
return handlers;
 
}
 
public static HandlerList getHandlerList() {
 
return handlers;
 
}
 
public boolean getRunning() {
 
return this.running;
 
}
 
public ThreadDown getThread() {
 
return this.thread;
 
}
 
public Object[] getDataProcessed() { return this.data; }
 
}

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

Code:java

 @Override
 
public void run() {
 
this.running = true;
 
Bukkit.getPluginManager().callEvent(new ThreadDownEvent(this.running, this, this.returnedData));
 
 
 
this.running = false;
 
Bukkit.getPluginManager().callEvent(new ThreadDownEvent(this.running, this, this.returnedData));
 
}

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

Code:java

 sql = Main.getInstance().c.prepareStatement("SELECT * FROM `yourdatabase` WHERE user_id=? LIMIT 1;");
 
sql.setString(1, this.uuid);
 
res = sql.executeQuery();
 
if (res.next()) {
 
contains = true;
 
}

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

 if (contains) {
 
sql = Main.getInstance().c.prepareStatement("SELECT * FROM `yourdatabase` WHERE user_id=?;");
 
sql.setString(1, this.uuid);
 
res = sql.executeQuery();
 
res.next();
 
Object[] s = {res.getString("user_id"), res.getInt("kills"), res.getInt("deaths")};
 
this.returnedData = s;
 
}

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

} else {
 
Object[] k = {this.returnedData, 0, 0};
 
this.returnedData = k;
 
}

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

Code:java

 if(sql != null) {
 
sql.close();
 
}
 
if(res != null) {
 
res.close();
 
}

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

 @EventHandler
 
public void onThreadDown(ThreadDownEvent event) {
 
if(!event.getRunning()) {
 
Object[] data = event.getDataProcessed();
 
FileConfiguration config = this.getConfig();
 
if(config.getString("players." + data[0]) != null) {
 
config.set("players." + data[0] + ".kills", data[1]);
 
config.set("players." + data[0] + ".deaths", data[2]);
 
}
 
}
 
}

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

Code:java

new ThreadDown(p.getUniqueId().toString());

Full ThreadDown class with imports:

Code:java

import com.SuperOmegaCow.TheCube.Events.ThreadDownEvent;
import com.SuperOmegaCow.TheCube.Main;
import org.bukkit.Bukkit;
 
import java.sql.PreparedStatement;
import java.sql.ResultSet;
 
public class ThreadDown implements Runnable {
 
private String uuid;
private PreparedStatement sql = null;
private ResultSet res = null;
private boolean running = false;
private Object[] returnedData;
 
 
public ThreadDown(String playerUUID) {
 
this.uuid = playerUUID;
Thread thread = new Thread(this);
thread.start();
}
 
@Override
public void run() {
this.running = true;
Bukkit.getPluginManager().callEvent(new ThreadDownEvent(this.running, this, this.returnedData));
Boolean contains = false;
 
try {
sql = Main.getInstance().c.prepareStatement("SELECT * FROM `yourdatabase` WHERE user_id=? LIMIT 1;");
sql.setString(1, this.uuid);
res = sql.executeQuery();
if (res.next()) {
contains = true;
}
 
if (contains) {
sql = Main.getInstance().c.prepareStatement("SELECT * FROM `yourdatabase` WHERE user_id=?;");
sql.setString(1, this.uuid);
res = sql.executeQuery();
res.next();
Object[] s = {res.getString("user_id"), res.getInt("kills"), res.getInt("deaths")};
this.returnedData = s;
} else {
Object[] k = {this.uuid, 0, 0};
this.returnedData = k;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (sql != null) {
sql.close();
}
if (res != null) {
res.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
 
 
this.running = false;
Bukkit.getPluginManager().callEvent(new ThreadDownEvent(this.running, this, this.returnedData));
 
}
 
}

Reserved for a thread up tutorial.

EDIT by Moderator: merged posts, please use the edit button instead of double posting.

 

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.

 

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...

 

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.

 

I simply create a new Thread for running my SQL on it. Don't worry, I keep it clean.

 

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

 

My question is, why nobody use the persistence layer from bukkit?
https://forums.bukkit.org/threads/persistence-databases-bukkit-tutorial.12404/
http://forums.bukkit.org/threads/bukkit-persistance-reimplemented-no-code-changes-required.24987/

 

Maven!