Docs

Docs

jOOQ Repositories

How to implement repositories with jOOQ.

jOOQ is a framework that allows you to write SQL queries in Java. It analyzes your database, and generates Java code that you use to build type safe SQL queries using a fluent API. This gives you a lot of degrees of freedom, but also requires more work and more skills.

Note
 This page describes how to build repositories with jOOQ in Vaadin applications. It assumes you have read the Repositories documentation page. It also assumes you are already familiar with jOOQ. If you have not used it before, you should at least go through the jOOQ tutorial before continuing.

When you build repositories with jOOQ, you need to know what you want. You need to know what kind of repository you want, what kind of entities you want, and how you want to map these entities to database tables. You have to implement more from scratch, compared to JPA and Spring Data.

You’ll be trading development time and effort for more control and flexibility. If you are not comfortable with this, you should consider building your repositories with JPA instead. You can also combine JPA and jOOQ so that you use JPA for your repositories, and jOOQ for your query objects. This combination has been successful in real-world Vaadin applications.

Project Setup

jOOQ is a database-first technology. This means that the database must already exist before you can run the jOOQ code generator. Because of this, you have to use Flyway migrations from the start.

Once you have configured your project to use Flyway, the next step is to set up the jOOQ code generator. jOOQ has a Maven plugin for this, but it requires JDBC access to your database. You’ll instead use a Maven plugin from Testcontainers. It uses Docker to start a new database, runs your Flyway migrations, and then runs the jOOQ code generator.

Important
 To use Testcontainers, you have to have Docker installed and running on your machine.

First, you declare the version of the plugin in your POM-file, like this:

<properties>
    ...
    <testcontainers-jooq-codegen-maven-plugin.version>0.0.4</testcontainers-jooq-codegen-maven-plugin.version> <!--(1)-->
</properties>

  1. You can check for the latest version on the MVN Repository.

Next, you configure the plugin itself, like this:

<plugin>
    <groupId>org.testcontainers</groupId>
    <artifactId>testcontainers-jooq-codegen-maven-plugin</artifactId>
    <version>${testcontainers-jooq-codegen-maven-plugin.version}</version>
    <dependencies> <!--(1)-->
        <dependency>
            <groupId>org.testcontainers</groupId>
            <artifactId>postgresql</artifactId>
            <version>${testcontainers.version}</version> <!--(2)-->
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>${postgresql.version}</version> <!--(3)-->
        </dependency>
    </dependencies>
    <executions>
        <execution>
            <id>generate-jooq-sources</id>
            <goals>
                <goal>generate</goal>
            </goals>
            <phase>generate-sources</phase>
            <configuration>
                <database> <!--(4)-->
                    <type>POSTGRES</type>
                    <containerImage>postgres:16-alpine</containerImage>
                </database>
                <flyway> <!--(5)-->
                    <locations>
                        filesystem:${pom.basedir}/src/main/resources/db/migration
                    </locations>
                </flyway>
                <jooq> <!--(6)-->
                    <generator>
                        <database>
                            <includes>.*</includes>
                            <excludes>flyway_schema_history</excludes> <!--(7)-->
                            <inputSchema>public</inputSchema>
                        </database>
                        <target>
                            <packageName>com.example.application.jooq</packageName> <!--(8)-->
                            <directory>target/generated-sources/jooq</directory>
                        </target>
                    </generator>
                </jooq>
            </configuration>
        </execution>
    </executions>
</plugin>

  1. This example uses a PostgreSQL database. See the Testcontainers documentation for a list of other supported databases.

  2. The testcontainers.version property is inherited from the Spring Boot starter parent.

  3. The postgresql.version property is inherited from the Spring Boot starter parent.

  4. Here, you configure which database Docker image to use. This example uses postgres:16-alpine.

  5. Here, you configure how the plugin should use Flyway.

  6. Here, you configure how the plugin should run the jOOQ code generator.

  7. You don’t need to generate Java classes for the Flyway table.

  8. This is the package that the jOOQ classes are generated into.

