Solved Connect to MySQL DB once; call from the connection

In my tests with MySQL, I am using this method:

Code:

    public void databaseQuery(String query) throws SQLException{
        try{
            Class.forName("com.mysql.jdbc.Driver");
            Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://pwncraftpvp.com/pwncraft_mctdm","pwncraft_jjpwn","PASSWORD");
            Statement statement = con.createStatement();
            statement.execute(query);
        }catch(ClassNotFoundException e){
            e.printStackTrace();
        }
    }

And, everytime I use that method, I am connecting to the MySQL database again, correct? So, if this is true, how would I make the Java Project only connect to the MySQL database once, and use that connection, let's say in this method with Statement statement = con.createStatement()?

I am asking this because when I use this method many times right next to each other, it lags the server for a few seconds, but it still completes the query.

 

How do you specify "only connect to the MySQL database once"
Once in total? or every method call?

What I think u need is
} finally { con.close(); }

 

I tried }finally con.close(); and it did nothing to help the lag that is caused. See, in my plugin, it tracks kills, points, deaths, kill-streaks, etc. And every time someone dies, I add kills, points, and kill-streaks to the killer's MySQL row (data), and I add deaths, point loss, kill-streak loss to the dyer's MySQL data. I use the databaseQuery() to do all this data updating. And every time I use the databaseQuery() method, it has to connect to the MySQL database. To give you a hint of how many times it must connect to the database within the seconds Java takes to read the lines:

Code:

                int playerpoints = mysql.databaseIntSelect("SELECT points FROM players WHERE username='" + p.getName() + "'", "points");
                int points = mysql.databaseIntSelect("SELECT points FROM players WHERE username='" + killer.getName() + "'", "points") + addpoints;
                int kills = mysql.databaseIntSelect("SELECT kills FROM players WHERE username='" + killer.getName() + "'", "kills") + 1;
                int deaths = mysql.databaseIntSelect("SELECT kills FROM players WHERE username='" + p.getName() + "'", "kills") + 1;
                int killstreak = mysql.databaseIntSelect("SELECT killstreak FROM players WHERE username='" + killer.getName() + "'", "killstreak") + 1;
                mysql.databaseQuery("UPDATE players SET points=" + points + " WHERE username='" + killer.getName() + "'");
                mysql.databaseQuery("UPDATE players SET kills=" + kills + " WHERE username='" + killer.getName() + "'");
                mysql.databaseQuery("UPDATE players SET deaths=" + deaths + " WHERE username='" + p.getName() + "'");
                mysql.databaseQuery("UPDATE players SET killstreak=" + killstreak + " WHERE username='" + killer.getName() + "'");
                mysql.databaseQuery("UPDATE players SET killstreak=0 WHERE username='" + killer.getName() + "'");
                if(playerpoints > 2){
                    if(playerpoints > 2 && playerpoints <= 200){
                        loss = 1 + (int)(Math.random() * ((3 - 1) + 1));
                        int deathloss = playerpoints - loss;
                        mysql.databaseQuery("UPDATE players SET points=" + deathloss + " WHERE username='" + p.getName() + "'");
                    }
                    if(playerpoints >= 200 && playerpoints <= 500){
                        loss = 2 + (int)(Math.random() * ((5 - 2) + 1));
                        int deathloss = playerpoints - loss;
                        mysql.databaseQuery("UPDATE players SET points=" + deathloss + " WHERE username='" + p.getName() + "'");
                    }
                    if(playerpoints >= 501 && playerpoints <= 700){
                        ...
                    }

So, I'm asking if there is a way to only connect to the database one time, and use the con variable anywhere without having to connect with class.forName(), DriverManager, etc

 

Why u don't do a SELECT * and splits the information into your variable. now u do like 10 querries instead of 2?
U will have to work with rs = st.executeQuery(Query); rs.next()

Sorry I'm new to MySQL, probably even less experienced than you.
But always look if something can be done in less steps, also close any connection rs.close(); st.close(); con.close();

*400th Post Hooray!*

 

Yes, I a recommend doing it. Create a Connection and Statement field, and open them in your onEnable. You can then open and close ResultSets each time you need to in the specific code. Just make sure you close the Connection and Statement in your onDisable.

 

Yes, this has cleared the lag. Well, the giant 5 second lag. Now it's just a .5 second delay.

 

It adds up! I made some of my code more efficient by just nanoseconds, but in total it shaved seconds!

 

Yep! Thanks for the help!