Type Parameters:: R - The record type of the table being inserted into

All Superinterfaces:: Attachable, AttachableQueryPart, ConditionProvider, DMLQuery<R>, Flow.Publisher<Integer>, Insert<R>, Publisher<Integer>, org.reactivestreams.Publisher<Integer>, Query, QueryPart, RowCountQuery, Serializable, Statement, StoreQuery<R>

public interface InsertQuery<R extends Record> extends StoreQuery<R>, Insert<R>, ConditionProvider

An INSERT statement (model API).

This type is the model API representation of a Insert statement, which can be mutated after creation. The advantage of this API compared to the DSL API is a more simple approach to writing dynamic SQL.

Instances can be created using DSLContext.insertQuery(Table) and overloads.

Author:: Lukas Eder

Method Summary

Modifier and Type

Method

Description

void

addConditions(Collection<? extends Condition> conditions)

Adds new conditions to the query, connecting them to existing conditions with Operator.AND.

void

addConditions(Condition condition)

Adds a new condition to the query, connecting it to existing conditions with Operator.AND.

void

addConditions(Condition... conditions)

Adds new conditions to the query, connecting them to existing conditions with Operator.AND.

void

addConditions(Operator operator, Collection<? extends Condition> conditions)

Adds new conditions to the query, connecting them to existing conditions with the provided operator.

void

addConditions(Operator operator, Condition condition)

Adds a new condition to the query, connecting it to existing conditions with the provided operator.

void

addConditions(Operator operator, Condition... conditions)

Adds new conditions to the query, connecting them to existing conditions with the provided operator.

void

addRecord(R record)

Short for calling newRecord() and StoreQuery.setRecord(Record).

<T> void

addValueForUpdate(Field<T> field, Field<T> value)

Add a value to the ON DUPLICATE KEY UPDATE clause of this INSERT statement, where this is supported.

<T> void

addValueForUpdate(Field<T> field, T value)

Add a value to the ON DUPLICATE KEY UPDATE clause of this INSERT statement, where this is supported.

void

addValuesForUpdate(Map<?,?> map)

Add multiple values to the ON DUPLICATE KEY UPDATE clause of this INSERT statement, where this is supported.

R

getReturnedRecord()

The record holding returned values as specified by any of the StoreQuery.setReturning() methods.

@NotNull Result<R>

getReturnedRecords()

The records holding returned values as specified by any of the StoreQuery.setReturning() methods.

void

newRecord()

Adds a new Record to the insert statement for multi-record inserts

void

onConflict(Collection<? extends Field<?>> fields)

Whether a ON CONFLICT clause should be added to this INSERT statement.

void

onConflict(Field<?>... fields)

Whether a ON CONFLICT clause should be added to this INSERT statement.

void

onConflictOnConstraint(Constraint constraint)

Whether use a ON CONFLICT or ON CONFLICT ON CONSTRAINT clause in this INSERT statement.

void

onConflictOnConstraint(Name constraint)

Whether use a ON CONFLICT or ON CONFLICT ON CONSTRAINT clause in this INSERT statement.

void

onConflictOnConstraint(UniqueKey<R> constraint)

Whether use a ON CONFLICT or ON CONFLICT ON CONSTRAINT clause in this INSERT statement.

void

onConflictWhere(Condition condition)

Adds a new condition the onConflict(Field...) clause.

void

onDuplicateKeyIgnore(boolean flag)

Whether an ON DUPLICATE KEY IGNORE clause should be added to this INSERT statement.

void

onDuplicateKeyUpdate(boolean flag)

Whether a ON DUPLICATE KEY UPDATE clause should be added to this INSERT statement.

void

setDefaultValues()

Set an empty record with the DEFAULT VALUES clause.

void

setRecordForUpdate(R record)

Add multiple values to the ON DUPLICATE KEY UPDATE clause of this INSERT statement, where this is supported.

void

setReturning()

Configure the INSERT or UPDATE statement to return all fields in R.

void

setReturning(Collection<? extends SelectFieldOrAsterisk> fields)

Configure the INSERT or UPDATE statement to return a list of fields in R.

void

setReturning(Identity<R,?> identity)

Configure the INSERT or UPDATE statement to return the generated identity value.

void

setReturning(SelectFieldOrAsterisk... fields)

Configure the INSERT or UPDATE statement to return a list of fields in R.

void

setSelect(Collection<? extends Field<?>> fields, Select<?> select)