Finally, you have to add the Spring Boot jOOQ starter to your project, like this:

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

This starter automatically configures jOOQ to use the primary data source of your application. It also configures jOOQ to work with the Spring transaction manager. This means that you can control database transactions in the same way regardless of whether you are using jOOQ or JPA.

If you are using a multi-module project, you need to configure the plugin in the module that contains the Flyway migrations. Add the dependency to the module that contains your repositories.

Entities

jOOQ deals with database tables, and so has no concept of entities. This means that you get to decide how you implement your entities. The entire Java toolbox is at your disposal: you can use records, sealed classes, mutable Java beans, final fields, initializing constructors, and so on. When using jOOQ, it is impossible to recommend a single Vaadin Way of designing entities, because it depends on so many things.

Regardless of how you design your entities, you need a way of mapping them to your database tables. If your entities are small, manually copying data between database records and entities is not a big deal.

However, as your entities grow, this becomes both boring and error prone. You might forget a field, copy the wrong column value to another field, or forget to handle null values. jOOQ provides different mappers that help with this, but they require your entities to be designed in a specific way.

If you are going to build your repositories with jOOQ, you should familiarize yourself with POJO:s and RecordMapper. This allows you to design your entities so that they are easier to use with jOOQ.

Generated Plain Old Java Objects

If you are only interested in working directly with the database tables, you don’t have to write separate entities. Instead, you can configure the jOOQ code generator to generate POJO:s for you. That means that every table and view in your database gets a POJO with the corresponding fields.

You can generate mutable POJO:s, immutable POJO:s, Java records, interfaces, and even JPA annotated entity classes. These POJO:s are easy to fetch and store with jOOQ. You can also use them across your Vaadin application, even in the presentation layer.

See the jOOQ documentation for more information.

Domain Primitives

If you have domain primitives in your entities, you can handle them in different ways, depending on how you choose to map your database records to your entities.

Converters

If you are mapping a single database column to a single domain primitive, you should create a Converter. You can either implement the org.jooq.Converter interface directly, or extend the AbstractConverter class. For example, a converter for an EmailAddress domain primitive could look like this:

import org.jooq.impl.AbstractConverter;

public class EmailAddressConverter extends AbstractConverter<String, EmailAddress> {

    public EmailAddressConverter() {
        super(String.class, EmailAddress.class);
    }

    @Override
    public EmailAddress from(String databaseObject) {
        return databaseObject == null ? null : new EmailAddress(databaseObject);
    }

    @Override
    public String to(EmailAddress userObject) {
        return userObject == null ? null : userObject.value();
    }
}

Converters can be applied in two ways: ad-hoc, and as fixed types.

Ad-Hoc Converters

When you only need to apply the converter for a single query, you can do it directly in code. For example, in this query, the email string column is converted to an EmailAddress domain primitive in the result:

var result = create
    .select(CUSTOMER.ID,
            CUSTOMER.NAME,
            CUSTOMER.EMAIL.convert(new EmailAddressConverter()))
    .from(CUSTOMER)
    .fetch();

You can also use ad-hoc converters on nested collections, and when storing data in the database. See the jOOQ documentation for more information.

Forced Types

When you always map the same columns to the same domain primitive, you can configure the jOOQ code generator to apply the generator for you. For example, to always map all columns whose names end with email to the EmailAddress domain primitive, make this change to your POM-file:

<jooq>
    <generator>
        <database>
            ...
            <forcedTypes>
                <forcedType>
                    <userType>com.example.application.domain.primitives.EmailAddress</userType>
                    <converter>
                        com.example.application.domain.primitives.jooq.EmailAddressConverter
                    </converter>
                    <includeExpression>.*email</includeExpression>
                </forcedType>
            </forcedTypes>
        </database>
    </generator>
</jooq>

