Insert records using JooqTemplate

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.1.0 dependency to project:

Maven

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

Gradle

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

// Kotlin DSL
implementation("net.sf.sprtool:sprtool-jooq:1.1.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);