I am using hibernate with c3p0 connection pool and using PostgreSQL database and configured 100 max Connection in connection pool
Scenario : Suposse multiple threads use DBSessioFactory to getConnection from hibernate c3p0 poll and every thread does not close the connection .
So there are Connection leak in every thread . So in some time all pool become unavailable . As all pool are in Busy state as also seen from jmx(jconsole) .
Issue : Now whats my issue is even if i restart postgresql , event though , busy connections showing is hibernate pool in jconsole is 100 . c3p0/hibernate does not understand that database has been restart and it should free this .
How should i achieve this ?
c3p0 has no way of knowing that Connections which, as far as it is concerned are valid and checked out, are no longer good because of a DB reset. Checked-out Connections belong to clients, c3p0 generally doesn't mess with them. The best thing to do is to fix your Connection leaks, so that this does not happen.
If you can't fix the leaks (just fix the leaks!), c3p0 does offer an ugly workaround, its unreturnedConnectionTimeout setting. You can set a timeout longer than a valid client would ever use, and c3p0 will eventually cleanup leaked Connections as this timeout is exceeded.