DEV Community

ts5432
ts5432

Posted on

Insert Returning In JooqTemplate

#database #java #sql #tutorial

Using JooqTemplate requires no code generation and no XML configuration.

Add Dependency

gradle:

implementation 'net.sf.sprtool:sprtool-jooq:1.7.0'
Enter fullscreen mode Exit fullscreen mode

maven:

<dependency>
    <groupId>net.sf.sprtool</groupId>
    <artifactId>sprtool-jooq</artifactId>
    <version>1.7.0</version>
</dependency>
Enter fullscreen mode Exit fullscreen mode

Inject JooqTemplate 

import net.sf.sprtool.jooq.JooqTemplate;

@Autowired
private JooqTemplate jt;
Enter fullscreen mode Exit fullscreen mode

Using JooqTemplate

Inserting with a Table Object

Pass a jOOQ Table object built with DSL.table(...) as the first argument; the behavior is exactly the same as passing the table name string — with a partial-field insert, unset fields are null, and an empty Map also inserts 1 record:

import org.jooq.Table;
import org.jooq.impl.DSL;

Table<?> productTable = DSL.table("product");

Map<String, Object> product = new HashMap<>();
product.put("name", "Bluetooth Headset");
product.put("category", "Electronics");
product.put("price", 299.00);

int result = jt.insert(productTable, product);
// result == 1
Enter fullscreen mode Exit fullscreen mode

Passing a null Map throws NullPointerException / IllegalArgumentException; when the table does not exist, an exception is thrown and its message contains the table name.

insertReturningv: Insert and Return Specified Fields

insertReturningv(table, values, fieldName...) inserts one record and returns a jOOQ Record containing the specified fields. The typical scenario is getting the auto-increment primary key id:

import org.jooq.Record;

Map<String, Object> order = new HashMap<>();
order.put("order_no", "A20260002");
order.put("user_id", 1001);
order.put("amount", 698.00);

// Returns the two fields id and order_no
Record returned = jt.insertReturningv("order", order, "id", "order_no");

Integer id = returned.get("id", Integer.class);           // auto-increment id generated by the database
String orderNo = returned.get("order_no", String.class);
Enter fullscreen mode Exit fullscreen mode

Returning Database-Generated Default Value Fields

The returned field list can include fields not assigned during the insert, such as create_time generated by database defaults:

Map<String, Object> product = new HashMap<>();
product.put("name", "Smart Band");
product.put("price", 199.00);

Record returned = jt.insertReturningv("product", product,
        "id", "name", "price", "create_time");

assertNotNull(returned.get("id", Integer.class));  // auto-increment primary key
assertNotNull(returned.get("create_time"));        // database default value
Enter fullscreen mode Exit fullscreen mode

insertReturningv with a Table Object

The first argument also accepts a Table object:

Table<?> productTable = DSL.table("product");
Record returned = jt.insertReturningv(productTable, product, "id", "name");
String name = returned.get("name", String.class);
Enter fullscreen mode Exit fullscreen mode

Without Specifying Return Fields

When no return fields are passed, the insert executes normally but the method returns null — be sure to pass "id" when you need the primary key:

Record returned = jt.insertReturningv("product", product);
// returned == null; the record was inserted but no return value is available
Enter fullscreen mode Exit fullscreen mode

Edge Cases

  • The table named or corresponding to the Table object does not exist: throws an exception whose message contains the table name.
  • Returned fields are read by type via Record.get(fieldName, type), e.g. get("id", Integer.class).

Top comments (0)