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

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

i hate stating questions apparently seem have lot of solutions online, cannot seem find valid best-practice solution our case, , hence felt had no choice.

we building restful server application in periods between utilize may differ couple of hours multiple months.

the server hosted jetty. not using orm, application layered 3 layers (webservice- , business- , info layer). info layer exist of one class injected through guice framework. jdbc (mysql connection) instantiated within constructor of class. @ first, had lot of problem many connections before understood guice default creates new instance on each request(ref). rid of problem, , because our info layer class stateful, made class injected singleton.

now we've foreseen might run problem when our rest application not used time, since connection time out, , no new connection instantiated, constructor called once.

we have multiple solutions, cannot seem figure out best way solve this, none of them seems good. input or suggestions other solutions appreciated.

1. extend configured mysql timeout interval not want this, think it's not best practice. should of course of study not have leaking connection objects, if have, fill free space of connections available.

2. instantiate new connection @ origin of each method, , close @ end is, far understand, not best practice @ all, cause lot of overhead, , should avoided if possible?

3. alter injections "per-request", , close pool @ end of each method worse #2, not instantiate new connection, instantiate new object on each request?

4. check status of connection @ origin of each method, , instantiate new connection if it's closed illustration ping (example) mysql, , instantiate new connection if throws exceptions. work, create overhead. ideas of whether input create difference performance?

5. explicitly grab exceptions beingness thrown in methods indicating connection down, , if - instantiate new connection way, rid of ping overhead, complicate our code remarkably, have figure out way create sure methods homecoming have returned if connection alive.

6. utilize connection pool not familiar connection pools, other when using application server (i.e glassfish). we're wondering whether solve our problem? , if so; suggestions on framework providing connection pools? here suggest using plus jetty.

please inquire if there's unclear. might have forgotten add together vital information. me more of design question, i'd glad provide code if thinks help.

thanks in advance!

connection pools way go. have number of advantages:

they check connections - deals timeouts they command number of connections you can close connection when done - don't need maintain references

you should maintain connections in sort of pool, , in fact end writing 1 if don't bite bullet. time have implemented connection checking don't go stale, sort of connection holder don't need re-open them each time, sort of exception handling code...you drift. have used dbcp , bonecp , both easy utilize , configure , save hours , hours of frustration dealing jdbc connection issues. not overly familiar guice assume has way provide own mill method object, can utilize connections pool , simple phone call close() when you're done homecoming them pool. if you're using webserver can utilize interceptor or filter bind connections work thread , discard them after processing in case connection provider need yank 1 tied current thread.

java mysql jdbc connection-pooling guice