Chapter 2: Customizing the jOOQ Level of Involvement

In the previous chapter, we introduced jOOQ in a Spring Boot application and used it for generating and executing a valid non-type-safe SQL statement. In this chapter, we will continue this journey and increase the jOOQ level of involvement via an astonishing feature – the so-called jOOQ Code Generator. In other words, jOOQ will be in control of the persistence layer via a straightforward flow that begins with type-safe queries, continues by generating Plain Old Java Objects (POJOs) used to map the query results as objects, and ends with generating DAOs used to shortcut the most common queries in object-oriented style.

By the end of this chapter, you'll know how to write type-safe queries, and how to instruct jOOQ to generate POJOs and DAOs that have custom names in Java and Kotlin applications, using Maven and Gradle. We will cover these topics declaratively (for instance, in XML files) and programmatically.

The...

Technical requirements

The code files used in this chapter can be found on GitHub:

https://github.com/PacktPublishing/jOOQ-Masterclass/tree/master/Chapter02

Understanding what type-safe queries are

Generally speaking, what actually is a type-safe API? In short, an API is type-safe if it relies on the type system of a programming language aiming to prevent and report type errors. Specifically, jOOQ enables the compiler to do that via the Code Generator features.

Working with type-safe SQL is preferable because there is no need to validate every SQL statement via dedicated tests, and it is faster to fix things during coding than while running the application. For example, you can significantly reduce the number of unit tests dedicated to SQL validation and focus on integration tests, which is always a good thing. So, SQL type safety really matters!

Declaring SQL statements as Java String statements (for example, in JPQL style, which is verified at execution time) doesn't take advantage of type safety. In other words, the compiler cannot guarantee that a SQL statement is valid. This happens in each of the following examples that...

Generating a jOOQ Java-based schema

All the previous queries were referencing the database schema explicitly by placing the table or column name between quotes and passing them as arguments to the jOOQ built-in table() and field() methods respectively.

But, using the jOOQ Code Generator allows the SQL statements expressed via jOOQ's query DSL API to take advantage of a Java-based schema that mirrors the one from the database. The code generation part is the job of the jOOQ generation tool (its starting point is the org.jooq.codegen.GenerationTool class).

Having a Java-based schema is quite useful. The SQL statements can be expressed via the Java data access layer and executed against the underlying database schema. Besides being type-safe, these SQL statements are not prone to typos, are easy to refactor (for example, to rename a column), and are less verbose than referencing the database schema explicitly.

jOOQ comes with several solutions for generating the Java-based...

Writing queries using a Java-based schema

Once jOOQ's Code Generator has done its job, we have access to the generated artifacts. Among these artifacts, we have the jooq.generated.tables folder, which contains the database tables mirrored as Java code. The generated artifacts are placed in the specified /target folder (in our case, target/generated-sources) under the specified package name (in our case, jooq.generated).

Important Note

Typically, you'll instruct the jOOQ Code Generator to store generated code under the /target folder (Maven), /build folder (Gradle), or /src folder. Basically, if you choose the /target or /build folder, then jOOQ regenerates the code at each build; therefore, you are sure that sources are always up to date. Nevertheless, to decide which path fits best to your strategic case, consider reading Lukas Eder's answer from Stack Overflow: https://stackoverflow.com/questions/25576538/why-does-jooq-suggest-to-put-generated-code-under-target...

Configuring jOOQ to generate POJOs

So far, we have used our own POJOs as our primary Data Transfer Objects (DTOs). This is a common approach in layered applications such as Spring Boot applications.

The Office and Order POJOs are Java mirrors of the OFFICE and ORDER tables, since our queries fetch all the columns from these tables. On the other hand, the CustomerAndOrder POJO maps columns from two different tables, CUSTOMER and ORDER. More precisely, it maps CUSTOMER_NAME from CUSTOMER and ORDER_DATE from ORDER.

Optionally, jOOQ can generate POJOs on our behalf via the jOOQ Code Generator. In Maven, this feature can be enabled via the following configuration into the <generator> tag:

