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 following topics will be covered in this chapter:

  • Understanding what type-safe queries are
  • Generating a jOOQ Java-based schema
  • Writing queries using a Java-based schema
  • Configuring jOOQ to generate POJOs
  • Configuring jOOQ to generate DAOs
  • Configuring jOOQ to generate interfaces
  • Tackling programmatic configuration
  • Introducing jOOQ settings

Let's start with a brief discussion about type-safe queries.

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 use different choices for the persistence layer. All these examples compile but fail at runtime.

Let's see a JdbcTemplate non-type-safe SQL example (with the wrong order of binding values):

public Manager findManager(Long id, String name) {
  String sql = "SELECT * FROM MANAGER 
    WHERE MANAGER_ID=? AND MANAGER_NAME=?";               
  Manager result = jdbcTemplate
    .queryForObject(sql, Manager.class, name, id);
}

Here, we have a Spring Data example (name should be String, not int):

@Query(value = "SELECT c.phone, p.cachingDate FROM Customer c 
       INNER JOIN c.payments p WHERE c.customer_name = ?1")
CustomerPojo fetchCustomerWithCachingDateByName(int name);

Here is a Spring Data derived query method example (name should be String, not int):

Customer findByName(int name);

The following is a jOOQ query builder without the Code Generator example (instead of v, it should be v.getOwnerName()):

public Customer findCustomer(Voucher v) {        
  ctx.select().from(table("CUSTOMER"))                
     .where(field("CUSTOMER.CUSTOMER_NAME").eq(v))...;    
}

Here's another jOOQ query builder without the Code Generator example (in our schema, there is no OFFICES table and no CAPACITY column):

ctx.select()
   .from(table("OFFICES"))
   .where(field("OFFICE.CAPACITY").gt(50));

These are just some simple cases that are easy to spot and fix. Imagine a non-type-safe complex query with a significant number of bindings.

But, if the jOOQ Code Generator is enabled, then jOOQ will compile the SQL statements against an actual Java-based schema that mirrors a database. This way, jOOQ ensures at least the following:

  • The classes and fields that occur in SQL exist, have the expected type, and are mapped to a database.
  • There are no type mismatches between the operators and operands.
  • The generated query is syntactically valid.

    Important Note

    I said at least because, besides type safety, jOOQ takes care of many other aspects, such as quotations, qualification, and case sensitivity of identifiers. These aspects are not easy to handle across SQL dialects, and thanks to the Code Generator feature, jOOQ makes the right choices for us upfront almost everywhere. As Lukas Eder said: "Using jOOQ with the Code Generator is just a little additional setup, but it will help jOOQ to make the right, carefully chosen default choices for so many silly edge cases that are so annoying to handle later on. I can't recommend it enough! :)"

Back to type safety, let's assume that the jOOQ Code Generator has produced the needed artifacts (a suite of classes that mirrors the database tables, columns, routines, views, and so on). In this context, the previous jOOQ examples can be rewritten in a type-safe manner, as follows. Note that none of the following snippets will compile:

import static jooq.generated.tables.Customer.CUSTOMER;
...
public Customer findCustomer(Voucher v) {        
  ctx.select().from(CUSTOMER)                   
     .where(CUSTOMER.CUSTOMER_NAME.eq(v))...;         
}

Besides being less verbose than the original example, this query is type-safe as well. This time, CUSTOMER (which replaced table("CUSTOMER")) is a static instance (shortcut) of the Customer class, representing the customer table. Moreover, CUSTOMER_NAME (which replaced field("CUSTOMER.CUSTOMER_NAME")) is also a static field in the Customer class, representing the customer_name column of the customer table. These Java objects have been generated by the jOOQ Code Generator as part of the Java-based schema. Note how this static instance was nominally imported here – if you find the technique of importing each static artifact cumbersome, then you can simply rely on the neat trick of importing the entire schema as import static jooq.generated.Tables.*.

The second jOOQ example can be rewritten in a type-safe manner, as follows:

import static jooq.generated.tables.Office.OFFICE;
...
ctx.select().from(OFFICES).where(OFFICE.CAPACITY.gt(50));

The following figure is a screenshot from the IDE, showing that the compiler complains about the type safety of this SQL:

Figure 2.1 – The compiler reports a type safety error

Figure 2.1 – The compiler reports a type safety error

Important Note

Lukas Eder said this: "As you probably know, the IDEs help writing SQL and JPQL strings, which is nice. But IDEs doesn't fail the build when a column name changes." Well, having type-safe queries covers this aspect, and the IDE can fail the build. So, thanks to jOOQ's fluency and expressiveness, the IDE can provide code completion and refactoring support. Moreover, with jOOQ, the bind variables are part of a non-dynamic Abstract Syntax Tree (AST); therefore, it is not possible to expose SQL injection vulnerabilities this way.

OK, but how do we obtain this Java-based schema?

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 schema via the jOOQ Code Generator. Mainly, jOOQ can generate the Java-based schema by applying the technique of reverse engineering to the database directly, the DDL files, JPA entities, or XML files containing the schema. Next, we will tackle the first three approaches, starting with the first approach, which generates the Java-based schema directly from the database. Mainly, we will use Flyway to migrate the database (Liquibase is supported as well), which is subsequently reverse engineered by jOOQ to obtain the Java-based schema.

Code generation from a database directly

The following figure represents the jOOQ Java-based schema generation flow:

Figure 2.2 – Java-based schema generation

Figure 2.2 – Java-based schema generation

So far, jOOQ will regenerate the Java-based schema every time the application starts (runs).

In other words, even if the database schema has not changed, jOOQ will regenerate the Java-based schema at each run. Obviously, this is preferable to regenerating the Java-based schema only when the underlying database schema is missing or has changed (for instance, a new column has been added to a table); otherwise, this is just a waste of time.

Conscious schema change management is a good thing, and having a tool for this is great! Most probably, you'll choose between Flyway and Liquibase. While we will only cover the Flyway approach in the next section, Liquibase is very well represented in the jOOQ manual (https://www.jooq.org/doc/latest/manual/code-generation/codegen-liquibase/).

Adding Flyway with Maven

Flyway is a great tool for database migration (https://flywaydb.org/). Mainly, Flyway keeps track of database schema modifications via a table named flyway_schema_history (or schema_version in Flyway prior to version 5). This table is automatically added to the database and is maintained by Flyway itself.

Typically, in Spring Boot, Flyway reads and executes all the database migration scripts located in the indicated path (the default path is src/main/resources/db/migration). For instance, in this book, we use an explicit path that points to a location outside the applications in the root folder (${root}/db/migration). We do this because we want to avoid multiplying the migrations scripts in every single application. To quickly start with Flyway, simply add to pom.xml the following dependency:

<dependency>
  <groupId>org.flywaydb</groupId>
  <artifactId>flyway-core</artifactId>
</dependency>

The Flyway default Maven phase for a migrate operation is pre-integration-test (right after package). On the other hand, jOOQ needs the migrations to take place in the generate-sources phase (right after validate), therefore much earlier.

Mainly, jOOQ triggers a SELECT query against the flyway_schema_history table to check the schema version. This means that jOOQ needs to wait for migrations to take place and the schema version to be updated. If the version is updated, then jOOQ regenerates the Java-based schema; otherwise, you'll see a message like this: Existing version 1.1 is up to date with 1.1 for schema classicmodels. Ignoring schema.

Scheduling migrations in the generate-sources phase can be done via the Flyway Maven plugin, as follows:

<phase>generate-sources</phase>

Let's try using Gradle.

Adding Flyway with Gradle

If you prefer to use Gradle, then you'll need build.gradle in the following code:

plugins { 
  id 'org.flywaydb.flyway' version '...'
}
dependencies {        
  implementation 'org.flywaydb:flyway-core'   
}
flyway {
  driver = ...
  url = ...
  ...
}

Next, let's add the SQL scripts following the Flyway naming conventions.

Adding SQL scripts for Flyway

In the applications developed in this book, the scripts read and executed by Flyway are named V1.1__Create.sql (this file contains the DDLs of the database schema) and afterMigrate.sql (this file contains the DMLs to populate the database) and are placed externally to the applications in the ${root}/db/migration folder. Adding a new file that respects the Flyway naming convention (for example, V1.2__AddColumn.sql) will instruct Flyway to update the database schema and jOOQ to regenerate the Java-based schema. As long as no migrations happen and the jOOQ-generated classes exist, jOOQ doesn't regenerate the Java-based schema.

The following figure represents the flow, which is particularly interesting for most use cases that contain DDL changes:

Figure 2.3 – Flyway migrations and the jOOQ Java-based schema generation

Figure 2.3 – Flyway migrations and the jOOQ Java-based schema generation

Note how Flyway migrations take place before jOOQ code generation. Finally, it's time to enable the jOOQ Code Generator.

From a developer perspective, enabling the jOOQ Code Generator is a setup task that gets materialized in a snippet of code, written in standalone migration scripts or pom.xml if there is a Maven-based project, or build.gradle if there is a Gradle-based project. jOOQ reads this information and uses it to configure and automatically execute the org.jooq.codegen.GenerationTool generator accordingly.

Running the Code Generator with Maven

Mainly, the jOOQ Code Generator can run in standalone mode or with Maven/Gradle. While there are no big differences between these two approaches, we prefer to go further with the Maven plugin, jooq-codegen-maven. Nevertheless, for a quick example of running the Code Generator from the command line in standalone mode, you have everything you need (including a README file) packed in a ZIP archive named standalone-codegen-jooq.zip. This is available for MySQL, PostgreSQL, SQL Server, and Oracle.

Now, configuring jOOQ's Code Generator requires some information that can be packed in an XML file. The climax of this file is the <configuration> tag used to shape an org.jooq.meta.jaxb.Configuration instance. Consider reading carefully each comment of the following jOOQ Code Generator configuration stub, since each comment provides important details about the tag that precedes it (in the bundled code, you'll see an expanded version of these comments, containing extra details):

<plugin>
  <groupId>...</groupId>
  <artifactId>jooq-codegen-maven</artifactId>
  <executions>
    <execution>
      <id>...</id>
      <phase>generate-sources</phase>
      <goals>
        <goal>generate</goal>
      </goals>
      <configuration xmlns = "...">
        <!-- Configure the database connection here -->
        <jdbc>...</jdbc>

Next, the <generator/> tag contains all the information needed for customizing the jOOQ generator:

        <generator>
          <!-- The Code Generator: 
          org.jooq.codegen.{Java/Kotlin/Scala}Generator
          Defaults to org.jooq.codegen.JavaGenerator -->
          <name>...</name>
          <database>
            <!-- The database type. The format here is:    
            org.jooq.meta.[database].[database]Database -->
            <name>...</name>
            <!-- The database schema-->
            <inputSchema>...</inputSchema>
            <!-- What should be included by the generator -->
            <includes>...</includes>
            <!-- What should be excluded by the generator -->
            <excludes>...</excludes>
            <!-- Schema version provider -->
            <schemaVersionProvider>...</schemaVersionProvider>
            <!-- Set generator queries timeout(default 5s) -->
            <logSlowQueriesAfterSeconds>
              ...
            </logSlowQueriesAfterSeconds>
          </database>
          <target>
            <!-- The output package of generated classes -->
            <packageName>...</packageName>
            <!—The output directory of generated classes -->
            <directory>...</directory>
          </target>
        </generator>
      </configuration>
    </execution>
  </executions>
</plugin>

Based on this stub and the comments, let's try to fill up the missing parts for configuring the jOOQ Code Generator against the classicmodels database in MySQL:

<plugin>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-codegen-maven</artifactId>
  <executions>
    <execution>
      <id>generate-for-mysql</id>
      <phase>generate-sources</phase>
      <goals>
        <goal>generate</goal>
      </goals>
      <configuration xmlns = "...">
        <jdbc>
          <driver>${spring.datasource.driverClassName}</driver>
          <url>${spring.datasource.url}</url>
          <user>${spring.datasource.username}</user>
          <password>${spring.datasource.password}</password>
        </jdbc>
        <generator>
          <name>org.jooq.codegen.JavaGenerator</name>
          <database>
            <name>org.jooq.meta.mysql.MySQLDatabase</name>
            <inputSchema>classicmodels</inputSchema>
            <includes>.*</includes>
            <excludes>
              flyway_schema_history | sequences 
              | customer_pgs | refresh_top3_product
              | sale_.* | set_.* | get_.* | .*_master
            </excludes>
            <schemaVersionProvider>
             SELECT MAX(`version`) FROM `flyway_schema_history`
            </schemaVersionProvider>
            <logSlowQueriesAfterSeconds>
              20
            </logSlowQueriesAfterSeconds>
          </database>
          <target>
            <packageName>jooq.generated</packageName>
            <directory>target/generated-sources</directory>
          </target>
        </generator>
      </configuration>
    </execution>
  </executions>
</plugin>

For brevity, the alternatives for PostgreSQL, SQL Server, and Oracle are not listed here, but you can find them in the code bundled with this book in the application named WriteTypesafeSQL.

Additionally, the Maven plugin supports the following flags in <configuration>:

  • Disabling the plugin via a Boolean property/constant:

<skip>false</skip>

  • Specifying an external XML configuration instead of an inline configuration:

<configurationFile>${externalfile}</configurationFile>

  • Alternatively, specifying several external configuration files, merged by using Maven's combine.children="append" policy:

<configurationFiles>

  <configurationFile>${file1}</configurationFile>

  <configurationFile>...</configurationFile>

</configurationFiles>

Next, let's run the jOOQ generator via Gradle.

Running the Code Generator with Gradle

Running the Code Generator via Gradle can be accomplished via gradle-jooq-plugin (https://github.com/etiennestuder/gradle-jooq-plugin/). The next snippet of code represents the climax of configuration for Oracle:

dependencies {        
  jooqGenerator 'com.oracle.database.jdbc:ojdbc8'
  jooqGenerator 'com.oracle.database.jdbc:ucp'
}
jooq {
  version = '...'
  edition = nu.studer.gradle.jooq.JooqEdition.TRIAL_JAVA_8
  configurations {
    main {
      generateSchemaSourceOnCompilation = true  // default
      generationTool {
        logging = org.jooq.meta.jaxb.Logging.WARN
        jdbc {
          driver = project.properties['driverClassName']
          url = project.properties['url']
          user = project.properties['username']
          password = project.properties['password']
        }
        generator {
          name = 'org.jooq.codegen.JavaGenerator'
          database {
            name = 'org.jooq.meta.oracle.OracleDatabase'
            inputSchema = 'CLASSICMODELS'
            includes = '.*'
            schemaVersionProvider = 'SELECT MAX("version") 
              FROM "flyway_schema_history"'
            excludes = '''
              flyway_schema_history | DEPARTMENT_PKG | GET_.*    
              | CARD_COMMISSION | PRODUCT_OF_PRODUCT_LINE
              ...
            '''
            logSlowQueriesAfterSeconds = 20
          }
          target {
            packageName = 'jooq.generated'
            directory = 'target/generated-sources'
          }
          strategy.name = 
            "org.jooq.codegen.DefaultGeneratorStrategy"
        }
  ...
}

In addition, we have to bind the jOOQ generator to the Flyway migration tool to execute it only when it is really needed:

tasks.named('generateJooq').configure { 
  // ensure database schema has been prepared by 
  // Flyway before generating the jOOQ sources
  dependsOn tasks.named('flywayMigrate')
  // declare Flyway migration scripts as inputs on this task
  inputs.files(fileTree('...'))
        .withPropertyName('migrations')
        .withPathSensitivity(PathSensitivity.RELATIVE)
  // make jOOQ task participate in 
  // incremental builds and build caching
  allInputsDeclared = true
  outputs.cacheIf { true }
}

In the bundled code, you can find the complete application (WriteTypesafeSQL) for MySQL, PostgreSQL, SQL Server, and Oracle, written for Java/Kotlin and Maven/Gradle combos.

Alternatively, if you prefer Ant, then read this: https://www.jooq.org/doc/latest/manual/code-generation/codegen-ant/. Next, let's tackle another approach to generating the Java-based schema.

Code generation from SQL files (DDL)

Another jOOQ approach for obtaining the Java-based schema relies on the DDL Database API, which is capable of accomplishing this task from SQL scripts (a single file or incremental files) containing the database schema. Mainly, the jOOQ SQL parser materializes our SQL scripts into an in-memory H2 database (available out of the box in Spring Boot), and the generation tool will reverse-engineer it to output the Java-based schema. The following figure depicts this flow:

Figure 2.4 – The jOOQ Java-based schema generation via the DDL Database API

Figure 2.4 – The jOOQ Java-based schema generation via the DDL Database API

The climax of the DDL Database API configuration relies on the jOOQ Meta Extensions, represented by org.jooq.meta.extensions.ddl.DDLDatabase.

Running the Code Generator with Maven

In this context, running the Code Generator via Maven relies on the following XML stub. Read each comment, since they contain valuable information (in the bundled code, you'll see an expanded version of these comments):

<configuration xmlns = "...">
  <generator>
    <name>...</name>
    <database>
      <name>org.jooq.meta.extensions.ddl.DDLDatabase</name>
      <properties>
        <!-- Specify the location of your SQL script -->
        <property>
          <key>scripts</key>
          <value>...</value>
        </property>
        <!-- The sort order of scripts in a directory
        (semantic, alphanumeric, flyway, none) -->
        <property>
          <key>sort</key>
          <value>...</value>
        </property>
        <!-- The default schema for unqualified objects
        (public, none) -->
        <property>
          <key>unqualifiedSchema</key>
          <value>...</value>
        </property>
        <!-- The default name case for unquoted objects
        (as_is, upper, lower) -->
        <property>
          <key>defaultNameCase</key>
          <value>...</value>
        </property>
      </properties>
      <inputSchema>PUBLIC</inputSchema>
      <includes>...</includes>
      <excludes>...</excludes>
      <schemaVersionProvider>...</schemaVersionProvider>
      <logSlowQueriesAfterSeconds>
      ...
      </logSlowQueriesAfterSeconds>
    </database>
    <target>
      <packageName>...</packageName>
      <directory>...</directory>
    </target>
  </generator>
</configuration>

In this context, jOOQ generates the Java-based schema without connecting to the real database. It uses the DDL files to produce an in-memory H2 database that is subsequently reverse-engineered into Java classes. The <schemaVersionProvider> tag can be bound to a Maven constant that you have to maintain in order to avoid running the Code Generator when nothing has changed.

Besides this stub, we need the following dependency:

<dependency>
  <groupId>org.jooq{.trial-java-8}</groupId>
  <artifactId>jooq-meta-extensions</artifactId>
  <version>${jooq.version}</version>
</dependency>

Based on this stub and the explanations from the comments, let's try to fill up the missing parts to configure the jOOQ Code Generator against the classicmodels database in PostgreSQL:

<configuration xmlns = "...">
  <generator>
    <name>org.jooq.codegen.JavaGenerator</name>
    <database>
      <name>org.jooq.meta.extensions.ddl.DDLDatabase</name>
      <properties>
        <property>
          <key>scripts</key>
          <value>...db/migration/ddl/postgresql/sql</value>
        </property>
        <property>
          <key>sort</key>
          <value>flyway</value>
        </property>
        <property>
          <key>unqualifiedSchema</key>
          <value>none</value>
        </property>
        <property>
          <key>defaultNameCase</key>
          <value>lower</value>
        </property>
      </properties>
      <inputSchema>PUBLIC</inputSchema>
      <includes>.*</includes>
      <excludes>
            flyway_schema_history | akeys | avals | defined 
          | delete.* | department_topic_arr | dup 
          |  ...
      </excludes>
        <schemaVersionProvider>
          ${schema.version} <!-- this is a Maven constant -->
        </schemaVersionProvider>
        <logSlowQueriesAfterSeconds>
          20
        </logSlowQueriesAfterSeconds>
      </database>
    <target>
      <packageName>jooq.generated</packageName>
      <directory>target/generated-sources</directory>
    </target>
  </generator>
</configuration>

The Gradle alternative is available in the bundled code.

Preparing the SQL files

Currently, it is impossible to use some vendor-specific stuff; therefore, our SQL files may contain parts that the jOOQ SQL parser may not understand. In such cases, we have to prepare our SQL files by delimiting these parts with the jOOQ default conventions from the following example:

-- [jooq ignore start]
IF OBJECT_ID('payment', 'U') IS NOT NULL 
  DROP TABLE payment;
-- [jooq ignore stop]

The code between -- [jooq ignore start] and -- [jooq ignore stop] is ignored by the jOOQ SQL parser. Turning on/off ignoring content between such tokens can be done via the parseIgnoreComments Boolean property, while customizing these tokens can be done via the parseIgnoreCommentStart and parseIgnoreCommentStop properties. For more details, refer to https://www.jooq.org/doc/latest/manual/code-generation/codegen-ddl/.

In the bundled code, you can see an implementation of this stub for MySQL, PostgreSQL, SQL Server, and Oracle via the Java/Kotlin and Maven/Gradle combos, under the name DeclarativeDDLDatabase.

Going forward, while the jOOQ SQL parser will become more powerful, this will be the recommended approach for using the jOOQ Code Generator. The goal is to delegate jOOQ to do more migration work out of the box.

Code generation from entities (JPA)

Let's assume that you have a JPA application that relies on a schema shaped as an entity model (JPA-annotated entities) and you want to obtain the jOOQ Java-based schema. If you cannot isolate the JPA entity model in a separate module of the application, then you can configure jOOQ to generate the Java-based schema directly from the real database (supposing that you have access to the real database schema during the development stage) or from the DDL files (assuming that you have such files). But, if you can easily place the entities in a separate module of the application, then you can rely on jOOQ's JPA Database API (org.jooq.meta.extensions.jpa.JPADatabase), which is capable of generating the Java-based schema from the JPA model. The JPA Database API requires entities in a separate module because it has to look them up from the classpath via Spring.

The following figure depicts the flow of the JPA Database API:

Figure 2.5 – The jOOQ Java-based schema generation via the JPA Database API

Figure 2.5 – The jOOQ Java-based schema generation via the JPA Database API

The flow of the JPA Database API uses Hibernate internally for generating an in-memory H2 database from the JPA model (entities). Subsequently, jOOQ reverse-engineers this H2 database into jOOQ classes (the Java-based schema).

Running the Code Generator with Maven

In this context, running the Code Generator via Maven relies on the following XML stub. Read each comment, since they contain valuable information (in the bundled code, you can find an expanded version of these comments):

<configuration xmlns="...">
  <!-- JDBC connection to the H2 in-memory database -->
  <jdbc>...</jdbc>
  <generator>
    <database>
      <name>org.jooq.meta.extensions.jpa.JPADatabase</name>
      <properties>
        <!-- The properties prefixed with hibernate... or 
        javax.persistence... will be passed to Hibernate -->
        <property>
          <key>...</key>
          <value>...</value>
        </property>
        <!-- Java packages (comma separated) that 
        contains your entities -->
        <property>
          <key>packages</key>
          <value>...</value>
        </property>
        <!-- Whether JPA 2.1 AttributeConverters should 
        be auto-mapped to jOOQ Converters (default true) -->
        <property>
          <key>useAttributeConverters</key>
          <value>...</value>
        </property>
        <!-- The default schema for unqualified objects
        (public, none) -->
        <property>
          <key>unqualifiedSchema</key>
          <value>...</value>
        </property>
      </properties>
      <includes>...</includes>
      <excludes>...</excludes>
      <schemaVersionProvider>...</schemaVersionProvider>
      <logSlowQueriesAfterSeconds>
      ...
      </logSlowQueriesAfterSeconds>
    </database>
    <target>
      <packageName>...</packageName>
      <directory>...</directory>
    </target>
  </generator>
</configuration>

Based on this stub and the comments, here is an example containing the popular settings (this snippet was extracted from a JPA application that uses MySQL as the real database):

<configuration xmlns="...">
  <jdbc>
    <driver>org.h2.Driver</driver>
    <url>jdbc:h2:~/classicmodels</url>
  </jdbc>
  <generator>
    <database>
      <name>org.jooq.meta.extensions.jpa.JPADatabase</name>
      <properties>
        <property>
          <key>hibernate.physical_naming_strategy</key>
          <value>
            org.springframework.boot.orm.jpa
               .hibernate.SpringPhysicalNamingStrategy
          </value>
        </property>
        <property>
          <key>packages</key>
          <value>com.classicmodels.entity</value>
        </property>
        <property>
          <key>useAttributeConverters</key>
          <value>true</value>
        </property>
        <property>
          <key>unqualifiedSchema</key>
          <value>none</value>
        </property>
      </properties>
   
      <includes>.*</includes>
       <excludes>
             flyway_schema_history | sequences 
             | customer_pgs | refresh_top3_product
             | sale_.* | set_.* | get_.* | .*_master
       </excludes>
   
       <schemaVersionProvider>
         ${schema.version}
       </schemaVersionProvider>                       
   
       <logSlowQueriesAfterSeconds>
         20
       </logSlowQueriesAfterSeconds>
     </database>
      <target>
        <packageName>jooq.generated</packageName>
        <directory>target/generated-sources</directory>
      </target>
    </generator>
  </configuration>

Besides this stub, we need the following dependency:

<dependency>
  <groupId>org.jooq{.trial-java-8}</groupId>
  <!-- before jOOQ 3.14.x, jooq-meta-extensions -->
  <artifactId>jooq-meta-extensions-hibernate</artifactId>
  <version>${jooq.meta.extensions.hibernate.version}
  </version>
</dependency>

This approach and the Gradle alternative are available in the bundled code for Java and Kotlin under the name DeclarativeJPADatabase.

Another approach that you'll find interesting is generating the Java-based schema from XML files: https://www.jooq.org/doc/latest/manual/code-generation/codegen-xml/. This is exemplified in DeclarativeXMLDatabase and ProgrammaticXMLGenerator.

Generally speaking, it is highly recommended to read the Code generation section of the jOOQ manual: https://www.jooq.org/doc/latest/manual/code-generation/. This section contains tons of settings and configurations that influence the generated artifacts.

If you need to manage multiple databases, schemas, catalogs, a shared-schema multitenancy, and so on, then refer to Chapter 17, Multitenancy in jOOQ.

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-and-not-under-src. It is also recommended to check out the Code generation and version control section from the jOOQ manual, available at https://www.jooq.org/doc/latest/manual/code-generation/codegen-version-control/.

Remember that, in the previous chapter (Chapter 1, Starting jOOQ and Spring Boot), we already used the jOOQ DSL API to write the following query:

ResultQuery<?> query = ctx.selectFrom(table("office")) 
  .where(field("territory").eq(territory));

This query references the database schema (table and columns). Rewriting this query referencing the Java-based schema produces the following code (jOOQ Record such as OfficeRecord are introduced in the next chapter; for now, think of it as the result set wrapped in a Java object):

import static jooq.generated.tables.Office.OFFICE; 
import jooq.generated.tables.records.OfficeRecord;
...
ResultQuery<OfficeRecord> query = ctx.selectFrom(OFFICE) 
  .where(OFFICE.TERRITORY.eq(territory));

Alternatively, generating and executing the query immediately can be done as follows (Office is a POJO):

public List<Office> findOfficesInTerritory(String territory) {
  List<Office> result = ctx.selectFrom(OFFICE) 
    .where(OFFICE.TERRITORY.eq(territory))
    .fetchInto(Office.class); 
  return result;
}

Depending on the database vendor, the generated SQL looks as follows with MySQL (note that jOOQ has correctly generated backticks specific to MySQL queries):

SELECT
  `classicmodels`.`office`.`office_code`, 
  `classicmodels`.`office`.`city`, 
  ...
  `classicmodels`.`office`.`territory` 
FROM `classicmodels`.`office` 
WHERE `classicmodels`.`office`.`territory` = ?

The generated SQL looks as follows with PostgreSQL (note that jOOQ has used the qualification containing the PostgreSQL schema):

SELECT 
  "public"."office"."office_code", 
  "public"."office"."city", 
  ...
  "public"."office"."territory" 
FROM "public"."office" 
WHERE "public"."office"."territory" = ?

The generated SQL looks as follows with Oracle (note that jOOQ has made the identifiers uppercase, exactly as Oracle prefers):

SELECT
  "CLASSICMODELS"."OFFICE"."OFFICE_CODE", 
  "CLASSICMODELS"."OFFICE"."CITY", 
  ...
  "CLASSICMODELS"."OFFICE"."TERRITORY" 
FROM "CLASSICMODELS"."OFFICE" 
WHERE "CLASSICMODELS"."OFFICE"."TERRITORY" = ?

The generated SQL looks as follows with SQL Server (note that jOOQ has used [], specific to SQL Server):

SELECT
  [classicmodels].[dbo].[office].[office_code], 
  [classicmodels].[dbo].[office].[city], 
  ...
  [classicmodels].[dbo].[office].[territory] 
FROM [classicmodels].[dbo].[office] 
WHERE [classicmodels].[dbo].[office].[territory] = ?

So, depending on the dialect, jOOQ has produced the expected query.

Important Note

Note that selectFrom(table("OFFICE")) has been rendered as *, while selectFrom(OFFICE) has been rendered as a list of column names. In the first case, jOOQ cannot infer the columns from the argument table; therefore, it projects *. In the second case, thanks to the Java-based schema, jOOQ projects the known columns from the table, which avoids the usage of the controversial *. Of course, * per se isn't controversial – just the fact that the columns aren't listed explicitly, as this article explains: https://tanelpoder.com/posts/reasons-why-select-star-is-bad-for-sql-performance/.

Let's try another example that queries the ORDER table. Since ORDER is a reserved word in most dialects, let's see how jOOQ will handle it. Note that our query doesn't do anything special to instruct jOOQ about this aspect:

ResultQuery<OrderRecord> query = ctx.selectFrom(ORDER)    
  .where(ORDER.REQUIRED_DATE.between(startDate, endDate));

Or, generating and executing it immediately (Order is a POJO):

public List<Order> findOrdersByRequiredDate(
      LocalDate startDate, LocalDate endDate) {
  List<Order> result = ctx.selectFrom(ORDER)
    .where(ORDER.REQUIRED_DATE.between(startDate, endDate))  
    .fetchInto(Order.class); 
  return result;
}

Let's see the valid SQL generated for MySQL:

SELECT 
  `classicmodels`.`order`.`order_id`, 
  ...
  `classicmodels`.`order`.`customer_number` 
FROM `classicmodels`.`order` 
WHERE `classicmodels`.`order`.`required_date` 
  BETWEEN ? AND ?

For brevity, we'll skip the generated SQL for PostgreSQL, Oracle, and SQL Server. Mainly, since jOOQ quotes everything by default, we can use reserved and unreserved names exactly in the same way and get back valid SQL statements.

Let's tackle one more example:

ResultQuery<Record2<String, LocalDate>> query = ctx.select(
         CUSTOMER.CUSTOMER_NAME, ORDER.ORDER_DATE)      
  .from(ORDER)
  .innerJoin(CUSTOMER).using(CUSTOMER.CUSTOMER_NUMBER)
  .orderBy(ORDER.ORDER_DATE.desc());

Or, generating and executing it immediately (CustomerAndOrder is a POJO):

public List<CustomerAndOrder> findCustomersAndOrders() {
  List<CustomerAndOrder> result 
    = ctx.select(CUSTOMER.CUSTOMER_NAME, ORDER.ORDER_DATE)
         .from(ORDER)
         .innerJoin(CUSTOMER).using(CUSTOMER.CUSTOMER_NUMBER)
         .orderBy(ORDER.ORDER_DATE.desc())
         .fetchInto(CustomerAndOrder.class); 
  return result;
}

This query uses the JOIN...USING syntax. Basically, instead of a condition via the ON clause, you supply a set of fields that have an important particularity – their names are common to both tables to the left and right of the join operator. However, some dialects (for example, Oracle) don't allow us to use qualified names in USING. Having qualified names leads to an error such as ORA-25154: column part of USING clause cannot have qualifier.

jOOQ is aware of this aspect and takes action. Following the Oracle dialect, jOOQ renders CUSTOMER.CUSTOMER_NUMBER as "CUSTOMER_NUMBER", not qualified as "CLASSICMODELS"."CUSTOMER"."CUSTOMER_NUMBER". Check this here:

SELECT 
  "CLASSICMODELS"."CUSTOMER"."CUSTOMER_NAME", 
  "CLASSICMODELS"."ORDER"."ORDER_DATE" 
FROM 
  "CLASSICMODELS"."ORDER" 
  JOIN "CLASSICMODELS"."CUSTOMER" USING ("CUSTOMER_NUMBER") 
ORDER BY
  "CLASSICMODELS"."ORDER"."ORDER_DATE" DESC

This was just an example of how jOOQ takes care of the generated SQL by emulating the correct syntax, depending on the dialect used! Thanks to jOOQ code generation, we benefit from default choices for so many silly edge cases that are so annoying to handle later on.

Let's summarize a handful of advantages brought by jOOQ code generation:

  • Type-safe SQL queries. Did I mention type-safe SQL queries?!
  • No need to worry about the identifier's case sensitivity, quotation, and qualification.
  • Using generated code makes for much leaner expressions. There's less wrapping noise such as field("X", "Y"), field(name("X", "Y")), or field(name("X", "Y"), DATA_TYPE). Via jOOQ code generation, this would just be X.Y.
  • The IDE can provide code completion and refactoring support.
  • We can use the IDE to find uses of tables and columns because they're Java objects.
  • The code will no longer compile when the columns are renamed, rather than having to run the query for it to fail.
  • Avoidance of issues caused by edge cases with vendor-specific data types.
  • Since jOOQ quotes everything by default, users don't have to think of quoting reserved names such as table(name("ORDER")). It's just ORDER, and jOOQ will produce `ORDER`, "ORDER", [ORDER], or whatever is specific to the used dialect.

    Important Note

    As a rule of thumb, always consider jOOQ code generation as the default way to exploit jOOQ. Of course, there are edge cases when code generation cannot be fully exploited (for instance, in the case of schemas that are created/modified dynamically at runtime), but this is a different story.

The application developed in this section is named WriteTypesafeSQL.

jOOQ versus JPA Criteria versus QueryDSL

All these three, jOOQ, JPA Criteria (or the Spring Data JPA Specifications API built on top of the Criteria API), and QueryDSL, can provide type-safe SQL.

If you come from a JPA background, then you know that JPA defines a Metamodel API for Criteria queries. So, the Criteria API and the Metamodel API can provide type safety for SQL as well. But, the Criteria API is quite complicated compared to QueryDSL. You don't have to take my word for it – try it! However, the Criteria API is something that you need to learn in addition to JPQL and all the JPA stuff. Also, it is not intuitive, it is poorly documented, and developers describe it as quite slow. Moreover, having 100% type safety means having to write all SQL statements that are prone to type errors via the Criteria API.

QueryDSL supports SQL type safety as well. Having support in Spring Boot, QueryDSL is well covered in this article at https://dzone.com/articles/querydsl-vs-jooq-feature, which contains a non-exhaustive list of jOOQ support beyond QueryDSL's "feature completeness." Nevertheless, that article is quite old and may be out of date. Meanwhile, jOOQ has even more advantages that you can find yourself by a quick search on reddit.com.

Next, let's go one step further and give more control to jOOQ.

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 type information. More precisely, they include two well-known validation annotations – @NotNull (javax/jakarta.validation.constraints.NotNull) and @Size (javax/jakarta.validation.constraints.Size). To enable these annotations, the configuration should be as follows:

<generate>
  <pojos>true</pojos>
  <validationAnnotations>true</validationAnnotations>
</generate>

Also, you should add the dependency for validation-api as in the bundled code.

By default, the names of the generated POJOs are the same as the names of the tables in Pascal case (for instance, the table named office_has_manager becomes OfficeHasManager). Altering the default behavior can be achieved via so-called generator strategies – basically, in Maven, a piece of XML delimited by the <strategy> tag that relies on regular expressions for producing custom (user-defined) output. For example, if the POJOs are prefixed with the Jooq text, then the generator strategy will be the following:

<strategy>
  <matchers>
    <tables>
      <table>
        <pojoClass>
          <expression>JOOQ_$0</expression>
          <transform>PASCAL</transform>
        </pojoClass>
      ...
</strategy>

This time, the table named office_has_manager results in a POJO source named JooqOfficeHasManager. More details about the generator strategies (including the programmatic approach) are available in Chapter 18, jOOQ SPI (Providers and Listeners). Also, it is recommended to read https://www.jooq.org/doc/latest/manual/code-generation/codegen-matcherstrategy/.

The Gradle alternative is available in the bundled code.

By default, jOOQ generates a POJO for each table in the database. Therefore, by default, jOOQ can generate a POJO as Office and Order (or JooqOffice and JooqOrder, conforming to the preceding strategy), but its purpose is not to generate more complex POJOs, such as composite POJOs or ones containing arbitrary objects (such as CustomerAndOrder). The following is the source code of JooqOffice, generated by jOOQ:

public class JooqOffice implements Serializable {
  private static final long serialVersionUID = 1821407394;
  private String officeCode;
  private String city;
  ...
  private String territory;
  public JooqOffice() {}
  public JooqOffice(JooqOffice value) {
    this.officeCode = value.officeCode;
    this.city = value.city;
    ...
    this.territory = value.territory;
  }
  public JooqOffice(String officeCode, 
         String city, ... String territory) {
    this.officeCode = officeCode;
    this.city = city;
    ...
    this.territory = territory;
  }
  @NotNull
  @Size(max = 10)
  public String getOfficeCode() {
    return this.officeCode;
  }
  public void setOfficeCode(String officeCode) {
    this.officeCode = officeCode;
  }
  // getters and setters and toString() omitted for brevity
}

Similar POJOs are generated for each table of the classicmodels database. This means that we can still use our CustomerAndOrder POJO, but there is no need to write our own POJOs for Office and Order because we can use those generated by jOOQ. The following code was cut out from ClassicModelsRepository and uses the generated JooqOffice and JooqOrder (note the imports – jOOQ placed the POJOs in the jooq.generated.tables.pojos package):

import jooq.generated.tables.pojos.JooqOffice;
import jooq.generated.tables.pojos.JooqOrder;
...
public List<JooqOffice> findOfficesInTerritory(
                               String territory) {
  List<JooqOffice> result = ctx.selectFrom(OFFICE)
    .where(OFFICE.TERRITORY.eq(territory))
    .fetchInto(JooqOffice.class); 
  return result;
}
public List<JooqOrder> findOrdersByRequiredDate(
           LocalDatestartDate, LocalDateendDate) {
  List<JooqOrder> result = ctx.selectFrom(ORDER)
    .where(ORDER.REQUIRED_DATE.between(startDate, endDate))  
      .fetchInto(JooqOrder.class); 
      return result;
}

Done! So, jOOQ-generated POJOs can be used as any regular POJOs. For instance, they can be returned from a REST controller, and Spring Boot will serialize them as JSON. We'll detail more types of supported POJOs later on when we tackle the mapping result set to POJOs.

The application developed in this section is available as GeneratePojos. Next, let's see how jOOQ can generate DAOs.

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 as the built-in SimpleJpaRepository. To achieve this, we use the <springAnnotations/> flag, as follows:

<generate>
  <daos>true</daos>
  <springAnnotations>true</springAnnotations>
</generate>

By default, the names of the generated DAOs are the same as the names of the tables in Pascal case and suffixed with the word Dao (for instance, the table named office_has_manager becomes OfficeHasManagerDao). Altering the default behavior can be achieved via so-called generator strategies. For instance, following the Spring style, we prefer OfficeHasManagerRepository instead of OfficeHasManagerDao. This can be achieved as follows:

<strategy>
  <matchers>
    <tables>
      <table>
        <daoClass>
          <expression>$0_Repository</expression>
          <transform>PASCAL</transform>
        </daoClass>
      ...
</strategy>

The Gradle alternative is available in the bundled code. For instance, the generated OfficeRepository looks as follows:

@Repository
public class OfficeRepository
       extends DAOImpl<OfficeRecord, JooqOffice, String> {
  public OfficeRepository() {
    super(Office.OFFICE, JooqOffice.class);
  }
  @Autowired
  public OfficeRepository(Configuration configuration) {
    super(Office.OFFICE, JooqOffice.class, configuration);
  }
  @Override
  public String getId(JooqOffice object) {
    return object.getOfficeCode();
  }
  public List<JooqOffice> fetchRangeOfOfficeCode(
       String lowerInclusive, String upperInclusive) {
    return fetchRange(Office.OFFICE.OFFICE_CODE, 
         lowerInclusive, upperInclusive);
  }
  // more DAO-methods omitted for brevity
}

Each generated DAO extends the common base implementation named DAOImpl. This implementation supplies common methods such as insert(), update(), delete(), and findById().

So far, our ClassicModelsRepository contains three query methods, represented by findOfficesInTerritory(), findOrdersByRequiredDate(), and findCustomersAndOrders().

However, let's check the query from findOfficesInTerritory():

List<JooqOffice> result = ctx.selectFrom(OFFICE) 
  .where(OFFICE.TERRITORY.eq(territory))
  .fetchInto(JooqOffice.class);

Here, we notice that the generated OfficeRepository already covers this query via the fetchByTerritory(String territory) method; therefore, we can use this built-in DAO method directly in our service, ClassicModelsService, as follows:

@Transactional(readOnly = true)
public List<JooqOffice> fetchOfficesInTerritory(
                               String territory) {
  return officeRepository.fetchByTerritory(territory);
}

Going further, check out the query from findOrdersByRequiredDate():

List<JooqOrder> result = ctx.selectFrom(ORDER)
  .where(ORDER.REQUIRED_DATE.between(startDate, endDate))  
  .fetchInto(JooqOrder.class);

This time, the previous query is covered in OrderRepository by the built-in DAO method, fetchRangeOfRequiredDate(LocalDate li, LocalDate ui). So, we can drop the previous query and rely on ClassicModelsService on the built-in one, as follows:

@Transactional(readOnly = true)
public List<JooqOrder> fetchOrdersByRequiredDate(
      LocalDate startDate, LocalDate endDate) {
  return orderRepository.fetchRangeOfRequiredDate(
      startDate, endDate);
}

At this point, the only query method left in ClassicModelsRepository is findCustomersAndOrders(). This query method doesn't have an alternative in the default generated DAOs; therefore, we still need it.

For now, you can check the application named GenerateDaos. Later on, we'll discuss extending and customizing the jOOQ-generated DAO.

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 = new Configuration()
  .withJdbc(new Jdbc()
    .withDriver("com.mysql.cj.jdbc.Driver")
    .withUrl("jdbc:mysql://localhost:3306/classicmodels")
    .withUser("root")
    .withPassword("root"))
  .withGenerator(new Generator()
    .withName("org.jooq.codegen.JavaGenerator")
    .withDatabase(new Database()
      .withName("org.jooq.meta.mysql.MySQLDatabase")       
      .withInputSchema("classicmodels")
      .withIncludes(".*")
      .withExcludes("flyway_schema_history | sequences"
        + " | customer_pgs | refresh_top3_product"
        + " | sale_.* | set_.* | get_.* | .*_master")      
      .withSchemaVersionProvider("SELECT MAX(`version`) 
          FROM `flyway_schema_history`")
      .withLogSlowQueriesAfterSeconds(20))
  .withGenerate(new Generate()
    .withDaos(true)
    .withValidationAnnotations(Boolean.TRUE)
    .withSpringAnnotations(Boolean.TRUE))
  .withStrategy(new Strategy()
    .withMatchers(new Matchers()
      .withTables(new MatchersTableType()
        .withPojoClass(new MatcherRule()
         .withExpression("Jooq_$0")
         .withTransform(MatcherTransformType.PASCAL))
        .withDaoClass(new MatcherRule()
          .withExpression("$0_Repository")
          .withTransform(MatcherTransformType.PASCAL)))))
  .withTarget(new Target()
    .withPackageName("jooq.generated")
    .withDirectory(System.getProperty("user.dir")
    .endsWith("webapp") ? "target/generated-sources"
              : "webapp/target/generated-sources")));
GenerationTool.generate(configuration);

The jOOQ Code Generator must generate the classes before the application's classes are compiled; therefore, the programmatic Code Generator should be placed in a separate module of your application and invoked at the proper moment before the compilation phase. As you'll see in the bundled code (ProgrammaticGenerator), this can be achieved via exec-maven-plugin for Maven or JavaExec for Gradle.

If you prefer the DDL Database API, then you'll love the programmatic approach from ProgrammaticDDLDatabase. If you prefer the JPA Database API, then check out the programmatic approach as well, ProgrammaticJPADatabase.

All the applications from this chapter are available for Java/Kotlin and Maven/Gradle combos.

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].[dbo].[customer].[customer_name] .
  • With these settings, jOOQ doesn't render the schema and catalog names – [customer].[customer_name] .

As you can see in the corresponding XSD (https://www.jooq.org/xsd/jooq-runtime-3.x.x.xsd), jOOQ supports a lot of settings, and most of them are for advanced users and serve only certain scenarios. Nevertheless, some of them are more popular than others, and you'll see them mentioned in the proper context throughout this book.

Moreover, jOOQ Settings can be programmatically shaped via @Bean, as follows:

@Bean
public Settings jooqSettings() {
  return new Settings()
   .withRenderCatalog(Boolean.FALSE)
   .withRenderSchema(Boolean.FALSE);
}

Via @Bean, we customize jOOQ settings globally (at the application level), but we can override them locally at the DSLContext level via the DSLContext constructor (DSL.using()), as shown in this example:

DataSource ds = ...;
DSLContext ctx = DSL.using(ds, SQLDialect.MYSQL, 
  new Settings()
    .withRenderCatalog(Boolean.FALSE)
    .withRenderSchema(Boolean.FALSE));

Alternatively, we can locally define DSLContext, derived from the current DSLContext (denoted as ctx) and having altered Settings:

ctx.configuration().derive(
  new Settings()
    .withRenderCatalog(Boolean.FALSE)
    .withRenderSchema(Boolean.FALSE))).dsl() 
    ... // some query

During this book, you'll have plenty of occasions to see Settings at work, so there is no need to bother too much for the moment.

It's time to summarize this chapter!

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset