Configure c3p0 to close Hibernate session when con

Question

I am using Hibernate with c3p0 for connection pooling. Since I am working in a multisharded database environment and the possibility of shards' downtime is a realistic use case scenario (by means external to the application at hand, e.g. someone just taking the shard down for whatever reason), I am trying to have c3p0 explicitly close the Hibernate session once it detects that the database connection is down so that I can skip the particular shard's session in my multi-shard scan.

Is there a way to configure c3p0 to notify Hibernate, by invoking Session.close() once it finds out that the connection is down so that calling Session.isOpen() can return a meaningful boolean?

RELATED: Preemptively and gracefully check that org.hibernate.Session is still connected (via c3p0)

Answer 1

the short answer is no, c3p0 won't help you here.

it sounds like (looking here and at your previous question) you are doing something that works at cross-purposes to c3p0, that is you are holding long-lived Sessions rather than creating Sessions on an as-needed basis and then destroying them promptly. that kind of thing is very fragile. it is the architecture Connection pools exist to avoid.

your best choice, IMO, would be "don't do that". don't ever cache Sessions at all. then ordinary Connection testing (perhaps with checkoutTimeout set too) will resolve the problem. if a shard is down, attempts to acquire a Connection will fail, and you can skip the shard if that's the best workaround.

c3p0 knows nothing whatsoever about hibernate. c3p0's author is grateful to hibernate for popularizing his (my) library, but c3p0 has no idea what a Session is, just a Connection. the only way that c3p0 could help you a little bit, in theory, would be that it could report to you an event when it notices that a database is down (which might be quite promptly or quite slowly depending on your configuration). you could respond to the event by close()ing sessions.

unfortunately, c3p0 doesn't (yet) offer hooks through which users could respond to acquisition failures. it might — that's a feature i'm thinking about adding for 0.9.6. but it doesn't now. however, it would be pretty trivial to implement something that polls your shards and closes their Sessions yourself. all c3p0 would do to notice outages would be to call DriverManager.getConnection( ... ) or dataSource.getConnection()and observe Exceptions. you can do that yourself!

i still strongly recommend the first solution, though. get rid of the long-lived Session objects.