Starting jOOQ and Spring Boot instantly

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.