Help!! Spring and Hibernate sessions

[Fragant]

Here we are trying to solve a problem and hope someone can help us because, if a Java application (not used in Tomcat) over the weekend, then on Monday morning, the sessions and connections in the pool are "dead". The first request then provides an exception like "java.sql.SQLException: I / O Error: Connection reset by peer: socket write error". The only solution so far: Restarting application in Tomcat. The whole has been configured using Spring with a session Factory (LocalSessionFactoryBean), TransactionManager (HibernateTransactionManager) and data source (JndiObjectFactoryBean). This combination works great so long to be regularly removed from the client requests and therefore still a valid session or connection is available.

[PARRISH]

Hello Fragant,
You can create discardConnection and then create new one after weekend task with the quartz. You should keep in mind that every half an hour during weekend it should send one to select DBServer. I am sure that will help you.

[Fragant]

Thank you for the answer!! No mySQL, but MS-SQL Server 2005. I'm sorry, of course, was worth mentioning ... As a JDBC driver is used jTDS 0.9.1. I could probably take one's own Transaction Manager, which inherited the original manager, and intercept the exception is explicit, but I suspect that it will go well with available materials (such as discardConnectionAndCreateNewOneAfterWeekend = true). Please continue to help.

[RichiRich]

The coding that is mentioned below may help you :

'! - Data Source -> 
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> 
<property name="driverClassName"> <value> jdbc.driverClassName $ () </ value> </ property> 
<property name="url"> <value> jdbc.url $ () </ value> </ property> 
<property name="username"> <value> jdbc.username $ () </ value> </ property> 
<property name="password"> <value> jdbc.password $ () </ value> </ property> 
<property name="validationQuery"> <value> jdbc.validationQuery $ () </ value> </ property> 
<property name="maxActive"> <value> jdbc.maxActive $ () </ value> </ property> 
'/ Bean>

The "ValidationQuery" is something like "select 1", and database-specific. The important thing is that it works to your DB and that it is fast.

[Timothi]

Even I think that the coding given by the "RichiRich", can really prove useful for you. The mechanism works as follows :

1. Connection is retrieved from the pool
2. The ValidationQuery is tested, whether they are still "alive"
3. If not, the exception is caught, the connection is discarded, a new one is created (in effect a reconnect) and inserted into the pool
4. The functioning Connection is returned

The reason is - always with the restriction that I can remember / have seen - that it is not possible with JDBC, see a connection, whether they come to an end 'is. The only possible test is to do a query with the Connection.

[Allan.d]

My guess based on intuition-only: Connections in the pool have a timeout (database side): when they exceed this, they will be closed from database side. The pool gets the big time with ... If this is true then should be the connection pool configures better - such as hanging by the pool, and documented, there is probably enough to ...

[CodGuru]

Here again are the final solution. The Property "validationQuery" there is not with us because we use the class as a data source JndiObjectFactoryBean. For the Connection using JNDI lookup on a container managed connection pool is concerned. Instead, one can in the Tomcat context.xml or context where the resource was configured, insert the attribute "validationQuery"

 <Resource name = "jdbc / jndiname" 
auth = "Container" 
type = "javax.sql.DataSource" 
username = "sa" 
password = "XXX" 
Driver Class Name = "net.sourceforge.jtds.jdbc.Driver" 
url = "jdbc: jTDS: sqlserver: / / HOSTNAME / DBNAME; NamedPipes = false; instance = MSSMLBIZ" 
validationQuery = "select 1" 
maxActive = "8" 
maxIdle = "4" />