What's the best way to share a connection between Hibernate's SessionFactory and a JDBC DAO?

Question

I'm using Spring 3.0.6, with Hibernate 3.2.7.GA in a Java-based webapp. I'm declaring transactions with @Transactional annotations on the controllers (as opposed to in the service layer). Most of the views are read-only.

The problem is, I've got some DAOs which are using JdbcTemplate to query the database directly with SQL, and they're being called outside of a transaction. Which means they're not reusing the Hibernate SessionFactory's connection. The reason they're outside the transaction is that I'm using converters on method parameters in the controller, like so:

@Controller
@Transactional
public class MyController {
    @RequestMapping(value="/foo/{fooId}", method=RequestMethod.GET)
    public ModelAndView get(@PathVariable("fooId") Foo foo) {
        // do something with foo, and return a new ModelAndView
    }
}

public class FooConverter implements Converter<String, Foo> {
    @Override
    public Foo convert(String fooId) {
        // call FooService, which calls FooJdbcDao to look up the Foo for fooId
    }
}

My JDBC DAO relies on SimpleJdbcDaoSupport to have the jdbcTemplate injected:

@Repository("fooDao")
public class FooJdbcDao extends SimpleJdbcDaoSupport implements FooDao {
    public Foo findById(String fooId) {
        getJdbcTemplate().queryForObject("select * from foo where ...", new FooRowMapper());
        // map to a Foo object, and return it
    }
}

and my applicationContext.xml wires it all together:

<mvc:annotation-driven conversion-service="conversionService"/>

<bean id="conversionService" class="org.springframework.context.support.ConversionServiceFactoryBean">
   <property name="converters">
       <set>
           <bean class="FooConverter"/>
           <!-- other converters -->
       </set>
   </property>
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
   <property name="dataSource" ref="dataSource"/>
</bean>
<bean id="transactionManager" class="org.springframework.orm.hibernate3.HibernateTransactionManager"
        p:sessionFactory-ref="sessionFactory" />

FooConverter (which converts a path variable String to a Foo object) gets called before MyController#get() is called, so the transaction hasn't been started yet. Thus when FooJdbcDAO is called to query the database, it has no way of reusing the SessionFactory's connection, and has to check out its own connection from the pool.

So my questions are:

1. Is there any way to share a database connection between the SessionFactory and my JDBC DAOs? I'm using HibernateTransactionManager, and from looking at Spring's DataSourceUtils it appears that sharing a transaction is the only way to share the connection.

2. If the answer to #1 is no, then is there a way to configure OpenSessionInViewFilter to just start a transaction for us, at the beginning of the request? I'm using "on_close" for the hibernate.connection.release_mode, so the Hibernate Session and Connection are already staying open for the life of the request.

The reason this is important to me is that I'm experiencing problems under heavy load where each thread is checking out 2 connections from the pool: the first is checked out by hibernate and saved for the whole length of the thread, and the 2nd is checked out every time a JDBC DAO needs one for a query outside of a transaction. This causes deadlocks when the 2nd connection can't be checked out because the pool is empty, but the first connection is still held. My preferred solution is to make all JDBC DAOs participate in Hibernate's transaction, so that TransactionSynchronizationManager will correctly share the one single connection.

Answer 1

1. Is there any way to share a database connection between the SessionFactory and my JDBC DAOs? I'm using HibernateTransactionManager, and from looking at Spring's DataSourceUtils it appears that sharing a transaction is the only way to share the connection.

--> Well you can share database connection between SessionFactory and JdbcTemplate. What you need to do is share same datasource between the two. Connection pooling is also shared between the two. I am using it in my application.

What you need to do is configure HibernateTransactionManager for both transactions.

Add JdbcDao class(with properties jdbcTemplate and dataSource with getter-setter) in your existing package structure(in dao package/layer), Extend your jdbc implementation classes by JdbcDao. If you have configured, hibernateTxManager for hibernate, you will not need to configure it.

The problem is, I've got some DAOs which are using JdbcTemplate to query the database directly with SQL, and they're being called outside of a transaction. Which means they're not reusing the Hibernate SessionFactory's connection.

--> You may be wrong here. You may be using same connection, I think, only problem may lie in HibernateTransaction configuration.

Check HibernateTransactionManager javadoc : This transaction manager is appropriate for applications that use a single Hibernate SessionFactory for transactional data access, but it also supports direct DataSource access within a transaction (i.e. plain JDBC code working with the same DataSource). This allows for mixing services which access Hibernate and services which use plain JDBC (without being aware of Hibernate)!

Check my question : Using Hibernate and Jdbc both in Spring Framework 3.0

Configuration : Add dao classes and service classes with your current hibernate classes, do not make separate packages for them, If you want to work with existing configuration. Otherwise configure HibernateTransactionManager in xml configuration and Use @Transactional annotation.

Mistake in your code :

@Controller
@Transactional
public class MyController {......

Use @Transactional annotation in service classes(best practice).

Correction :

@Transactional(readOnly = true)
public class FooService implements FooService {

  public Foo getFoo(String fooName) {
    // do something
  }

  // these settings have precedence for this method
  @Transactional(readOnly = false, propagation = Propagation.REQUIRES_NEW)
  public void updateFoo(Foo foo) {
    // do something
  }
}