To make the configuration easier, you should name your columns consistently. For example, if you have a ProductId domain primitive that is used to identify records in the PRODUCT table, you should name the primary key column product_id. Then, if you have an OrderItem that refers to a Product, you should name the foreign key column product_id as well. Now you can use a single forced type definition to convert both columns.

See the jOOQ documentation for more information about forced types.

When you are using forced types, you may have to cast them to their database types for certain queries to work. For example, if you want to perform a contains query on an email field, you could write something like this:

var result = create
    .select(CUSTOMER.ID,
            CUSTOMER.NAME,
            CUSTOMER.EMAIL)
    .from(CUSTOMER)
    .where(CUSTOMER.EMAIL.cast(SQLDataType.VARCHAR).contains(searchTerm))
    .fetch();

The reason for this is that the CUSTOMER.EMAIL.contains(..) method accepts not a string, but an EmailAddress. And EmailAddress is a domain primitive that can only contain complete email addresses, not parts of them. By casting the column back to a VARCHAR, you can work with ordinary strings.

Embeddable Types

If you are using multi-value domain primitives, you can configure jOOQ to generate embeddable types for certain column groups. This is such a complex feature that it won’t be covered here. See the jOOQ documentation for more information.

Manual Mapping

Because you are in charge of how you map your entities to your database tables, you can choose to do everything manually. For example, to fetch a MonetaryAmount domain primitive that consists of a BigDecimal and a CurrencyUnit enum, you could do something this:

public class Offer {
    private OfferId offerId;
    private MonetaryAmount price;
    ...
    // Constructors, getters and setters omitted.
}
...

public Optional<Offer> findById(OfferId offerId) {
    return create.fetchOptional(OFFER, OFFER.OFFER_ID.eq(offerId)).map(record -> {
        var offer = new Offer(offerId);
        offer.setPrice(new MonetaryAmount(record.getCurrency(), record.getPrice())));
        // Calls to other setter methods omitted.
        return offer;
    });
}

Likewise, to write the monetary amount back to the database, you could do something like this:

public void update(Offer offer) {
    var record = create.fetchOne(OFFER, OFFER.OFFER_ID.eq(offer.getOfferId()));
    if (record == null) {
        throw new IllegalArgumentException("Offer does not exist");
    }
    record.setCurrency(offer.getPrice().currency());
    record.setPrice(offer.getPrice().value());
    // Calls to other setter methods omitted.
    record.update();
}

Repositories

jOOQ has no repository interface for you to extend. Instead, you get to design your own from scratch. To keep things simple, you should design persistence oriented repositories, like this:

public interface Repository<ID, E> {
    Optional<E> findById(ID id);
    E save(E entity);
    void delete(ID id);
}

If you want to, you can split up the save method into separate insert and update methods. Whether these methods should return an entity or not depends on how you implement them. When you insert a new entity, you often want to return its generated ID in some way. When you update an existing entity, and use optimistic locking, you often want to return the new version number in some way. If your entities are mutable, you can update them directly. Then, there is no need to return a new instance. If your entities are immutable, you have to return a new, updated instance.

If you don’t need to support multiple repository implementations, you can skip the interfaces and instead create an abstract base class, like this:

public abstract class AbstractRepository<ID, E> {
    protected final DSLContext create;

    protected AbstractRepository(DSLContext create) {
        this.create = create;
    }

    public abstract Optional<T> findById(ID id);

    public abstract T save(T entity);

    public abstract void delete(ID id);
}

How you implement the different methods depends on how your database is structured, and what kind of entity classes you use. You may be tempted to generalize as much functionality as possible into the base class. If you do this, you should make sure that the functionality is, in fact, generic, such as audit logging. Otherwise, you risk painting yourself into a corner in the future. For example, if you assume that an entity is always mapped to one table, you run into problems when you need to map an entity to multiple tables.

Storing Entities

