jOOQ insert query with returning generated keys jOOQ insert query with returning generated keys sql sql

jOOQ insert query with returning generated keys


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


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) { }


Try

YoutableRecord result = create.insertInto(YOURTABLE).set(YOURTABLE.PROD_NAME, "VAL").returning(YOURTABLE.ID_PR).fetchOne();int id = result.getValue(Products.PRODUCTS.ID_PR);