🦇 💯 👊🏽 JOOQ and his rabbit hole. How to survive without hibernate 👨🏿‍🤝‍👨🏽 👎🏿 🤦🏻

In this article I will not drown for JOOQ. I prefer Hibernate and all the Spring Data JPA power behind it. But the article will not be about them.

When we use Hibernate and Spring Data JPA, we don’t need to think about internal processes - know the annotations and write the correct method names in the repository - these two monsters will do the rest for you. In the case of JOOQ, unfortunately for many, it will take a little effort and write more than findAllByUserId (Long userId) .

What is a JOOQ?

Let's write a simple SQL query:

select * from countries c where c.population > 10000000;

We can execute this request in the console. Okay

We don’t feel like in the console. We want our application to send this request. That it was not a one-time script and that it was validated at least for syntax. We do this through Spring Data JPA:

List<Country> findAllByPopulationAfter(Long amount);

The same request is executed as above, but by Spring.

What are the clear advantages of this approach? It is performed by a powerful framework; it also validates a request for errors. But the query management takes care of the framework. And we want to completely manage the request, but at the same time, so that the request is completely validated.
UseQuery:

@Query("select c from Country c where c.population > :amount")
List<Country> findAllPopulationGreaterThan(@Param("amount") Long amount);

Such a trade-off between SQL and DSL is also good. But if we do not want to mess with SQL, we will be pleased to see something like:

return dsl.selectFrom(COUNTRIES)
                .where(COUNTRIES.POPULATION.greaterThan(amount))
                .fetch();

Several libraries are suitable for this:

QueryDSL
JOOQ
Speedment

About QueryDSL I wrote a couple of years ago. I didn’t dig Speedment, but it seems to be something more than a simple DSL generator, plus I’ll have to learn the methods by which they encrypted SQL query commands. In general, today we’ll talk about JOOQ.

JOOQ Queries

Yes, we have already seen one of these queries above:

return dsl.selectFrom(COUNTRIES)
                .where(COUNTRIES.POPULATION.greaterThan(amount))
                .fetch();

What else are there?

For example, a simple get request:

return dsl.selectFrom(Countries.COUNTRIES)
                .where(Countries.COUNTRIES.ID.eq(id))
                .fetchAny();

Or insert request:

return dsl.insertInto(Countries.COUNTRIES)
                .set(Countries.COUNTRIES.NAME, country.getName())
                .set(Countries.COUNTRIES.POPULATION, country.getPopulation())
                .set(Countries.COUNTRIES.GOVERNMENT_FORM, nameOrNull(country.getGovernmentForm()))
                .returning()
                .fetchOne();

As you can see, everything is clear, nothing more. But this is only at first glance. Finding out how deep this rabbit hole can take several weeks.

But let's start from the beginning.

Yes it will be maven

I prefer Gradle. But for some reason, JOOQ developers pay less attention to Gradle, offering to go for third-party plugins. Okay, the training project will be on Maven. But if you, dear reader, rummaged as it should in the depths of the github and are ready to reveal a fully customized project on Gradle - write to me and you will become a co-author of this article. The same applies to H2 (the training project will be on PostgreSQL).

Why I didn’t succeed with Gradle and H2

, Gradle. jooq-generator . H2, « » H2 , , , .

Necessary Maven dependencies:

<dependencies>

        <!-- Spring Starters -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jooq</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- Database -->
        <dependency>
            <groupId>org.flywaydb</groupId>
            <artifactId>flyway-core</artifactId>
        </dependency>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!-- Helpers -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>${commons.lang3.version}</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <!--Test -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <!-- JOOQ Generator -->
        <dependency>
            <groupId>org.jooq</groupId>
            <artifactId>jooq</artifactId>
            <version>${jooq.version}</version>
        </dependency>
        <dependency>
            <groupId>org.jooq</groupId>
            <artifactId>jooq-meta</artifactId>
            <version>${jooq.version}</version>
        </dependency>
        <dependency>
            <groupId>org.jooq</groupId>
            <artifactId>jooq-codegen</artifactId>
            <version>${jooq.version}</version>
        </dependency>

    </dependencies>

Spring already has a JOOQ starter that will self-configure the DataSource according to the specified settings from application.yml. But for the full work of JOOQ, this is not enough. Entities must be generated. Yes, yes, we do not write entities, but generate them. The so-called table-first approach.

The usual structure of the application with a focus on working with the database looks like this:

Until the service layer, the data travels through the application in the form of a flat model (DTO). Further, when the service needs to work with the database, it converts the DTO into an entity, sends it to the repository, and it already saves it to the database. JOOQ developers see everything differently:

As we can see, JOOQ seriously revised the standard pattern and decided to go their own way. The differences are significant:

Conversion of DTO into an entity occurs already in the repository.
We do not write an entity as such. It is written for us by the JOOQ generator.

Already this is enough to conclude that JOOQ is not so simple. A highly customizable generated entity, incomprehensible mapping in DTO and other difficulties will frighten away many. But in cases where there is nowhere to go, a focused study of these aspects of JOOQ can seriously carry away and take days, or even weeks. I am sure my post will significantly save your time.

We will cover the following aspects of working with JOOQ:

Entity Generation.
Writing CRUD Queries.
Optimization of CRUD requests.
And another CRUD request optimization.
Mapping entities using the standard JOOQ library.
and discuss why all this is necessary.

Let's go.

What do we write?

Our project will have two entities:

Country:

@Data
public class Country {

    private Long id;
    private String name;
    private GovernmentForm governmentForm;
    private Integer population;

    private List<City> cities;
}

City:

@Data
public class City {

    private Long id;
    private Long countryId;
    private String name;
}

One-to-many relationship. Country contains many related City. City contains countryId.
Flyway migration looks like this:

create table countries
(
    id              bigserial primary key,
    name            varchar(255),
    government_form varchar(255),
    population      int
);

create table cities
(
    id         bigserial primary key,
    country_id bigint,
    name       varchar(255)
);

Let's start with entity generation.

Entities, as I said, are generated separately. There are two ways to do this.

Through the Maven plugin.
In Java code.

Generating Entities in Maven

When the project builds, Maven starts the generator and generates entities. And you can call the generator at any convenient time and generate entities. This is required in those moments when, say, the structure of the base has changed. The plugin in Maven looks like this:

            <!-- JOOQ Generator Plugin -->
            <plugin>
                <groupId>org.jooq</groupId>
                <artifactId>jooq-codegen-maven</artifactId>
                <version>${jooq.version}</version>
                <executions>
                    <execution>
                        <phase>generate-sources</phase>
                        <goals>
                            <goal>generate</goal>
                        </goals>
                    </execution>
                </executions>
                <configuration>
                    <jdbc>  <!--    -->
                        <driver>${db.driver}</driver>
                        <url>${db.url}</url>
                        <user>${db.username}</user>
                        <password>${db.password}</password>
                    </jdbc>
                    <generator>
                        <database>
                            <includes>.*</includes>  <!--     -->
                            <excludes>  <!--     -->
                                flyway_schema_history
                            </excludes>
                            <inputSchema>public</inputSchema>  <!--  -->
                        </database>
                        <generate>
                            <records>true</records>
                        </generate>
                        <target>
                            <!--      -->
                            <packageName>ru.xpendence.jooqexample.domain</packageName>
                            <!--  .    target. -->
                            <directory>target/generated-sources/jooq</directory>
                        </target>
                    </generator>
                </configuration>
            </plugin>

If you did everything correctly, update Maven and among the plugins you will see jooq-codegen.

Run it. It will generate entities for you.

These are the very entities that you will use to access the database. The first nuisance: they are immutable. That is, they are mutable, but the type change occurs in such a contrived way that this process description will be pulled to a separate article. Therefore, try to think over data types at the stage of table generation.

Entities look peculiar. Here, for example, is the signature of the CountriesRecord class:

public class CountriesRecord extends UpdatableRecordImpl<CountriesRecord> implements Record4<Long, String, String, Integer> {
    //...

As we can see, CountriesRecord implements Record4, which is typed by 4 types. The countries table has 4 columns, therefore Record4. There are 3 columns in cities, so Record3. Why this was invented, I do not know. There are 22 such records in the JOOQ library, Record1 ... Record22. From which we can conclude that the capabilities of JOOQ are limited to processing tables with a maximum of 22 columns, but this is not so. I have tables with dozens of columns, and JOOQ just immediately implements Record. Well, that ...

Generating JOOQ entities in Java code looks like this:

@Component
public class AfterStartupApplicationListener implements ApplicationListener<ContextRefreshedEvent> {

    @Value("${spring.datasource.driver-class-name}")
    private String driver;

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

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

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

    @Value("${jooq.generator.database.name}")
    private String databaseName;

    @Value("${jooq.generator.database.with-includes}")
    private String databaseWithIncludes;

    @Value("${jooq.generator.database.with-input-schema}")
    private String databaseWithInputSchema;

