Share feedback
Answers are generated based on the documentation.

Create the Spring Boot project

Table of contents

Set up the project

Create a Spring Boot project from Spring Initializr by selecting Maven as the build tool and adding the JOOQ Access Layer, Flyway Migration, Spring Boot DevTools, PostgreSQL Driver, and Testcontainers starters.

Alternatively, clone the guide repository.

jOOQ (jOOQ Object Oriented Querying) provides a fluent API for building typesafe SQL queries. To get the full benefit of its typesafe DSL, you need to generate Java code from your database tables, views, and other objects.

Tip

To learn more about how the jOOQ code generator helps, read Why You Should Use jOOQ With Code Generation.

The typical process for building and testing the application with jOOQ code generation is:

  1. Create a database instance using Testcontainers.
  2. Apply Flyway database migrations.
  3. Run the jOOQ code generator to produce Java code from the database objects.
  4. Run integration tests.

The testcontainers-jooq-codegen-maven-plugin automates this as part of the Maven build.

Create Flyway migration scripts

The sample application has users, posts, and comments tables. Create the first migration script following the Flyway naming convention.

Create src/main/resources/db/migration/V1__create_tables.sql:

create table users
(
    id         bigserial not null,
    name       varchar   not null,
    email      varchar   not null,
    created_at timestamp,
    updated_at timestamp,
    primary key (id),
    constraint user_email_unique unique (email)
);

create table posts
(
    id         bigserial                    not null,
    title      varchar                      not null,
    content    varchar                      not null,
    created_by bigint references users (id) not null,
    created_at timestamp,
    updated_at timestamp,
    primary key (id)
);

create table comments
(
    id         bigserial                    not null,
    name       varchar                      not null,
    content    varchar                      not null,
    post_id    bigint references posts (id) not null,
    created_at timestamp,
    updated_at timestamp,
    primary key (id)
);

ALTER SEQUENCE users_id_seq RESTART WITH 101;
ALTER SEQUENCE posts_id_seq RESTART WITH 101;
ALTER SEQUENCE comments_id_seq RESTART WITH 101;

The sequence values restart at 101 so that you can insert sample data with explicit primary key values for testing.

Configure jOOQ code generation

Add the testcontainers-jooq-codegen-maven-plugin to pom.xml:

<properties>
    <testcontainers.version>2.0.4</testcontainers.version>
    <testcontainers-jooq-codegen-maven-plugin.version>0.0.4</testcontainers-jooq-codegen-maven-plugin.version>
</properties>

<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>testcontainers-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:16-alpine</containerImage>
                        </database>
                        <flyway>
                            <locations>
                                filesystem:src/main/resources/db/migration
                            </locations>
                        </flyway>
                        <jooq>
                            <generator>
                                <database>
                                    <includes>.*</includes>
                                    <excludes>flyway_schema_history</excludes>
                                    <inputSchema>public</inputSchema>
                                </database>
                                <target>
                                    <packageName>com.testcontainers.demo.jooq</packageName>
                                    <directory>target/generated-sources/jooq</directory>
                                </target>
                            </generator>
                        </jooq>
                    </configuration>
                </execution>
            </executions>
        </plugin>
    </plugins>
</build>

Here's what the plugin configuration does:

  • The <configuration>/<database> section sets the database type to POSTGRES and the Docker image to postgres:16-alpine.
  • The <configuration>/<flyway> section points to the Flyway migration scripts.
  • The <configuration>/<jooq> section configures the package name and output directory for the generated code. You can use any configuration option that the official jooq-code-generator plugin supports.

When you run ./mvnw clean package, the plugin uses Testcontainers to spin up a PostgreSQL container, applies the Flyway migrations, and generates Java code under target/generated-sources/jooq.

Create model classes

Create model classes to represent the data structures for various use cases. These records hold a subset of column values from the tables.

User.java:

package com.testcontainers.demo.domain;

public record User(Long id, String name, String email) {}

Post.java:

