How to configure mutliple transaction managers with Spring + DBUnit + JUnit

Question

In a nutshell

My command line Java application copies data from one datasource to another without using XA. I have configured two separate datasources and would like a JUnit test that can rollback data on both datasources. I use DBUnit to load data into the "source" database, but I cannot get this to rollback. I can get the "target" datasource to rollback.

My Code

Given this config...

<tx:annotation-driven />

<!-- note the default transactionManager name on this one -->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource"     ref="dataSourceA" />
</bean>

<bean id="transactionManagerTarget" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource"     ref="dataSourceB" />
</bean>

and this code...

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"classpath:resources/spring-context.xml",
                                "classpath:resources/spring-db.xml"})  
@Transactional
@TransactionConfiguration(transactionManager = "transactionManagerTarget", defaultRollback = true) 
public class MyIntegrationTest {

    @Autowired
    private MyService service;

    @Autowired
    @Qualifier("dataSourceA")
    private DataSource dataSourceA;

    private IDataSet loadedDataSet;

    /**
     * Required by DbUnit
     */
    @Before
    public void setUp() throws Exception {
        SybaseInsertIdentityOperation.TRUNCATE_TABLE.execute(getConnection(), getDataSet());
        SybaseInsertIdentityOperation.INSERT.execute(getConnection(), getDataSet());
    }

    /**
     * Required by DbUnit
     */
    protected IDataSet getDataSet() throws Exception {
        loadedDataSet = DbUnitHelper.getDataSetFromFile(getConnection(), "TestData.xml");
        return loadedDataSet;
    }

    /**
     * Required by DbUnit
     */
    protected IDatabaseConnection getConnection() throws Exception{
        return new DatabaseConnection(dataSourceA.getConnection());
    }   

    @Test
    public void testSomething() {

        // service.doCopyStuff();

    }

}

The problem as I see it, is that @TransactionConfiguration only states the target datasource for enabling a rollback. DBUnit is being passed dataSourceA explicitly and is picking up the default transaction manager named transactionManager (I'm not sure how) which has not been told to rollback.

Question

How can I tell both transaction managers to rollback?

Can I use a single transaction manager when my datasources do not support XA transactions?

Note: The application does not require a transaction manager on dataSourceA when running in production as it will only be read-only. This issue is for my tests classes only.

Minuscule

Answer 1

A possible workaround would be to introduce a helper bean annotated as @Transactional("transactionManagerTarget") and leave your test annotated as @Transactional("transactionManager"), configuring both with defaultRollback = true. Your test would then have to call the helper bean, which in turn would call your service bean under test. This should cause the transaction around your service to roll back, then the transaction around DBUnit.

It's a bit messy, though.

Other possible approaches:

• Using an in-memory database such as H2 instead of your production database- you could configure this to drop all of its data when required.
• Allow DBUnit to commit, and have a compensating transaction in your tear-down method to clear the data out.

Flouncing

I tried factoring out a helper bean but this does not help the siutation. Somewhere under the hood DBUnit will not rollback when there are multiple transactionManagers. I'm going to accept your answer for using HSQL as the way forward, however in my case I'm still stuck because I use Sybase to create a temporary table on this datasource and the syntax is not compatible with HSQL. – Minuscule

Answer 2

Use the <qualifier> element inside your transaction manager definition.

<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSourceA" />
    <qualifier value="transactionManager" />
</bean>

<bean id="transactionManagerTarget" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSourceB" />
    <qualifier value="transactionManagerTarget" />
</bean>

Then you can reference which one you want to use directly in the @Transactional annotation, i.e.

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations={"classpath:resources/spring-context.xml",
                                "classpath:resources/spring-db.xml"})  
@Transactional("transactionManagerTarget")
@TransactionConfiguration(defaultRollback = true) 
public class MyIntegrationTest {
...

Condenser

My understanding of why this won't work is that the @Before and @Test methods will execute in the same transaction and I am unable to specify more than one @Transactional annotation. In your answer, I can't see how it tells transactionManager to rollback. Am I missing something? Good point on using the <qualifier> though. – Minuscule

I tried various uses of @Transactional on my test class but unfortunately as I suspected the @Test method starts a transaction and the @Before cannot start another for DBUnit so it continues to run with transactionManagerTarget and therefore will not rollback the DBUnit data. I think I tried everything I could using the annotations but no luck. – Minuscule

Answer 3

I have used XA transactions and rollbacks in JUnit tests using the open source TM Atomikos. One nice feature is that Atomikos allows using non-XA enabled data sources to participate in XA transactions. Check this link out for an example: http://www.atomikos.com/Documentation/NonXaDataSource

On the other hand, if XA is a decent solution for your JUnit issues is another story. Do your tests focus a lot on the database implementation (Sybase) or is it more about Java logic? I usually setup embedded DBs like Apache Derby or HQSQL for JUnit tests. Then I do not have to care much about clean ups, since GC will handle that :)

Perfectionist

Thanks for your response. DBUnit is only being used to load test data because that datasource will already have been populated in production by another service. So I have no XA issues in production as I only write to the one target datasource. I like your idea of using HSQL for my "source" dataSource. I just have to figure out how to store my schema for HSQL to load and keep it consistent with the Sybase schema. – Minuscule

Any JTA TM is no good to me because both my dataSources are non-XA. Most of the JTA TM's allow you to involve one non-XA datasource but no more – Minuscule

A very Long shot, but what the.. Skip HQSQL, use Apache Derby for one of the datasources (source). Can't you store the schema in like test/resources/source_db_setup.sql? Derby is XA enabled. Then you could use sybase as your non-xa source. I think there should be some other option for you, although, I don't have any good idea at the moment. – Perfectionist