Integrate Spring Boot With jOOQ, Liquibase, and Testcontainers

gowebing

In this blog, you will learn how to get started with jOOQ, Liquibase, and Testcontainers. You will create a basic Spring Boot application and integrate the aforementioned techniques including a test setup. Furthermore, you will use Spring Boot Docker Compose support to run the application in no time. Enjoy!

1. Introduction

The starting point for this blog was to get more acquainted with jOOQ, a database-first approach instead of using an Object Relation Mapper (ORM) framework. Being able to just write SQL including typesafety is very appealing and interesting. However, during the setup of the application, some extra requirements popped up. Note that these requirements are my own requirements and choices — these are not imposed by jOOQ.

  1. Liquibase needs to be used for creating the database tables.
  2. PostgreSQL has to be used as a database.
  3. The Maven plugin testcontainers-jooq-codegen-maven-plugin has to be used for generating jOOQ code.
  4. Testcontainers should be used for integration tests.

Besides that, the application should be accessible via a Rest API defined with an OpenAPI specification and the code should be generated by means of the openapi-generator-maven-plugin. This will not be explained further in this blog, but you can read more about it in a previous blog if you are interested.

In the end, you also want a running application with a "real" database. That is where the Spring Boot Docker Compose support will help you.

Sources used in this blog are available on GitHub.

2. Prerequisites

Quite some technologies are used in this blog. You do not need to be an expert in all of them, but at least you need to know what is used for and have some basic knowledge about it. Prerequisites are:

  1. Basic knowledge of Java, Java 21 is used
  2. Basic knowledge of Spring Boot
  3. Basic knowledge of Liquibase — more information about Liquibase can be found in a previous blog
  4. Basic knowledge of Testcontainers — more information about Testcontainers can be found in a previous blog
  5. Basic knowledge of OpenAPI if you want to dive into the source code

3. Application Setup

In order to get started with a Spring Boot application, you navigate to Spring Initializr. You add the Spring Web dependency and you are ready to go. The application will be able to do three things:

  1. Create a customer
  2. Retrieve a single customer
  3. Retrieve all customers

A customer consists of an ID, a first name, a last name, and a country, represented by the Customer class.

Java
  
public class Customer {
    private long id;
    private String firstName, lastName, country;
 
    // Getters and setters left out for brevity
}


The OpenAPI specification will define the REST endpoints, and based on this specification, the openapi-generator-maven-plugin will generate code for you. An interface CustomersApi is generated for the controller which you need to implement.

Java
  
@RestController
public class CustomerController implements CustomersApi {
 
    @Override
    public ResponseEntity<Void> createCustomer(Customer apiCustomer) {
         // to be implemented
    }
 
    @Override
    public ResponseEntity<List<CustomerFullData>> getCustomers() {
         // to be implemented
        return null;
    }
 
    @Override
    public ResponseEntity<CustomerFullData> getCustomer(Long customerId) {
        // to be implemented
        return null;
    }
 
}


4. Add Liquibase

You need database scripts for creating the tables because jOOQ uses a database-first approach. In order to create the database, Liquibase will be used.

Add the Liquibase dependency to the pom.

XML
  
<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
</dependency>


There are several ways to set up and organize Liquibase. In this blog, you will make use of a root changelog file and several separate changelog files for the database. What does this look like?

Add the root changelog file to src/main/resources/db/changelog. It only mentions looking in the directory migration for the real changelogs.

XML
  
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.1.xsd">
  <includeAll path="./migration" relativeToChangelogFile="true"/>
</databaseChangeLog>


Add the changelogs in the directory src/main/resources/db/changelog/migration. Two changelogs are added just to demonstrate how a database migration script can be added.

The changelog db.changelog-1.0.xml creates the customer table with the id, first name, and last name columns.

XML
  
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.1.xsd">
 
  <changeSet author="gunter" id="changelog-1.0">
    <createTable tableName="customer">
      <column name="id" type="serial" autoIncrement="true">
        <constraints nullable="false" primaryKey="true"/>
      </column>
      <column name="first_name" type="varchar(255)">
        <constraints nullable="false"/>
      </column>
      <column name="last_name" type="varchar(255)">
        <constraints nullable="false"/>
      </column>
    </createTable>
    <rollback>
      <dropTable tableName="customer"/>
    </rollback>
  </changeSet>
 
</databaseChangeLog>


The db.changelog-2.0.xml adds the country column to the customer table.

XML
  
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.1.xsd">
 
  <changeSet author="gunter" id="changelog-2.0">
    <addColumn tableName="customer">
      <column name="country" type="varchar(255)"/>
    </addColumn>
    <rollback>
      <dropColumn tableName="customer">
        <column name="country" type="varchar(255)"/>
      </dropColumn>
    </rollback>
  </changeSet>
 