package com.testcontainers.demo.domain;

import java.time.LocalDateTime;
import java.util.List;

public record Post(
  Long id,
  String title,
  String content,
  User createdBy,
  List<Comment> comments,
  LocalDateTime createdAt,
  LocalDateTime updatedAt
) {}

Comment.java:

package com.testcontainers.demo.domain;

import java.time.LocalDateTime;

public record Comment(
  Long id,
  String name,
  String content,
  LocalDateTime createdAt,
  LocalDateTime updatedAt
) {}

Implement repositories using jOOQ

Create UserRepository.java with methods to create a user and look up a user by email:

package com.testcontainers.demo.domain;

import static com.testcontainers.demo.jooq.tables.Users.USERS;
import static org.jooq.Records.mapping;

import java.time.LocalDateTime;
import java.util.Optional;
import org.jooq.DSLContext;
import org.springframework.stereotype.Repository;

@Repository
class UserRepository {

  private final DSLContext dsl;

  UserRepository(DSLContext dsl) {
    this.dsl = dsl;
  }

  public User createUser(User user) {
    return this.dsl.insertInto(USERS)
      .set(USERS.NAME, user.name())
      .set(USERS.EMAIL, user.email())
      .set(USERS.CREATED_AT, LocalDateTime.now())
      .returningResult(USERS.ID, USERS.NAME, USERS.EMAIL)
      .fetchOne(mapping(User::new));
  }

  public Optional<User> getUserByEmail(String email) {
    return this.dsl.select(USERS.ID, USERS.NAME, USERS.EMAIL)
      .from(USERS)
      .where(USERS.EMAIL.equalIgnoreCase(email))
      .fetchOptional(mapping(User::new));
  }
}

The jOOQ DSL looks similar to SQL but written in Java. Because the code is generated from the database schema, it stays in sync with the database structure and provides type safety. For example, where(USERS.EMAIL.equalIgnoreCase(email)) expects a String value. If you pass a non-string value like 123, you get a compiler error.

Fetch complex object graphs

jOOQ shines when it comes to complex queries. The database has a many-to-one relationship from Post to User and a one-to-many relationship from Post to Comment.

Create PostRepository.java to load a Post with its creator and comments using a single query with jOOQ's MULTISET feature:

package com.testcontainers.demo.domain;

import static com.testcontainers.demo.jooq.Tables.COMMENTS;
import static com.testcontainers.demo.jooq.tables.Posts.POSTS;
import static org.jooq.Records.mapping;
import static org.jooq.impl.DSL.multiset;
import static org.jooq.impl.DSL.row;
import static org.jooq.impl.DSL.select;

import java.util.Optional;
import org.jooq.DSLContext;
import org.springframework.stereotype.Repository;

@Repository
class PostRepository {

  private final DSLContext dsl;

  PostRepository(DSLContext dsl) {
    this.dsl = dsl;
  }

  public Optional<Post> getPostById(Long id) {
    return this.dsl.select(
        POSTS.ID,
        POSTS.TITLE,
        POSTS.CONTENT,
        row(POSTS.users().ID, POSTS.users().NAME, POSTS.users().EMAIL)
          .mapping(User::new)
          .as("createdBy"),
        multiset(
          select(
            COMMENTS.ID,
            COMMENTS.NAME,
            COMMENTS.CONTENT,
            COMMENTS.CREATED_AT,
            COMMENTS.UPDATED_AT
          )
            .from(COMMENTS)
            .where(POSTS.ID.eq(COMMENTS.POST_ID))
        )
          .as("comments")
          .convertFrom(r -> r.map(mapping(Comment::new))),
        POSTS.CREATED_AT,
        POSTS.UPDATED_AT
      )
      .from(POSTS)
      .where(POSTS.ID.eq(id))
      .fetchOptional(mapping(Post::new));
  }
}

This uses jOOQ's nested records for the many-to-one Post-to-User association and MULTISET for the one-to-many Post-to-Comment association.

Write tests with Testcontainers »