What is jOOQ ?

jOOQ is a Java library that lets you to generate Java code from the database and lets you write type-safe SQL queries. jOOQ transforms tables in a database into Java classes, which we can use to retrieve or update data.

Using jOOQ Library

Inititalize Database Tables

Execute the SQL statements in src/resources/initial_table.sql file on a Postgres server to create users, categories, transacations tables.

initial_table.sql content
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS categories CASCADE;
DROP TABLE IF EXISTS transactions CASCADE;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT NOT NULL PRIMARY KEY,
  username TEXT,
  email TEXT,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS categories (
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY(START WITH 200000000) ,
  user_id BIGINT REFERENCES users(id),
  name TEXT,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  type TEXT,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS transactions (
  id BIGINT NOT NULL PRIMARY KEY,
  user_id BIGINT REFERENCES users(id),
  category_id BIGINT REFERENCES categories(id),
  amount BIGINT,
  transaction_date DATE NOT NULL DEFAULT CURRENT_DATE,
  note TEXT,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Add jOOQ Dependency

To use jOOQ in Spring Boot, we declare a dependency to spring-boot-starter-jooq:

1
2
3
4

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

Generate Code

To generate jOOQ classes, we need to add jooq-codegen-maven plugin in the pom.xml. We use jOOQ version 3.18.6 in this project.

pom.xml content
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

<plugin>
  <groupId>org.jooq</groupId>
  <artifactId>jooq-codegen-maven</artifactId>
  <version>3.18.6</version>
  <executions>
    <execution>
      <id>generate-postgre</id>
      <phase>generate-sources</phase>
      <goals>
        <goal>generate</goal>
      </goals>
      <configuration>
        <jdbc>
          <driver>org.postgresql.Driver</driver>
          <url>${pg.url}</url>
          <user>${pg.username}</user>
          <password>${pg.password}</password>
        </jdbc>
        <generator>
          <database>
            <name>
              org.jooq.meta.postgres.PostgresDatabase
            </name>
            <includes>.*</includes>
            <excludes></excludes>
            <inputSchema>public</inputSchema>
          </database>
          <generate>
            <pojos>true</pojos>
            <pojosEqualsAndHashCode>
                true
            </pojosEqualsAndHashCode>
            <javaTimeTypes>true</javaTimeTypes>
            <fluentSetters>true</fluentSetters>
          </generate>
          <target>
            <packageName>
              com.tabunganku.jooq.model
            </packageName>
            <directory>
              target/generated-sources/jooq
            </directory>
          </target>
        </generator>
      </configuration>
    </execution>
  </executions>
</plugin>

I will explain what the plugin does.

1
2
3
4
5

<id>generate-postgre</id>
<phase>generate-sources</phase>
<goals>
  <goal>generate</goal>
</goals>

We create a new Maven process named generate-postgre, which will be executed during generate-sources phase.

1
2
3
4
5
6

<jdbc>
  <driver>org.postgresql.Driver</driver>
  <url>${pg.url}</url>
  <user>${pg.username}</user>
  <password>${pg.password}</password>
</jdbc>

The plugin connects to a Postgres database using this specified configuration.

1
2
3
4
5
6
7
8

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

We use Postgres dialect to generate classes from schema.

1
2
3
4
5
6
7
8

<generate>
  <pojos>true</pojos>
  <pojosEqualsAndHashCode>
      true
  </pojosEqualsAndHashCode>
  <javaTimeTypes>true</javaTimeTypes>
  <fluentSetters>true</fluentSetters>
</generate>

It configures the classes generated by jOOQ:

  • pojos: jOOQ will generate POJO classes.
  • pojosEqualsAndHashCode: generated POJOs classes will have equals and hashCode functions.
  • javaTimeTypes: supports JSR-310 java.time types.
  • fluentSetters: setter functions in generated classes will not return void.
1
2
3
4
5
6
7
8

<target>
  <packageName>
    com.tabunganku.jooq.model
  </packageName>
  <directory>
    target/generated-sources/jooq
  </directory>
</target>

The generated classes will be located in com.tabunganku.jooq.model package and can be found in the target/generated-sources/jooq directory.

To manually generate the code, run command mvn generate-sources (for Tabunganku, run mvn generate-sources -Dpg.url=<pg_url> -Dpg.username=<pg_username> -Dpg.password=<pg_password>) in Terminal.

generate-sources output in Terminal

Implementation

To see jOOQ in action, let’s look at CategoriesRepository class implementation.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

@Repository
public class CategoriesRepository {
  private final DSLContext context;

  public CategoriesRepository(DSLContext context) {
    this.context = context;
  }

  public CategoriesRecord getById(long categoryId, long userId) {
    return context.selectFrom(Tables.CATEGORIES).where(
        Tables.CATEGORIES.ID.eq(categoryId)
            .and(Tables.CATEGORIES.USER_ID.eq(userId))
    ).fetchOne();
  }
  ...
}

Tables is a class generated by jOOQ, which is used to access the Categories table. To get data from database, we use jOOQ’s DSLContext class to execute commands. The function getById will retrieve data from Categories table where both category ID and user ID match the specified value.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

  public Long save(
      long userId,
      String name,
      TransactionType type) {
    return context.insertInto(
        Tables.CATEGORIES,
            Categories.CATEGORIES.USER_ID,
            Categories.CATEGORIES.NAME,
            Categories.CATEGORIES.TYPE
        )
        .values(userId, name, type.toString())
        .returningResult(Categories.CATEGORIES.ID)
        .fetchOne()
        .value1();
  }

The save function will insert a new data into Categories table and return the category ID of the newly added category.

Resources

  • Maven Lifecycle: link
  • jOOQ: link
  • jOOQ Code Generation: link
  • jOOQ Advanced Code Generation: link
  • Tabunganku BE Repository: link