What transaction manager should I use for JBDC template When using JPA ?

Question

I am using standard JPA transaction manager for my JPA transactions. However, now I want to add some JDBC entities which will share the same 'datasource'. How can I make the JDBC operations transactional with spring transaction? Do I need to swith to JTA transaction managers? Is it possible to use both JPA & JDBC transactional service with same datasource? Even better, is it possible to mix these two transactions?

UPDATE: @Espen :

I have a dao extended from SimpleJdbcDaoSupport which uses getSimpleJDBCTemplate.update to insert a database row. When a RuntimeException is thrown from the service code, the transaction never rolls back when using JPATransactionManager. It does rollback when using DatasourceTransactionManager. I tried to debug the JPATransactionManager and seems that it never performs rollback on underlying JDBCConnection(I guess due to the fact that the datasource is not necessarily has to be JDBC for JPA). My configuration setup are exactly like you explained here.

Here are my test codes:

<context:property-placeholder location="classpath:*.properties"/>

<!-- JPA EntityManagerFactory -->
<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    <property name="persistenceXmlLocation"
        value="classpath:/persistence-test.xml" />
    <property name="persistenceProvider">
        <bean class="org.hibernate.ejb.HibernatePersistence" />
    </property>

</bean>

<bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactory"/>
</bean>

<!--
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSource"></property>
</bean>
-->

<!-- Database connection pool -->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="${database.driverClassName}" />
    <property name="url" value="${database.url}" />
    <property name="username" value="${database.username}" />
    <property name="password" value="${database.password}" />
    <property name="testOnBorrow" value="${database.testOnBorrow}" />
    <property name="validationQuery" value="${database.validationQuery}" />
    <property name="minIdle" value="${database.minIdle}" />
    <property name="maxIdle" value="${database.maxIdle}" />
    <property name="maxActive" value="${database.maxActive}" />
</bean>




<!-- Initialize the database -->
<!--<bean id="databaseInitializer" class="com.vantage.userGroupManagement.logic.StoreDatabaseLoader">
    <property name="dataSource" ref="storeDataSource"/>
</bean>-->

<!-- ANNOTATION SUPPORT -->

<!-- Enable the configuration of transactional behavior based on annotations -->
<tx:annotation-driven transaction-manager="transactionManager"/>

<!-- JPA annotations bean post processor -->
<bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor"/>

<!-- Exception translation bean post processor (based on Repository annotation) -->
<bean class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor"/>

<!-- throws exception if a required property has not been set -->
<bean class="org.springframework.beans.factory.annotation.RequiredAnnotationBeanPostProcessor"/>


<bean id="userService" class="com.rfc.example.service.UserServiceImpl"> 
    <property name="userDao" ref="userDao"></property>
    <property name="contactDao" ref="contactDao"></property>
    <property name="callRecordingScheduledProgramTriggerDAO" ref="com.rfc.example.dao.CallRecordingScheduledProgramTriggerDAO"></property>
</bean>

<bean id="userDao" class="com.rfc.example.dao.UserDaoJPAImpl" />

<bean id="contactDao" class="com.rfc.example.dao.ContactDaoJPAImpl"></bean>

<bean id="com.rfc.example.dao.CallRecordingScheduledProgramTriggerDAO" class="com.rfc.example.dao.CallRecordingScheduledProgramTriggerDAOJDBCImpl">
    <property name="dataSource" ref="dataSource"></property>
</bean>

AND HERE IS THE DAO:

@Transactional

public class CallRecordingScheduledProgramTriggerDAOJDBCImpl extends SimpleJdbcDaoSupport implements CallRecordingScheduledProgramTriggerDAO{ private static final Log log = LogFactory.getLog(CallRecordingScheduledProgramTriggerDAOJDBCImpl.class);

@SuppressWarnings("unchecked")
public CallRecordingScheduledProgramTrigger save(
        CallRecordingScheduledProgramTrigger entity) {
    log.debug("save -> entity: " + entity);



    String sql = null;
    Map args = new HashMap();

    String agentIdsString = getAgentIdsString(entity.getAgentIds());


    String insertSQL = "insert into call_recording_scheduled_program_trigger" +
            "       (  queue_id, queue_id_string, agent_ids_string, caller_names, caller_numbers, trigger_id, note, callcenter_id, creator_id_string, creator_id) " +
            " values(:queueId, :queueIdString, :agentIdsString, :callerNames, :callerNumbers, :triggerId, :note, :callcenterId , :creatorIdString, :creatorId  )";

    args.put("queueId", entity.getQueueId());
    args.put("agentIdsString",agentIdsString);
    args.put("callerNames", entity.getCallerNames());       
    args.put("queueIdString", entity.getQueueIdString());
    args.put("callerNumbers", entity.getCallerNumbers());
    args.put("triggerId", entity.getTriggerId());
    args.put("note", entity.getNote());
    args.put("callcenterId", entity.getCallcenterId());
    args.put("creatorId", entity.getCreatorId());
    args.put("creatorIdString", entity.getCreatorIdString());

    sql = insertSQL;
    getSimpleJdbcTemplate().update(sql, args);
    System.out.println("saved: ----------" + entity);
    return entity;
}

}

Here is the client code that calls the dao and throws exception (spring service)

