This chapter is a practical guide to start working with jOOQ (open source and free trial commercial) in Spring Boot applications. For convenience, let's assume that we have a Spring Boot stub application and plan to implement the persistence layer via jOOQ.

The goal of this chapter is to highlight the fact that setting the environment for generating and executing SQL queries via jOOQ in a Spring Boot application is a job that can be accomplished almost instantly in any of the Java/Kotlin and Maven/Gradle combinations. Besides that, this is a good opportunity to have your first taste of the jOOQ DSL-fluent API and to get your first impressions.

The topics of this chapter include the following:

• Starting jOOQ and Spring Boot instantly
• Using the jOOQ query DSL API to generate a valid SQL statement
• Executing the generated SQL and mapping the result set to a POJO

Let's get started!

The code for this chapter can be found on GitHub at https://github.com/PacktPublishing/jOOQ-Masterclass/tree/master/Chapter01.

Spring Boot provides support for jOOQ, and this aspect is introduced in the Spring Boot official documentation under the Using jOOQ section. Having built-in support for jOOQ makes our mission easier, since, among other things, Spring Boot is capable of dealing with aspects that involve useful default configurations and settings.

Consider having a Spring Boot stub application that will run against MySQL and Oracle, and let's try to add jOOQ to this context. The goal is to use jOOQ as a SQL builder for constructing valid SQL statements and as a SQL executor that maps the result set to a POJO.

Adding the jOOQ open source edition

Adding the jOOQ open source edition into a Spring Boot application is quite straightforward.

Adding the jOOQ open source edition via Maven

