spring Hibernate session time out

Question

I'm using Spring Hibernate template apache tomcat and MySQL. Everything works great in development, but when I deploy the app to a test server and let it run (largely idle) overnight, I'm usually greeted with this in the morning:

Following is my spring servlet.xml：

<bean id="multipartResolver"
    class="org.springframework.web.multipart.commons.CommonsMultipartResolver" >
      <property name="maxUploadSize" value="1000000"/>
</bean>


<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close" p:driverClassName="${jdbc.driverClassName}"
    p:url="${jdbc.databaseurl}" p:username="${jdbc.username}" p:password="${jdbc.password}" />

<bean id="sessionFactory"
    class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="configLocation">
        <value>classpath:hibernate.cfg.xml</value>
    </property>

    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">${jdbc.dialect}</prop>
            <prop key="hibernate.show_sql">true</prop>
        </props>
    </property>
</bean>

<tx:annotation-driven transaction-manager="transactionManager" />

<bean id="transactionManager"
    class="org.springframework.orm.hibernate4.HibernateTransactionManager">
    <property name="sessionFactory" ref="sessionFactory" />
</bean>

How can i configure the spring servlet.xml to avoid this problem ? Thanks.

Answer 1

you can use hibernate properties to set pool of sessions and can also configure timeout.

<property name="hibernate.c3p0.timeout">300</property>.

hope this will help you.

Answer 2

Hi @jans it is said we should not use hibernate built-in connection poolin production. You can use C3p0 for connection pooling to avoid the error. Also from something I learned just make sure you are closing all the sessions you used. You can add below code in your "hibernateProperties" property.

<prop key= "hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</prop>      
<prop key="hibernate.c3p0.acquire_increment">5</prop>
<prop key="hibernate.c3p0.idle_test_period">300</prop>
<prop key="hibernate.c3p0.min_size">5</prop>
<prop key="hibernate.c3p0.timeout">1800</prop>