@Transactional(propagation=Propagation.REQUIRED)
public void jdbcTransactionTest() {
    System.out.println("entity: " );
    CallRecordingScheduledProgramTrigger entity = new CallRecordingScheduledProgramTrigger();

    entity.setCallcenterId(10L);
    entity.setCreatorId(22L);
    entity.setCreatorIdString("sajid");
    entity.setNote(System.currentTimeMillis() + "");
    entity.setQueueId(22);
    entity.setQueueIdString("dddd");
    String triggerId = "id: " + System.currentTimeMillis();
    entity.setTriggerId(triggerId);
    callRecordingScheduledProgramTriggerDAO.save(entity);

    System.out.println("entity saved with id: " + triggerId );

    throw new RuntimeException();
}

NOTE: the code works as expected when using DatasourceTransactionManager

UPDATE - 2:

Ok I have found the root cause of the problem. Thanks to Espen.

My entity manager configuration was like this(copied from spring pet-clinic app):

    <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    <property name="persistenceXmlLocation"
        value="classpath:/persistence-test.xml" />
    <property name="persistenceProvider">
        <bean class="org.hibernate.ejb.HibernatePersistence" />
    </property>

</bean>

Then I changed it to like this:

    <bean id="entityManagerFactory"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="persistenceXmlLocation"
        value="classpath:/persistence-test.xml" />
    <property name="dataSource" ref="dataSource"/>

    <property name="jpaVendorAdapter">
        <bean
            class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
       <property name="showSql" value="true" />
       <property name="generateDdl" value="true" />
       <property name="databasePlatform" value="org.hibernate.dialect.MySQL5Dialect" />
    </bean>

 </property>
</bean>

Now everything seems to be working! Can anyone explain the difference between these two approach ?

Answer 1

I've not really worked this out in detail yet as I've not mixed both JDBC and JPA but if you get your JDBC connection for an XA datasource then they are JTA transaction. So if you run your code in Stateless session bean for example with transaction turned on, then you automatically get both your Entities and JDBC managed by JTA.

EDIT Here is an example code from Servlet

private @Resource DataSource xaDatasource;
private @Resource UserTransaction utx;
private @PersistenceUnit EntityManagerFactory factory;

public void doGet(HttpServletRequest req, HttpServletResponse res) ... {
   utx.begin();
   //Everything below this will be in JTA
   Connection conn = xaDatasource.getConnection();
   EntityManager mgr = factory.createEntityManager();
   //Do your stuff
   ...
   utx.commit();
}

Disclaimer: Code not tested.

Just realize this is not Spring but I'll leave it up anyway

Answer 2

It's possible to mix JPA and JDBC code in the same transaction using the JpaTransactionManager.

A snippet from Spring 3's JavaDoc:

This transaction manager 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 JPA and services which use plain JDBC (without being aware of JPA)!

You should be aware though that JPA caches the queries and executes all of them at the end of a transaction. So if you want to persist some data inside a transaction with JPA and then retrieve the data with JDBC, it will not work without explicitely flushing the JPA's persistence context before you attempt to retreive it with JDBC code.

A code example that asserts with JDBC code that the JPA code deleted a row inside a transaction:

@Test
@Transactional
@Rollback(false)
public void testDeleteCoffeeType() {

    CoffeeType coffeeType = coffeeTypeDao.findCoffeeType(4L);
    final String caffeForte = coffeeType.getName();

    coffeeTypeDao.deleteCoffeeType(coffeeType);
    entityManager.flush();

    int rowsFoundWithCaffeForte = jdbcTemplate
        .queryForInt("SELECT COUNT(*) FROM COFFEE_TYPES where NAME = ?", 
            caffeForte);
    assertEquals(0, rowsFoundWithCaffeForte);
}

And if you prefer to use the JpaTemplate class, just replace the entityManager.flush() with jpaTemplate.flush();

In response to Sajids' comment: With Spring you can configure a transaction manager that supports both JPA and JDBC like this:

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

<!-- Transaction manager -->
<bean id="transactionManager" class="org.springframework.orm.jpa
            .JpaTransactionManager">
    <property name="entityManagerFactory" ref="entityManagerFactory" />
</bean>

In order to make it work, the JDBC queries must be executed with the JdbcTemplate or the SimpleJdbcTemplate class. In your case with the DAO that extends the SimpleJdbcDaoSupport, you should use the getSimpleJdbcTemplate(..) method.

And finally to let two DAO methods participate in the same transaction, call both DAO methods from a service class metho annotated with @Transactional. With the <tx:annotation-driven> element in your config, Spring will handle the transaction for you with the given transaction manager.

On the business layer:

public class ServiceClass {..

@Transactional
public void updateDatabase(..) {
  jpaDao.remove(..);
  jdbcDao.insert(..);
}
}

Edit 2: Then something is wrong. It works for me exactly as specified in the Javadoc. Does your entity manager has a datasource property like my bean below? It will only work as long you're injecting the same datasource into the entity manager and your extended JpaDaoSupport classes.

<bean id="entityManagerFactoryWithExternalDataSoure" primary="true"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor
                .HibernateJpaVendorAdapter" />
    </property>
    <property name="jpaProperties">
        <value>
            hibernate.format_sql=true
        </value>
    </property>
</bean>