MySQL when to pull data

Hello,

I am writing a plugin which uses MySQL - which I am not very well versed in - and I was wondering,
how often can I pull data from my database? Right now I am reading from my database a lot. I use it in different events like the EntityDamageByEntityEvent and the BlockBreakEvent.
Is this bad?
Should I save my data in variables or should I save them locally and only make changes to the database in intervals?

Thank you.

 

It's definitely bad, as MySQL's slow when sending out requests. You should cache data globally in for example a Map, and yes as you said, at certain intervals, send out the data from the cache.

Pulled from the thread about common mistakes:

 

So you mean I should write to a local file and take the information from there, then update the MySQL database each 10 or so seconds?
I have player-based information though, so I would need to make files for each player (or keep it in one big file).
Then what is the point of the database?
Sorry if this is an uneducated question. I'm not that well-versed in MySQL, as I just learnt it a few days ago.

 

I'm absolutely terrible with MySQL myself, and to be honest, I despise its syntax hence why I don't bother learning it properly. I just mess around in PhPMyAdmin with queries to test what to do. Anyway, no, you don't make a file, you just store the data in a Map, and every 15 seconds or so (maybe even this is too short - a minute?), send the queries.

An example would be like this:

Code:

public class CachedPlayer {
    private final UUID playerUUID;
    private final String playerName;

    private int blocksBroken = 0;
   
    public CachedPlayer(Player player) {
        this.playerName = player.getName();
        this.playerUUID = player.getUniqueId();
    }

    public int getBlocksBroken() {
        return this.blocksBroken;
    }
   
    public Player getPlayer() {
        return Bukkit.getServer().getPlayer(this.playerUUID);
    }

    public String getUsername() {
        return this.playerName;
    }

    public void setBlocksBroken(int blocksBroken) {
        this.blocksBroken = blocksBroken;
    }
}

Then a cached player manager:

Code:

public class CacheManager {
    private static Map<String, CachedPlayer> cachedPlayers = new HashMap<>();
   
    public static CachedPlayer getPlayer(String playerName) {
        return cachedPlayers.get(playerName);
    }

    public static List<CachedPlayer> getPlayers() {
        return new ArrayList<>(cachedPlayers.values());
    }
}

Then, every 30 seconds or w/e, loop through every CachedPlayer in the list, and execute queries to update the values. One flaw to this is that if the plugin is disabled, the queries won't be executed in time. My only solution to this is that you serialize all the CachedPlayer instances into a config temporarily, and when the plugin starts up, deserialize all of the player caches and store them again, and also execute the update queries, then delete the cache file.

 

Just retrieve any relevant info to a player you need on login, store it in an object, and update it in the database when they quit.

 

Ah I see. Very neat. I was thinking storing data to a map would be a bad idea, seeing as though a crash could cause data loss, but this should work.
I'll try it.
Thank you very much!

@KingFaris11
Hello, I've now tried to create my own cache. I've not used your full idea, but more something alike 1Rogue's idea.
I make my setters and getters like this:

Code:

    final UUID playerUUID;
    final String playerName;
    int profession01;
    int profession02;

  public TobPlayer(Player player){
        this.playerUUID = player.getUniqueId();
        this.playerName = player.getName();
    }

public void setProfession(int profession, int professionColumn){
        if(professionColumn == 1){
            this.profession01 = profession;
        } else if(professionColumn == 2){
            this.profession02 = profession;
        }
    }

    public int getProfession(int professionColumn){
        if(professionColumn == 1){
            return profession01;
        } else if(professionColumn == 2){
            return profession02;
        }
        return 0;
    }

Then when I try to access it by creating an instance of my TobPlayer it doesn't seem to update the number?

Code:

        TobPlayer tobPlayer = new TobPlayer(p);

        //Profession 2 is already set
        if(tobPlayer.getProfession(2) != 0){
            p.sendMessage(ChatColor.DARK_RED + "Both your professions are already set!");
            return;
        }

        if(tobPlayer.getProfession(1) != 0){
            //Profession 1 is already set. Setting profession 2
         
            tobPlayer.setProfession(profession.getId(), 2);
            p.sendMessage(ChatColor.GREEN + "Your successfully set your second profession to " + profession.getName());
            return;
        }

        //Setting profession 1 to profession
        tobPlayer.setProfession(profession.getId(), 1);
     

How come this is happening? Do I need to store the data in a map, no matter what?

EDIT by Timtower: merged posts

EDIT by khave:
I figured out how to do it. Yes, you need to save it to a map and then pull the player from there.