CODEWITHSUNDEEP
javamysqljdbcconnection-poolingguice
Skovly
Skovly

Reputation: 234

How to properly handle long-lived MySQL connections in Java when using Guice Injections?

I hate stating questions that apparently seem to have a lot of solutions online, but we really cannot seem to find any valid best-practice solution for our case, and therefore felt we had no choice.

We are building an RESTful server application in which the periods between use may differ from a couple of hours to multiple months.

The server is hosted by Jetty. We are not using any ORM, but the application is layered into three layers (WebService- , Business- and Data Layer). The Data layer exist of one class injected through the Guice framework. The JDBC (MySQL connection) is instantiated within the constructor of this class. At first, we had a lot of trouble with too many connections before we understood that Guice by default creates a new instance on each request(ref). To get rid of this problem, and because our Data layer class is stateful, we made the class injected as Singleton.

Now we've foreseen that we might run into trouble when our REST application is not used for some time, since the connection will time out, and no new connection will be instantiated, as the constructor will only be called once.

We now have multiple solutions, but we cannot seem to figure out the best way to solve this, as none of them really seems to be that good. Any input or suggestions to other solutions would be well appreciated.

1. Extend the configured mysql timeout interval We really do not want this, as we think it's really not best practice. We should of course not have any leaking connection objects, but if we have, they would fill up the free space of connections available.

2. Instantiate a new connection at the beginning of each method, and close it at the end This is, as far as we understand, not best practice at all, as it would cause a lot of overhead, and should be avoided if possible?

3. Change the injections back to "per-request", and close the pool at the end of each method This would be even worse than #2, as we would not only instantiate a new connection, but also instantiate a new object on each request?

4. Check the status of the connection at the beginning of each method, and instantiate a new connection if it's closed An example would be to ping (example) the mysql, and instantiate a new connection if it throws an exceptions. This would work, but it would create some overhead. Any ideas of whether this input actually would make any difference to the performance?

5. Explicitly catch any exceptions being thrown in the methods indicating that the connection is down, and if so - instantiate a new connection This way, we would get rid of the ping overhead, but it would complicate our code remarkably, as we would have to figure out a way to make sure that the methods will return what they would have returned if the connection where already alive.

6. Use a connection pool We are not familiar with connection pools, other than when using an application server (i.e Glassfish). We're also wondering whether this actually would solve our problem? And if so; any suggestions on any framework providing us with connection pools? Here they suggest using PLUS with Jetty.

Please ask if there's anything unclear. I might have forgotten to add some vital information. This is to me more of a design question, but I'd be glad to provide any code if anyone thinks that would help.

Thanks in advance!

Upvotes: 0

Views: 676

Answers (2)

Thorbjørn Ravn Andersen
Thorbjørn Ravn Andersen

Reputation: 75406

Inject a Provider<Connection> instead and have the provider give out connections (EDIT: at the time you need it) from a connection pool which can detect stale entries.

Unreturned connections should be discarded from the pool.

Upvotes: 1

Boris the Spider
Boris the Spider

Reputation: 61168

Connection pools are the way to go.
They have a number of advantages:

  1. They check your connections for you - this deals with timeouts
  2. They control the number of connections
  3. You can simply close the connection when your done - you don't need to keep references

You should certainly keep connections in some sort of pool, and in fact you will almost certainly end up writing one yourself eventually if you don't bite the bullet. By the time you have implemented connection checking so that they don't go stale, some sort of connection holder so that you don't need to re-open them each time, some sort of exception handling code...you get my drift.
I have used dbcp and boneCP and both are very easy to use and configure and will save you hours and hours of frustration dealing with JDBC connection issues.
I am not overly familiar with Guice but I assume it has some way to provide your own factory method for Object, so you can use that to get connections from your pool and then simple call close() when you're done to return them to the pool.
If you're using a webserver you can always use an interceptor or filter to bind connections to the work thread and discard them after processing in which case your connection provider would only need to yank the one tied to the current thread.

Upvotes: 2

Related Questions