Building Query Conditions In JooqTemplate

Using JooqTemplate requires no code generation and no XML configuration.

Add Dependency

gradle:

implementation 'net.sf.sprtool:sprtool-jooq:1.7.0'

maven:

<dependency>
    <groupId>net.sf.sprtool</groupId>
    <artifactId>sprtool-jooq</artifactId>
    <version>1.7.0</version>
</dependency>

Inject JooqTemplate

import static net.sf.sprtool.jooq.JooqFields.*;

import net.sf.sprtool.jooq.JooqTemplate;

@Autowired
private JooqTemplate jt;

Using JooqTemplate

Method 1: conditions() — Field-Value Pair Varargs (Recommended)

conditions(Object... fieldValuePairs) takes arguments in pairs of "field name, value"; the operator is written directly after the field name string, and when no operator is written the default is ==. It returns a List<Condition>, and conditions whose values are null are automatically ignored:

// name = 'Smartphone' and price > 1000 and status = 'on_sale'
List<Condition> conds = jt.conditions(
        "name", "Smartphone",
        "price>", 1000,        // the > operator follows the field name
        "stock<=", 50,         // the <= operator
        "status", "on_sale");  // default operator ==

// You can also separate the field and operator with a colon: "price:>"
List<Condition> conds2 = jt.conditions("price:>", 1000);

When a parameter value is null, that condition is ignored — ideal for dynamic queries: if the frontend doesn't send a value, no filter is applied:

// When nameParam is null, no name condition is generated
List<Condition> conds = jt.conditions("name", nameParam, "category", categoryParam);

You can also mix native jOOQ Condition objects directly into the varargs:

Condition other = DSL.field("price").ge(1000);
List<Condition> conds = jt.conditions("category", "Electronics", other);

Method 2: and() / or() — Directly Returning a Single Combined Condition

and(...) and or(...) take arguments exactly like conditions(); the difference is the return value: multiple conditions are merged with AND / OR into a single Condition, and null is returned when there are no conditions at all:

// AND: name = 'John' and age > 30 and status = 'active'
Condition cond = jt.and("name", "John", "age>", 30, "status", "active");

// OR: name like 'Soph%' or name like 'Fra%'
Condition cond2 = jt.or("name:%l", "Soph", "name:%l", "Fra");

// Empty arguments return null
Condition empty = jt.and();   // null

Find two users with OR (corresponding to the user_table example in JooqTemplateOrTest):

// name = 'John' or name = 'Lee'
Condition cond = jt.or("name", "John", "name", "Lee");
List<LowerCaseRecord> records = jt.queryLowerCaseRecord("user_table", cond);

Method 3: condition() — Single-Condition Method

condition(Name fieldName, String operator, Object[] values) accepts a jOOQ Name, an operator string, and a value array, and generates a single Condition. The number of values must match what the operator requires, otherwise an IllegalArgumentException is thrown:

import org.jooq.Condition;
import org.jooq.Name;
import org.jooq.impl.DSL;

// Equality: == requires 1 value
Condition c1 = jt.condition(DSL.name("name"), "==", new Object[]{"Smartphone"});

// Comparison: > requires 1 value
Condition c2 = jt.condition(DSL.name("price"), ">", new Object[]{1000});

// in: requires 1 value, and the value must be a Collection or Object[]
Condition c3 = jt.condition(DSL.name("category"), "in",
        new Object[]{Arrays.asList("Electronics", "Appliances", "Apparel")});

// between: requires 2 values
Condition c4 = jt.condition(DSL.name("created_at"), "between",
        new Object[]{beginDate, endDate});

// isnull: requires 0 values
Condition c5 = jt.condition(DSL.name("remark"), "isnull", new Object[]{});

This method also follows the null rules: a null value returns null (ignored); wrapping with ND(null) generates is null; a null field name or operator returns null; an invalid operator throws IllegalArgumentException:

// Value is null → returns null, condition ignored
Condition c = jt.condition(DSL.name("name"), "==", new Object[]{null});   // null

// ND wrapping null → generates name is null
Condition c2 = jt.condition(DSL.name("name"), "==", new Object[]{ND(null)});

// Value count mismatch → throws IllegalArgumentException("Operator require value count is 1")
Condition c3 = jt.condition(DSL.name("name"), "==", new Object[]{});

not() — Negating a Condition

not(Condition) negates an existing condition and returns null when the input is null:

Condition original = jt.and("status", "disabled");
Condition notDisabled = jt.not(original);   // not (status = 'disabled')

orOne() — Multiple Fields Matching the Same Value

orOne(Object... sameOneValues) starts with several "fields" (single-value operators), and the last argument is the shared value, generating field1 op value or field2 op value .... A typical "global keyword search" scenario:

// name = 'Apple' or nick_name = 'Apple' or remark = 'Apple'
Condition cond = jt.orOne("name", "nick_name", "remark", "Apple");

Null Fallback for between

between / notbetween take 2 values; when either end is null, it automatically falls back to a one-sided comparison, and when both ends are null the whole condition is ignored:

// Both begin and end are non-null: price between 100 and 500
jt.conditions("price:between", 100, 500);

// begin=100, end=null: price >= 100
jt.conditions("price:between", 100, null);

// notbetween fallback: begin=null, end=500 → price > 500
jt.conditions("price:notbetween", null, 500);

Operator Reference

Condition operators (written after the field name or passed to condition(); all are case-insensitive):

Operator	Value Count	Meaning
== (default)	1	Equal to
<>	1	Not equal to
> >= < <=	1	Comparison
% / like	1	Contains %value%
%l / llike	1	Prefix match value%
%r / rlike	1	Suffix match %value
in / notin	1	Value is a Collection or Object[]
between / notbetween	2	Range; a null end falls back automatically
isnull / isnotnull	0	Null check

Sort operators (only used in the order part of a query or in orders()): asc, ascnf (nulls first), ascnl (nulls last), desc, descnf, descnl.

Null handling rules:

• In queries (conditions() / and() / or() / condition()), a null value → the condition is ignored.
• A value wrapped with ND(null) → generates field is null.
• In UPDATE / DELETE, a null value → generates field is null.