Use a SELECT statement as the source of values for the INSERT statement.

void

setSelect(Field<?>[] fields, Select<?> select)

Use a SELECT statement as the source of values for the INSERT statement.

Methods inherited from interface org.jooq.Attachable
attach, configuration, detach

Methods inherited from interface org.jooq.AttachableQueryPart
getBindValues, getParam, getParams, getSQL, getSQL

Methods inherited from interface org.jooq.Publisher
subscribe

Methods inherited from interface org.reactivestreams.Publisher
subscribe

Methods inherited from interface org.jooq.Query
bind, bind, cancel, execute, executeAsync, executeAsync, isExecutable, keepStatement, poolable, queryTimeout

Methods inherited from interface org.jooq.QueryPart
$replace, $replace, $traverse, $traverse, equals, hashCode, toString

Methods inherited from interface org.jooq.StoreQuery
addValue, addValue, addValues, getResult, setRecord

Method Details

newRecord

@Support void newRecord()

Adds a new Record to the insert statement for multi-record inserts
Calling this method will cause subsequent calls to StoreQuery.addValue(Field, Object) (and similar) to fill the next record.
If this call is not followed by StoreQuery.addValue(Field, Object) calls, then this call has no effect.
If this call is done on a fresh insert statement (without any values yet), then this call has no effect either.
addRecord

@Support void addRecord(R record)

Short for calling newRecord() and StoreQuery.setRecord(Record).

Parameters:

record - The record to add to this insert statement.
onConflict

@Support void onConflict(Field<?>... fields)

Whether a ON CONFLICT clause should be added to this INSERT statement.
When setting this flag to true, be sure to also add values "for update" using the addValueForUpdate(Field, Field) methods.
See Also:
- InsertOnDuplicateStep.onDuplicateKeyUpdate()
onConflict

@Support void onConflict(Collection<? extends Field<?>> fields)

Whether a ON CONFLICT clause should be added to this INSERT statement.
When setting this flag to true, be sure to also add values "for update" using the addValueForUpdate(Field, Field) methods.
See Also:
- InsertOnDuplicateStep.onDuplicateKeyUpdate()
onConflictOnConstraint

@Support({AURORA_POSTGRES,COCKROACHDB,CUBRID,DB2,DERBY,DUCKDB,EXASOL,FIREBIRD_3_0,H2,HANA,HSQLDB,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES_9_5,SQLSERVER,SYBASE,YUGABYTEDB}) void onConflictOnConstraint(Name constraint)

Whether use a ON CONFLICT or ON CONFLICT ON CONSTRAINT clause in this INSERT statement.
onConflictOnConstraint

@Support({AURORA_POSTGRES,COCKROACHDB,CUBRID,DB2,DERBY,DUCKDB,EXASOL,FIREBIRD_3_0,H2,HANA,HSQLDB,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES_9_5,SQLSERVER,SYBASE,YUGABYTEDB}) void onConflictOnConstraint(Constraint constraint)

Whether use a ON CONFLICT or ON CONFLICT ON CONSTRAINT clause in this INSERT statement.
onConflictOnConstraint

@Support void onConflictOnConstraint(UniqueKey<R> constraint)

Whether use a ON CONFLICT or ON CONFLICT ON CONSTRAINT clause in this INSERT statement.
onDuplicateKeyUpdate

@Support({AURORA_MYSQL,AURORA_POSTGRES,COCKROACHDB,CUBRID,DB2,DERBY,DUCKDB,EXASOL,FIREBIRD_3_0,H2,HANA,HSQLDB,INFORMIX,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES_9_5,SNOWFLAKE,SQLITE,SQLSERVER,SYBASE,TERADATA,YUGABYTEDB}) void onDuplicateKeyUpdate(boolean flag)

