Jooq: Add DSLContext.batchInsert(records).onDuplicateKeyUpdate() and Ignore()

[codefromthecrypt]

I'd love to be able to have something like this:

queries = records.stream().map(
            record -> DSL.using(context.configuration())
                .insertInto(MY_TABLE)
                .set(record)
                .onDuplicateKeyUpdate()
                .set(record))
    .toArray(Query[]::new);
context.batch(queries).execute()

compressed to this context.batchInsert(records).onDuplicateKeyUpdate()

[lukaseder]

I can see how this would add value to certain use-cases. Already today, there is batchStore(records), although that will emit an INSERT or UPDATE statement on a per-record basis, which might not be just as efficient as an actual INSERT .. ON DUPLICATE KEY UPDATE or MERGE in the database.

The actual execution of this feature will need some more thought...

Beautiful Java 8 example, by the way!

[lukaseder]

We should take inspiration from various vendor's UPSERT statement implementations, e.g. HANA #3887

[vicmosin]

Is this gonna be implemented after all?

[lukaseder]

@vicmosin Maybe, but so far, no really compelling API has been found. This change isn't just about the batchInsert() functionality. For such a change to be reasonable, we'd have to have similar functionality in a variety of other API as well, which needs more research (and more community traction)

[lukaseder]

This has been implemented as DSLContext.batchMerge(): https://github.com/jOOQ/jOOQ/issues/10046, which was added for consistency reasons, along with other MERGE operations, such as UpdatableRecord.merge(): https://github.com/jOOQ/jOOQ/issues/2961

While the name was chosen to reflect MERGE semantics, it can execute INSERT .. ON DUPLICATE KEY UPDATE in MySQL style dialects, or INSERT .. ON CONFLICT in PostgreSQL style dialects, as suggested in the original feature request.

Closing this as a duplicate of the other issues.