</databaseChangeLog>


Finally, add the following to the application.properties file.

Properties files
  
spring.liquibase.change-log=classpath:db/changelog/db.changelog-root.xml


5. Add jOOQ Code Generation

In order to let jOOQ generate code, you need a running instance of the database including your tables. This is where Testcontainers come into play. Testcontainers are typically used for integration tests with a database, but it can also be used as a database for generating the jOOQ code. This can be done with the help of the testcontainers-jooq-codegen-maven-plugin. The documentation of the plugin can be found here.

Add the following to your pom. There is quite a lot to see here:

  1. Two dependencies for PostgreSQL are needed: one for Testcontainers and one for the driver.
  2. In the configuration section, you define which type of database you use and which version should be used for the container image.
  3. In the jOOQ generator parameters, you define the tables that should be included or excluded. Note that the Liquibase-specific tables (DATABASECHANGELOG and DATABASECHANGELOGLOCK) are excluded.
  4. In the jOOQ generator parameters, you define in which package the generated code should be located.
  5. Also note that with tag liquibase, the plugin knows that Liquibase is being used with some default settings.
XML
  
<build>
  <plugins>
    <plugin>
      <groupId>org.testcontainers</groupId>
      <artifactId>testcontainers-jooq-codegen-maven-plugin</artifactId>
      <version>${testcontainers-jooq-codegen-maven-plugin.version}</version>
      <dependencies>
        <dependency>
          <groupId>org.testcontainers</groupId>
          <artifactId>postgresql</artifactId>
          <version>${testcontainers.version}</version>
        </dependency>
        <dependency>
          <groupId>org.postgresql</groupId>
          <artifactId>postgresql</artifactId>
          <version>${postgresql.version}</version>
        </dependency>
      </dependencies>
      <executions>
        <execution>
          <id>generate-jooq-sources</id>
          <goals>
            <goal>generate</goal>
          </goals>
          <phase>generate-sources</phase>
          <configuration>
            <database>
              <type>POSTGRES</type>
              <containerImage>postgres:15-alpine</containerImage> <!-- optional -->
            </database>
            <liquibase/>
            <!-- Generator parameters -->
            <jooq>
              <generator>
                <database>
                  <includes>.*</includes>
                  <excludes>DATABASECHANGELOG.*</excludes>
                  <inputSchema>public</inputSchema>
                </database>
                <target>
                  <packageName>com.mydeveloperplanet.myjooqplanet.jooq</packageName>
                  <directory>target/generated-sources/jooq</directory>
                </target>
              </generator>
            </jooq>
          </configuration>
        </execution>
      </executions>
    </plugin>
  </plugins>
</build>


Generate the code:

Shell
  
$ mvn generate-sources


In the console output, you will notice that:

  1. A Testcontainer is started.
  2. The Liquibase migration scripts are applied.
  3. The jOOQ code is generated.

Check the target/generated-sources/jooq directory.

The Tables class contains the tables.

Java
  
public class Tables {
 
    /**
     * The table <code>public.customer</code>.
     */
    public static final Customer CUSTOMER = Customer.CUSTOMER;
}


The Keys class contains the primary keys.

Java
  
public class Keys {
 
    // -------------------------------------------------------------------------
    // UNIQUE and PRIMARY KEY definitions
    // -------------------------------------------------------------------------
 
    public static final UniqueKey<CustomerRecord> CUSTOMER_PKEY = Internal.createUniqueKey(Customer.CUSTOMER, DSL.name("customer_pkey"), new TableField[] { Customer.CUSTOMER.ID }, true);
}


A Customer class contains the table definition.

Java
  
public class Customer extends TableImpl<CustomerRecord> {
 
    private static final long serialVersionUID = 1L;
 
    /**
     * The reference instance of <code>public.customer</code>
     */
    public static final Customer CUSTOMER = new Customer();
 
    /**
     * The class holding records for this type
     */
    @Override
    public Class<CustomerRecord> getRecordType() {
        return CustomerRecord.class;
    }
    
    // And a lot more
}


A CustomerRecord defines a record in the table.

Java
  
public class CustomerRecord extends UpdatableRecordImpl<CustomerRecord> implements Record4<Integer, String, String, String> {
 
    private static final long serialVersionUID = 1L;
 
    /**
     * Setter for <code>public.customer.id</code>.
     */
    public void setId(Integer value) {
        set(0, value);
    }
 
    /**
     * Getter for <code>public.customer.id</code>.
     */
    public Integer getId() {
        return (Integer) get(0);
    }
 
    // And a lot more
}


