insert ignore multiple pojo with JOOQ

Question

I would like to achieve something like the following with Jooq:

INSERT IGNORE INTO table1 (col1,col2,col3) 
VALUES ('val1','val2','val3'),('val4','val5','val6');

but using a List of pojos

something like:

public list insertIgnoreMulti(List records) { // ignore if exists return dsl.batchStore(records).execute(); }

But I can't seem to find documentation on that.

Thanks.

I have tried Lukas' example:

public Loader<ScopusRecord> insertIgnoreMulti(List<ScopusRecord> records) throws IOException {
                return dsl.
                    loadInto(Tables.SCOPUS).
                    onDuplicateKeyIgnore().
                    loadRecords(records).
                    fields(Tables.SCOPUS.fields()).
                    execute();
    }

but I get an exception and the following in my log:

> org.jooq.tools.LoggerListener - Executing query          : select 1 as
> `one` from dual where exists (select `db`.`Scopus`.`id_word`,
> `db`.`Scopus`.`word`, `db`.`Scopus`.`frequency`,
> `db`.`Scopus`.`type_word`, `db`.`Scopus`.`phonetic`,
> `db`.`Scopus`.`norma`, `db`.`Scopus`.`status_word`,
> `db`.`Scopus`.`wiki`, `db`.`Scopus`.`aspl`, `db`.`Scopus`.`med`,
> `db`.`Scopus`.`last_process_date` from `db`.`Scopus` where
> `db`.`Scopus`.`id_word` = ?) org.jooq.tools.LoggerListener - -> with
> bind values      : select 1 as `one` from dual where exists (select
> `db`.`Scopus`.`id_word`, `db`.`Scopus`.`word`,
> `db`.`Scopus`.`frequency`, `db`.`Scopus`.`type_word`,
> `db`.`Scopus`.`phonetic`, `db`.`Scopus`.`norma`,
> `db`.`Scopus`.`status_word`, `db`.`Scopus`.`wiki`,
> `db`.`Scopus`.`aspl`, `db`.`Scopus`.`med`,
> `db`.`Scopus`.`last_process_date` from `db`.`Scopus` where
> `db`.`Scopus`.`id_word` = null) org.jooq.tools.StopWatch - Query
> executed           : Total: 16.566ms org.jooq.tools.StopWatch -
> Finishing                : Total: 19.398ms, +2.832ms
> org.jooq.tools.LoggerListener - Fetched result           : +----+
> org.jooq.tools.LoggerListener -                          : | one|
> org.jooq.tools.LoggerListener -                          : +----+
> org.jooq.tools.LoggerListener - Executing query          : insert into
> `db`.`Scopus` (`id_word`, `word`, `frequency`, `type_word`,
> `phonetic`, `norma`, `status_word`, `wiki`, `aspl`, `med`,
> `last_process_date`) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
> org.jooq.tools.LoggerListener - -> with bind values      : insert into
> `db`.`Scopus` (`id_word`, `word`, `frequency`, `type_word`,
> `phonetic`, `norma`, `status_word`, `wiki`, `aspl`, `med`,
> `last_process_date`) values (null, 'INTERNAL', 0, 'ENG', 'ntrnAl',
> null, null, null, 1, null, null) o.s.b.f.xml.XmlBeanDefinitionReader -
> Loading XML bean definitions from class path resource
> [org/springframework/jdbc/support/sql-error-codes.xml]
> o.s.j.support.SQLErrorCodesFactory - SQLErrorCodes loaded: [DB2,
> Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase,
> Hana] org.jooq.tools.LoggerListener - Exception                
> org.springframework.dao.DuplicateKeyException: jOOQ; SQL [insert into
> `db`.`Scopus` (`id_word`, `word`, `frequency`, `type_word`,
> `phonetic`, `norma`, `status_word`, `wiki`, `aspl`, `med`,
> `last_process_date`) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)];
> Duplicate entry 'INTERNAL' for key 'IX_Scopus'; nested exception is
> com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
> Duplicate entry 'INTERNAL' for key 'IX_Scopus'    at
> org.jooq_3.9.1.MYSQL.debug(Unknown Source)    at
> org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
>   at
> org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
>   at
> com.op.u2i.exceptions.ExceptionTranslator.exception(ExceptionTranslator.java:39)
>   at
> org.jooq.impl.ExecuteListeners.exception(ExecuteListeners.java:245)
>   at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:364)  at
> org.jooq.impl.LoaderImpl.executeSQL(LoaderImpl.java:808)  at
> org.jooq.impl.LoaderImpl.executeRows(LoaderImpl.java:709)     at
> org.jooq.impl.LoaderImpl.execute(LoaderImpl.java:640)     at
> org.jooq.impl.LoaderImpl.execute(LoaderImpl.java:100)     at
> com.op.u2i.services.db.ScopusService.insertIgnoreMulti(ScopusService.java:46)
> Caused by:
> com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
> Duplicate entry 'INTERNAL' for key 'IX_Scopus'    at
> sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
>   at
> sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
>   at
> sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
>   at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
>   at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)     at
> com.mysql.jdbc.Util.getInstance(Util.java:408)    at
> com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)     at
> com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)    at
> com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)    at
> com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)     at
> com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)  at
> com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2501)   at
> com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
>   at
> com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197)
>   at
> com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
>   at
> com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
>   at
> org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:194)
>   at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:431)  at
> org.jooq.impl.AbstractDMLQuery.execute(AbstractDMLQuery.java:335)     at
> org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:349)   ... 25
> common frames omitted DEBUG org.jooq.tools.StopWatch - Exception      
> : Total: 324.324ms DEBUG org.jooq.tools.StopWatch - Warning           
> : Total: 326.594ms, +2.27ms DEBUG org.jooq.tools.StopWatch - Finishing
> : Total: 327.434ms, +0.84ms

Answer 1

There are several options:

Static SQL

If your SQL statement is sufficiently static, you could use the DSL API for this:

dsl.insertInto(TABLE1)
   .columns(TABLE1.COL1, TABLE1.COL2, TABLE1.COL3)
   .set(dsl.newRecord(TABLE1).values("val1", "val2", "val3"))
   .newRecord()
   .set(dsl.newRecord(TABLE1).values("val4", "val5", "val6"))
   .newRecord()
   .onDuplicateKeyIgnore() // This translates to MySQL's INSERT IGNORE
   .execute();

If your SQL is dynamic (variable length lists of records), you could assign the intermediate DSL API types to local variables and iterate over the records. But in that case, you might be better off with ...

Dynamic SQL and Loader API

... the Loader API:

dsl.loadInto(TABLE1)
   .onDuplicateKeyIgnore() // This translates to MySQL's INSERT IGNORE
   .loadRecords(records)
   .fields(TABLE1.COL1, TABLE1.COL2, TABLE1.COL3)
   .execute();

This API will also allow you to fine-tune bulk, batch, and commit sizes depending on the amount of data you want to insert.