Prevent closing ResultSet on transaction commit

Question

I must preserve a ResultSet which was opened within a @Transactional Controller, to be consumed within a MessageConverter. To that end I have configured the following:

1. MVC Interceptor:

   <mvc:interceptors>
      <bean class="org.springframework.orm.hibernate4.support.OpenSessionInViewInterceptor" 
            p:sessionFactory-ref="sessionFactory"/>
   </mvc:interceptors>
   

2. on the SessionFactory bean:

   <bean id="sessionFactory" class="org.springframework.orm.hibernate4.LocalSessionFactoryBean"
     <property name="hibernateProperties">
       <props>
         <prop key="hibernate.connection.release_mode">on_close</prop>
         ....
       </props>
     </property>
   </bean>
   

3. within the Controller method:

   session.doWork((con) -> { con.setHoldability(HOLD_CURSORS_OVER_COMMIT); });
   

Yet the PSQLException: This ResultSet is closed. persists. This is a relevant snippet in the log when the transaction commits upon controller method return:

TRACE o.h.e.j.i.JdbcCoordinatorImpl - Registering result set [org.apache.tomcat.dbcp.dbcp2.DelegatingResultSet@5a63c2aa] 
DEBUG o.h.e.t.s.AbstractTransactionImpl - committing 
TRACE o.h.internal.SessionImpl - Automatically flushing session 
TRACE o.h.internal.SessionImpl - before transaction completion 
DEBUG o.h.e.t.i.j.JdbcTransaction - committed JDBC Connection 
DEBUG o.h.e.t.i.j.JdbcTransaction - re-enabling autocommit 
TRACE o.h.e.t.i.TransactionCoordinatorImpl - after transaction completion 
TRACE o.h.internal.SessionImpl - after transaction completion 
TRACE o.h.internal.SessionImpl - Setting flush mode to: MANUAL 
DEBUG o.h.internal.SessionImpl - Disconnecting session 
TRACE o.h.e.j.i.JdbcCoordinatorImpl - Releasing JDBC container resources [org.hibernate.engine.jdbc.internal.JdbcCoordinatorImpl@97bfa5f] 
TRACE o.h.e.j.i.JdbcCoordinatorImpl - Closing result set [org.apache.tomcat.dbcp.dbcp2.DelegatingResultSet@5a63c2aa] 
TRACE o.h.e.j.i.JdbcCoordinatorImpl - Closing prepared statement [select...] 
DEBUG o.h.e.j.i.LogicalConnectionImpl - Releasing JDBC connection 
DEBUG o.h.e.j.i.LogicalConnectionImpl - Released JDBC connection 
DEBUG o.h.e.j.s.SqlExceptionHelper - could not advance using next() [n/a] org.postgresql.util.PSQLException: This ResultSet is closed.

Is there something more I can do to stop this from happening?

Answer 1

By default Spring will manage the Hibernate session on its own. One of the consequences, as documented in the HibernateTransactionManager Javadoc, is that Spring will explicitly call session.disconnect(), which will invalidate your connection.release_mode=on_close setting. To change this behavior to Hibernate-managed sessions, be sure to set the hibernateManagedSession property on HibernateTransactionManager to true:

<bean id="transactionManager" 
   class="org.springframework.orm.hibernate4.HibernateTransactionManager"
   p:hibernateManagedSession="true" />

Doing it this way is bound to disturb certain mechanisms which assume the default behavior. One of them is transaction management via the TransactionTemplate: no Hibernate session will be automatically created. This can be fixed by explicitly binding a session to the current thread, and is best captured in a subclass of TransactionTemplate:

@Component
public class HibernateTransactionTemplate extends TransactionTemplate
{
  @Autowired private SessionFactory sf;

  @Autowired @Override public void setTransactionManager(PlatformTransactionManager txm) {
    super.setTransactionManager(txm);
  }

  @Override public <T> T execute(TransactionCallback<T> action) throws TransactionException {
    final Session ses = sf.openSession();
    TransactionSynchronizationManager.bindResource(sf, new SessionHolder(ses));
    try { return super.execute(action); }
    finally {
      ses.close();
      TransactionSynchronizationManager.unbindResource(sf);
    }
  }
}