Recovery from a lost database connection

[wpoitras]

I am developing database code which will run on a Oracle 10g RAC. Someone has asked if its possible for the Spring jdbc classes to recover when a node in a RAC goes down, such that the connection is reastablished and then all the statement creation is rerun and the statement is reexecuted automatically without having to write application level code. Right now we have code in various places of our non-Spring JDBC code which doesn't handle it in a generic fashion.

I am particularly concerned about being to able to reregistering TypeMaps for the newly recreated Connection.

Any help would be appreciated.

[Rod Johnson]

You will need to write application code to handle this. For example:

try {
   doThingsWithSpringJdbc();
}
catch (CannotGetJdbcConnectionException ex) {
   // recover here
   // you should be able to use Spring JDBC again, as it should try
   // to obtain another connection from the DataSource
}

[trisberg]

If you are using a connection pool, then this would be the responibility of the connection pool implementation. Each operation in the spring data access layer obtains a connection from the pool and returns it when the operation completes, so unless the connection pool cooperates, you might not be able to control the connections from your application code.

[wpoitras]

It looks like overriding JdbcTemplate might do the trick for me. I noticed that all JdbcTemplate operations call one of the following:

execute CallableStatementCreator, CallableStatementCallback)
execute(ConnectionCallback)
execute(PreparedStatementCreator, PreparedStatementCallback)
execute(StatementCallback)

Each of them create a connection and execute the statments created.

Either create a custom class which wraps each of these operations in a try/catch logic and reexecute the command when certain exceptions are thrown, or use Spring AOP to inject the proper code into the existing JdbcTemplate class.

What do people think?

[Maarten]

Interesting... We're also running on Oracle 10g RAC and my boss would
really like such a feature, although I am quite sceptic.
I think we're never gonna use it in reality and it will complicate things.
But please, let us know your experience.

Succes,
Maarten