4

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.

glyphx
  • 195
  • 3
  • 10
  • 1
    That's probably a question for the `programmers` @ `stackexchange` – Eel Lee Nov 07 '13 at 16:44
  • Possible duplicate of [Closing Database Connections in Java](https://stackoverflow.com/questions/2225221/closing-database-connections-in-java) – Vadzim Apr 14 '19 at 12:55

1 Answers1

2

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.

Nick Holt
  • 33,455
  • 4
  • 52
  • 58
  • Currently this code has a ConnectionPool Class that simply holds a list of connections. Each connection has a "isUsed" flag that is set true when its handed out. Then at the end of a method doing sql it tells the pool "i'm done" basically, and the pool commits any thing in the connection and sets "isUsed" back to false so it knows it can hand out the connection again. --- From what you say, and I am reading online, it looks like this is wrong. That the point of a pool is just to have fresh connections ready to go, not to keep reusing the same connection over and over. is that right? – glyphx Nov 07 '13 at 17:04
  • A 'proper' connection pool would use the call `connection.close` to return the `connection` to the pool (rather than actually closing it). This way the calling code is aware of whether or not a pool is being used. – Nick Holt Nov 07 '13 at 17:10
  • Im confused... sorry for the newb questions... but it sounds like you are saying after closing the connection it is ready to return to the pool... I was thinking once it is closed, its gone... a new connection would need to be created... but are you saying a connection could be reused/reopened after it is closed? – glyphx Nov 07 '13 at 17:15
  • The way it's typcially done is that the pool gives you its implementation of `Connection`, which is a wrapper around the actual `connection`. All the methods on the pool's implementation of `Connection` delegate to the actual `connection` with the exception of `close`, which puts the connection back into the pool. – Nick Holt Nov 07 '13 at 17:22
  • ah - I see... So is there a basic best pratice for said close implimentation? Should it always call commit? Is there some sort of checking that can or should be done to make sure locks are released, etc and the connection is fresh and ready for the next caller? – glyphx Nov 07 '13 at 17:37
  • The best practice is to use a 3rd party connection pool like c3p0, which I mentioned in my answer. Not sure why you'd write your own, but if you did then the connection should be returned to its original state when it's returned to the pool - so yes, any transactions should be committed. – Nick Holt Nov 08 '13 at 08:24
  • Since Java 7 there is much safer and cleaner approach: https://stackoverflow.com/questions/2225221/closing-database-connections-in-java/16331963#16331963 that wouldn't overwrite original exception on another exception fired from `close()` call. – Vadzim Apr 14 '19 at 12:57