Solved How to get started on MySQL?

Hi,

I know all the MySQL stuff, the queries and commands, infact I've worked with mySQL and JDBC before, it's just that. When I access the loaded MySQL db a lot, sometimes it can lower the tps of my server.

I've heard about a system where there is tasks on a separate thread. Then you just add the tasks, and it just does it at it's own pace not slowing down the server's tps or anything like that?

Can anyone point me in a direction so I can achieve that?

Thanks,
Neil

 

Async

 

When you make connections, make sure you do so on a different Thread.

 

onEnable() method:
So I start a new thread, asynchronously.

Code:

mysqlConnection = new MysqlConnection(this);
            new BukkitRunnable(){
                @Override
                public void run() {
                    Thread thread = new Thread(mysqlConnection);
                    thread.start();
                }
            }.runTaskAsynchronously(plugin);

MysqlConnection run() method:

Code:

@Override
    public void run() {
        try {
            openConnection();
        } catch (SQLException e) {e.printStackTrace()}
    }

What I'm trying to achieve: I'm trying to make a plugin that basically connects to a mysql database, gets how much money a player has. I'm not sure how to get that value in a safe async manner.

The problem: I do not fathom how I get the value in a safe async manner. Must I always run a thread in my 'mysqlConnection' object to get a resultset?

 

@Neilnet

By using something called callbacks. Alternatively, you have lazy data or you can reschedule work on the main thread. Those are the three ways you could do it, off the top of my head.

Why are you manually creating a Thread? There's literally no reason to do that in Java.

 

Yeah I see now,
@mythbusterma

But I don't know how to get started on that, this is what I'm thinking:
onEnable() > open mysql connection
in the onEnable(),

Code:

getServer().getScheduler().runTaskAsynchronously(plugin, new Runnable(){
                public void run() {
                    try {
                        connection = DriverManager.getConnection(hostname, user, pass);
                        System.out.println("[VoteShopTokens] Connection to Database: Successful");
                    } catch (SQLException e) {
                        System.out.println("[VoteShopTokens] Failed DB connection. Check your mySQL details!");
                        e.printStackTrace();
                    }
                }

            });

Is there any tutorials on call backs?

 

@1Rogue

The only reason you would create a Thread manually in Java is if you were writing your own task executor, when the Java API provides perfectly good executors, and there's many more you can find online.

So, I should say, "there's no good reason to create a Thread manually in Java unless you're creating a task executor, which you really shouldn't be."


@Neilnet

If initilising the plugin relies on information obtained from the database, you can request that information synchronously in the onEnable() method, as taking a little extra time there doesn't really matter. I suppose I could really quickly write a call back.

Code:

public class Callbackee {
   
    private Map <Key, Value> map;

    public Value requestInformation(Key key) {
         synchronized (map) {
               return map.get(key);
         }
     }
    

     public void updateValue(Key key, Value value) { // this is the "callback" method
         synchronized(map) {
               map.put(key,value);
          }
      }
}

Then, in a class that accesses SQL:

Code:

public void accessInformation(Callbackee callback) {
     // get the information and store in key, value

     callback.updateValues(key, value);
}

 

Code:

public class Callbackee {
    private Map <Key, Value> map;
    public Value requestInformation(Key key) {
         synchronized (map) {
               return map.get(key);
         }
     }
     public void updateValue(Key key, Value value) { // this is the "callback" method
         synchronized(map) {
               map.put(key,value);
          }
      }
}
}

[/QUOTE]

Thanks for the demo, so essentially I'm just gonna load in all the mysql in the onEnable and then whenever I want to access it, I just updateValues then get the data?

EDIT:
This stuff seems a tad bit hard, is there any library that can do this? I mean, I want to incorporate mySQL into my plugin without it making my server lag.

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

 

@Neilnet

No. If you can't understand how to do it, then you don't need SQL.

I gave an example of what a callback might be, that's not anywhere something that should be included in any plugin.

 

I think I understand callbacks, I've been watching a tutorial. Too bad it's in German.


This is in German, but the video is about callbacks? In 21:54, would that be a proper use of a callback?

 

@Neilnet

Why not just schedule work to be done on the main thread after you've gotten your information? That seems easiest to me.

 

What do you mean? If I run work on the main thread, the server gets all laggy.

So I understand callbacks,

Code:

public int getTokens(){
            playerBalance = SQLBase.getTokens(uid, new Callback<String>(){
                public void onSuccess(String tokens) {
                   //How would I return tokens to the base method getTokens in this encapsulated space?
                }
                public void onFailure(Throwable cause) {
                   
                }
            });
}

How would I return tokens to the base method getTokens in this encapsulated space?

 

@Neilnet

You update a structure or field after it completes, by scheduling work to be done on the main thread.

 

@mythbusterma

This right here gets the amount of tokens the player has.

Code:

    public static void getTokens(String uid, Callback<String> callback){
        new Thread(new Runnable(){
            public void run() {
                try {
                    String query = "SELECT `tokens` FROM `vtokens` WHERE username='"+uid+"' LIMIT 1";
                    PreparedStatement ps = con.prepareStatement(query);
                    ResultSet rs = ps.executeQuery();

                    if(rs.next()){
                        int tokens = rs.getInt("tokens");
                        callback.onSuccess(tokens);
                    }
                } catch(SQLException e){
                    e.printStackTrace();
                    callback.onFailure(-1);
                }
            }
        }).start();
    }

When I access getTokens, I set the values in a hashmap.

Code:

            SQLBase.getTokens(uid, new Callback<String>(){
                @Override
                public void onSuccess(int tokens) {
                   playerTokenMap.put(uid, tokens); //Gets added to hashmap
                }
                @Override
                public void onFailure(int i) {
                    playerTokenMap.put(uid, -1);
                }
            });
            playerBalance = playerTokenMap.get(uid);
        }
        return playerBalance;

Well, it works. Thanks for all your help so far, although the value is first -1, then I have to recall the method getBalance to get the balance, is this a concurrency problem?

 

Bump

 

@Neilnet

You would have something that constantly checks the value on the main thread, or you could submit work to be done with the variables on the main thread.

 

Though this is a get method, it has to get something through that callback and return it, so I don't see how that is feasible.

Solved! Thanks @mythbusterma

I basically just used different methods that executed things through the callbacks rather than using getters for the MySQL.

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