Should a database connection stay open all the time or only be opened when needed?

Question

I have a bukkit plugin (minecraft) that requires a connection to the database.

Should a database connection stay open all the time, or be opened and closed when needed?

Answer 1

The database connection must be opened only when its needed and closed after doing all the necessary job with it. Code sample:

• Prior to Java 7:

    Connection con = null;
    try {
        con = ... //retrieve the database connection
        //do your work...
    } catch (SQLException e) {
        //handle the exception
    } finally {
        try {
            if (con != null) {
                con.close();
            }
        } catch (SQLException shouldNotHandleMe) {
            //...
        }
    }
  

• Java 7:

    try (Connection con = ...) {
    } catch (SQLException e) {
    }
    //no need to call Connection#close since now Connection interface extends Autocloseable
  

But since manually opening a database connection is too expensive, it is highly recommended to use a database connection pool, represented in Java with DataSource interface. This will handle the physical database connections for you and when you close it (i.e. calling Connection#close), the physical database connection will just be in SLEEP mode and still be open.

Related Q/A:

• Java Connection Pooling

Some tools to handle database connection pooling:

• BoneCP
• c3po
• Apache Commons DBCP
• HikariCP