Whether a ON DUPLICATE KEY UPDATE clause should be added to this INSERT statement.
When setting this flag to true, be sure to also add values "for update" using the addValueForUpdate(Field, Field) methods.
The ON DUPLICATE KEY UPDATE flag is mutually exclusive with the ON DUPLICATE KEY IGNORE flag (see onDuplicateKeyIgnore(boolean). Setting one will unset the other
See Also:
- InsertOnDuplicateStep.onDuplicateKeyUpdate()

onDuplicateKeyIgnore

@Support void onDuplicateKeyIgnore(boolean flag)

Whether an ON DUPLICATE KEY IGNORE clause should be added to this INSERT statement.

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` and `SQLDialect.SQLITE`	`INSERT INTO … ON CONFLICT DO NOTHING`
`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] )`

The ON DUPLICATE KEY UPDATE flag is mutually exclusive with the ON DUPLICATE KEY IGNORE flag (see onDuplicateKeyIgnore(boolean). Setting one will unset the other

addValueForUpdate

@Support({AURORA_MYSQL,AURORA_POSTGRES,COCKROACHDB,CUBRID,DB2,DERBY,DUCKDB,EXASOL,FIREBIRD_3_0,H2,HANA,HSQLDB,INFORMIX,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES_9_5,SNOWFLAKE,SQLITE,SQLSERVER,SYBASE,TERADATA,YUGABYTEDB}) <T> void addValueForUpdate(Field<T> field, T value)

Add a value to the ON DUPLICATE KEY UPDATE clause of this INSERT statement, where this is supported.
See Also:
- InsertOnDuplicateStep.onDuplicateKeyUpdate()
addValueForUpdate

@Support({AURORA_MYSQL,AURORA_POSTGRES,COCKROACHDB,CUBRID,DB2,DERBY,DUCKDB,EXASOL,FIREBIRD_3_0,H2,HANA,HSQLDB,INFORMIX,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES_9_5,SNOWFLAKE,SQLITE,SQLSERVER,SYBASE,TERADATA,YUGABYTEDB}) <T> void addValueForUpdate(Field<T> field, Field<T> value)

Add a value to the ON DUPLICATE KEY UPDATE clause of this INSERT statement, where this is supported.
See Also:
- InsertOnDuplicateStep.onDuplicateKeyUpdate()
addValuesForUpdate

@Support({AURORA_MYSQL,AURORA_POSTGRES,COCKROACHDB,CUBRID,DB2,DERBY,DUCKDB,EXASOL,FIREBIRD_3_0,H2,HANA,HSQLDB,INFORMIX,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES_9_5,SNOWFLAKE,SQLITE,SQLSERVER,SYBASE,TERADATA,YUGABYTEDB}) void addValuesForUpdate(Map<?,?> map)

Add multiple values to the ON DUPLICATE KEY UPDATE clause of this INSERT statement, where this is supported.
Please assure that key/value pairs have matching <T> types. Values can either be of type <T> or Field<T>
See Also:
- InsertOnDuplicateStep.onDuplicateKeyUpdate()
setRecordForUpdate

@Support({AURORA_MYSQL,AURORA_POSTGRES,COCKROACHDB,CUBRID,DB2,DERBY,DUCKDB,EXASOL,FIREBIRD_3_0,H2,HANA,HSQLDB,INFORMIX,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES_9_5,SNOWFLAKE,SQLITE,SQLSERVER,SYBASE,TERADATA,YUGABYTEDB}) void setRecordForUpdate(R record)

Add multiple values to the ON DUPLICATE KEY UPDATE clause of this INSERT statement, where this is supported.
This works like StoreQuery.setRecord(Record).

Parameters:

record - The record to add to this insert statement.
onConflictWhere

@Support({AURORA_POSTGRES,POSTGRES_9_5,SQLITE,YUGABYTEDB}) void onConflictWhere(Condition condition)

Adds a new condition the onConflict(Field...) clause.
This is for use with SQLDialect.POSTGRES's onConflict(Field...) clause.

Parameters:

condition - The condition
addConditions

@Support({AURORA_MYSQL,AURORA_POSTGRES,COCKROACHDB,CUBRID,DERBY,DUCKDB,EXASOL,H2,HANA,MARIADB,MYSQL,ORACLE,POSTGRES_9_5,SNOWFLAKE,SQLITE,SQLSERVER,YUGABYTEDB}) void addConditions(Condition condition)

Adds a new condition to the query, connecting it to existing conditions with Operator.AND.
This is for use with SQLDialect.POSTGRES's onConflict(Field...) clause.

Specified by:

addConditions in interface ConditionProvider

Parameters:

condition - The condition
addConditions

@Support({AURORA_MYSQL,AURORA_POSTGRES,COCKROACHDB,CUBRID,DERBY,DUCKDB,EXASOL,H2,HANA,MARIADB,MYSQL,ORACLE,POSTGRES_9_5,SNOWFLAKE,SQLITE,SQLSERVER,YUGABYTEDB}) void addConditions(Condition... conditions)

Adds new conditions to the query, connecting them to existing conditions with Operator.AND.
This is for use with SQLDialect.POSTGRES's onConflict(Field...) clause.

Specified by:

addConditions in interface ConditionProvider

Parameters:

conditions - The condition
addConditions

@Support({AURORA_MYSQL,AURORA_POSTGRES,COCKROACHDB,CUBRID,DERBY,DUCKDB,EXASOL,H2,HANA,MARIADB,MYSQL,ORACLE,POSTGRES_9_5,SNOWFLAKE,SQLITE,SQLSERVER,YUGABYTEDB}) void addConditions(Collection<? extends Condition> conditions)

Adds new conditions to the query, connecting them to existing conditions with Operator.AND.
This is for use with SQLDialect.POSTGRES's onConflict(Field...) clause.

Specified by:

addConditions in interface ConditionProvider

Parameters:

conditions - The condition
addConditions

@Support({AURORA_MYSQL,AURORA_POSTGRES,COCKROACHDB,CUBRID,DERBY,DUCKDB,EXASOL,H2,HANA,MARIADB,MYSQL,ORACLE,POSTGRES_9_5,SNOWFLAKE,SQLITE,SQLSERVER,YUGABYTEDB}) void addConditions(Operator operator, Condition condition)

Adds a new condition to the query, connecting it to existing conditions with the provided operator.
This is for use with SQLDialect.POSTGRES's onConflict(Field...) clause.

Specified by:

addConditions in interface ConditionProvider

Parameters:

condition - The condition
addConditions

@Support({AURORA_MYSQL,AURORA_POSTGRES,COCKROACHDB,CUBRID,DERBY,DUCKDB,EXASOL,H2,HANA,MARIADB,MYSQL,ORACLE,POSTGRES_9_5,SNOWFLAKE,SQLITE,SQLSERVER,YUGABYTEDB}) void addConditions(Operator operator, Condition... conditions)

Adds new conditions to the query, connecting them to existing conditions with the provided operator.
This is for use with SQLDialect.POSTGRES's onConflict(Field...) clause.

Specified by:

addConditions in interface ConditionProvider

Parameters:

conditions - The condition
addConditions

@Support({AURORA_MYSQL,AURORA_POSTGRES,COCKROACHDB,CUBRID,DERBY,DUCKDB,EXASOL,H2,HANA,MARIADB,MYSQL,ORACLE,POSTGRES_9_5,SNOWFLAKE,SQLITE,SQLSERVER,YUGABYTEDB}) void addConditions(Operator operator, Collection<? extends Condition> conditions)

Adds new conditions to the query, connecting them to existing conditions with the provided operator.
This is for use with SQLDialect.POSTGRES's onConflict(Field...) clause.

Specified by:

addConditions in interface ConditionProvider

Parameters:

conditions - The condition
setDefaultValues

@Support({ACCESS,ASE,AURORA_MYSQL,AURORA_POSTGRES,BIGQUERY,CLICKHOUSE,COCKROACHDB,CUBRID,DB2,DERBY,DUCKDB,EXASOL,FIREBIRD,H2,HANA,HSQLDB,IGNITE,INFORMIX,INGRES,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES,REDSHIFT,SNOWFLAKE,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA,VERTICA,YUGABYTEDB}) void setDefaultValues()

Set an empty record with the DEFAULT VALUES clause.
setSelect

@Support void setSelect(Field<?>[] fields, Select<?> select)

Use a SELECT statement as the source of values for the INSERT statement.
setSelect

@Support void setSelect(Collection<? extends Field<?>> fields, Select<?> select)

Use a SELECT statement as the source of values for the INSERT statement.
setReturning

@Support void setReturning()

Configure the INSERT or UPDATE statement to return all fields in R.
This feature works with INSERT statements for all SQL dialects
Specified by:

setReturning in interface StoreQuery<R extends Record>

See Also:
- StoreQuery.getReturnedRecords()
setReturning

@Support void setReturning(Identity<R,?> identity)

Configure the INSERT or UPDATE statement to return the generated identity value.
This feature works with INSERT statements for all SQL dialects
Specified by:

setReturning in interface StoreQuery<R extends Record>

Parameters:

identity - The table's identity

See Also:
- StoreQuery.getReturnedRecords()
setReturning

@Support void setReturning(SelectFieldOrAsterisk... fields)

Configure the INSERT or UPDATE statement to return a list of fields in R.
This feature works with INSERT statements for all SQL dialects
Specified by:

setReturning in interface StoreQuery<R extends Record>

Parameters:

fields - Fields to be returned

See Also:
- StoreQuery.getReturnedRecords()
setReturning

@Support void setReturning(Collection<? extends SelectFieldOrAsterisk> fields)

Configure the INSERT or UPDATE statement to return a list of fields in R.
This feature works with INSERT statements for all SQL dialects
Specified by:

setReturning in interface StoreQuery<R extends Record>

Parameters:

fields - Fields to be returned

See Also:
- StoreQuery.getReturnedRecords()
getReturnedRecord

@Nullable @Support R getReturnedRecord()
The record holding returned values as specified by any of the StoreQuery.setReturning() methods.
If the insert statement returns several records, this is the same as calling getReturnedRecords().get(0)
This implemented differently for every dialect:
- Firebird and Postgres have native support for INSERT … RETURNING and UPDATE … RETURNING clauses
- HSQLDB, Oracle, and DB2 JDBC drivers allow for retrieving any table column as "generated key" in one statement
- Derby, H2, Ingres, MySQL, SQL Server only allow for retrieving IDENTITY column values as "generated key". If other fields are requested, a second statement is issued. Client code must assure transactional integrity between the two statements.
- Sybase and SQLite allow for retrieving IDENTITY values as @@identity or last_inserted_rowid() values. Those values are fetched in a separate SELECT statement. If other fields are requested, a second statement is issued. Client code must assure transactional integrity between the two statements.
This feature works with INSERT statements for all SQL dialects
Specified by:

getReturnedRecord in interface StoreQuery<R extends Record>

Returns:

The returned value as specified by any of the StoreQuery.setReturning() methods. This may return null in case jOOQ could not retrieve any generated keys from the JDBC driver.

See Also:
- StoreQuery.getReturnedRecords()
getReturnedRecords

@NotNull @Support @NotNull Result<R> getReturnedRecords()
The records holding returned values as specified by any of the StoreQuery.setReturning() methods.
This implemented differently for every dialect:
- Firebird and Postgres have native support for INSERT … RETURNING and UPDATE … RETURNING clauses
- HSQLDB, Oracle, and DB2 JDBC drivers allow for retrieving any table column as "generated key" in one statement
- Derby, H2, Ingres, MySQL, SQL Server only allow for retrieving IDENTITY column values as "generated key". If other fields are requested, a second statement is issued. Client code must assure transactional integrity between the two statements.
- Sybase and SQLite allow for retrieving IDENTITY values as @@identity or last_inserted_rowid() values. Those values are fetched in a separate SELECT statement. If other fields are requested, a second statement is issued. Client code must assure transactional integrity between the two statements.
[#5070] Due to an early API design flaw, this method historically returns the type R, not a more generic type Record. This means that only actual columns in R can be returned. For a more generic set of column expressions, use StoreQuery.getResult() instead.
This feature works with INSERT statements for all SQL dialects
Specified by:

getReturnedRecords in interface StoreQuery<R extends Record>

Returns:
The returned values as specified by any of the StoreQuery.setReturning() methods. Note:
- Not all databases / JDBC drivers support returning several values on multi-row inserts!
- This may return an empty Result in case jOOQ could not retrieve any generated keys from the JDBC driver.

Interface InsertQuery<R extends Record>

Method Summary

Methods inherited from interface org.jooq.Attachable

Methods inherited from interface org.jooq.AttachableQueryPart

Methods inherited from interface org.jooq.Publisher

Methods inherited from interface org.reactivestreams.Publisher

Methods inherited from interface org.jooq.Query

Methods inherited from interface org.jooq.QueryPart

Methods inherited from interface org.jooq.StoreQuery

Method Details

newRecord

addRecord

onConflict

onConflict

onConflictOnConstraint

onConflictOnConstraint

onConflictOnConstraint

onDuplicateKeyUpdate

onDuplicateKeyIgnore

addValueForUpdate

addValueForUpdate

addValuesForUpdate

setRecordForUpdate

onConflictWhere

addConditions

addConditions

addConditions

addConditions

addConditions

addConditions

setDefaultValues

setSelect

setSelect

setReturning

setReturning

setReturning

setReturning

getReturnedRecord

getReturnedRecords