JQQ insert query with returned generated keys

Question

I installed jOOQ in eclipse, generated classes for mySQL, but I still have problems writing some basic queries.

I tried to compose an insert request with the return of the generated keys, but the compiler throws an 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 other different ways how to do it right?

thanks haris

Answer 1

The syntax you use is for inserting multiple records. This will insert 4 records, each with one field.

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

But you declared 4 fields in order not to work:

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

What you probably want to do is the following:

 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(); 

Perhaps you are even better off using

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

See manual for more details:

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

Or Javadoc to create INSERT that return values:

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

Answer 2

OFFERS A DECISION

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

Answer 3

Try

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