    @Value("${jooq.generator.target.package-name}")
    private String targetPackageName;

    @Value("${jooq.generator.target.directory}")
    private String targetDirectory;

    @SneakyThrows
    @Override
    public void onApplicationEvent(ContextRefreshedEvent contextRefreshedEvent) {
        new GenerationTool().run(configureGenerator());
    }

    private Configuration configureGenerator() {
        return new Configuration()
                .withJdbc(new Jdbc()
                        .withDriver(driver)
                        .withUrl(url)
                        .withUser(username)
                        .withPassword(password))
                .withGenerator(new Generator()
                        .withDatabase(new Database()
                                .withName(databaseName)
                                .withIncludes(databaseWithIncludes)
                                .withExcludes("")
                                .withInputSchema(databaseWithInputSchema))
                        .withTarget(new Target()
                                .withPackageName(targetPackageName)
                                .withDirectory(targetDirectory)));
    }
}

In my case, the generator starts after a full start of the application, when all new migrations are rolled up and the database is up to date.

So, we have models, we have entities, we have a base. It's time to create a repository and write queries.

Create Repository

All requests go through DslContext. Repeat the DslContext.

@Repository
@RequiredArgsConstructor
public class CountryRepository implements CrudRepository<Country> {

    private final DSLContext dsl;

}

The repository is ready.

Writing CRUD Queries

Insert

If we used SQL, the request to add another country would be:

insert into countries(name, government_form, population)
values (' -', 'UNITARY', 100500) returning *;

In JOOQ, a query is as close as possible to SQL syntax:

    public Country insertValues(Country country) {
        return dsl.insertInto(Countries.COUNTRIES)  //insert into countries
                .values(country.getId(), country.getName(), country.getPopulation(), nameOrNull(country.getGovernmentForm()))  //values (? ? ? ?)
                .returning()  //returning
                .fetchOne()  //*
                .into(Country.class);
    }

As we see, nothing complicated. However, such a request does not suit us, because in our case the ID is generated in the database and we must take this into account. If we write something like:

.values(null, country.getName(), country.getPopulation(), nameOrNull(country.getGovernmentForm()))

we'll get

org.postgresql.util.PSQLException: :     "id"   NOT NULL

However, if we generated the ID on the application side, such a request would be a ride. We will have to rewrite the request, giving each field a specific value. So you can:

    public Country insert(Country country) {
        return dsl.insertInto(Countries.COUNTRIES)
                .set(Countries.COUNTRIES.NAME, country.getName())
                .set(Countries.COUNTRIES.POPULATION, country.getPopulation())
                .set(Countries.COUNTRIES.GOVERNMENT_FORM, nameOrNull(country.getGovernmentForm()))
                .returning()
                .fetchOne()
                .into(Country.class);
    }

In this case, we manually set the object in the field. This option is quite working, but there is better. It would be ideal to send the whole object for saving, as is done in Spring Data JPA:

repository.save(country);

Could be so. To do this, we need to map our model into the extends Record entity and send it for saving:

    public Country insert(Country country) {
        return dsl.insertInto(Countries.COUNTRIES)
                .set(dsl.newRecord(Countries.COUNTRIES, country))  //   
                .returning()
                .fetchOptional()
                .orElseThrow(() -> new DataAccessException("Error inserting entity: " + country.getId()))
                .into(Country.class);
    }

This method is the simplest and most understandable for those cases when we do not need to configure mapping. But the setting of mapping will be lower.

As we noticed, this query returns the whole entity. You can determine what exactly you need to return in the fetch () method. It looks like this:

    public Long insertAndReturnId(Country country) {
        return dsl.insertInto(Countries.COUNTRIES)
                .set(dsl.newRecord(Countries.COUNTRIES, country))
                .returning(Countries.COUNTRIES.ID) //  Record    - id
                .fetchOptional()
                .orElseThrow(() -> new DataAccessException("Error inserting entity: " + country.getId()))
                .get(Countries.COUNTRIES.ID); // id
    }

A little cumbersome, but, again, with what to compare.

We will write the rest of the CRUD methods.

Update

SQL query:

update countries
set name            = '',
    government_form = 'CONFEDERATE',
    population      = 100500
where id = 1
returning *;

JOOQ request:

    public Country update(Country country) {
        return dsl.update(Countries.COUNTRIES)
                .set(dsl.newRecord(Countries.COUNTRIES, country))
                .where(Countries.COUNTRIES.ID.eq(country.getId()))
                .returning()
                .fetchOptional()
                .orElseThrow(() -> new DataAccessException("Error updating entity: " + country.getId()))
                .into(Country.class);
    }

Select

A query in SQL would be:

select *
from countries c
where id = ?;

In JOOQ, the query will look accordingly:

