MySql question

  • Post by james1bow ().

    This post was deleted by the author themselves ().
  • 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

    Yahgiggle Steam Signature, real name Deon Hamilton :thumbsup: Server @ ip 139.99.136.76:4255 Name (The Federation of territory) Unity :thumbsup:

    If at first your code does not work call it version 0.1 with the odd bug :thinking:

    My Own Website




    31245-logo-png
  • 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:

    Java: Try-with-resources block (good code)
    try (ResultSet result = db.executeQuery("SELECT * FROM `MyTable`")) {
    while (result.next()) {
    //do something
    }
    }


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

    Java: Bad code
    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:

    Java: Verbose code - it's better to use try-with-resources instead, as above
    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 ;)

Participate now!

Don’t have an account yet? Create a new account now and be part of our community!