6. Add Repository

Now it is time to connect some dots. First, you need a repository for the database access. The only thing you need to do is to inject a DSLContext which is auto-configured by Spring Boot. Add the following dependency to the pom.

XML
  
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jooq</artifactId>
</dependency>


With the DSLContext, you can implement the required repository methods, making use of the generated jOOQ code. It is pretty straightforward and very readable.

Java
  
@Repository
public class CustomerRepository {
 
    private final DSLContext create;
 
    CustomerRepository(DSLContext create) {
        this.create = create;
    }
 
    public void addCustomer(final Customer customer) {
        create.insertInto(CUSTOMER, CUSTOMER.FIRST_NAME, CUSTOMER.LAST_NAME, CUSTOMER.COUNTRY)
                .values(customer.getFirstName(), customer.getLastName(), customer.getCountry())
                .execute();
    }
 
    public CustomerRecord getCustomer(int customerId) {
        return create.selectFrom(CUSTOMER).where(CUSTOMER.ID.eq(customerId)).fetchOne(Records.mapping(CustomerRecord::new));
    }
 
    public List<CustomerRecord> getAllCustomers() {
        return create.selectFrom(CUSTOMER)
                .fetch(Records.mapping(CustomerRecord::new));
    }
 
}


Build the application:

Shell
  
$ mvn clean verify


7. Complete Controller

The last thing to do is to implement the controller. You inject the repository and implement the interface methods.

Java
  
@RestController
public class CustomerController implements CustomersApi {
 
    public final CustomerRepository customerRepository;
 
    public CustomerController(CustomerRepository customerRepository) {
        this.customerRepository = customerRepository;
    }
 
    @Override
    public ResponseEntity<Void> createCustomer(Customer apiCustomer) {
        com.mydeveloperplanet.myjooqplanet.Customer customer = new com.mydeveloperplanet.myjooqplanet.Customer();
        customer.setFirstName(apiCustomer.getFirstName());
        customer.setLastName(apiCustomer.getLastName());
        customer.setCountry(apiCustomer.getCountry());
 
        customerRepository.addCustomer(customer);
 
        return ResponseEntity.ok().build();
    }
 
    @Override
    public ResponseEntity<List<CustomerFullData>> getCustomers() {
        List<CustomerRecord> customers = customerRepository.getAllCustomers();
 
        List<CustomerFullData> convertedCustomers = convertToCustomerFullData(customers);
        return ResponseEntity.ok(convertedCustomers);
    }
 
    @Override
    public ResponseEntity<CustomerFullData> getCustomer(Long customerId) {
        CustomerRecord customer = customerRepository.getCustomer(customerId.intValue());
        return ResponseEntity.ok(repoToApi(customer));
    }
 
    ...
}


Build the application:

Shell
  
$ mvn clean verify


8. Use Testcontainers for Integration Test

Testcontainers are used for generating the jOOQ code, but you can also use it for your integration test.

The test uses:

  1. @Testcontainers to indicate that this test requires Testcontainers
  2. @SpringBootTest to start the Spring Boot application
  3. WebTestClient in order to send a request to the application
  4. @Container in order to ensure that Testcontainers will manage the lifecycle of the Testcontainer
  5. @ServiceConnection in order for Spring Boot to use the default configuration to connect to the Testcontainer — Note that nowhere any database configuration has been added in application.properties.
Java
  
@Testcontainers
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
class MyJooqPlanetApplicationTests {
 
    @Autowired
    private WebTestClient webTestClient;
 
    @Container
    @ServiceConnection
    static PostgreSQLContainer<?> postgreSQLContainer =  new PostgreSQLContainer<>(DockerImageName.parse("postgres:15-alpine"));
 
    @Test
    void whenCreateCustomer_thenReturnSuccess() throws Exception {
        String body = """
                {
                  "firstName": "John",
                  "lastName": "Doe",
                  "country": "Belgium"
                }
                """;
 
        webTestClient
                .post()
                .uri("/customers")
                .contentType(MediaType.APPLICATION_JSON)
                .bodyValue(body)
                .exchange()
                .expectStatus()
                .is2xxSuccessful();
 
    }
 
    @Test
    void givenCustomer_whenRetrieveAllCustomers_thenReturnSuccess() throws Exception {
        String body = """
                {
                  "firstName": "John",
                  "lastName": "Doe",
                  "country": "Belgium"
                }
                """;
 
        webTestClient
                .post()
                .uri("/customers")
                .contentType(MediaType.APPLICATION_JSON)
                .bodyValue(body)
                .exchange()
                .expectStatus()
                .is2xxSuccessful();
 
        webTestClient
                .get()
                .uri("/customers")
                .exchange()
                .expectStatus()
                .is2xxSuccessful()
                .expectHeader()
                .contentType(MediaType.APPLICATION_JSON)
                .expectBody()
                .jsonPath("$[0].customerId").isEqualTo(1)
                .jsonPath("$[0].firstName").isEqualTo("John")
                .jsonPath("$[0].lastName").isEqualTo("Doe")
                .jsonPath("$[0].country").isEqualTo("Belgium");
 
    }
 
}


