Getting Started with jOOQ

1. Introduction

In this tutorial, we’re going to take a quick tour of running an application with jOOQ (Java Object Orientated Query). This library generates Java classes based on the database tables and lets us create type-safe SQL queries through its fluent API.

We’ll cover the whole setup, PostgreSQL database connection, and a few examples of CRUD operations.

2. Maven Dependencies

For the jOOQ library, we’ll need the following three jOOQ dependencies:

<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq</artifactId>
    <version>3.19.0</version>
</dependency>
<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-meta</artifactId>
    <version>3.19.0</version>
</dependency>
<dependency>
    <groupId>org.jooq</groupId>
    <artifactId>jooq-codegen</artifactId>
    <version>3.19.0</version>
</dependency>

We’ll also need one dependency for the PostgreSQL driver:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.5.4</version>
    <scope>runtime</scope>
</dependency>

3. Database Structure

Before we start, let’s create a simple DB schema for our examples. We’ll use a simple Author and an Article relationship:

create table AUTHOR
(
    ID         integer PRIMARY KEY,
    FIRST_NAME varchar(255),
    LAST_NAME  varchar(255),
    AGE        integer
);

create table ARTICLE
(
    ID          integer PRIMARY KEY,
    TITLE       varchar(255) not null,
    DESCRIPTION varchar(255),
    AUTHOR_ID   integer
        CONSTRAINT fk_author_id REFERENCES AUTHOR
);

4. Database Connection

Now, let’s take a look at how we’ll be connecting to our database.

Firstly, we need to provide the user, password, and a full URL to the database. We’ll use these properties to create a Connection object by using the DriverManager and its getConnection method:

String userName = "user";
String password = "pass";
String url = "jdbc:postgresql://db_host:5432/baeldung";
Connection conn = DriverManager.getConnection(url, userName, password);

Next, we need to create an instance of DSLContext. This object will be our entry point for jOOQ interfaces:

DSLContext context = DSL.using(conn, SQLDialect.POSTGRES);

In our case, we’re passing the POSTGRES dialect, but there are a few other ones available like H2, MySQL, SQLite, and more.

5. Code Generation

To generate Java classes for our database tables, we’ll need the following jooq-config.xml file:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<configuration xmlns="http://www.jooq.org/xsd/jooq-codegen-3.13.0.xsd">
    
    <jdbc>
        <driver>org.postgresql.Driver</driver>
        <url>jdbc:postgresql://db_url:5432/baeldung_database</url>
        <user>username</user>
        <password>password</password>
    </jdbc>

    <generator>
        <name>org.jooq.codegen.JavaGenerator</name>

        <database>
            <name>org.jooq.meta.postgres.PostgresDatabase</name>
            <inputSchema>public</inputSchema>
            <includes>.*</includes>
            <excludes></excludes>
        </database>

        <target>
            <packageName>com.baeldung.jooq.model</packageName>
            <directory>C:/projects/baeldung/tutorials/jooq-examples/src/main/java</directory>
        </target>
    </generator>
</configuration>

The custom configuration requires changes in the <jdbc> section where we place the database credentials and in the <target> section in which we configure the package name and location directory for classes that we’ll generate.

To execute the jOOQ code generation tool, we need to run the following code:

GenerationTool.generate(
  Files.readString(
    Path.of("jooq-config.xml")
  )    
);

After the generation is complete, we’ll get the two following classes, each corresponding to its database table:

com.baeldung.model.generated.tables.Article;
com.baeldung.model.generated.tables.Author;

6. CRUD Operations

Now, let’s take a look at a few basic CRUD operations that we can perform with the jOOQ library.

6.1. Creating

Firstly, let’s create a new Article record. To do so, we need to invoke the newRecord method with a proper table reference as a parameter:

ArticleRecord article = context.newRecord(Article.ARTICLE);

The Article.ARTICLE variable is a reference instance to the ARTICLE database table. It was automatically created by jOOQ during code generation.

Next, we can set values for all needed properties:

article.setId(2);
article.setTitle("jOOQ examples");
article.setDescription("A few examples of jOOQ CRUD operations");
article.setAuthorId(1);

Finally, we need to invoke the store method on the record to save it in the database:

article.store();

6.2. Reading

Now, let’s see how we can read values from the database. As an example, let’s select all the authors:

Result<Record> authors = context.select()
  .from(Author.AUTHOR)
  .fetch();

Here, we’re using the select method combined with from clause to indicate from which table we want to read. Invoking the fetch method executes the SQL query and returns the generated result.

The Result object implements the Iterable interface, so it’s easy to iterate over each element. And while having access to a single record, we can get its parameters by using the getValue method with a proper field reference:

authors.forEach(author -> {
    Integer id = author.getValue(Author.AUTHOR.ID);
    String firstName = author.getValue(Author.AUTHOR.FIRST_NAME);
    String lastName = author.getValue(Author.AUTHOR.LAST_NAME);
    Integer age = author.getValue(Author.AUTHOR.AGE);

    System.out.printf("Author %s %s has id: %d and age: %d%n", firstName, lastName, id, age);
});

We can limit the select query to a set of specific fields. Let’s fetch only the article ids and titles:

Result<Record2<Integer, String>> articles = context.select(Article.ARTICLE.ID, Article.ARTICLE.TITLE)
  .from(Author.AUTHOR)
  .fetch();

We can also select a single object with the fetchOne method. The parameters for this one are the table reference and a condition to match the proper record.

In our case, let’s just select an Author with an id equal to 1:

AuthorRecord author = context.fetchOne(Author.AUTHOR, Author.AUTHOR.ID.eq(1))

If no record matches the condition, the fetchOne method will return null.

6.3. Updating

To update a given record, we can use the update method from the DSLContext object combined with a set method invocations for every field we need to change. This statements should be followed by a where clause with a proper match condition:

context.update(Author.AUTHOR)
  .set(Author.AUTHOR.FIRST_NAME, "David")
  .set(Author.AUTHOR.LAST_NAME, "Brown")
  .where(Author.AUTHOR.ID.eq(1))
  .execute();

The update query will run only after we call the execute method. As a return value, we’ll get an integer equal to the number of records that were updated.

It’s also possible to update an already fetched record by executing its store method:

ArticleRecord article = context.fetchOne(Article.ARTICLE, Article.ARTICLE.ID.eq(1));
article.setTitle("A New Article Title");
article.store();

The store method will return 1 if the operation was successful or 0 if the update was not necessary. For example, nothing was matched by the condition.

6.4. Deleting

To delete a given record, we can use the delete method from the DSLContext object. The delete condition should be passed as a parameter in the following where clause:

context.delete(Article.ARTICLE)
  .where(Article.ARTICLE.ID.eq(1))
  .execute();

The delete query will run only after we call the execute method. As a return value, we’ll get an integer equal to the number of deleted records.

It’s also possible to delete an already fetched record by executing its delete method:

ArticleRecord articleRecord = context.fetchOne(Article.ARTICLE, Article.ARTICLE.ID.eq(1));
articleRecord.delete();

The delete method will return 1 if the operation was successful or 0 if deletion was not necessary. For example, when nothing was matched by the condition.

7. Conclusion

In this article, we’ve learned how to configure and create a simple CRUD application using the jOOQ framework.