Java Variable Question

Hello. A short question: I query a MySQL database many times in a row. This sometimes causes lag with my plugin and the server. Would making a variable that queries the database, and calling that variable later on make it so it only queries the database once (in the variable), or would calling the variable make it query the MySQL database again?

 

That is not how variables work in Java. My suggestion is to do away with the database entirely until you are more familiar with how Java works.

 

He might mean a method.

 

The way the question is worded does not make sense if you substitute the variable for method. Regardless, if he doesn't understand how methods work, he should do away with the database until he is more familiar with how Java works.

 

I don't think you understand what I'm asking, a yes or no question. Here's my deal: I am making a pvp plugin, mimicking the Call of Duty franchise in a way. It's a customized server, meaning the plugin is what runs the server. So when players kill other players on the other team, I track that, and add points to their stats, and increase their kill-streak, and see if their kill-streak is higher than their highest kill-streak, and give them kill-streak rewards, and see if they have the right stats to rank up. All in all, a TON happens when a player dies. So, this causes a bit a lag sometimes, wouldn't you think? Even when I delay the data updating, separating most data updates by 2 seconds, it still causes lag when many people die at once.

When I update stats, I query the database again to get the player's kills, deaths, etc. As a Java variable is a " container that stores a meaningful value that can be used throughout a program.", I was wondering if I could make a variable for, let's say, the player's kills. So, when I want to see if a player can rank up, instead of querying the database right there to get how many kills the player has, could I use the variable, and it wouldn't query the database again for the player's kills because I used the variable that already queries the database?

 

You can declare any variable you want. You can assign any value you want to any declared variable of the same type. And strictly speaking, you do not call variable, as variables do no work. No matter how many times you reference a variable to results will be constant unless the variable has been assigned again.

 

I would create three HashMaps for player kills, deaths, and kill streaks and update the SQL database every, say, 30 seconds. Sometimes constant SQL table updating can be harsh and cause lag.

 

