Configure c3p0 to close Hibernate session when disconnecting

Question

I use Hibernate with c3p0 to pool pools. Since I work in a multi-user database environment, and the possibility of skull downtime is a realistic use case (using external means for the application, for example, someone just takes a splinter for some reason), I try to have c3p0 explicitly close the Hibernate session. as soon as it detects that the database connection is not working, so I can skip the specific shard session in my multiple scan.

Is there a way to set c3p0 to notify Hibernate by calling Session.close() after it finds out that the connection is not working, so that calling Session.isOpen() can return a meaningful boolean?

RELATED: Proactive and elegant verification that org.hibernate.Session is still connected (via c3p0)

Answer 1

the short answer is no, c3p0 will not help you.

It seems that (look here and at your previous question) you are doing something that works for cross purposes up to c3p0, i.e. you are holding long-lived sessions, rather than creating sessions as needed, and then destroy them quickly. such a thing is very fragile. It is an architecture in which connection pools exist.

Your best bet, IMO, is "don't do this." never cache sessions at all. then normal connection testing (possibly with setting checkoutTimeout too) will solve the problem. if the shard does not work, attempts to get the connection will fail, and you can skip the shard if this is the best way around.

c3p0 knows nothing about sleep mode. The author of c3p0 is grateful for hibernation for popularizing his (my) library, but c3p0 has no idea what a session is, just a connection. the only way c3p0 could help you a little, theoretically, would be to let you know about an event when it notices that the database is down (which can be pretty fast or pretty slow depending on your configuration). You can respond to the event in closed sessions.

Unfortunately, c3p0 does not yet offer hooks through which users can respond to crashes upon failure. he can - what am I thinking of adding for 0.9.6. but this is not so. however, it would be trivial to implement something that will interrogate your shards and close your sessions yourself. all c3p0 will do to notice that disconnecting would be calling DriverManager.getConnection (...) or dataSource.getConnection () and observing the exceptions. you can do it yourself!

However, I still highly recommend the first solution. Get rid of long-lived session objects.