jOOQ insert query with returning generated keys

Question

I installed jOOQ into eclipse, generated classes for my mySQL, but I still have problems to write also some basic queries.

I tried to compose insert query with returning of generated keys, but compiler throws error

Table: tblCategory Columns: category_id, parent_id, name, rem, uipos

Result<TblcategoryRecord> result= create.insertInto(Tblcategory.TBLCATEGORY, 
    Tblcategory.PARENT_ID, Tblcategory.NAME, Tblcategory.REM, Tblcategory.UIPOS)
        .values(node.getParentid())
        .values(node.getName())
        .values(node.getRem())
        .values(node.getUipos())
        .returning(Tblcategory.CATEGORY_ID)
        .fetch();

tried also other differnt ways how to do it right way?

thanks charis

Lukas Eder · Accepted Answer · 2012-11-19T10:29:49.597

17

The syntax you're using is for inserting multiple records. This is going to insert 4 records, each with one field.

.values(node.getParentid())
.values(node.getName())
.values(node.getRem())
.values(node.getUipos())

But you declared 4 fields, so that's not going to work:

create.insertInto(Tblcategory.TBLCATEGORY, 
  Tblcategory.PARENT_ID, Tblcategory.NAME, Tblcategory.REM, Tblcategory.UIPOS)

What you probably want to do is this:

Result<TblcategoryRecord> result = create
  .insertInto(Tblcategory.TBLCATEGORY, 
    Tblcategory.PARENT_ID, Tblcategory.NAME, Tblcategory.REM, Tblcategory.UIPOS)
  .values(node.getParentid(), node.getName(), node.getRem(), node.getUipos())
  .returning(Tblcategory.CATEGORY_ID)
  .fetch();

Or alternatively:

Result<TblcategoryRecord> result = create
  .insertInto(Tblcategory.TBLCATEGORY) 
  .set(Tblcategory.PARENT_ID, node.getParentid())
  .set(Tblcategory.NAME, node.getName())
  .set(Tblcategory.REM, node.getRem())
  .set(Tblcategory.UIPOS, node.getUipos())
  .returning(Tblcategory.CATEGORY_ID)
  .fetch();

Probably, you're even better off by using

TblcategoryRecord result =
  // [...]
  .fetchOne();

For more details, consider the manual:

http://www.jooq.org/doc/2.6/manual/sql-building/sql-statements/insert-statement/

Or the Javadoc for creating INSERT statements that return values:

http://www.jooq.org/javadoc/latest/org/jooq/InsertReturningStep.html

edited Nov 19 '12 at 10:29

answered Nov 15 '11 at 12:34

Lukas Eder

211,314
129
689
1,509

thanks, added preferred solution, but Eclipse still needs casting – Charis997 Nov 15 '11 at 18:50
@Charis997: It is usually not recommended to put answers in questions on Stack Overflow. It will be difficult for subsequent visitors to understand what's going on. Instead, you can "accept" this answer, or provide an answer yourself, if this one isn't clear enough – Lukas Eder Nov 16 '11 at 08:02
@Charis997: About the casting: You're right. That won't be necessary anymore with jOOQ 2.0. But it's still needed with jOOQ 1.6.9 – Lukas Eder Nov 16 '11 at 08:03
Lets honour the code! Preffered solution posted in an answer below. Looking forward to jOOQ 2.0 Thx – Charis997 Nov 16 '11 at 19:21
It would be useful to have tableRecord.insertReturning(), so you don't need to map every single field in the record object to the respective table columns to be able to return a generated field value on insertion. – andresp Nov 08 '21 at 13:30
1

@andresp, you can call `set(record)`, to transfer changed fields from the record to an insert statement... – Lukas Eder Nov 08 '21 at 17:03
@LukasEder perfect. that works too. thank you. – andresp Nov 09 '21 at 17:07

score 4 · Answer 2 · answered Nov 16 '11 at 19:13

4

preffered SOLUTION

  try {
    TblcategoryRecord record = (TblcategoryRecord) create
      .insertInto(Tblcategory.TBLCATEGORY) 
      .set(Tblcategory.PARENT_ID, node.getParentid())
      .set(Tblcategory.NAME, node.getName())
      .set(Tblcategory.REM, node.getRem())
      .set(Tblcategory.UIPOS, node.getUipos())
      .returning(Tblcategory.CATEGORY_ID)
      .fetchOne();

      node.setId(record.getCategoryId());

    } catch (SQLException e1) { }

answered Nov 16 '11 at 19:13

Charis997

335
1
6
11

5

You really shouldn't ignore that SQLException! If it's just an example, you may leave the whole try/catch block out of it. – Matthijs Bierman May 01 '12 at 13:03

score 0 · Answer 3 · answered Apr 25 '15 at 17:22

0

Try

YoutableRecord result = create
.insertInto(YOURTABLE)
.set(YOURTABLE.PROD_NAME, "VAL")
.returning(YOURTABLE.ID_PR)
.fetchOne();


int id = result.getValue(Products.PRODUCTS.ID_PR);

answered Apr 25 '15 at 17:22

Adel

5,341
2
21
31

jOOQ insert query with returning generated keys

3 Answers3

Linked