Spring(Boot), QueryDSL and @Transactional

[google...@sql-workbench.net]

Hello,

I can't get Spring's @Transcaction to work with QueryDSL (neither when running the application nor when running DbUnit tests)

My setup is as follows.

I have a @Configuration class that looks like this:

@EnableTransactionManagement
@Configuration
@PropertySources({
    @PropertySource(value = "dbunit.properties", ignoreResourceNotFound = false),
    @PropertySource(value = "dbunit-${user.name}.properties", ignoreResourceNotFound = true)
})
@ComponentScan
public class TestConfig {

    @Autowired
    private Environment env;

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource driverManagerDataSource = new DriverManagerDataSource();
        driverManagerDataSource.setDriverClassName(env.getProperty("jdbc.driverClassName"));
        driverManagerDataSource.setUrl(env.getProperty("jdbc.url"));
        driverManagerDataSource.setUsername(env.getProperty("jdbc.username"));
        driverManagerDataSource.setPassword(env.getProperty("jdbc.password"));
        return driverManagerDataSource;
    }
   
    @Bean
    public PlatformTransactionManager transactionManager(DataSource ds) {
        return new DataSourceTransactionManager(ds);
    }

    @Bean
    public SQLQueryFactory sqlQueryFactory(DataSource ds) {
        SQLTemplates sqlTemplates = new PgTemplate();
        com.querydsl.sql.Configuration configuration = new com.querydsl.sql.Configuration(sqlTemplates);
        return new SQLQueryFactory(configuration, ds, false);
    }

}

We are using Postgres and to be able to use things like array_agg() we are using our own Template which extends PostgreSQLTemplates.

In our Service class we have a method like this:

@Transactional
public Integer addPlanningUnit(PlanningUnitDTO plu, PlannedCampaignDTO campaign) {
    try {
        Integer id = sqlQueryFactory.insert(QPlanningUnit.planningUnit).populate(planningUnitDTO.toPlaningUnit()).executeWithKey(Integer.class);
        campaign.setPlanningUnitId(id);
        sqlQueryFactory.insert(QPlannedCampaign.plannedCampaign).populate(campaign).execute();
    } catch (RuntimeException rte) {
        logger.error("Could not save planningUnit with name={}", planningUnitDTO.getName(), rte);
        throw rte;
    }
    return id;
}

Now if the second insert fails, the first insert needs to be rolled back as well. However the @Transactional annotation does not seem to work here.

I also tried injecting the DataSource into the service class and then do manual transaction handling like this:

TransactionDefinition def = new DefaultTransactionDefinition();
TransactionStatus trans = transactionManager.getTransaction(def);
try {
    ....
    transactionManager.commit(trans);
} catch (RuntimeException rte) {
    transactionManager.rollback(trans);
    throw rte;
}

But that didn't work either. The first insert is always committed (seems like the connection is never set to autocommit = off)

I also tried

@EnableTransactionManagement(proxyTargetClass = true, mode = AdviceMode.PROXY)

In the TestConfig class, but that didn't help as well. I also tried creating an interface for the Service. I also tried annotating the class with @Transactional not only the methods.

When debugging the code I can see that the two inserts are executed on two different phyiscal connections which explains why they can't be handled in a single transaction.

I'm sure I'm overlooking something obivous here, but I can't figure out what it is.

[timowest]

To get a tx bound connection you will need to use Querydsl Spring integration classes http://www.querydsl.com/static/querydsl/4.1.2/reference/html_single/#d0e1449

[google...@sql-workbench.net]

Ah, thanks.

Using

Provider<Connection> provider = new SpringConnectionProvider(dataSource());

did the trick.

There is one minor problem though: when I call a service method that is not marked @Transactional (e.g. because it only reads data) I get an exception: "Connection is not transactional".

It's caused by the check isConnectionTransactional() in SpringConnectionProvider and I wonder why that check is there. DataSourceUtils.getConnection() does return a properly configured connection in case of a transactional method.

Do I lose anything if I use my own provider that does not check this (but does use DataSourceUtils.getConnection())?
Or is it better to mark the whole Service class (or all methods) as transactional?

Thanks again for the quick answer.