Batch Insert 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;

Using JooqTemplate

Inserting Multiple Records with List

Pass a List<Map<String,Object>> to insert multiple records at once; the return value is the number of inserted records:

List<Map<String, Object>> productList = new ArrayList<>();

Map<String, Object> p1 = new HashMap<>();
p1.put("name", "Wireless Mouse");
p1.put("category", "Electronics");
p1.put("price", 129.00);
productList.add(p1);

Map<String, Object> p2 = new HashMap<>();
p2.put("name", "Mechanical Keyboard");
p2.put("category", "Electronics");
p2.put("price", 399.00);
productList.add(p2);

Map<String, Object> p3 = new HashMap<>();
p3.put("name", "Monitor Stand");
p3.put("category", "Office Supplies");
p3.put("price", 199.00);
productList.add(p3);

int result = jt.insert("product", productList);
// result == 3; 3 records inserted

Inserting a List with a Single Record

When the List contains only 1 Map, it inserts 1 record and returns 1:

List<Map<String, Object>> orderList = new ArrayList<>();
Map<String, Object> order = new HashMap<>();
order.put("order_no", "A20260001");
order.put("amount", 258.00);
orderList.add(order);

int result = jt.insert("order", orderList);
// result == 1

Using a jOOQ Table Object

The first argument can also be a jOOQ Table object built with DSL.table(...); the effect is the same as passing the table name string:

Table<?> productTable = DSL.table("product");
int result = jt.insert(productTable, productList);
// result == 3

Inserting in Batches with a Specified batchSize

insert(table, list, batchSize) commits in batches according to batchSize. With 5 records and batchSize=2, it splits into 3 batches (2+2+1), and all records are inserted successfully:

int result = jt.insert("product", productList, 2);
// result == 5 (all 5 records inserted in batches)

// batchSize equals the list size: all inserted in one batch
int result2 = jt.insert("product", productList, productList.size());

// batchSize greater than the list size: still inserted only once, without affecting the result
int result3 = jt.insert("product", productList, 10);

The Table object version also supports batchSize:

int result = jt.insert(productTable, productList, 2);

Edge Cases

Behaviors verified by unit tests:

• Empty List: no records inserted, returns 0:

int result = jt.insert("product", new ArrayList<>());
// result == 0

• null List: throws NullPointerException / IllegalArgumentException.
• batchSize of 0 or negative: no error; inserts normally.
• Nonexistent table name: throws an exception whose message contains the table name.