Querydsl + Spring + Transactional ignored

Question

I want to confirm rollback behavior, but I'm having trouble getting it to work.

I have a postgres DB with the following tables:

select * from cat;

               pkid                   | name  
--------------------------------------+-------
 c75d6e8b-6aff-4214-ad45-d17db254857b | Abbey


select * from toy;

                 pkid                 |     name      |                description                
--------------------------------------+---------------+-------------------------------------------
 dda72782-a1aa-4c0e-9cf6-a408db58a1ae | Laser pointer | Red laser.
 f4d7e67d-1b26-4d8d-bb98-1a5c69f3cb49 | String        | Colored string attached to a plastic rod.

select * from cattoy;

pkid  | fkcat | fktoy 
------+-------+-------

I have created a CatService implementation with the idea being you can create a cat, toy, and associate that toy with that cat. If any one of the 3 operations fails I want them all to rollback.

DefaultCatService.java:

import java.util.List;
import java.util.UUID;

import javax.inject.Inject;

import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.bonkeybee.dao.CatDao;
import com.bonkeybee.dao.CatToyDao;
import com.bonkeybee.dao.ToyDao;
import com.bonkeybee.querydsl.Cat;
import com.bonkeybee.querydsl.Cattoy;
import com.bonkeybee.querydsl.Toy;

@Inject
private CatDao catDao;

@Inject
private CatToyDao catToyDao;

@Inject
private ToyDao toyDao;

@Override
@Transactional
public UUID createCat(final Cat cat){
    LOG.debug("Creating cat");
    UUID catPkid = catDao.createCat(cat);

    Toy toy = new Toy();
    toy.setName("Box");
    toy.setDescription("Cardboard box.");
    toy.setPkid(toyDao.createToy(toy));

    Cattoy catToy = new Cattoy();
    catToy.setFkcat(catPkid);
    catToy.setFktoy(toy.getPkid());
    catToyDao.createCatToy(catToy);

    return catPkid;
}

I have created DAO's and their implementations for each table with basic CRUD operations.

CatDaoJdbc.java:

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.UUID;

import javax.inject.Inject;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.bonkeybee.querydsl.Cat;
import com.bonkeybee.querydsl.QCat;
import com.querydsl.core.BooleanBuilder;
import com.querydsl.core.types.Predicate;
import com.querydsl.core.types.dsl.SimplePath;
import com.querydsl.core.types.dsl.StringPath;
import com.querydsl.sql.SQLQueryFactory;

private static final QCat CAT = QCat.cat;
private static final SimplePath<Object> CAT_PKID = CAT.pkid;
private static final StringPath CAT_NAME = CAT.name;

@Inject
private SQLQueryFactory sqlQueryFactory;

@Override
public UUID createCat(final Cat cat) {
    UUID catPkid = UUID.randomUUID();
    sqlQueryFactory.insert(CAT)
        .columns(CAT_PKID, CAT_NAME)
        .values(catPkid, cat.getName())
        .execute();
    return catPkid;
}

ToyDaoJdbc.java

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.UUID;

import javax.inject.Inject;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.bonkeybee.querydsl.QToy;
import com.bonkeybee.querydsl.Toy;
import com.querydsl.core.BooleanBuilder;
import com.querydsl.core.types.Predicate;
import com.querydsl.core.types.dsl.SimplePath;
import com.querydsl.core.types.dsl.StringPath;
import com.querydsl.sql.SQLQueryFactory;

private static final QToy TOY = QToy.toy;
private static final SimplePath<Object> TOY_PKID = TOY.pkid;
private static final StringPath TOY_NAME = TOY.name;
private static final StringPath TOY_DESCRIPTION = TOY.description;

@Inject
private SQLQueryFactory sqlQueryFactory;

@Override
public UUID createToy(Toy toy) {
    UUID toyPkid = UUID.randomUUID();
    sqlQueryFactory.insert(TOY)
        .columns(TOY_PKID, TOY_NAME, TOY_DESCRIPTION)
        .values(toyPkid, toy.getName(), toy.getDescription())
        .execute();
    return toyPkid;
}

CatToyDaoJdbc.java:

import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.UUID;

import javax.inject.Inject;

import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.bonkeybee.querydsl.Cattoy;
import com.bonkeybee.querydsl.QCat;
import com.bonkeybee.querydsl.QCattoy;
import com.bonkeybee.querydsl.QToy;
import com.bonkeybee.querydsl.Toy;
import com.querydsl.core.BooleanBuilder;
import com.querydsl.core.types.Predicate;
import com.querydsl.core.types.dsl.SimplePath;
import com.querydsl.sql.SQLQueryFactory;

