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

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