Insert records using JooqTemplate

Source: Dev.to

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

    net.sf.sprtool
    sprtool-jooq
    1.1.0

Enter fullscreen mode


Exit fullscreen mode

Gradle

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

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

Enter fullscreen mode


Exit fullscreen mode

Java

@Autowired
JooqTemplate jt;

Enter fullscreen mode


Exit fullscreen mode

2. INSERT

Insert a single record into the database table.

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

Enter fullscreen mode


Exit fullscreen mode

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);

Enter fullscreen mode


Exit fullscreen mode

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)

Enter fullscreen mode


Exit fullscreen mode

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);

Enter fullscreen mode


Exit fullscreen mode

4. Batch insert

Batch-insert multiple records.

public int insert(String table, List bean)
public int insert(Table table, List bean)

Enter fullscreen mode


Exit fullscreen mode

Example:

List 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);

Enter fullscreen mode


Exit fullscreen mode