How to make an integration test with one transaction for all database calls and rollback it afterwards?

I’m, writing an integration test, annotated with @SpringBootTest.

Suppose I create some product using pure SQL from my test, call calculation service that do some calculations based on it, and it calls another service to save those calculation results in different tables.

What I need is to rollback all the changes made after test is finished.

I read different questions, tried to use @Transactional on class or test-method.

Tried to put database property autocommit=false.

Tried to make SqlSession object and Spy it in @Before into service that saves data into different tables and rollback it in @After. Also, tried to use Connection with autocommit=false but it won’t work, it’s all different transactions anyway.

The @Repository beans are also annotated with @Mapper because we use My-Batis. It seems my-batis creates new transaction whenever it’s mapper is called.

So the only idea I have left is to initiate in-memory database for integration tests and use it. Or probably I am missing some things and it could be done in other way just trough the transactions management?

How do I do all the calls in one transaction so they see the changes made and rollback it afterwards?

Here is an example code of a test I am trying to do:

@Slf4j
@RunWith(SpringJUnit4ClassRunner.class)
@SpringBootTest
public class MyTestClass {

    @Autowired
    private DataRepository dataRepository;
    @Autowired
    private CalculateService calculateService;

    @Before
    public void setUp() throws SQLException {
        SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        connection = session.getConnection();
        connection.setAutoCommit(false);
        spySession = Mockito.spy(session);
        // other stuff with  SqlSessionFactory mocking and injecting the mock 
       }
    
    @After
    public void cleanUp() {
        spySession.rollback();
    }

    @Test
    public void myTest(){
        ResultSet resultSet = connection.prepareStatement("Insert into...").executeQuery(); // create new product
        resultSet.next();
        String createdProductId = resultSet.getString(1);

        Product createdProduct = dataRepository.getProduct(createdProductId);
        assertNotNull(createdProduct); // fails here

        calculateService.calculate(createdProduct); // this call creates new threads there
        // and calls other service that saves data in different trasaction aswell

        createdProduct = dataRepository.getProduct(createdProductId); // call again to assert calculation results
        
        assertSomeField(createdProduct.getSomeField);
        // other assertions here
    }

 
}   

Answer

After some time, I found solution to this problem. It is not sofisticated and beatifull, I’d say it’s a bit ugly but it works.

Mybatis’s SqlSession has a method getMapper, using it you can get a mapper that sees the changes done in your current transaction, it looks like this:

DataRepository dataRepository = session.getMapper(DataRepository.class);

So I got all the mappers I need and injected them into beans using ReflectionTestUtils.setField.

SqlSession itself was rolled back in @After annotated method.