Before you start implementing your store method, you need to make some decisions. First, you need to decide how to deduce whether to insert or update the entity. Second, you need to decide how the entity receives its ID. The easiest solution is to use identity columns in the database for your primary keys. That way, an entity without an ID must be inserted, and an entity with an ID updated.

You also need to decide whether you are going to use optimistic locking or not. If you choose to use optimistic locking, you can either implement it yourself, or use jOOQ’s built-in support for it.

To use built-in optimistic locking, you have to make sure all tables, that should use optimistic locking, have a column for the version number. This column should have the same name in all tables. For example, you could call this column opt_lock_ver.

Next, you have to configure the jOOQ code generator to use this column for optimistic locking. Make the following change to your POM-file:

<jooq>
    <generator>
        <database>
            ...
            <recordVersionFields>opt_lock_ver</recordVersionFields>
        </database>
    </generator>
</jooq>

Optimistic locking is turned off by default. To use it, you have to enable it in your repositories. If you use an abstract base class, you can do it there, like this:

public abstract class AbstractRepository<ID, E> {
    protected final DSLContext create;

    protected AbstractRepository(DSLContext create) {
        this.create = create
            .configuration()
            .deriveSettings(settings -> settings.withExecuteWithOptimisticLocking(true))
            .dsl();
    }

    ...
}

To use built-in optimistic locking, you have to use org.jooq.UpdatableRecord instead of creating your INSERT and UPDATE statements yourself. This is an API that makes it easy to implement CRUD. You can use it like this:

@Override
public Product save(Product entity) {
    var record = getRecordFor(entity);
    record.setDescription(entity.description());
    record.setQuantityUnit(entity.quantityUnit());
    ...
    record.store(); // (1)
    return new Product(record.getProductId(), record.getOptLockVersion(), record.getDescription(), record.getQuantityUnit(), ...);
}

private ProductRecord getRecordFor(Product entity) {
    var productId = entity.productId();
    if (productId == null) {
        return create.newRecord(PRODUCT);
    } else {
        var record = create.fetchOne(PRODUCT, PRODUCT.PRODUCT_ID.eq(entity.productId()));
        if (record == null) {
            throw new DataChangedException("Product does not exist");
        }
        record.setOptLockVersion(entity.version());
        return record;
    }
}

  1. The store method knows whether to issue an INSERT or UPDATE, depending on whether the record was created using newRecord or fetchOne.

See the jOOQ documentation for more information.

Deleting Entities

The easiest way of deleting an entity is to write a DELETE…​FROM query, like this:

public void delete(ProductId productId) {
    create.deleteFrom(PRODUCT)
        .where(PRODUCT.PRODUCT_ID.eq(productId))
        .execute();
}

If your entity is mapped to multiple tables, you delete them all, one at a time, like this:

public void delete(OrderId orderId) {
    create.deleteFrom(ORDER_ITEM)
        .where(ORDER_ITEM.ORDER_ID.eq(orderId))
        .execute();
    create.deleteFrom(ORDER)
        .where(ORDER.ORDER_ID.eq(orderId))
        .execute();
}

Remember the order so that you don’t break any foreign key constraints. Also remember to run the delete method inside a single transaction.

Query Methods

When you write query methods, you often want to specify things like ordering, limits and offsets. If the ordering is always fixed, you can implement something simple, like this:

public List<Product> findByDescriptionContaining(String searchTerm, int fetchMax) {
    return create.selectFrom(PRODUCT)
        .where(PRODUCT.DESCRIPTION.contains(searchTerm))
        .orderBy(PRODUCT.DESCRIPTION.asc())
        .limit(fetchMax)
        .fetch(Records.mapping(Product::new)); // (1)
}

  1. This assumes there is a Java record called Product that has the same fields as the PRODUCT table.

If you want to specify the ordering as a method parameter, the easiest way is to re-use org.springframework.data.domain.Sort from Spring Data. Then, you can implement something like this:

