6. Integration of jOOQ and Spring

This chapter focuses on the basic integration of jOOQ and Spring, including automatic data source injection, automatic DAO injection, and transaction management.

Spring Maven Related Dependencies

On top of the previous dependencies, Spring-related dependencies need to be added, and Spring can also be used
Spring's basic dependencies are mainly containers, annotations, and data source dependencies, the HikariCP used here in the connection pool, known as the database connection pool with the highest Java performance


<!-- data source -->

Main Configuration

Data Source Configuration

We put the configuration of the database in the jdbc.properties file in the resource file directory



Next, configure the data source and transaction manager, and enable annotated transaction management

public class DataSourceConfig {
    private String url;

    private String username;

    private String password;

    public PlatformTransactionManager transactionManager(DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);

    public DataSource dataSource() {
        HikariConfig config = new HikariConfig();
        return new TransactionAwareDataSourceProxy(new HikariDataSource(config));


jOOQ related configuration

The code generator for jOOQ can automatically annotate the DAO class as a SpringBean by adding @Repository annotation to it when code is generated with the relevant configuration.The @Autowrited annotation is automatically added to the parametric constructor XxxDao (Configuration configuration)

Code Generator Configuration:
Add the springAnnotations configuration on the previous basis



Generated DAO s such as S1UserDao:

public class S1UserDao extends DAOImpl<S1UserRecord, S1UserPojo, Integer> {
    // ...
    public S1UserDao(Configuration configuration) {
        super(TS1User.S1_USER, S1UserPojo.class, configuration);
    // ...


As you can see, DAO initialization requires a Configuration instance, so we need to declare some jOQ-related beans, in addition to Configuration instances, DSLContext, which is often used, can also be declared SpringBean

public class JooqConfiguration {
    public DSLContext dslContext(org.jooq.Configuration configuration) {
        return DSL.using(configuration);

    public org.jooq.Configuration configuration(DataSource dataSource) {
        org.jooq.Configuration configuration = new DefaultConfiguration();
        return configuration;

With these configurations, we can start a Spring + jOOQ integrated project and add an entry file:

public class Section6Main {
    public static void main(String[] args) {
        ApplicationContext context = new AnnotationConfigApplicationContext(Section6Main.class);

        S1UserDao bean = context.getBean(S1UserDao.class);

        List<S1UserPojo> s1UserPojo = bean.findAll();



test case

Test cases can play a very important role in project development, especially after the test coverage is high, it will play a great role in the stability of the project.However, for most of the back-end programs, many test cases are for database operations, which will inevitably affect the database data.

At this point, there are ways to take advantage of the nature of transactions to roll back the test case after it has been executed.This prevents the generation of dirty data, pollution data, etc.

JDBC-style transaction rollback

In the previous series of articles, most of the code was done through test cases. To avoid contamination of the database, the data is rollback after the test case is executed by annotating @BeforeEach@AfterEach of the test framework. The code snippets are as follows

The principle is that before each test method is executed, the autoCommit of the database connection is changed to false, and no autocommit is done. After the method executes, the rollback method of the connection object is called to rollback the operation

public class BaseTest {
    DSLContext dslContext;
    Connection connection;

    Logger log = LoggerFactory.getLogger(this.getClass());

    public void initDSLContext() throws SQLException {
        String jdbcUrl = "jdbc:mysql://localhost:3306/learn-jooq?serverTimezone=GMT%2B8";
        String jdbcUsername = "root";
        String jdbcPassword = "root";
        connection = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword);
        dslContext = DSL.using(connection, SQLDialect.MYSQL);

        Integer fetchOneResult = dslContext.select().fetchOne().into(Integer.class);
        Assertions.assertEquals(1, fetchOneResult.intValue());

    public void beginTransaction() throws SQLException {

    public void rollbackTransaction() throws SQLException {

Spring-style transaction rollback

After using Spring, you can do physical operations through the transaction manager in spring-jdbc, changing the underlying test class to the following, adding just a few notes

public class BaseTest {
    DSLContext dslContext;

This test case can be used to verify that the data is rolled back after the insert test case ends, so when findById executes, no corresponding data is found through the previously temporary insertUserId, which can verify whether the transaction is working

class S1UserDaoTest extends BaseTest {
    S1UserDao s1UserDao;

    Integer insertUserId = null;

    public void findAll() {
        List<S1UserPojo> userAll = s1UserDao.findAll();
        Assertions.assertTrue(userAll.size() > 0);

    public void insert() {
        S1UserPojo s1UserPojo = new S1UserPojo();
        s1UserPojo.setUsername("hell username");
        insertUserId = s1UserPojo.getId();

    public void findById() {
        S1UserPojo findById = s1UserDao.findById(1);

        if (insertUserId != null) {
            S1UserPojo userPojo = s1UserDao.findById(insertUserId);

It is also important to note that when using Junit 5, the corresponding version of maven-surefire-plugin needs more than 2.22.0 when running the mvn test command. If you find that the mvn test command does not respond or the number of test cases is 0, check this configuration



Content Summary

Source code for this chapter: https://github.com/k55k32/learn-jooq/tree/master/section-6

This chapter introduces the most basic integration of jOOQ and Spring. In fact, there is not much content. It mainly introduces the support for Spring annotation generation, transaction management and the support method for automatic rollback of test cases.Is a very basic integrated tutorial.The integration with SpringBoot will be described later.Understanding the contents of this chapter will help you a lot in the future

Published 0 original articles, received 0 reviews and visited 30
Private letter follow

Tags: Spring JDBC Database Maven

Posted on Tue, 03 Mar 2020 18:21:54 -0800 by assonitis