After reinventing several times the wheel implementing myself db connection pools I have finally decided to give c3p0 a try. It seems to be easy to use and configure once you go through the documentation.
One thing I could not find in the documentation is how to detect, via listener or other mechanism, whenever the db is down or up using c3p0. In our application I need to tell the client the db is down, so it cannot send certain orders that need to be logged in the db. I am wondering if there's a standard way to detect these changes using c3p0 or I have to implement myself an external thread that queries the db periodically by executing a simple select statement.
I feel that this feature is a must in a connection pool and there must be a way to do so using c3p0 but I just can't find it. If this doesn't exists I feel like reinventing the wheel again.
A connection pool has no way of knowing what the state of the DB or even its connections are until some operation is performed on them. At least some connection pools can be configured to re-create a connection after a certain time, so the pool would at that point notice that there's something wrong. There are also other mechanisms such as a test query that will be executed before the connection is given out, to verify that the connection is valid.
If you want to keep track of the status, you have no option than to poll the DB yourself.