@Override
public UUID createCatToy(Cattoy catToy) {
    throw new RuntimeException("Simulating exception");
}

Main.java:

import java.util.UUID;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.transaction.annotation.Transactional;

import com.bonkeybee.querydsl.Cat;
import com.bonkeybee.querydsl.Cattoy;
import com.bonkeybee.querydsl.Toy;
import com.bonkeybee.service.CatService;
import com.bonkeybee.service.CatToyService;
import com.bonkeybee.service.ToyService;

public static void main(String[] args) {
    try (ConfigurableApplicationContext applicationContext = new AnnotationConfigApplicationContext(ApplicationConfiguration.class)) {
        CatService catService = applicationContext.getBean(CatService.class);

        Cat newCat = new Cat();
        newCat.setName(DORA);
        newCat.setPkid(catService.createCat(newCat));
    }
}

ApplicationConfiguration.java:

import java.beans.PropertyVetoException;

import javax.inject.Inject;
import javax.sql.DataSource;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.querydsl.sql.PostgreSQLTemplates;
import com.querydsl.sql.SQLQueryFactory;

@Configuration
@EnableTransactionManagement
@ComponentScan("com.bonkeybee")
public class ApplicationConfiguration {

    @Bean
    public DataSource getDataSource() throws PropertyVetoException {
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
        comboPooledDataSource.setDriverClass(JDBC_DRIVER);
        comboPooledDataSource.setJdbcUrl(JDBC_URL);
        comboPooledDataSource.setUser(USER);
        comboPooledDataSource.setPassword(PASSWORD);
        comboPooledDataSource.setMinPoolSize(MIN_POOL_SIZE);
        comboPooledDataSource.setInitialPoolSize(MIN_POOL_SIZE);
        comboPooledDataSource.setMaxPoolSize(MAX_POOL_SIZE);
        return comboPooledDataSource;
    }

    @Bean
    @Inject
    public PlatformTransactionManager getPlatformTransactionManager(final DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    public com.querydsl.sql.Configuration getQueryDslConfiguration() {
        return new com.querydsl.sql.Configuration(PostgreSQLTemplates.builder().build());
    }

    @Bean
    @Inject
    public SQLQueryFactory getSQLQueryFactory(final com.querydsl.sql.Configuration configuration, final DataSource dataSource) {
        return new SQLQueryFactory(configuration, dataSource);
    }
}

When Main calls catService.createCat() the cat and toy are created, then a RuntimeException is thrown as expected, however inspection of the tables afterward show the new cat and new toy created instead of being rolled back. Please SO, help me ensure no cat goes toyless >:3

EDIT: Adding imports as requested

Answer 1

Solved it after more searching, there were two configuration issues.

First: the transaction manager bean spring looks for by default should be named "transactionManager" otherwise you have to explicitly set the name.

Second: I added a dependency on "querydsl-sql-spring" artifact and changed my SQLQueryFactory to use a SpringConnectionProvider instead of the DataSource bean (found from this example from the querydsl people). Below is the final configuration:

@Configuration
@EnableTransactionManagement
@ComponentScan("com.bonkeybee")
public class ApplicationConfiguration {

    @Bean
    public DataSource getDataSource() throws PropertyVetoException {
        ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
        comboPooledDataSource.setDriverClass(JDBC_DRIVER);
        comboPooledDataSource.setJdbcUrl(JDBC_URL);
        comboPooledDataSource.setUser(USER);
        comboPooledDataSource.setPassword(PASSWORD);
        comboPooledDataSource.setMinPoolSize(MIN_POOL_SIZE);
        comboPooledDataSource.setInitialPoolSize(MIN_POOL_SIZE);
        comboPooledDataSource.setMaxPoolSize(MAX_POOL_SIZE);
        return comboPooledDataSource;
    }

    @Inject
    @Bean(name = "transactionManager")
    public PlatformTransactionManager getPlatformTransactionManager(final DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    @Bean
    public com.querydsl.sql.Configuration getQueryDslConfiguration() {
        return new com.querydsl.sql.Configuration(PostgreSQLTemplates.builder().build());
    }

    @Inject
    @Bean
    public SQLQueryFactory getSQLQueryFactory(final com.querydsl.sql.Configuration configuration, final DataSource dataSource) {
        Provider<Connection> provider = new SpringConnectionProvider(dataSource);
        return new SQLQueryFactory(configuration, provider);
    }
}

Thanks querydsl people for such a cool lib.