Closing a DB connection in Java

Question

Its just a foolish queation from a java programmer. I have something like

PreparedStatement stmt = DBConnection.getConnection()
                .prepareStatement(sql);

Now in finally block I check if stmt exists and then close it. What will happen to Connection object in this case. I know if I write in separate lines then I can close it as I have a reference to connection object which I don't have in this case.

Matt Ball · Accepted Answer · 2014-06-29 03:17:56Z

3

What will happen to Connection object in this case.

Nothing. There is no magic, so the connection isn't magically closed, just because you didn't assign it to a variable.

That's why you use a separate variable for the Connection: so you can call close()! Or use try-with-resources:

try (Connection connection = DBConnection.getConnection()) {
    PreparedStatement stmt = connection.prepareStatement(sql);
}

answered Jun 29, 2014 at 3:17

Matt Ball

361k102 gold badges655 silver badges725 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brett Okken · Accepted Answer · 2014-06-29 03:33:38Z

-1

The connection will remain open until it is garbage collected at some time in the future.

However, in the case of connection pools, yours might not be the only reference to the Connection (if the pool also maintains a reference to connections which have been borrowed). In this case garbage collection will not take place, and the collection will not be closed. It will have "leaked" from the pool, which will likely eventually cause the pool to be exhausted and no further connections will be available.

edited Jun 29, 2014 at 3:33

answered Jun 29, 2014 at 3:19

Brett Okken

6,3261 gold badge21 silver badges25 bronze badges

5 Comments

Matt Ball Over a year ago

No, GC will not close open connections for you. That's why the close() method exists.

Brett Okken Over a year ago

@MattBall I suppose it is up to the jdbc driver implementation. Connections /should/ be closed in finalize if not closed explicitly. This is not a recommendation to rely on garbage collection to close connections. They absolutely should be closed in a finally block. This is similar to InputStreams and other resources.

Matt Ball Over a year ago

You cannot safely rely on finalize alone. And what about connection pooling? The point is that the code must somehow explicitly tell the system when it's done with the connection/stream/resource.

Mark Rotteveel Over a year ago

@MattBall Even with connection pooling gc could work if the connection pool hands out a proxy or wrapper that wraps the physical connection (which BTW is the usual implementation) and does not keep a strong reference to this proxy.

Brett Okken Over a year ago

@MattBall Again, I am simply stating what /will/ happen, which was the question, not one /should/ do. I added some additional detail around the potential impact of pooling further delaying or preventing GC.

Collectives™ on Stack Overflow

Closing a DB connection in Java

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related