org.jooq

Interface InsertOnDuplicateStep<R extends Record>

All Superinterfaces:

Attachable, AutoCloseable, Insert<R>, InsertFinalStep<R>, InsertReturningStep<R>, Query, QueryPart, Serializable, Statement

All Known Subinterfaces:

InsertSetMoreStep<R>, InsertValuesStep1<R,T1>, InsertValuesStep10<R,T1,T2,T3,T4,T5,T6,T7,T8,T9,T10>, InsertValuesStep11<R,T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,T11>, InsertValuesStep12<R,T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,T11,T12>, InsertValuesStep13<R,T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,T11,T12,T13>, InsertValuesStep14<R,T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,T11,T12,T13,T14>, InsertValuesStep15<R,T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,T11,T12,T13,T14,T15>, InsertValuesStep16<R,T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,T11,T12,T13,T14,T15,T16>, InsertValuesStep17<R,T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,T11,T12,T13,T14,T15,T16,T17>, InsertValuesStep18<R,T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,T11,T12,T13,T14,T15,T16,T17,T18>, InsertValuesStep19<R,T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,T11,T12,T13,T14,T15,T16,T17,T18,T19>, InsertValuesStep2<R,T1,T2>, InsertValuesStep20<R,T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,T11,T12,T13,T14,T15,T16,T17,T18,T19,T20>, InsertValuesStep21<R,T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,T11,T12,T13,T14,T15,T16,T17,T18,T19,T20,T21>, InsertValuesStep22<R,T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,T11,T12,T13,T14,T15,T16,T17,T18,T19,T20,T21,T22>, InsertValuesStep3<R,T1,T2,T3>, InsertValuesStep4<R,T1,T2,T3,T4>, InsertValuesStep5<R,T1,T2,T3,T4,T5>, InsertValuesStep6<R,T1,T2,T3,T4,T5,T6>, InsertValuesStep7<R,T1,T2,T3,T4,T5,T6,T7>, InsertValuesStep8<R,T1,T2,T3,T4,T5,T6,T7,T8>, InsertValuesStep9<R,T1,T2,T3,T4,T5,T6,T7,T8,T9>, InsertValuesStepN<R>

public interface InsertOnDuplicateStep<R extends Record>
extends InsertReturningStep<R>

This type is used for the Insert's DSL API.

Example:

 DSLContext create = DSL.using(configuration);

 create.insertInto(table, field1, field2)
       .values(value1, value2)
       .values(value3, value4)
       .onDuplicateKeyUpdate()
       .set(field1, value1)
       .set(field2, value2)
       .execute();
 

Referencing XYZ*Step types directly from client code

It is usually not recommended to reference any XYZ*Step types directly from client code, or assign them to local variables. When writing dynamic SQL, creating a statement's components dynamically, and passing them to the DSL API statically is usually a better choice. See the manual's section about dynamic SQL for details: https://www.jooq.org/doc/latest/manual/sql-building/dynamic-sql.

Drawbacks of referencing the XYZ*Step types directly:

• They're operating on mutable implementations (as of jOOQ 3.x)
• They're less composable and not easy to get right when dynamic SQL gets complex
• They're less readable
• They might have binary incompatible changes between minor releases

Author:

Lukas Eder

Method Summary

Modifier and Type	Method and Description
InsertOnConflictDoUpdateStep<R>	onConflict(Collection<? extends Field<?>> keys) Add an ON CONFLICT clause to this insert query.
InsertOnConflictDoUpdateStep<R>	onConflict(Field<?>... keys) Add an ON CONFLICT clause to this insert query.
InsertReturningStep<R>	onConflictDoNothing() Add an ON CONFLICT DO NOTHING clause to this insert query.
InsertOnConflictDoUpdateStep<R>	onConflictOnConstraint(Constraint constraint) Add a ON CONFLICT ON CONSTRAINT clause to this query.
InsertOnConflictDoUpdateStep<R>	onConflictOnConstraint(Name constraint) Add a ON CONFLICT ON CONSTRAINT clause to this query.
InsertOnConflictDoUpdateStep<R>	onConflictOnConstraint(UniqueKey<R> constraint) Add a ON CONFLICT ON CONSTRAINT clause to this query.
InsertReturningStep<R>	onDuplicateKeyIgnore() Add an ON DUPLICATE KEY IGNORE clause to this insert query.
InsertOnDuplicateSetStep<R>	onDuplicateKeyUpdate() Add an ON DUPLICATE KEY UPDATE clause to this insert query.

Methods inherited from interface org.jooq.InsertReturningStep

