[ACCEPTED]-When my app loses connection, how should I recover it?-database-connection

Accepted answer

Score: 17

This is not the correct way of retrieving 24 a connection. You're retrieving the connection 23 and assigning it as an instance (or worse, static) variable 22 of the class. Basically, you're keeping 21 the connection open forever and reusing 20 a single connection for all queries. This 19 may end up in a disaster if the queries 18 are executed by different threads. Also, when 17 it's been kept open for too long, the DB 16 will reclaim it because it assumes that 15 it's dead/leaked.

You should acquire and close the 14 connection in the shortest possible scope. I.e. in the very same 13 try block as where you're executing the query. Something 12 like this:

public Entity find(Long id) throws SQLException {
    Entity entity = null;

    try (
        Connection connection = dataSource.getConnection(); // This should return a NEW connection!
        PreparedStatement statement = connection.prepareStatement(SQL_FIND);
    ) {
        statement.setLong(1, id);

        try (ResultSet resultSet = preparedStatement.executeQuery()) {
            if (resultSet.next()) {
                entity = new Entity(
                    resultSet.getLong("id"),
                    resultSet.getString("name"),
                    resultSet.getInt("value")
                );
            }
        }
    }       

    return entity;
}

If you worry about connecting 11 performance and want to reuse connections, then 10 you should be using a connection pool. You 9 could homegrow one, but I strongly discourage 8 this as you seem to be pretty new to the 7 stuff. Just use an existing connection pool 6 like BoneCP, C3P0 or DBCP. Note that you should not change 5 the JDBC idiom as shown in the above example. You 4 still need to acquire and close the connection 3 in the shortest possible scope. The connection 2 pool will by itself worry about actually 1 reusing, testing and/or closing the connection.

[ACCEPTED]-When my app loses connection, how should I recover it?-database-connection

See also:

ORM Technologies vs JDBC?

Concurrent use of same JDBC connection by multiple threads

What is a validationQuery with respect to databases and JNDI?

Eclipse - How to add a new MariaDB connection

Why 'Quarkus' chose 'Agroal' instead of 'HikariCP' as the preferred datasource and connection pooling implementation?

Does closing Connection automatically close statement and resultset?

Why is it bad practice to make multiple database connections in one request?

How do I change a Crystal Report's ODBC database connection at runtime?

Specifying Port With SqlConnectionStringBuilder?

Refresh the database connection if connection drops or times out