Keeping MySQL Connection open

So, I have a couple of main questions with this thread. I am working on a tokens system for my minecraft server where I use MySQL. This leads to question #1
1) Is it better to keep the connection open at all times, or open and close when it is required? I have heard both, but it just sounds to me like keeping it open at all times would be better. That leads into question #2
2) I currently use some methods (see below) and I check if the connection is closed. If the connect is closed, I then proceed to open the connection. However, if the connection was closed and I try to return an integer or retrieve information it always returns null. I am guessing this is because the time it takes to connect to the MySQL server is not instant. Does anyone have a good way of keeping the MySQL connection open? Thanks for the help!
Example Method:

Code:java

    public static Integer checkCoins(String UUID) {
        Integer retval = null;
        if(playerExists(UUID)) {
            try {
                if (MGTokens.conn.isClosed()){ //MGTokens.conn is the connection
                    MGTokens.openCon(); //Method which opens the connection
                }
 
                ResultSet res = MGTokens.conn.createStatement().executeQuery("SELECT * FROM playerpoints WHERE username = '" + UUID + "';");
                if(res.next()) {
                    if((Integer) res.getInt("balance") != null) {
                        retval = res.getInt("balance");
                    }
                    else {
 
                    }
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } else {
            createPlayer(UUID);
 
        }
        return retval;
 
    }

Also, I plan on switching to prepared statements as soon as I can figure out the whole deal on keeping the connection open.

 

To answer your first question, it depends on how often you're using the connection. For example, a logging plugin would almost certainly want to have a persistent connection, given it's writing to the database several times per minute or even second. However, something that only writes once every few minutes or even less would be more likely to just open a new connection when needed, and close it once finished.

Edit: Never mind, listen to 1Rogue. He knows more than I do on the subject.

 

You should never have a consistent connection, especially with MySQL. If your plugin is public, all the more reason not to - MySQL and other sql varieties have connection timeouts where after a while it will automatically close the connection.

Your options are to either use a connection pool or simply open and close the connection as needed. You really shouldn't be doing much with an sql database that would warrant keeping it open anyhow when you are threading everything correctly.

 

1Rogue ShadyPotato Thanks for the advice! Seems to be working if I just open and close it every time. ALthough it is a little bit slower, I believe it will work better. Thanks again!

1Rogue ShadyPotato Actually this has caused a major bug for me. I think this is from opening and closing the connection too much. At the end of a minigame I made, I add coins to everyone on the server. This causes the connection to be opened and closed multiple times very quickly. It seemed to "lag out", as the next method in the for loop of all online players didn't happen. I checked and there was an error. I was not able to get the error though as I restarted my server, and it is not in the latest.log. I can try to reproduce if you need me to, but I think that if I am opening and closing repeatedly it will cause major lag issues.

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

 

The Fancy Whale
- its probably in one of the last gzipped logs
- use a connection pool

 

Open the connection once for that, then use PreparedStatements and commit all the changes at once. Then close your connection.