How to handle: Communication failure

Question

We are using Spring 2.5.4 / Hibernate 3.2 / Websphere Application Server 6.1.0.17. We deployed the application in an AIX window. The next day, when I log in, I try to enter the application. I get this exception on the page:

Error 500: Request processing failed; nested exception is org.springframework.dao.DataAccessResourceFailureException: could not execute query; nested exception is org.hibernate.exception.JDBCConnectionException: could not execute query

I checked the System.Out log and see further details. (Used by pastebin, because formatting the log does distort page layout)

---

The line of our code that throws our exception is:

 List loginList = getHibernateTemplate().find("from Login where storeId =" + id + " and password ='" + password + "'"); 

We connect the connection in Spring applicationContext.xml. We know that the connection on the AS400 crashes from time to time (they can restart the system overnight - I'm not sure). However, we would not want to open a new connection when we connect everything in applicationContext.

This issue occurs regardless of using DataSource / JNDI or JDBC

Does anyone know of any settings to add to Spring or Hibernate to check if the connection is out of date, and if so, drop it and create a new one? Or any other ideas to solve this problem? Let me know if you need more information / code.

Very important,

Chris

Update:

I checked several posts on the Spring community forums, and I implemented my DataSource with commons-dbcp, which has some properties like testWhileIdle and validationQuery. I am going to leave the application and check it again in AM. Will publish an update of the results.

Update # 2:

Using dbcp-commons The BasicDataSource source file fixes this problem, which seems to be a network problem. Websphere has merged connections, and if there is a problem with the network on the AS400 side, it will try to use its existing connection, which, as he does not know, is "obsolete". Using validationQuery with a time interval is a cheap (but effective) way to solve this problem, but there may be a better way on the Websphere side in the configuration. But, it may also not change what is not broken, so until it works, it will probably be our decision in the future.

Answer 1

Ah, that’s what I was going to say ... the problem should be solved in the connection pool by checking the connections before returning them. DBCP has validationQuery, and JBoss also has something similar; I am sure that Websphere should have something similar in the connection pool in order to verify the connections before transferring them. Even if you do not use testWhileIdle, if the connection is declared invalid, a new one is created and sent to you by the pool instead of an invalid one.