How to work with transactions? Found some strange behavior.

[kirill-kravchenko]

I'm using spring-boot-starter-data-jdbc and infobip-spring-data-jdbc-querydsl-boot-starter(5.4.2) together.
In my tests with org.springframework.transaction.annotation.Transactional annotation I have the following code:

@Test
@Transactional
void test() {
    repository.save(ReportList.builder().userName("username").build());

    var reports = repository.query(query -> query
            .select(entityProjection())
            .from(QReportList.reportList)
            .fetch());
}

Expected result: variable reports is not empty
Actual result: variable reports is empty

If I remove @Transactional (or use default findAll method from QuerydslJdbcRepository) it starts to return results.
Am I doing something wrong?

[kirill-kravchenko]

After some time of digging I found the following lines of code:

public class SQLQueryFactory extends AbstractSQLQueryFactory<SQLQuery<?>> {

    static class DataSourceProvider implements Provider<Connection> {

        @Override
        public Connection get() {
            try {
                return ds.getConnection();
            } catch (SQLException e) {
                throw new RuntimeException(e.getMessage(), e);
            }
        }
    }

ds.getConnection() returns new connection instead of existing one (with transaction).

I added querydsl-sql-spring with the following configuration and my code start to work as expected:

@Bean
public SQLQueryFactory queryFactory(com.querydsl.sql.Configuration configuration, DataSource dataSource) {
   var provider = new SpringConnectionProvider(dataSource);
   return new SQLQueryFactory(configuration, provider);
}

Should I stick with it or there is another way to work with transactions? Is it possible to add some details to README?

[lpandzic]

My understanding is that the issue is caused by 2 connections being used and changes in one are not visible in the other.
With SpringConnectionProvider connection is reused through Spring Transaction Management connection retrieval infrastructure which makes the issue go away.
I'll close the issue once the fix has been released.

[lpandzic]

Fixed in 6.2.1