Hi, i am using Hibernate & Spring for database access and want to know how to capture just the SQLException related to connection pool failure

We are using the Multi-pool using Weblogic 8.1 for the Hibernate 3.0 and need to intercept just the connection pool failure condition.

I think lots of people might have handled this type situation, please share your thoughts.


public int getSequence() throws Exception {
Integer nextVal;
try {
nextVal = (Integer) getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session session) {
Query query = session.createSQLQuery("select testseq.nextval as nextval from dual") .addScalar("nextval", Hibernate.INTEGER);
return query.uniqueResult();
}
});
} catch (DataAccessException ex) {
throw new Exception("db exception", ex);
}

return nextVal.intValue();
}


In the above code, we are capturing DataAccessException and do not know the exception is due to some connection pool problem or not. Spring provides HibernateInterceptor but i do not know that will help to solve this problem. Also, it would be good that if i capture at the top level instead of handling in each of DAOImpl.

Thanks in advance for you help.

-Cabir

Maybe I am missing something if I have misunderstood your question.

Just having new catch block with SQLException should work, if you want to catch that particular exception.

Hi bkmr_77, thanks for your imformation.

I think Hibernate might convert the SQLException to HibernatException and Spring will convert all checked exceptions (including HibernateException) to an unchecked exception (namely DataAccessException). Correct me if i am wrong.

So, it won't throw SQLException and do not know how to handle connection pool failure condition.

Also, i tried inserting an interceptor for org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider to capture the getConnection() method and see if there is any exception during getConnection() method, but it is not working with Hibernate. Because, the hibernate is doing some caching of the database connection in org.hibernate.jdbc.ConnectionManager

Is it possible to tell ConnectionManager not to cache the connection?

I am really struck with this and any help would be really appreciated.

Thanks,
Cabir
PS: Basic requirement is to capture the connection pool failure and update an internal variable and to set back the same variable to good state when the connection pool is restored.

In such scenario, I have no idea how to capture required exception. Curious though, what is the source of the error? - Is it because max size reached in connection pool.

As far as I understand you can analyse nested exceptions, which are accessibly using Exception.getCause(). These exceptions form a stack and HibernateException wraps simple the cause (which may be SQLException or be in turn other exception wrapping SQLException etc). So analysing exception stack you can seek the SQLException and analyse its error message.

Hi Slava,

thanks very much for the reply. The SQLException is created by weblogic wrapped by ResourceException. There is no SQL error code associated and there are different messages for different scenarios (like shutting down of DB, listeners gone, administratively the pool disabled, so and so). There is no unique way of detecting the pool failure.

I am planning to detect the pool failure at the getConnection() method call of DataSource.

I added an interceptor to capture the getConnection() method call and detect the pool failure. It worked for direct datasource.getConnection(). When it is called using the Hibernate, it does stores local copy of Connection object in ConnectionManager. I read the source code and the news group http://forum.springframework.org/showthread.php?t=19825
looks like there is an Hibernate bug which sets the aggresiveRelease mode to close on session close. Now i have to try to set the release mode to "auto" and try.

I will post my test result later if it works. Meanwhile if someone has better idea to handle this scenario, please share your thoughts.

Thanks,
CAbir