Jooq: Support MySQL's INSERT .. VALUES(), () ON DUPLICATE KEY UPDATE x = VALUES(x) syntax (multi-insert with update)

[lukaseder]

This is probably not properly supported:

INSERT INTO beautiful (name, age)
      VALUES
      ('Helen', 24),
      ('Katrina', 21),
      ('Samia', 22),
      ('Hui Ling', 25),
      ('Yumie', 29)
ON DUPLICATE KEY UPDATE
     age = VALUES(age),
     ...

As referenced here:

• https://groups.google.com/d/topic/jooq-user/UcfkQQByCBk/discussion
• https://groups.google.com/forum/#!topic/jooq-user/D8RjRIBwxaE

[codefromthecrypt]

Not sure if this is the same issue, but I'd love to be able to have something like context.batchInsert().onDuplicateKeyUpdate(true).

[lukaseder]

@adriancole : Could you please create a separate issue for this?

[codefromthecrypt]

Sure!

[killermonk]

If you use the following on a normal insert query builder

.onDuplicateKeyUpdate()
    .set(field("DateValue"), (Object)field("VALUES(DateValue)"))
    .set(field("NumericValue"), (Object)field("VALUES(NumericValue)"))
    .set(field("TextValue"), (Object)field("VALUES(TextValue)"));

Then a call to getSQL() gives you the following SQL:

on duplicate key update
    DateValue = VALUES(DateValue),
    NumericValue = VALUES(NumericValue),
    TextValue = VALUES(TextValue)

There might be a better way to do this, but this worked for me.

[lukaseder]

@killermonk : Thanks for contributing. This is actually a very good way to go ahead and do this. Alternatively, you could extract the functionality in your own class:

class MyDSL {
    public static <T> Field<T> values(Field<T> field) {
        return DSL.field("VALUES({0})", field.getDataType(), field);
    }
}

That could now be used as such in a type safe way:

.onDuplicateKeyUpdate()
    .set(T.DATE_VALUE, MyDSL.values(T.DATE_VALUE))
    .set(T.NUMERIC_VALUE, MyDSL.values(T.NUMERIC_VALUE))
    .set(T.TEXT_VALUE, MyDSL.values(T.TEXT_VALUE))

[lukaseder]

Added MySQLDSL.values() due to popular request.

[rui-gao]

@adriancole Could you share the url of the separate issue about text.batchInsert().onDuplicateKeyUpdate(true) ? I want to use this functionality as well.

[codefromthecrypt]

@demongaorui as far as I can tell I never created that issue

[rui-gao]

@adriancole I see...

@lukaseder Do we have a way to do ext.batchInsert().onDuplicateKeyUpdate(true) now?
I want to do multiple rows insert/onDuplicateKeyUpdate in only one execution.

[lukaseder]

@demongaorui: The suggested API probably won't work this way. Perhaps, we'll add a UpdatableRecord.upsert() and thus a batchUpsert() method at some point, but the ON DUPLICATE KEY UPDATE clause is too powerful to be passed to the batchInsert() statement as a simple extension.

Right now, however, you can easily batch actual ON DUPLICATE KEY UPDATE statements like this:

insertInto(TABLE).set(record).onDuplicateKeyUpdate().set(record)

Hope this helps

[rui-gao]

@lukaseder Thank you very much for rapid reply.

Currently, I am using

for (Record record : records) {
  db.insertInto(TABLE).set(record)
     .onDuplicateKeyUpdate().set(record)
     .execute();
}

But this will cause running many queries to db. For direct insert, we could do like

db.insertInto(TABLE).set(record1).newRecord().set(record2).execute();

So all the records could be inserted in just one execution.

Is there any way we could do insert/onDuplicateKeyUpdate for multiple records in one execution?

[lukaseder]

Yes, I'm sorry I may not have been clear. You can batch the statements with this API:
https://www.jooq.org/doc/latest/manual/sql-execution/batch-execution

[rui-gao]

@lukaseder Thank you very much for your info!