I faced an issue with jOOQ not inserting entities unless the repository is annotated with @Transactional.

Here's my configuration:

@Configuration
@EnableTransactionManagement
@RequiredArgsConstructor
public class PersistenceConfig {

    @Value("${spring.datasource.url}")
    private String dbUrl;

    @Value("${spring.datasource.username}")
    private String dbUser;

    @Value("${spring.datasource.password}")
    private String dbPassword;

    @Bean
    @SneakyThrows
    public DataSource dataSource() {
        HikariConfig config = new HikariConfig();

        // https://mariadb.com/kb/en/about-mariadb-connector-j/
        config.setDriverClassName(DatabaseDriver.MARIADB.getDriverClassName());
        config.setJdbcUrl(dbUrl);
        config.setUsername(dbUser);
        config.setPassword(dbPassword);
        config.setAutoCommit(false);

        // https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration
        config.addDataSourceProperty("cacheServerConfiguration", true);
        config.addDataSourceProperty("useServerPrepStmts", true);
        config.addDataSourceProperty("useLocalSessionState", true);
        config.addDataSourceProperty("cacheResultSetMetadata", true);
        config.addDataSourceProperty("rewriteBatchedStatements", true);
        config.addDataSourceProperty("elideSetAutoCommits", true);
        config.addDataSourceProperty("maintainTimeStats", false);
        config.addDataSourceProperty("cachePrepStmts", true);
        config.addDataSourceProperty("prepStmtCacheSize", 350);
        config.addDataSourceProperty("prepStmtCacheSqlLimit", 2048);

        return new HikariDataSource(config);
    }

    @Bean
    public TransactionAwareDataSourceProxy transactionAwareDataSource() {
        return new TransactionAwareDataSourceProxy(dataSource());
    }

    @Bean
    public DataSourceTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dataSource());
    }

    @Bean
    public DataSourceConnectionProvider connectionProvider() {
        return new DataSourceConnectionProvider(transactionAwareDataSource());
    }

    @Bean
    public ExceptionTranslator exceptionTransformer() {
        return new ExceptionTranslator();
    }

    @Bean
    public SpringTransactionProvider springTransactionProvider() {
        return new SpringTransactionProvider(transactionManager());
    }

    @Bean
    public DefaultConfiguration configuration() {
        DefaultConfiguration jooqConfiguration = new DefaultConfiguration();

        jooqConfiguration.set(connectionProvider());
        jooqConfiguration.set(new DefaultExecuteListenerProvider(exceptionTransformer()));
        jooqConfiguration.set(SQLDialect.MARIADB);
        jooqConfiguration.set(springTransactionProvider());

        return jooqConfiguration;
    }

    @Bean
    public DefaultDSLContext dsl() {
        return new DefaultDSLContext(configuration());
    }

    @Bean
    public TransactionTemplate transactionTemplate() {
        return new TransactionTemplate(transactionManager());
    }

    private static class ExceptionTranslator extends DefaultExecuteListener {
        public void exception(ExecuteContext context) {
            SQLDialect dialect = context.configuration().dialect();
            SQLExceptionTranslator translator
                = new SQLErrorCodeSQLExceptionTranslator(dialect.name());
            context.exception(translator
                .translate("Access database using jOOQ", context.sql(), context.sqlException()));
        }
    }

}

repository:

@Repository
public class UserRepository extends UserDao {

    private final DSLContext dslContext;

    public UserRepository(DSLContext dslContext) {
        super(dslContext.configuration());
        this.dslContext = dslContext;
    }
}

So, calling userRepository.insert(...) doesn't actually insert into the database although the logs say that the following:

org.jooq.tools.LoggerListener            : Executing query          : insert into `user` (...)
org.jooq.tools.LoggerListener            : -> with bind values      : insert into `user` ...

However, If I overload UserDao's insert method and annotate it with @Transacational - it works, the rows actually get inserted. I suppose I have misconfigured something.

Spring Boot with jOOQ boot starter is used.

CodePudding user response：

The problem is actually with setAutoCommit(false).