How to Use jOOQ Library to Write SQL in Java using Fluent Style?

The jOOQ (https://www.jooq.org/) is a rich Java Library that allows us to write SQL using fluent style. It generates type-safe SQL and avoids SQL injections rather than building your SQL by yourself e.g. string concatenation. It also provides other important features such as generation of the classes fromt the database metadata.

In order to use jOOQ, you don’t have to provide an existing database connection. Rather, you can specify the SQL Dialect such as MySQL, ORACLE etc.

The following is a simple example that will generate a type-safe SQL string:

select name
from table
where (a = 1)
limit 10
offset 10

The jooq is fluent style.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

package com.helloacm
 
import org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
 
import static org.jooq.impl.DSL.field;
import static org.jooq.impl.DSL.table;
 
public class Main {
    public static void main(String[] args) {
        DSLContext dsl = DSL.using(SQLDialect.MYSQL);
        String sql = dsl.
                select(field("name")).
                from(table("name")).
                where("a = 1").
                offset(10).
                limit(10).toString();
        System.out.println(sql);
    }
}

package com.helloacm

import org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;

import static org.jooq.impl.DSL.field;
import static org.jooq.impl.DSL.table;

public class Main {
    public static void main(String[] args) {
        DSLContext dsl = DSL.using(SQLDialect.MYSQL);
        String sql = dsl.
                select(field("name")).
                from(table("name")).
                where("a = 1").
                offset(10).
                limit(10).toString();
        System.out.println(sql);
    }
}

If the Method getSQL() is invoked instead of toString(), the generated SQL will substitute the parameters with question marks e.g. ? which is prepared.

select * from name where (a = 1) limit ? offset ?

If you choose a different SQL dialect, the generated SQL will be sightly different. We can select more fields at a time:

1

select(field("a"), field("b"))

select(field("a"), field("b"))

Selecting all fields aka asterisk (*)

1

select(asterisk())

select(asterisk())

The where() can be used to add contions. We can add a single condition (where cause) or several ones. Instead of giving a SQL where cause string, it is better to construct the statement using the static functions, which is fluent, straightforward and avoids SQL injections.

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

package com.helloacm;
 
import org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
import static org.jooq.impl.DSL.*;
 
public class Main {
    public static void main(String[] args) {
        DSLContext dsl = DSL.using(SQLDialect.POSTGRES);
        String sql = dsl.
                select(asterisk()).
                from(table("name")).
                where(field("a").eq("data1"), field("b").eq("data2")).
                offset(10).
                limit(10).toString();
        System.out.println(sql);
    }
}

package com.helloacm;

import org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
import static org.jooq.impl.DSL.*;

public class Main {
    public static void main(String[] args) {
        DSLContext dsl = DSL.using(SQLDialect.POSTGRES);
        String sql = dsl.
                select(asterisk()).
                from(table("name")).
                where(field("a").eq("data1"), field("b").eq("data2")).
                offset(10).
                limit(10).toString();
        System.out.println(sql);
    }
}

This prepares the following SQL statement:

select *
from name
where (
  a = 'data1'
  and b = 'data2'
)
limit 10
offset 10

We can also construct a where clause using string (i.e. condition) – also we can use the value() function to avoid the SQL projection:

1

where(condition("a_field = " + value("a value")))

where(condition("a_field = " + value("a value")))

The jOOQ also supports complex join (union) statements.

–EOF (The Ultimate Computing & Technology Blog) —