In order to be able to run this test, you need to add the following test dependencies to the pom.

XML
  
<!-- Needed for the testcontainers integration test -->
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-testcontainers</artifactId>
  <scope>test</scope>
</dependency>
 <dependency>
  <groupId>org.testcontainers</groupId>
  <artifactId>junit-jupiter</artifactId>
  <scope>test</scope>
</dependency>
<dependency>
  <groupId>org.testcontainers</groupId>
  <artifactId>postgresql</artifactId>
  <scope>test</scope>
</dependency>
 
<!-- Needed for WebTestClient -->
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-webflux</artifactId>
  <scope>test</scope>
</dependency>


In order to run the application, you need to add the following dependencies to the pom.

XML
  
<!-- Needed for database access -->
<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <scope>runtime</scope>
</dependency>
<dependency>
  <groupId>org.liquibase</groupId>
  <artifactId>liquibase-core</artifactId>
  <scope>runtime</scope>
</dependency>


Run the test.

Shell
  
$ mvn clean verify


9. Run Application

You have built an application, code is generated, and an integration test is successful. But now you also want to run the application. And you will need to have a running database for that. This can easily be accomplished if you add the spring-boot-docker-compose dependency to your pom.

XML
  
<dependency>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-docker-compose</artifactId>
  <scope>runtime</scope>
  <optional>true</optional>
</dependency>


Add a compose.yaml to the root of your repository which contains a definition for your database setup.

YAML
  
services:
  postgres:
    image: 'postgres:15-alpine'
    environment:
      - 'POSTGRES_DB=mydatabase'
      - 'POSTGRES_PASSWORD=secret'
      - 'POSTGRES_USER=myuser'
    labels:
      - "org.springframework.boot.service-connection=postgres"
    ports:
      - '5432'


Start the application.

Shell
  
$ mvn spring-boot:run


Notice that the PostgreSQL container is started in the console logs.

Shell
  
2024-07-06T15:59:52.037+02:00  INFO 78905 --- [MyJooqPlanet] [           main] .s.b.d.c.l.DockerComposeLifecycleManager : Using Docker Compose file '/<home directory>/myjooqplanet/compose.yaml'
2024-07-06T15:59:52.276+02:00  INFO 78905 --- [MyJooqPlanet] [utReader-stderr] o.s.boot.docker.compose.core.DockerCli   :  Container myjooqplanet-postgres-1  Created
2024-07-06T15:59:52.277+02:00  INFO 78905 --- [MyJooqPlanet] [utReader-stderr] o.s.boot.docker.compose.core.DockerCli   :  Container myjooqplanet-postgres-1  Starting
2024-07-06T15:59:52.610+02:00  INFO 78905 --- [MyJooqPlanet] [utReader-stderr] o.s.boot.docker.compose.core.DockerCli   :  Container myjooqplanet-postgres-1  Started
2024-07-06T15:59:52.610+02:00  INFO 78905 --- [MyJooqPlanet] [utReader-stderr] o.s.boot.docker.compose.core.DockerCli   :  Container myjooqplanet-postgres-1  Waiting
2024-07-06T15:59:53.114+02:00  INFO 78905 --- [MyJooqPlanet] [utReader-stderr] o.s.boot.docker.compose.core.DockerCli   :  Container myjooqplanet-postgres-1  Healthy


Create a customer.

Shell
  
$ curl -X POST http://localhost:8080/customers  \
   -H 'Content-Type: application/json'        \
   -d '{"firstName": "John",                  
        "lastName": "Doe",                    
        "country": "Belgium"                 
       }'


Retrieve the customer.

Shell
  
$ curl http://localhost:8080/customers/1
{"firstName":"John","lastName":"Doe","country":"Belgium","customerId":1}


Retrieve all customers.

Shell
  
$ curl http://localhost:8080/customers
[{"firstName":"John","lastName":"Doe","country":"Belgium","customerId":1}]


10. Conclusion

In this blog, you learned how to integrate a Spring Boot Application with jOOQ, Liquibase, and Testcontainers. You used Testcontainers for an integration test, and you used Spring Boot Docker Compose in order to run your application with a database out-of-the-box. Cool stuff!

 

 

 

 

Top