CODEWITHSUNDEEP
javasqldatabase-connection
glyphx
glyphx

Reputation: 195

java.sql.Connection closing/caching best practices

What are best practices for closing or caching sql connections?
I see things saying to always close it in finally block. Though I also see things talking about caching them for future use.

How expensive is it to open new connections for each task?

I just started to work on a java data warehousing app someone else programmed. (and I don't have DB experience) It is a single threaded app that loads files into the DB, and does some aggregation and analysis. We have had some issues with deadlocks, which I wouldn't think should be a problem in a single threaded app. I see exceptions are swallowed and not logged all over the class that handles the DB connections, so I am hoping adding logging to those should provide some insight.

But I am hoping for some guidance on best practices for dealing with DB connections in the meantime.

Upvotes: 4

Views: 8949

Answers (1)

Nick Holt
Nick Holt

Reputation: 34311

Regardless of whether or not you are using connection pooling, any database code should follow this form:

try (
  Connection connection = dataSource.getConnection();
  PreparedStatement preparedStatement = ...
)
{
  ...
}

The dataSource could be a pool or a simple connection factory, to your code it shouldn't matter, it's simply where it gets connections from.

This said, I'd use Spring's JdbcTemplate class, which does pretty much what the code above does.

Regarding the question 'how expensive is it to open new connections for each task?' - the answer is very, at least in comparison to grabbing one from a pool. You also have to consider what happens if large numbers of tasks are run - most databases will be configured to limit the number of connections.

The bottom line is, in anything but the most simple application, you should use a connection pool like c3po and size it according you your needs

Regarding your deadlock, this is most likely occurring in the database where there are a variety of locks that can be made when data is updated depending on how the database is configured.

Upvotes: 2

Related Questions