MySql question

Quote from james1bow

as my server is using the database a lot im starting to get connection open/locked issues. tho currently ive not noticed any negative effects of this im going to switch to mySql. my question is, do i just make one connection for all players? or should i make a new connection for each connected player and close it when they disconnect? asking as i have never messed with mySql

the try catch automatically close's the connection anyway well, its meant to so you shouldn't need to close the connection, i was doing this and then started to run into closed connection problems lol

I always close my connections cleanly. Just two cent.

Then again, I also like to be original

I leave the most Important data in variables, and memory in Intervalen in the DB, if it can be a lot of Different requests.
Small things, write/read, and again.

Basically it's important to either re-use existing connections (e.g. create a connection once, store it in a variable and re-use that connection), or alternatively close the connection once it's no longer needed. Everytime "getSQLiteConnection()" is called, a new connection is created. Same about "getMySQLConnection()".

Only exception is the world database (this reuses the same connection automatically and doesn't need to be closed manually).

If you re-use a single connection, it's important to close statements and ResultSets. This is a typical pitfall. For example, when calling executeQuery() (which returns a ResultSet), make sure to close the ResultSet once you're done with the query (i.e. almost always a few lines after your query). The easiest and safest way to do that is to use a try-with-resources block (it closes the ResultSet automatically, even if an exception occurs), e.g. like this:

try (ResultSet result = db.executeQuery("SELECT * FROM `MyTable`")) {
    while (result.next()) {
        //do something
    }
}

It's not recommendable to do it this way, for example:

ResultSet result = db.executeQuery("SELECT * FROM `MyTable`");
while (result.next()) {
    //do something
}
result.close();

The reason is that if an exception occurs between line 2 and 4, the result will never be closed (because an exception interrupts code execution, so line 5 will never be executed).

You can "fix" this by using a try-finally block, but it's ugly and more verbose than the try-with-resources block at the very top:

ResultSet result = null;
try {
    result = db.executeQuery("SELECT * FROM `MyTable`");
    while (result.next()) {
        //do something
    }
}
finally {
    if (result != null) result.close();
}

You don't have to close ResultSets if you either close the connection, or alternatively use getConnection() and create statements manually (and close them accordingly - because closing the statement will automatically close related ResultSets). But even in these cases it's still better to just always use try-with-resources blocks when working with ResultSets