Jooq - Ignore Duplicates

Question

I'm currently using Jooq for a project, but I need a way to ignore duplicate keys on insert.

I've got an array of objects I want to write into a table but if they already exist determined by a composite unique index on START_TS and EVENT_TYPE I want the insert to silently fail.

My Code looks something like this:

InsertValuesStep<MyRecord> query = fac.insertInto(MY_REC,
                MY_REC.START_TS,
                MY_REC.STOP_TS,
                MY_REC.EVENT_DATA,
                MY_REC.EVENT_TYPE,
                MY_REC.PUBLISHED_TS,
                MY_REC.MY_ID
                );

        for(int i=0;i<recs.length;i++)
        {
            MyClass evt = recs[i]; 
            query.values(
                    new java.sql.Date(evt.startTS.getTime()),
                    (evt.stopTS == null) ? null : new java.sql.Date(evt.stopTS.getTime()),
                    evt.eventData,
                    evt.type.name(),
                    date,
                    id)

        }

        query.execute();

A solution like this would be ideal: https://stackoverflow.com/a/4920619/416338

I figure I need to add something like:

.onDuplicateKeyUpdate().set(MY_REC.EVENT_TYPE,MY_REC.EVENT_TYPE);

But whatever I add it still seems to throw an error on duplicates.

I don't know this tool, but in plain SQL you can do something like `INSERT INTO table (SELECT col1, col2 FROM (VALUES(val1, val2)) as temp(col1, col2) WHERE NOT EXISTS (SELECT '1' FROM table where col1 = val1))` which has the virtue of working for pretty much every RDBMS (or a slight variation). — Clockwork-Muse, Apr 20 '12 at 15:47
@LukasEder `INSERT INTO t (column1, ... columnN) SELECT ... FROM some other tables ...` is supported by almost all (if not all) DBMS. — ypercubeᵀᴹ, Apr 20 '12 at 22:29

score 2 · Accepted Answer · answered Apr 20 '12 at 22:25

Support for MySQL's INSERT IGNORE INTO syntax is on the roadmap for jOOQ 2.3.0. This had been discussed recently on the jOOQ user group. This syntax will be simulated in all other SQL dialects that support the SQL MERGE statement.

In the mean time, as a workaround, you could try to insert one record at a time

Jooq - Ignore Duplicates

1 Answers1