使用JOOQ+SQLite执行executeInsert后如何获取自动生成ID？

Question Context #

Here's the code adapted from official docs for a jOOQ + SQLite3 development scenario:

// A "mutable" POJO class
public class MyBook {
    public int id;
    public String title;
}

// Create a new POJO instance
MyBook myBook = new MyBook();
// myBook.id = 10; <-- Id is NOT set here because the database auto-generates it
myBook.title = "Animal Farm";

// Load a jOOQ-generated BookRecord from your POJO
BookRecord book = create.newRecord(BOOK, myBook);

// Insert it (explicitly)
create.executeInsert(book);

// What is value of the auto-generated id?

After running executeInsert to complete the insertion, how can I get the auto-generated ID from SQLite? Do I need to manually set the ID before saving?

Answer #

Let's break this down simply:

1. You don't need to manually set the ID before saving
   SQLite's INTEGER PRIMARY KEY AUTOINCREMENT (or even just INTEGER PRIMARY KEY, which auto-increments by default) takes care of ID generation automatically. Manually setting the ID would either be ignored by the database (if it's configured to auto-generate) or lead to conflicts if that ID already exists in the table.

2. Two straightforward ways to get the auto-generated ID

Option 1: Fetch from the existing BookRecord

jOOQ automatically populates the auto-generated ID back into your BookRecord object right after the insert finishes. As long as your jOOQ-generated BOOK table metadata marks the ID column as an auto-incrementing primary key (which it will if your SQLite table is defined correctly), you can grab the ID directly from the record:

create.executeInsert(book);
int generatedId = book.getId(); // Or use book.id if accessing the raw field directly

Option 2: Use returning() for explicit retrieval

For a more explicit approach (especially handy if you need to fetch multiple auto-generated fields), use jOOQ's returning() method with your insert statement. This returns the inserted record with all auto-populated fields:

BookRecord insertedBook = create.insertInto(BOOK)
                               .set(BOOK.TITLE, myBook.title)
                               .returning(BOOK.ID)
                               .fetchOne();

if (insertedBook != null) {
    int generatedId = insertedBook.getId();
}

Both methods work reliably with SQLite—just ensure your table's ID column is properly configured as an auto-incrementing primary key in your schema (e.g., id INTEGER PRIMARY KEY AUTOINCREMENT).

内容的提问来源于stack exchange，提问作者Chris Dutrow