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.