Recover Hibernate connection

Question

Does anybody know a way to reestablish/retry again hibernate connection. I mean for example: the remote DB is down and I start my application. Hibernate is not able to establish a connection. it fails. But the application is not closed. Is there a way to say hibernate to try one more time to establish a connecton?

Thanks in advance

Answer 1

You should really go for C3P0 connection pooling: http://www.mchange.com/projects/c3p0/index.html#hibernate-specific

There is a section in C3P0 documentation on that subject: http://www.mchange.com/projects/c3p0/index.html#configuring_recovery

First you have to properly configure c3p0, which in case of using hibernate must happen in c3p0.properties file.

In your c3p0.properties put these properties to retry to reconnect indefinitely every 3 seconds when database is down:

c3p0.acquireRetryAttempts = 0
c3p0.acquireRetryDelay = 3000
c3p0.breakAfterAcquireFailure = false

Also, to avoid broken connections lying in your pool indefinitely, use connection age management:

c3p0.maxConnectionAge = 6000
c3p0.maxIdleTime = 6000
c3p0.maxIdleTimeExcessConnections = 1800
c3p0.idleConnectionTestPeriod = 3600

These may be quite expensive, but helpful if above is not enough:

c3p0.testConnectionOnCheckout = true
c3p0.preferredTestQuery = SELECT 1;

You may also want to check for connection leaks which prevent recovery:

c3p0.debugUnreturnedConnectionStackTraces = true

And finally, make sure that C3P0 is hooked with hibernate correctly, enable debug logging for "com.mchange" package and see if C3P0 tells you anything about itself. It should state configuration properties which are loaded, so see if it's all there.

I hope this helps.

Answer 2

C3P0 is the internal connection-pool implementation for hibernate.

Add "hibernate.connection.provider_class = org.hibernate.connection.C3P0ConnectionProvider" in hibernate properties file. Create a file c3p0.properties setting the parameters accordingly. This file & c3p0-x.jar must be in classpath.

c3p0.properties

• c3p0.idleConnectionTestPeriod : If this is a number greater than 0, c3p0 will test all idle, pooled but unchecked-out connections, every this number of seconds.

• c3p0.testConnectionOnCheckout : Use only if necessary. Expensive. If true, an operation will be performed at every connection checkout to verify that the connection is valid. Better choice: verify connections periodically using idleConnectionTestPeriod.

There are several other properties that can be configured in hibernate.properties & c3p0.properties.