returning, returning, returning, returningResult, returningResult, returningResult, returningResult, returningResult, returningResult, returningResult, returningResult, returningResult, returningResult, returningResult, returningResult, returningResult, returningResult, returningResult, returningResult, returningResult, returningResult, returningResult, returningResult, returningResult, returningResult, returningResult, returningResult

Methods inherited from interface org.jooq.Query

bind, bind, cancel, close, execute, executeAsync, executeAsync, getBindValues, getParam, getParams, getSQL, getSQL, getSQL, isExecutable, keepStatement, queryTimeout

Methods inherited from interface org.jooq.QueryPart

equals, hashCode, toString

Methods inherited from interface org.jooq.Attachable

attach, configuration, detach

Method Detail

onConflictOnConstraint

@Support(value={AURORA_POSTGRES,CUBRID,DB2,FIREBIRD_3_0,HSQLDB,ORACLE,POSTGRES_9_5,SQLSERVER,SYBASE})
InsertOnConflictDoUpdateStep<R> onConflictOnConstraint(Constraint constraint)

Add a ON CONFLICT ON CONSTRAINT clause to this query.

onConflictOnConstraint

@Support(value={AURORA_POSTGRES,CUBRID,DB2,FIREBIRD_3_0,HSQLDB,ORACLE,POSTGRES_9_5,SQLSERVER,SYBASE})
InsertOnConflictDoUpdateStep<R> onConflictOnConstraint(Name constraint)

Add a ON CONFLICT ON CONSTRAINT clause to this query.

onConflictOnConstraint

@Support
InsertOnConflictDoUpdateStep<R> onConflictOnConstraint(UniqueKey<R> constraint)

Add a ON CONFLICT ON CONSTRAINT clause to this query.

onConflict

@Support
InsertOnConflictDoUpdateStep<R> onConflict(Field<?>... keys)

Add an ON CONFLICT clause to this insert query.

Only SQLDialect.POSTGRES has native support for this clause. The other dialects can emulate it using MERGE, if table meta data is available.

onConflict

@Support
InsertOnConflictDoUpdateStep<R> onConflict(Collection<? extends Field<?>> keys)

Add an ON CONFLICT clause to this insert query.

Only SQLDialect.POSTGRES has native support for this clause. The other dialects can emulate it using MERGE, if table meta data is available.

onConflictDoNothing

@Support
InsertReturningStep<R> onConflictDoNothing()

Add an ON CONFLICT DO NOTHING clause to this insert query.

Only SQLDialect.POSTGRES has native support for this clause. The other dialects can emulate it using MERGE, if table meta data is available.

onDuplicateKeyUpdate

@Support(value={AURORA_MYSQL,AURORA_POSTGRES,CUBRID,DB2,H2,HSQLDB,INFORMIX,MARIADB,MYSQL,ORACLE,POSTGRES_9_5,SQLSERVER,SYBASE})
InsertOnDuplicateSetStep<R> onDuplicateKeyUpdate()

Add an ON DUPLICATE KEY UPDATE clause to this insert query.

This will try to INSERT a record. If there is a primary key or unique key in this INSERT statement's affected table that matches the value being inserted, then the UPDATE clause is executed instead.

MySQL and CUBRID natively implements this type of clause. jOOQ can emulate this clause using a MERGE statement on some other databases. The conditions for a RDBMS to emulate this clause are:

• The INSERT statement's table is a Table with a Table.getPrimaryKey()
• The RDBMS supports the MERGE clause (see DSLContext.mergeInto(Table)).

H2 supports this clause in MySQL mode.

onDuplicateKeyIgnore

@Support
InsertReturningStep<R> onDuplicateKeyIgnore()

Add an ON DUPLICATE KEY IGNORE clause to this insert query.

This will try to INSERT a record. If there is a primary key or unique key in this INSERT statement's affected table that matches the value being inserted, then the INSERT statement is ignored.

This clause is not actually supported in this form by any database, but can be emulated as such:

Dialect	Emulation
SQLDialect.MYSQL and SQLDialect.MARIADB	INSERT IGNORE INTO ..
SQLDialect.POSTGRES_9_5	INSERT INTO .. ON CONFLICT DO NOTHING
SQLDialect.CUBRID	INSERT INTO .. ON DUPLICATE KEY UPDATE [any-field] = [any-field]
SQLDialect.DB2 SQLDialect.HSQLDB SQLDialect.ORACLE SQLDialect.SQLSERVER SQLDialect.SYBASE	MERGE INTO [dst] USING ([values]) ON [dst.key] = [values.key] WHEN NOT MATCHED THEN INSERT ..
All the others	INSERT INTO [dst] ( ... ) SELECT [values] WHERE NOT EXISTS ( SELECT 1 FROM [dst] WHERE [dst.key] = [values.key] )