[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(

    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.

See also:

Score: 0

Where in your code are the errors on losing 8 connection coming from? This would probably 7 be the best place to start.

Off the top of 6 my head (and I may be wrong), JDBC connections 5 will only close on an actual fatal error, so 4 you won't know they've failed until you 3 try to do something.

What I've done in the 2 past is to invalidate the connection at 1 the point of failure and retry periodically.

More Related questions