Menu
What I actually want is to write following query in JOOQ:
stmt = connection.prepareStatement(
"INSERT INTO `tbl` (`name`, `service_id`, `device_id`) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`)",
Statement.RETURN_GENERATED_KEYS
);
I'm not able to find a way to do this in JOOQ. Is it possible?
325
ON DUPLICATE KEY UPDATE inserts or updates a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. The ON DUPLICATE KEY UPDATE clause can contain multiple column assignments, separated by commas. The use of VALUES() to refer to the new row and columns is deprecated beginning with MySQL 8.0.
Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error.
The Insert on Duplicate Key Update statement is the extension of the INSERT statement in MySQL. When we specify the ON DUPLICATE KEY UPDATE clause in a SQL statement and a row would cause duplicate error value in a UNIQUE or PRIMARY KEY index column, then updation of the existing row occurs.
Currently (as of jOOQ 3.4-3.6), that's not possible due to a flaw in jOOQ's INSERT API:
80
This might help you. For time being I am using this:
Field<Integer> LAST_INSERT_ID = DSL.function("LAST_INSERT_ID", Integer.class, PACKAGE.ID);
dsl.insertInto(PACKAGE)
.set(dsl.newRecord(PACKAGE, packagePojo))
.onDuplicateKeyUpdate()
.set(PACKAGE.ID, LAST_INSERT_ID)
.set(PACKAGE.PTR_JOB, packagePojo.getPtrJob())
.set(PACKAGE.PACK_NUMBER, packagePojo.getPackNumber())
.set(PACKAGE.RACK, packagePojo.getRack()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