From the Maven perspective, adding the jOOQ open source edition into a Spring Boot application starts from the pom.xml file. The jOOQ open source edition dependency is available at Maven Central (https://mvnrepository.com/artifact/org.jooq/jooq) and can be added like this:

<dependency>
  <groupId>org.jooq</groupId>
  <artifactId>jooq</artifactId>
  <version>...</version> <!-- optional -->
</dependency>

Alternatively, if you prefer a Spring Boot starter, then rely on this one:

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

If you are a fan of Spring Initializr (https://start.spring.io/), then just select the jOOQ dependency from the corresponding list of dependencies.

That's all! Note that <version> is optional. If <version> is omitted, then Spring Boot will properly choose the jOOQ version compatible with the Spring Boot version used by the application. Nevertheless, whenever you want to try a different jOOQ version, you can simply add <version> explicitly. At this point, the jOOQ open source edition is ready to be used to start developing the persistence layer of an application.

Adding the jOOQ open source edition via Gradle

From the Gradle perspective, adding the jOOQ open source edition into a Spring Boot application can be accomplished via a plugin named gradle-jooq-plugin (https://github.com/etiennestuder/gradle-jooq-plugin/). This can be added to your build.gradle, as follows:

plugins {
  id 'nu.studer.jooq' version ...
}

Of course, if you rely on Spring Initializr (https://start.spring.io/), then just select a Gradle project, add the jOOQ dependency from the corresponding list of dependencies, and once the project is generated, add the gradle-jooq-plugin plugin. As you'll see in the next chapter, using gradle-jooq-plugin is quite convenient for configuring the jOOQ Code Generator.

Adding a jOOQ free trial (commercial edition)

Adding a free trial commercial edition of jOOQ (jOOQ Express, Professional, and Enterprise editions) to a Spring Boot project (overall, in any other type of project) requires a few preliminary steps. Mainly, these steps are needed because the jOOQ free trial commercial distributions are not available on Maven Central, so you have to manually download the one that you need from the jOOQ download page (https://www.jooq.org/download/). For instance, you can choose the most popular one, the jOOQ Professional distribution, which comes packaged as a ZIP archive. Once you have unzipped it, you can install it locally via the maven-install command. You can find these steps exemplified in a short movie in the bundled code (Install_jOOQ_Trial.mp4).

For Maven applications, we use the jOOQ free trial identified as org.jooq.trial (for Java 17) or org.jooq.trial-java-{version}. When this book was written, the version placeholder could be 8 or 11, but don't hesitate to check for the latest updates. We prefer the former, so in pom.xml, we have the following:

<dependency>
  <groupId>org.jooq.trial-java-8</groupId>
  <artifactId>jooq</artifactId>
  <version>...</version>
</dependency>

For Java/Gradle, you can do it, as shown in the following example, via gradle-jooq-plugin:

jooq {
  version = '...'
  edition = nu.studer.gradle.jooq.JooqEdition.TRIAL_JAVA_8
}

For Kotlin/Gradle, you can do it like this:

jooq {
  version.set(...)
  edition.set(nu.studer.gradle.jooq.JooqEdition.TRIAL_JAVA_8)
}

In this book, we will use jOOQ open source in applications that involve MySQL and PostgreSQL, and jOOQ free trial in applications that involve SQL Server and Oracle. These two database vendors are not supported in jOOQ open source.

If you're interested in adding jOOQ in a Quarkus project then consider this resource: https://github.com/quarkiverse/quarkus-jooq

Injecting DSLContext into Spring Boot repositories

One of the most important interfaces of jOOQ is org.jooq.DSLContext. This interface represents the starting point of using jOOQ, and its main goal is to configure the behavior of jOOQ when executing queries. The default implementation of this interface is named DefaultDSLContext. Among the approaches, DSLContext can be created via an org.jooq.Configuration object, directly from a JDBC connection (java.sql.Connection), a data source (javax.sql.DataSource), and a dialect needed for translating the Java API query representation, written via jOOQ into a database-specific SQL query (org.jooq.SQLDialect).

Important Note

For java.sql.Connection, jOOQ will give you full control of the connection life cycle (for example, you are responsible for closing this connection). On the other hand, connections acquired via javax.sql.DataSource will be automatically closed after query execution by jOOQ. Spring Boot loves data sources, therefore the connection management is already handled (acquire and return connection from/to the connection pool, transaction begin/commit/rollback, and so on).

All jOOQ objects, including DSLContext, are created from org.jooq.impl.DSL. For creating a DSLContext, the DSL class exposes a static method named using(), which comes in several flavors. Of these, the most notable are listed next:

// Create DSLContext from a pre-existing configuration
DSLContext ctx = DSL.using(configuration);
// Create DSLContext from ad-hoc arguments
DSLContext ctx = DSL.using(connection, dialect);

For example, connecting to the MySQL classicmodels database can be done as follows:

try (Connection conn = DriverManager.getConnection(
    "jdbc:mysql://localhost:3306/classicmodels", 
    "root", "root")) {
  DSLContext ctx = 
    DSL.using(conn, SQLDialect.MYSQL);
  ...
} catch (Exception e) {
  ...
}

Alternatively, you can connect via a data source:

DSLContext ctx = DSL.using(dataSource, dialect);

For example, connecting to the MySQL classicmodels database via a data source can be done as follows:

DSLContext getContext() {
  MysqlDataSource dataSource = new MysqlDataSource();
  dataSource.setServerName("localhost");
  dataSource.setDatabaseName("classicmodels");
  dataSource.setPortNumber("3306");
  dataSource.setUser(props.getProperty("root");
  dataSource.setPassword(props.getProperty("root");
  return DSL.using(dataSource, SQLDialect.MYSQL);
}

But Spring Boot is capable of automatically preparing a ready-to-inject DSLContext based on our database settings. For example, Spring Boot can prepare DSLContext based on the MySQL database settings specified in application.properties:

spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/
                 classicmodels?createDatabaseIfNotExist=true
spring.datasource.username=root
spring.datasource.password=root
spring.jooq.sql-dialect=MYSQL

Once Spring Boot detects the jOOQ presence, it uses the preceding settings to create org.jooq.Configuration, which is used to prepare a ready-to-inject DSLContext.

Important Note

While DSLContext has a high degree of configurability and flexibility, Spring Boot performs only the minimum effort to serve a default DSLContext that can be injected and used immediately. As you'll see in this book (but especially in the official jOOQ manual – https://www.jooq.org/doc/latest/manual/), DSLContext has tons of configurations and settings that allow taking control of almost anything that happens with our SQL statements.

The DSLContext object provided by Spring Boot can be easily injected into our persistence repositories. For instance, the next snippet of code serves such a DSLContext object directly into ClassicModelsRepository:

@Repository
public class ClassicModelsRepository {
  private final DSLContext ctx;
  public ClassicModelsRepository(DSLContext ctx) {
    this.ctx = ctx;
  }
  ...
}

Don't conclude here that the application needs to keep a reference to DSLContext. That can still be used directly in a local variable, as you saw earlier (which means that you can have as many DSLContext objects as you want). It only means that, in a Spring Boot application, for most common scenarios, it is more convenient to simply inject it as shown previously.

Internally, jOOQ can use java.sql.Statement or PreparedStatement. By default, and for very good and strong reasons, jOOQ uses PreparedStatement.

Typically, the DSLContext object is labeled as ctx (used in this book) or dsl. But, other names such as dslContext, jooq, and sql are also good choices. Basically, you name it.

Okay, so far, so good! At this point, we have access to DSLContext provided out of the box by Spring Boot, based on our settings from application.properties. Next, let's see DSLContext at work via jOOQ's query DSL API.

Using the jOOQ query DSL API to generate valid SQL is a good start for exploring the jOOQ world. Let's take a simple SQL statement, and let's express it via jOOQ. In other words, let's use the jOOQ query DSL API to express a given SQL string query into the jOOQ object-oriented style. Consider the next SQL SELECT written in the MySQL dialect:

SELECT * FROM `office` WHERE `territory` = ?

The SQL, SELECT * FROM `office` WHERE `territory` = ?, is written as a plain string. This query can be generated by jOOQ if it is written via the DSL API, as follows (the value of the territory binding variable is supplied by the user):

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

Alternatively, if we want to have the FROM clause closer to SQL look, then we can write it as follows:

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

Most schemas are case-insensitive, but there are databases such as MySQL and PostgreSQL that prefer mostly lowercase, while others such as Oracle prefer mostly uppercase. So, writing the preceding query in Oracle style can be done as follows:

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

Alternatively, you can write it via an explicit call of from():

ResultQuery<?> query = ctx.select()
  .from(table("OFFICE"))                  
  .where(field("TERRITORY").eq(territory));

The jOOQ fluent API is a piece of art that looks like fluent English and, therefore, is quite intuitive to read and write.

Reading the preceding queries is pure English: select all offices from the OFFICE table where the TERRITORY column is equal to the given value.

Pretty soon, you'll be amazed at how fast you can write these queries in jOOQ.

Important Note

As you'll see in the next chapter, jOOQ can generate a Java-based schema that mirrors the one in the database via a feature named the jOOQ Code Generator. Once this feature is enabled, writing these queries becomes even simpler and cleaner because there will be no need to reference the database schema explicitly, such as the table name or the table columns. Instead, we will reference the Java-based schema.

And, thanks to the Code Generator feature, jOOQ makes the right choices for us upfront almost everywhere. We no longer need to take care of queries' type-safety and case-sensitivity, or identifiers' quotation and qualification.

The jOOQ Code Generator atomically boosts the jOOQ capabilities and increases developer productivity. This is why using the jOOQ Code Generator is the recommended way to exploit jOOQ. We will tackle the jOOQ Code Generator in the next chapter.

Next, the jOOQ query (org.jooq.ResultQuery) must be executed against the database, and the result set will be mapped to a user-defined simple POJO.

Executing the generated SQL and mapping the result set to a POJO via jOOQ can be done via the fetching methods available in the jOOQ API. For instance, the next snippet of code relies on the fetchInto() flavor:

public List<Office> findOfficesInTerritory(String territory) {
  List<Office> result = ctx.selectFrom(table("office"))
    .where(field("territory").eq(territory))
    .fetchInto(Office.class); 
  return result;
}

What happened there?! Where did ResultQuery go? Is this black magic? Obviously not! It's just that jOOQ has immediately fetched results after constructing the query and mapped them to the Office POJO. Yes, the jOOQ's fetchInto(Office.class) or fetch().into(Office.class) would work just fine out of the box. Mainly, jOOQ executes the query and maps the result set to the Office POJO by wrapping and abstracting the JDBC complexity in a more object-oriented way. If we don't want to immediately fetch the results after constructing the query, then we can use the ResultQuery object like this:

// 'query' is the ResultQuery object
List<Office> result = query.fetchInto(Office.class);

The Office POJO is available in the code bundled with this book.

Important Note

jOOQ has a comprehensive API for fetching and mapping a result set into collections, arrays, maps, and so on. We will detail these aspects later on in Chapter 8, Fetching and Mapping.

The complete application is named DSLBuildExecuteSQL. Since this can be used as a stub application, you can find it available for Java/Kotlin in combination with Maven/Gradle. These applications (along with, in fact, all the applications in this book) use Flyway for schema migration. As you'll see later, Flyway and jOOQ make a great team.

So, let's quickly summarize this chapter before moving on to exploit the astonishing jOOQ Code Generator feature.

Note that we barely scratched the surface of jOOQ's capabilities by using it only for generating and executing a simple SQL statement. Nevertheless, we've already highlighted that jOOQ can generate valid SQL against different dialects and can execute and map a result set in a straightforward manner.

In the next chapter, we learn how to trust jOOQ more by increasing its level of involvement. jOOQ will generate type-safe queries, POJOs, and DAOs on our behalf.