hibernate does not reconnect once the database is bounced

Question

I've found that after we've bounced (stopped and started) our database (postgresql 8.3), our apps which use hibernate (3.2.6) fail to re-acquire connections, instead getting a SocketException with the message "broken pipe".

I believe we're configured to use the built in connection pooling.

how can i make hibernate re-acquire connections after a db restart without restarting the app?

p.

Answer 1

What you want is a feature called connection testing provided by connection pools - the connection pool should run a quick query to verify that the connection it is about to hand out is not stale. Unfortunately DriverManagerConnectionProvider, Hibernate's default connection pooling class, does not support this feature. Hibernate team strongly discourages the use of this connection pool in production code.

Hibernate's own connection pooling algorithm is, however, quite rudimentary. It is intended to help you get started and is not intended for use in a production system, or even for performance testing. You should use a third party pool for best performance and stability.

My recommendation is that you switch to some other connection pool implementation. If you switch to C3P0 (which is shipped with Hibernate), connection testing can be configured as explained here. If you use Apache DBCP, it lets you set a validationQuery as explained here.

Answer 2

If you are using hibernate in jboss, you can configure it in file *-ds.xml in deploy directory to execute some query before taking connection from connections pool: For example:

<check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>

If you are using your your own connection pool, you can write something like that:

Connection getConnectionFromPool() {
   try {
      //get connection from pool
      //execute some simple uery that should always work
   } catch (SocketException s) {
      //close broken connection and get a new one
   }
   return connection;
}

Or maybe there is mechanism for this in hibernate, but I don't know about it.

EDIT: ok, reading your question I've missed that you are using hibernate connection pool. So ignore my answer and look here: Recover Hibernate connection