DEV Community: ts5432

Query records using JooqTemplate

ts5432 — Sun, 14 Jun 2026 07:06:33 +0000

1. query

Query multiple records matching the condition and map them to a list of entities.

// Single condition
public <E> List<E> query(String table, Class<E> cls, Condition condition)
public <E> List<E> query(String table, Class<E> cls, Condition condition, List<? extends OrderField> orderBy)
// Multiple conditions
public <E> List<E> query(String table, Class<E> cls, List<Condition> conditions)
public <E> List<E> query(String table, Class<E> cls, List<Condition> conditions, List<? extends OrderField> orderBy)
// Using RecordMapper
public <R,E> List<E> query(String table, RecordMapper<R,E> mapper, Condition condition)
public <R,E> List<E> query(String table, RecordMapper<R,E> mapper, List<Condition> conditions)

Returns: List — list of entities, never null.
Example:

// Single condition
List<User> list = jt.query("user_table", User.class,
    F("name").contains("John"));

// Multiple conditions + order
List<User> list = jt.query("user_table", User.class,
    jt.conditions("name%", name, "birthday>=", beginDate),
    Arrays.asList(F("birthday").desc(), F("name").asc()));

// Using RecordMapper
List<User> list = jt.query("user_table", new MyMapper(),
    F("id").gt(0));

2. queryRecord

public Result<Record> queryRecord(String table, Condition condition)
public Result<Record> queryRecord(String table, List<Condition> conditions, List<? extends OrderField> orderBy)
public Result<Record> queryRecordv(String table, Object... conditions)

Example:

Result<Record> rs = jt.queryRecordv("user_table", "name%", "Jo");
for(Record r : rs) {
    System.out.println(r.get("name"));
}

3. queryLowerCaseRecord

public List<LowerCaseRecord> queryLowerCaseRecord(String table, Condition condition)
public List<LowerCaseRecord> queryLowerCaseRecordv(String table, Object... conditions)

4. queryv (varargs conditions)

Query a list using varargs conditions. Combine conditions and ordering in a single line.

public <E> List<E> queryv(String table, Class<E> cls, Object... conditions)
public <E> List<E> queryv(TableLike table, Class<E> cls, Object... conditions)

Example:

// Fuzzy + range query
List<User> list = jt.queryv("user_table", User.class,
    "name%", name, "birthday>=", beginDate, "birthday<=", endDate);

// Mixed ordering
List<User> list = jt.queryv("user_table", User.class,
    "birthday:asc", F("name").asc().nullsLast());

Load a single record using JooqTemplate

ts5432 — Thu, 11 Jun 2026 05:29:21 +0000

1. load

Load a single record matching the condition and map it to the specified entity class. Automatically appends LIMIT 1.

public <E> E load(String table, Class<E> cls, Condition condition)
public <E> E load(String table, Class<E> cls, Condition condition, List<? extends OrderField> orderBy)
public <E> E load(String table, Class<E> cls, List<Condition> conditions)
public <E> E load(String table, Class<E> cls, List<Condition> conditions, List<? extends OrderField> orderBy)

Returns: Entity object, or null if not found.
Example:

// Single condition
User user = jt.load("user_table", User.class, F("id").eq(id));

// Multiple conditions + order
User user = jt.load("user_table", User.class,
    Arrays.asList(F("name").eq("John"), F("birthday").isNotNull()),
    Arrays.asList(F("create_time").desc()));

2. loadv (varargs conditions)

Load a single record using varargs for conditions. Supports embedding operators and ordering in field names.

public <E> E loadv(String table, Class<E> cls, Object... conditions)
public <E> E loadv(TableLike table, Class<E> cls, Object... conditions)

Example:

// Simple equality
User user = jt.loadv("user_table", User.class, "id", id);

// Combined conditions + sort
User user = jt.loadv("user_table", User.class,
    "name", name, "name:isnotnull", "birthday:desc");

// Mix with Condition object
User user = jt.loadv("user_table", User.class,
    "id", id, F("name").isNotNull());

3. loadRecord

public Record loadRecord(String table, Condition condition)
public Record loadRecord(String table, List<Condition> conditions)
public Record loadRecord(String table, Condition condition, List<? extends OrderField> orderBy)
public Record loadRecord(String table, List<Condition> conditions, List<? extends OrderField> orderBy)
public Record loadRecordv(String table, Object... conditions)

4. loadLowerCaseRecord

public LowerCaseRecord loadLowerCaseRecord(String table, Condition condition)
public LowerCaseRecord loadLowerCaseRecord(String table, List<Condition> conditions)
public LowerCaseRecord loadLowerCaseRecordv(String table, Object... conditions)

Delete record using JooqTemplate

ts5432 — Wed, 10 Jun 2026 04:36:54 +0000

1. delete

Delete records matching the given condition.

// Single Condition
public int delete(String table, Condition condition)
public int delete(Table table, Condition condition)
// List<Condition>
public int delete(String table, List<Condition> conditions)
public int delete(Table table, List<Condition> conditions)

Returns: int — number of affected rows.
Example:

jt.delete("user_table", F("id").eq(100));
jt.delete("user_table", Arrays.asList(F("id").eq(100)));

2. deletev (varargs conditions)

Specify delete conditions via varargs.

public int deletev(String table, Object... conditions)
public int deletev(Table table, Object... conditions)

Returns: int — number of affected rows.
Example:

// Delete by equality
jt.deletev("user_table", "id", 100);

