Does jOOQ have support for the VALUES() function on for use in INSERT... ON DUPLICATE KEY UPDATE?
If not, any suggestions on how to do it instead? (For a lot of rows)
See: http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_values
jOOQ doesn't have this MySQL specific VALUES() support in the API, but you can use the PostgreSQL specific equivalent via DSL.excluded() starting from jOOQ 3.17. This is documented here.
A statement like this:
insertInto(AUTHOR, AUTHOR.ID, AUTHOR.LAST_NAME) .values(3, "X") .onDuplicateKeyUpdate() .set(AUTHOR.LAST_NAME, excluded(AUTHOR.LAST_NAME)) .execute(); Will be translated to this MySQL equivalent statement:
INSERT INTO AUTHOR (ID, LAST_NAME) VALUES ( 3, 'X' ) AS t ON DUPLICATE KEY UPDATE AUTHOR.LAST_NAME = t.LAST_NAME Or on older versions of MySQL:
INSERT INTO AUTHOR (ID, LAST_NAME) VALUES ( 3, 'X' ) ON DUPLICATE KEY UPDATE AUTHOR.LAST_NAME = VALUES(LAST_NAME) Note that VALUES(column_name) has been deprecated with more recent versions of MySQL, and as such shouldn't be used anymore.
As of jOOQ 3.8, there's no such support and there's currently no support planned to be added. You can easily get that working on your side using plain SQL, though:
public static <T> Field<T> values(Field<T> field) { return DSL.field("values({0})", field.getDataType(), field); } For more information about "plain SQL" in jOOQ, see: http://www.jooq.org/doc/latest/manual/sql-building/plain-sql
Update for 2021, you can use Jooq's InsertOnConflictWhereStep.
Here is a sample of what this could look like (in Kotlin):
fun saveOrUpdate(items: Iterable<SomeClass>) { dsl.insertInto( TABLE, TABLE.FIELD1, TABLE.FIELD2, ).apply { items.forEach { item -> values( item.field1, item.field2 ) .onConflict( TABLE.FIELD1 ) .doUpdate() .set(TABLE.FIELD2, item.field2) .set(TABLE.UPDATED_AT, currentOffsetDateTime()) } } .execute() } 