MySQL - Keep opening connection or keep it open

Discussion in 'Spigot Plugin Development' started by SystemUpdate_, Jun 21, 2015.

?

Title of thread

  1. Keep Connection Open

    60.0%
  2. Open Connection When Required

    40.0%
  1. Hello,

    So I've done a lot of work with MySQL and usually I would open the connection when it was required but I'm unsure of the best method and was wondering if someone could point me the right direction on if the connection should be kept open or if it should be opened when required.
     
  2. sothatsit

    Patron

    What i generally do is use a connection pool and close each connection when it is older than 5 mins. Im not really sure if this is the best way but it works well for me.
     
  3. I use that method instead of using executeUpdate directly:
    Code (Text):

        /**
        *
        * @return either (1) the row count for SQL Data Manipulation Language (DML)
        *         statements or (2) 0 for SQL statements that return nothing
        */
        public int executeUpdate(String cmd) throws DatabaseException {
            try {
                if (con.isClosed()) {
                    reconnect();
                }
                return stat.executeUpdate(cmd);
            } catch (SQLException e) {
                try {
                    if (con.isClosed()) {
                        if (reconnect())
                            return stat.executeUpdate(cmd);
                    }
                } catch (SQLException e1) {
                    throw new DatabaseException(e1);
                }
                throw new DatabaseException(e);
            }
        }
     
    reconnect() simply reconnects to the database and returns true if the attempt to reconnect was successful,
    DatabaseException is a custom exception class,
    stat is the statement I use and con is the connection.
    This method has always worked for me and always executes the update (or query, that would be the same code basically) even when the connection is already closed, it just tries to execute it again then.
     
  4. Use a connection pool. They are designed to take these decisions off of you.
     
  5. Where can I learn to create/use a connection pool?
     
  6. I can't really vote because it depends how frequently the database would need to be used or accessed.
     
  7. It will be a ticket system, so whenever someone checks the status of a ticket/opens a new one/replies
     
  8. In that case i'd keep it open.
     
  9. you only want to use the my sql query when something happens or else sometimes it will cause lag to the server because the connection is opened
     
  10. JamesJ

    Supporter

    BoneCP, DBPool, there are quite a few, just google for some. They usually come with tutorials on how to get started.