public List<Product> findByDescriptionContaining(String searchTerm, int fetchMax, Sort sort) {
    var query = create.selectFrom(PRODUCT)
        .where(PRODUCT.DESCRIPTION.contains(searchTerm));
    if (sort != null && sort.isSorted()) {
        query.orderBy(sort.stream().map(this::toOrderField).toList());
    }
    return query.limit(fetchMax).fetch(Records.mapping(Product::new));
}

private OrderField<?> toOrderField(Sort.Order sortOrder) {
    var field = PRODUCT.field(sortOrder.getProperty()); // (1)
    if (field == null) {
        throw new IllegalArgumentException("Unknown field: " + sortOrder.getProperty());
    }
    return sortOrder.isAscending() ? field.asc() : field.desc();
}

  1. You can also look up the corresponding field in some other way, in case the sort properties are not matching the database column names.

If you want to use pagination, you can reuse org.springframework.data.domain.Pageable from Spring Data, like this:

public List<Product> findByDescriptionContaining(String searchTerm, Pageable pageable) {
    var query = create.selectFrom(PRODUCT)
        .where(PRODUCT.DESCRIPTION.contains(searchTerm));
    if (pageable.getSort().isSorted()) {
        query.orderBy(pageable.getSort().stream().map(this::toOrderField).toList());
    }
    return query.limit(pageable.getOffset(), pageable.getPageSize()).fetch(Records.mapping(Product::new));
}

Query Specifications

A query specification in jOOQ is a org.jooq.Condition. You can use the conditions directly, like this:

public List<Product> findBySpecification(Condition condition) {
    return create.selectFrom(PRODUCT)
        .where(condition)
        .fetch(Records.mapping(Product::new));
}

You would write specifications like this:

public final class ProductSpecifications {

    public static Condition descriptionContains(String searchTerm) {
        return PRODUCT.DESCRIPTION.contains(searchTerm);
    }

    private ProductSpecifications() {
    }
}

However, if you do not want your repositories to expose jOOQ in their public API, you can define a Specification interface like this:

import org.jooq.Condition;

public interface Specification<T> {

    Condition toCondition();

    static <T> Specification<T> not(Specification<T> other) {
        return () -> other.toCondition().not();
    }

    default Specification<T> and(Specification<T> other) {
        return () -> toCondition().and(other.toCondition());
    }

    default Specification<T> or(Specification<T> other) {
        return () -> toCondition().or(other.toCondition());
    }
}

Your repository then becomes something like this:

public List<Product> findBySpecification(Specification<Product> specification) {
    return create.selectFrom(PRODUCT)
        .where(specification.toCondition())
        .fetch(Records.mapping(Product::new));
}

You would write specifications like this:

public final class ProductSpecifications {

    public static Specification<Product> descriptionContains(String searchTerm) {
        return () -> PRODUCT.DESCRIPTION.contains(searchTerm);
    }

    private ProductSpecifications() {
    }
}

Although Condition is still a part of the specification API, it is not meant for public use. As long as clients don’t call the toCondition() method, they can use and combine specifications without depending directly on the jOOQ API.

Query Objects

Query objects in jOOQ are implemented in the same way as repositories. In other words, you get to decide what they look like.

However, to keep things simple, you should use Java records for the result, and have their fields match the fields selected in your query. That way, you can use Records.mapping to convert the result into your Java record in a type safe way.

Here is an example of a query object that returns only the product ID and the product name:

@Component
public class ProductListItemQuery {

    private final DSLContext create;

    ProductListItemQuery(DSLContext create) {
        this.create = create;
    }

    public List<ProductListItem> findAll() { // Pagination and sorting omitted for clarity
        return create.select(PRODUCT.PRODUCT_ID, PRODUCT.NAME)
                .from(PRODUCT)
                .fetch(Records.mapping(ListItem::new));
    }

    record ProductListItem(Long productId, String name) {}
}

In you query objects, you should of course also pay attention to sorting and pagination.