On that note, SQL operations should not performed on the main server thread, as most SQL operations will take upwards of milliseconds, (a remote server operation could take longer than the amount of time one tick should be processed in.

 

Alright, I can use a variable.

Code:

                            int kills = mctdmplayer.getKills(killer.getName());
                            int points = mctdmplayer.getPoints(killer.getName());
                            if(kills < 100){
                                mysql.databaseQuery("UPDATE players SET rank='Private' WHERE username='" + killer.getName() + "'");
                            }
                            if(kills == 100){
                                String rank = mysql.databaseStringSelect("SELECT rank FROM players WHERE username='" + killer.getName() + "'", "rank");
                                int newpoints = points + 50;
                                mysql.databaseQuery("UPDATE players SET rank='Specialist' WHERE username='" + killer.getName() + "'");
                                mysql.databaseQuery("UPDATE players SET points=" + newpoints + " WHERE username='" + killer.getName() + "'");
                                mctdmplayer.setDisplayName(killer);
                                killer.sendMessage(ChatColor.DARK_RED + "[HQ] " + ChatColor.DARK_AQUA + "You earned " + ChatColor.DARK_GREEN + "50 " + ChatColor.DARK_AQUA + "points for ranking up!");
                                killer.sendMessage(ChatColor.DARK_RED + "[HQ] " + ChatColor.DARK_AQUA + "You have ranked up from a " + ChatColor.DARK_GREEN + rank + ChatColor.DARK_AQUA + " to a " + ChatColor.DARK_GREEN + "Specialist" + ChatColor.DARK_AQUA + "!");
                            }
                            if(kills == 200){
                                String rank = mysql.databaseStringSelect("SELECT rank FROM players WHERE username='" + killer.getName() + "'", "rank");
                                int newpoints = points + 75;
                                mysql.databaseQuery("UPDATE players SET rank='Corporal' WHERE username='" + killer.getName() + "'");
                                mysql.databaseQuery("UPDATE players SET points=" + newpoints + " WHERE username='" + killer.getName() + "'");
                                mctdmplayer.setDisplayName(killer);
                                killer.sendMessage(ChatColor.DARK_RED + "[HQ] " + ChatColor.DARK_AQUA + "You earned " + ChatColor.DARK_GREEN + "75 " + ChatColor.DARK_AQUA + "points for ranking up!");
                                killer.sendMessage(ChatColor.DARK_RED + "[HQ] " + ChatColor.DARK_AQUA + "You have ranked up from a " + ChatColor.DARK_GREEN + rank + ChatColor.DARK_AQUA + " to a " + ChatColor.DARK_GREEN + "Corporal" + ChatColor.DARK_AQUA + "!");
                            }
                            if(kills == 300){
                                String rank = mysql.databaseStringSelect("SELECT rank FROM players WHERE username='" + killer.getName() + "'", "rank");
                                int newpoints = points + 100;
                                mysql.databaseQuery("UPDATE players SET rank='Sergeant' WHERE username='" + killer.getName() + "'");
                                mysql.databaseQuery("UPDATE players SET points=" + newpoints + " WHERE username='" + killer.getName() + "'");
                                mctdmplayer.setDisplayName(killer);
                                killer.sendMessage(ChatColor.DARK_RED + "[HQ] " + ChatColor.DARK_AQUA + "You earned " + ChatColor.DARK_GREEN + "100 " + ChatColor.DARK_AQUA + "points for ranking up!");
                                killer.sendMessage(ChatColor.DARK_RED + "[HQ] " + ChatColor.DARK_AQUA + "You have ranked up from a " + ChatColor.DARK_GREEN + rank + ChatColor.DARK_AQUA + " to a " + ChatColor.DARK_GREEN + "Sergeant" + ChatColor.DARK_AQUA + "!");
                            }

You see, I use the variable "kills" to get the player kills. So, would be doing this call the database only once? Instead of doing this:

Code:

                            if(mysql.databaseIntSelect("SELECT kills FROM players WHERE username='" + killer.getName() + "'", "kills") < 100){
                                mysql.databaseQuery("UPDATE players SET rank='Private' WHERE username='" + killer.getName() + "'");
                            }
                            if(mctdmplayer.getKills(killer.getName()) == 100){
                                String rank = mysql.databaseStringSelect("SELECT rank FROM players WHERE username='" + killer.getName() + "'", "rank");
                                int newpoints = mctdmplayer.getPoints(killer.getName()) + 50;
                                mysql.databaseQuery("UPDATE players SET rank='Specialist' WHERE username='" + killer.getName() + "'");
                                mysql.databaseQuery("UPDATE players SET points=" + newpoints + " WHERE username='" + killer.getName() + "'");
                                mctdmplayer.setDisplayName(killer);
                                killer.sendMessage(ChatColor.DARK_RED + "[HQ] " + ChatColor.DARK_AQUA + "You earned " + ChatColor.DARK_GREEN + "50 " + ChatColor.DARK_AQUA + "points for ranking up!");
                                killer.sendMessage(ChatColor.DARK_RED + "[HQ] " + ChatColor.DARK_AQUA + "You have ranked up from a " + ChatColor.DARK_GREEN + rank + ChatColor.DARK_AQUA + " to a " + ChatColor.DARK_GREEN + "Specialist" + ChatColor.DARK_AQUA + "!");
                            }
                            if(mctdmplayer.getKills(killer.getName()) == 200){
                                String rank = mysql.databaseStringSelect("SELECT rank FROM players WHERE username='" + killer.getName() + "'", "rank");
                                int newpoints = mctdmplayer.getPoints(killer.getName()) + 75;
                                mysql.databaseQuery("UPDATE players SET rank='Corporal' WHERE username='" + killer.getName() + "'");
                                mysql.databaseQuery("UPDATE players SET points=" + newpoints + " WHERE username='" + killer.getName() + "'");
                                mctdmplayer.setDisplayName(killer);
                                killer.sendMessage(ChatColor.DARK_RED + "[HQ] " + ChatColor.DARK_AQUA + "You earned " + ChatColor.DARK_GREEN + "75 " + ChatColor.DARK_AQUA + "points for ranking up!");
                                killer.sendMessage(ChatColor.DARK_RED + "[HQ] " + ChatColor.DARK_AQUA + "You have ranked up from a " + ChatColor.DARK_GREEN + rank + ChatColor.DARK_AQUA + " to a " + ChatColor.DARK_GREEN + "Corporal" + ChatColor.DARK_AQUA + "!");
                            }
                            if(mctdmplayer.getKills(killer.getName()) == 300){
                                String rank = mysql.databaseStringSelect("SELECT rank FROM players WHERE username='" + killer.getName() + "'", "rank");
                                int newpoints = mctdmplayer.getPoints(killer.getName()) + 100;
                                mysql.databaseQuery("UPDATE players SET rank='Sergeant' WHERE username='" + killer.getName() + "'");
                                mysql.databaseQuery("UPDATE players SET points=" + newpoints + " WHERE username='" + killer.getName() + "'");
                                mctdmplayer.setDisplayName(killer);
                                killer.sendMessage(ChatColor.DARK_RED + "[HQ] " + ChatColor.DARK_AQUA + "You earned " + ChatColor.DARK_GREEN + "100 " + ChatColor.DARK_AQUA + "points for ranking up!");
                                killer.sendMessage(ChatColor.DARK_RED + "[HQ] " + ChatColor.DARK_AQUA + "You have ranked up from a " + ChatColor.DARK_GREEN + rank + ChatColor.DARK_AQUA + " to a " + ChatColor.DARK_GREEN + "Sergeant" + ChatColor.DARK_AQUA + "!");
                            }

mctdmplayer.getKills(...) queries the database for the player's kills.

 

JjPwN1
The entire right hand side of the assignment is operator is evaluated before the assignment to the variable takes place.

 

JjPwN1
Some suggestions.

1) Show us the code you are using for your SQL queries, hopefully you are not creating a new connection every time you need to run a query.
2) Are you using prepared statements, if not you should be for this as it will help reduce the lag.
3) I'd recommend only running the query if the player requests the data instead of storing it persistently. Ie run a query to fetch their k/d ratio if they ask for it and then do away with that variable after it has displayed it to the player, if this is viable of course.

 