    public Country find(Long id) {
        return dsl.selectFrom(Countries.COUNTRIES) //select * from countries
                .where(Countries.COUNTRIES.ID.eq(id))  //where id = ?
                .fetchAny()  // ,    
                .into(Country.class);
    }

Delete

There are requests in which we have nothing to return. Such queries return the number of rows affected. In delete, we have nothing to return, but the information we receive will still be useful to us.

    public Boolean delete(Long id) {
        return dsl.deleteFrom(Countries.COUNTRIES)
                .where(Countries.COUNTRIES.ID.eq(id))
                .execute() == 1;
    }

We delete one line. So the SQL query will return something like:

1 row affected in 5 ms

Having received such an answer, we know for sure that the row has been deleted. This will be enough to declare the operation successful.

It was not a fairy tale. It was just a lubricant. Using a regular maper for thin mapping of an entity in Record and vice versa

“Okay,” you say, reader, “where is one-to-many, emoe?” Something I did not see the moment in which Country grows with a multitude of City. ”

And you will be right. This is not Hibernate; here you have to manually. All you need to do is get a list of City for which id = country.getId (). I have already shown the into () method, which uses a regular mapper to map Record into an entity. But JOOQ has an additional map () method that allows us to do whatever we want with Record. Let's look at its functionality and add the addition of cities:

    public Country find(Long id) {
        return dsl.selectFrom(Countries.COUNTRIES)
                .where(Countries.COUNTRIES.ID.eq(id))
                .fetchAny()
                .map(r -> {
                    Country country = r.into(Country.class);
                    country.setCities(cityRepository.findAll(Cities.CITIES.COUNTRY_ID.eq(country.getId())));
                    return country;
                });
    }

As we see, now we first map the Record in Country, and then we make another request in cities, we get all City for this Country and set them into essence.

But there can be dozens of such sets, which means there can be dozens of requests. And to describe these requests directly in the method is like that. The correct solution is to write a separate mapper and put all these requests there.

@RequiredArgsConstructor
@Component
public class CountryRecordMapper implements RecordMapper<CountriesRecord, Country> {

    private final CityRepository cityRepository;

    @Override
    public Country map(CountriesRecord record) {
        Country country = record.into(Country.class);
        country.setCities(cityRepository.findAll(Cities.CITIES.COUNTRY_ID.eq(country.getId())));
        return country;
    }
}

The query will now look like this:

    public Country findWithCustomMapper(Long id) {
        return dsl.selectFrom(Countries.COUNTRIES)
                .where(Countries.COUNTRIES.ID.eq(id))
                .fetchAny()
                .map(r -> countryRecordMapper.map((CountriesRecord) r));
    }

It is more concise, and does not contain additional logic.

Ok, we learned how to map Record into an entity, but what about fine-tuning the mapping of an entity in Record?

So far we have this design:

.set(dsl.newRecord(Countries.COUNTRIES, country))

Already good, but what if we need to specifically map a field? For example, we have LocalDateTime there, and a generator for PostgreSQL like Timestamp generated an OffsetDateTime. In this case, the field simply will not be mapped and not recorded in the database. For such cases, we will need another mapper who will do the same, but in the opposite direction.

Yes, for every mapper we have unmapper

He is called that. Let's write his heir.

@Component
@RequiredArgsConstructor
public class CountryRecordUnmapper implements RecordUnmapper<Country, CountriesRecord> {

    private final DSLContext dsl;

    @Override
    public CountriesRecord unmap(Country country) throws MappingException {
        CountriesRecord record = dsl.newRecord(Countries.COUNTRIES, country);
        record.setPopulation(-1);
        return record;
    }
}

Insert with its application will look like this:

    public Country insertWithUnmapper(Country country) {
        return dsl.insertInto(Countries.COUNTRIES)
                .set(countryRecordUnmapper.unmap(country))
                .returning()
                .fetchOptional()
                .orElseThrow(() -> new DataAccessException("Error inserting entity: " + country.getId()))
                .into(Country.class);
    }

As we see, also quite.

Conclusions and Conclusions

Personally, I like Hibernate more. Probably, for 90 +% of applications its use will be more justified. But if you, the reader, want to control every request, JOOQ or another similar library is more suitable for you.

As always, I post the training project. He is lying here .

JOOQ and his rabbit hole. How to survive without hibernate