Query Single Record 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 net.sf.sprtool.jooq.JooqTemplate;

@Autowired
private JooqTemplate jt;

// Inject JooqMapperTemplate when using RecordMapper for custom mapping
import net.sf.sprtool.jooq.JooqMapperTemplate;

@Autowired
private JooqMapperTemplate jmt;

Using JooqTemplate

v Methods (Recommended, More Concise)

The v-suffixed loadv / loadRecordv / loadLowerCaseRecordv pass conditions and ordering directly as varargs, with operators embedded in field names — no need to build jOOQ Condition objects. This is the most concise style:

// Conditions as field-value pairs; the default operator is ==
UserEntity user = jt.loadv("user_table", UserEntity.class, "name", "John");

// Mixed conditions and ordering: name like '%Lee%' and dept_id = 2, ordered by id ascending
UserEntity r = jt.loadv("user_table", UserEntity.class,
        "name%", "Lee", "dept_id", 2, "id:asc");

// Record / LowerCaseRecord versions
Record record = jt.loadRecordv("product", "name", "Smartphone");
LowerCaseRecord lr = jt.loadLowerCaseRecordv("product", "name", "Smartphone", "id:asc");
String name = lr.get("name");

// The first argument also accepts a Table object
UserEntity u = jt.loadv(DSL.table("user_table"), UserEntity.class, "id", 1);

The following sections describe the equivalent Condition-style usage for side-by-side reference.

Map to a Java Bean

load(table, Class, condition) maps the query result to a Java Bean; snake_case database columns (e.g. nick_name) are automatically mapped to camelCase properties (nickName). Returns null when no record matches, and the first record when multiple match:

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

Condition condition = DSL.field("name").eq("Smartphone");

Product product = jt.load("product", Product.class, condition);
// product.getName() / product.getNickName() ... mapped automatically

// Complex condition: name = 'xxx' and nick_name = 'xxx'
Condition complex = DSL.field("name").eq("Lee")
        .and(DSL.field("nick_name").eq("Big Lee"));
UserEntity user = jt.load("user_table", UserEntity.class, complex);

Load a Single Field Value

Combined with JooqFields.SL(...) to select a single field, the value is loaded directly as a String / Integer or similar type:

import net.sf.sprtool.jooq.JooqFields;

String name = jt.load("user_table", String.class, JooqFields.SL("name"), condition);
Integer id = jt.load("user_table", Integer.class, JooqFields.SL("id"), condition);

Map to a Record

loadRecord(table, condition) returns a native jOOQ Record; read values by type with get(fieldName, type):

import org.jooq.Record;

Record record = jt.loadRecord("user_table", condition);
String name = record.get("name", String.class);
Integer id = record.get("id", Integer.class);

Map to a LowerCaseRecord

loadLowerCaseRecord(table, condition) returns a LowerCaseRecord with case-insensitive field names; get needs no type argument, and you can also call intoMap() to convert it to a Map or containsKey to check whether a field exists:

import net.sf.sprtool.jooq.LowerCaseRecord;

LowerCaseRecord record = jt.loadLowerCaseRecord("user_table", condition);
String name = record.get("name");

// Use it as a Map
Map<String, Object> map = record.intoMap();
assertTrue(map.containsKey("name"));
assertFalse(map.containsKey("nonexistent_field"));

Use TableLike Objects

The first argument likewise accepts TableLike / Table objects built with DSL.table(...):

TableLike<?> userTable = DSL.table("user_table");

UserEntity user = jt.load(userTable, UserEntity.class, condition);
Record record = jt.loadRecord(userTable, condition);
LowerCaseRecord lr = jt.loadLowerCaseRecord(userTable, condition);

Behavior Notes

• No matching record: load / loadRecord / loadLowerCaseRecord all return null.
• Multiple matches: the first record is returned.
• null Condition: passing null to the TableLike variants does not throw (equivalent to an unconditional query); however, an incomplete field-value pair such as jt.and("id") throws an IllegalArgumentException inside and().
• Table does not exist: an exception is thrown, and its message includes the table name.