Adamki11s
To answer your questions:

1) In a separate class called "MySQL", I have these three methods in it to send queries to the database:

Code:

    public int databaseIntSelect(String query, String column) throws SQLException{
        plugin.statement.executeQuery(query);
        ResultSet rs = plugin.statement.getResultSet();
        int intResult = 0;
        while(rs.next()){
            intResult = rs.getInt(column);
        }
        return intResult;
    }
    public String databaseStringSelect(String query, String column) throws SQLException{
        plugin.statement.executeQuery(query);
        ResultSet rs = plugin.statement.getResultSet();
        String stringResult = "";
        while(rs.next()){
            stringResult = rs.getString(column);
        }
        return stringResult;
    }
    public void databaseQuery(String query) throws SQLException{
        plugin.statement.execute(query);
    }

While plugin.statement is a java.sql.Statement inside of my main class. On the enable of my plugin, with the Connection's and Statement's null value, it sets their value to what it needs to be to connect to the MySQL database. So no, not connecting to the database every time something is needed.

2) I don't believe I am using prepared statements, but this can be changed.

 

Look at this for prepared statements http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html.

Your code looks fine, not sure why it would be lagging.

Also remember to call rs.close() when you are done checking your result set object. Faster than leaving the DB to do it automatically.

 

When the plugin starts, you get the data from the database and store it logically using arrays or whatever. When something happens, you update these arrays, and asynchronously save to the database. In a nutshell, read once, write many.