How to synchronize JOOQ with SpringBatch JdbcTemplate

Question

I'm working with Spring batch and trying to build TASKLET with two 'ORM' frameworks: use jdbcTemplate for simple queries, and JOOQ framework for more complex query.

Here is a part of spring config:

<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    <property name="dataSource" ref="dataSourceDbcp_MySQL" /> 
</bean>
<bean id="jobRepository" class="org.springframework.batch.core.repository.support.MapJobRepositoryFactoryBean">
    <property name="transactionManager" ref="transactionManager" />
</bean>
<bean id="jobLauncher" class="org.springframework.batch.core.launch.support.SimpleJobLauncher">
    <property name="jobRepository" ref="jobRepository" />
</bean>
<job id="importProductsJob" xmlns="http://www.springframework.org/schema/batch">
    <step id="readWrite">
        <tasklet transaction-manager="transactionManager">
            <chunk  reader="multiResourceReader" 
                    processor="itemProcessor" 
                    writer="itemWriter" 
                    commit-interval="250" />
        </tasklet>
    </step>
</job>
<bean id="itemWriter" class="com.myexample.writer.JdbcSequenceWriter">
    <property name="dataSourceTransactionManager" ref="transactionManager" />
    <!-- and some more specific properties -->
</bean>

I initialise my ORMs at setter in com.myexample.writer.JdbcSequenceWriter:

private JdbcTemplate jdbcTemplate;
private JdbcTemplate jdbcTemplate2; 
private DSLContext dslContext;

public void setDataSourceTransactionManager(DataSourceTransactionManager trxManager) {
    this.jdbcTemplate = new JdbcTemplate(trxManager.getDataSource());
    this.jdbcTemplate2 = new JdbcTemplate(trxManager.getDataSource());
    dslContext = DSL.using(trxManager.getDataSource(), SQLDialect.MYSQL);
}

Both jdbcTemplate's has one session and I can INSERT a record using 'jdbcTemplate', and find this record using SELECT with 'jdbcTemplate2'. But if try to INSERT a record with any 'jdbcTemplate' and find it using dslContext (JOOQ ORM) I have empty result. I undarstand that Spring Batch use some tricky tansaction manager, witch rollback all the operation if 'writer' can't finish all it's operations. But how can I synchronize another framework with single transaction manager?

Answer 1

The generally expected behavior is what you observe between JdbcTemplate and dslContext: each acquires a separate DB connection from the DataSource.

Your first finding, with two JdbcTemplate instances, is special behavior which JdbcTemplate had to be specifically designed for. Apparently, all its instances share the same connection internally.

You can make both work (probably) by first acquiring a JDBC Connection from the DataSource and then passing it to both the JdbcTemplate and dslContext. This is not the supported usage pattern and there may still be problems with it.

Once you have acquired a connection, naturally you will be in charge of releasing it as well.