Lets say I have a table Person(id, fname, lname) and it contains a record (1, 'Michael', 'Bay'). Now I wish to create another record in Person table with the same fname and lname but different id i.e. (453456, 'Michael', 'Bay'). This is the way I would do in plain SQL

INSERT INTO Person(id, fname, lname)

SELECT 453456, Person.fname, Person.lname

FROM Person

WHERE Person.id = 1;

How can this be done with JOOQ (ideally while retaining JOOQ's code generation and type safety features)?

I am aware that JOOQ provides ability to copy entire records from one table to same or another table with its selectFrom syntax

jooq.insertInto(PERSON)

.select(selectFrom(PERSON).where(PERSON.ID.eq(1)))

.execute();

But in my case, there are only certain columns in the record that I need to copy while the rest of the values needed to be set explicitly

Another solution I could think of is the following

jooq.insertInto(PERSON)

.values(452452)

.execute();

jooq.update(PERSON)

.set(row(PERSON.FNAME, PERSON.LNAME),

select(PERSON.FNAME, PERSON.LNAME)

.from(PERSON)

.where(PERSON.ID.eq(1)))

.where(PERSON.ID.eq(452452))

.execute();

But it doesn't feel right. I would be grateful if someone could provide any other solution/workaround for this problem.

解决方案

jooq.insertInto(PERSON)

.columns(PERSON.ID, PERSON.FNAME, PERSON.LNAME)

.select(select(val(452452), PERSON.FNAME, PERSON.LNAME)

.from(PERSON)

.where(PERSON.ID.eq(1)))

.execute();

As always, this is assuming the following static import:

import static org.jooq.impl.DSL.*;