// Multiple conditions  WHERE name = ? AND birthday >= ?
jt.deletev("user_table", "name", "John", "birthday>=", beginDate);

Update record using JooqTemplate

ts5432 — Tue, 09 Jun 2026 04:57:48 +0000

1. update

Update records matching the given condition. Supports both Condition and List.

// Single Condition
public int update(String table, Map bean, Condition condition)
public int update(Table table, Map bean, Condition condition)
// List<Condition>
public int update(String table, Map bean, List<Condition> conditions)
public int update(Table table, Map bean, List<Condition> conditions)

Returns: int — number of affected rows.
Example:

// Using Condition
jt.update("user_table", values, F("id").eq(user.getId()));

// Using List<Condition>
jt.update("user_table", values,
    jt.conditions("id", user.getId(), "name<>", user.getName()));

// Using Table object
jt.update(T("user_table"), values, jt.conditions("id", user.getId()));

2. updatev (varargs conditions)

Specify update conditions via varargs without manually constructing Condition objects. Operators can be embedded in field names.

public int updatev(String table, Map bean, Object... where)
public int updatev(Table table, Map bean, Object... where)

Returns: int — number of affected rows.
Example:

// Equality  WHERE id = ?
jt.updatev("user_table", values, "id", user.getId());

// Multiple conditions  WHERE id = ? AND name <> ?
jt.updatev("user_table", values,
    "id", user.getId(), "name<>", user.getName());

// Mix Condition
jt.updatev(T("user_table"), values,
    "id", user.getId(), "name:isnotnull");

Insert records using JooqTemplate

ts5432 — Mon, 08 Jun 2026 02:18:11 +0000

JooqTemplate is a wrapper around JOOQ providing concise CRUD methods. Key features:

Operate tables by name string directly — no code generation required
Three ways to pass conditions: Condition / List / varargs
Embed operators in field names via conditions() — combine multiple conditions in one line
Built-in Java Bean ↔ Map conversion, camelCase/snake_case interconversion, and other utilities

1. SETUP

Add the net.sf.sprtool:sprtool-jooq:1.2.0 dependency to project:

Maven

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

Gradle

// Groovy DSL
implementation 'net.sf.sprtool:sprtool-jooq:1.2.0'

// Kotlin DSL
implementation("net.sf.sprtool:sprtool-jooq:1.2.0")

Java

@Autowired
JooqTemplate jt;

2. INSERT

Insert a single record into the database table.

public int insert(String table, Map map)
public int insert(Table table, Map map)

table Table name, e.g. "user_table" or T("user_table")
map Column-value map; key is the column name, value is the value
Returns: int — number of affected rows.
Example:

Map user = new HashMap();
user.put("name", "John");
user.put("birthday", LocalDate.now());
user.put("create_time", LocalDateTime.now());
jt.insert("user_table", user);

3. insertReturningv

Insert a record and return the values of specified fields (commonly used for retrieving auto-generated IDs).

public Record insertReturningv(String table, Map map, Object... fields)
public Record insertReturningv(Table table, Map map, Object... fields)

Returns: Record — a JOOQ Record containing the returned field values. Use get("id", Integer.class) to extract values.
Example:

Map values = JooqMaps.toSnakeCaseWithNull(user);
values.remove("id");
values.put("create_time", LocalDateTime.now());
Record r = jt.insertReturningv("user_table", values, "id");
int newId = r.get("id", Integer.class);

4. Batch insert

Batch-insert multiple records.

public int insert(String table, List<Map> bean)
public int insert(Table table, List<Map> bean)

Example:

List<Map> batch = new ArrayList();
for(int i = 0; i < 10; i++) {
    Map user = new HashMap();
    user.put("name", "User" + i);
    batch.add(user);
}
jt.insert("user_table", batch);

Jooq util JooqTemplate Quickstart

ts5432 — Mon, 01 Jun 2026 07:02:03 +0000

1. Start a new Spring Boot project

Use start.spring.io to create a project with jooq

2. Add dependency

gradle:

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

maven:

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

3. Inject JooqTemplate

@Autowired
private JooqTemplate jt;

4. Use JooqTemplate

insert:

Map values=JooqMaps.toSnakeCase(someBean);
jt.insert("some_table",values);

update:

Map values=JooqMaps.toSnakeCase(someBean);
jt.updatev("some_table",values,"id",someBean.getId());

delete:

jt.deletev("some_table","id",idParam);

query single:

SomeBean someBean=jt.loadv("some_table",SomeBean.class,"id",id);

query multiple:
Final sql will not include "name" or "gender" when nameParam is null or genderParam is null.
The supported operators include: %,%L,%R,>,>=,==,<,<=,isnull,isnotnull,in,notin,between,notbetween,asc,ascnf,ascnl,desc,descnf,descnl.

Condition otherCondition=DSL...;
List<SomeBean> beanList=jt.queryv("some_table",SomeBean.class,"name%",nameParam,"gender",genderParam,"bidrthday:between",beignDate,endDate,otherCondition,"id:desc");

pagination query

List conditions=jt.conditions("id",idParam,"name%",nameParam,"birthday>=",birthDayParam,...);
LimitSelect limitSelect=new LimitSelect() {
    public SelectOrderByStep from(SelectSelectStep select) {
        return select.from(T("some_table")).where(conditions);
    }
    public List<OrderField> orderBy(){
        return Arrays.asList(F("birthday").desc());
    }
};
LimitResult result=jt.query(SomeBean.class,limitSelect,LimitRange.of(limitParam,offsetParam));