<generator>
  ...
  <generate>
    <pojos>true</pojos>
  </generate>
  ...
</generator>

Additionally, jOOQ can add to the generated POJOs a set of Bean Validation API annotations to convey...

Configuring jOOQ to generate DAOs

If you are familiar with Spring Data JPA/JDBC, then you're already used to relying on a DAO layer that wraps the queries. Both Spring Data JDBC and JPA provide a built-in DAO that exposes a set of CRUD operations and can be extended via user-defined repositories.

jOOQ code generation can produce similar DAOs. Basically, for each table of the database, jOOQ can generate an org.jooq.DAO implementation that exposes methods such as findById(), delete(), findAll(), insert(), and update().

In Maven, this feature can be enabled via the following configuration in the <generator> tag:

<generator>
  ...
  <generate>
    <daos>true</daos>
  </generate>
  ...
</generator>

jOOQ DAOs make use of POJOs; therefore, jOOQ will implicitly generate POJOs as well. Since we are in Spring Boot, it will be nice to have the generated DAOs annotated with @Repository...

Configuring jOOQ to generate interfaces

Besides POJOs and DAOs, jOOQ can generate an interface for each table. Each column is associated with a getter and a setter. In Maven, this can be done as shown here:

<generate>
  <interfaces>true</interfaces>
  <immutableInterfaces>true</immutableInterfaces>
</generate>

Basically, jOOQ generates interfaces that look like Spring Data's so-called interfaces-based closed projections. We can use these interfaces for mapping results sets exactly as we do with closed projections.

Nevertheless, note that at the time of writing, this feature has been proposed to be removed. You can track the deprecation here: https://github.com/jOOQ/jOOQ/issues/10509.

Next, let's continue with the programmatic configuration of the jOOQ Code Generator.

Tackling programmatic configuration

If you prefer programmatic configurations, then jOOQ exposes a fluent API (org.jooq.meta.jaxb.*) that can be used for configuring code generation in programmatic fashion. First, for Maven, add the following dependency in pom.xml:

<dependency>
  <groupId>org.jooq{.trial-java-8}</groupId>
  <artifactId>jooq-codegen</artifactId>
</dependency>

Alternatively, in Gradle, add implementation 'org.jooq{.trial-java-8}:jooq-codegen'.

Note that Configuration refers to org.jooq.meta.jaxb.Configuration, not org.jooq.Configuration, which is used for creating DSLContext and other jOOQ contexts.

This programmatic API mirrors the declarative approach and, therefore, is very intuitive. For instance, here it is the programmatic alternative of the declarative approach presented in the Configuring jOOQ to generate DAOs section for the MySQL classicmodels schema:

Configuration configuration...

Introducing jOOQ settings

jOOQ supports a bunch of optional settings (org.jooq.conf.Settings) that are mostly used to customize rendered SQL. While all these settings rely on defaults that have been carefully chosen for a wide range of cases, there are still situations when we have to alter them.

If you prefer the declarative approach, then you can alter these settings via an XML file, named jooq-settings.xml, placed in the application classpath. For instance, if the rendered SQL doesn't contain the name of the catalog/schema, then jooq-settings.xml will be as follows:

<?xml version="1.0" encoding="UTF-8"?>
<settings>
  <renderCatalog>false</renderCatalog>
  <renderSchema>false</renderSchema>
</settings>

Without these settings, jOOQ renders the name of the catalog/schema for each generated SQL. Here is an example in SQL Server:

• Without these settings, jOOQ renders [classicmodels...

Summary

In this chapter, we have reached several targets, but the most important was the introduction of the jOOQ Code Generator using configurative and programmatic approaches. More specifically, you saw how to write type-safe queries and how to generate and use POJOs and DAOs. These are fundamental skills in jOOQ that we'll develop during the entire book.

From this point forward, we'll focus on other topics that will help you to become a jOOQ power user.

In the next chapter, we will start diving into the jOOQ core concepts.