Module org.jooq

Package org.jooq

Interface InsertQuery<R extends Record>

Type Parameters:

R - The record type of the table being inserted into

All Superinterfaces:

Attachable, AttachableQueryPart, AutoCloseable, ConditionProvider, 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(); 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	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, close, execute, executeAsync, executeAsync, isExecutable, keepStatement, poolable, queryTimeout

Methods inherited from interface org.jooq.QueryPart

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(); 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,EXASOL,FIREBIRD_3_0,HSQLDB,MEMSQL,MYSQL,ORACLE,POSTGRES_9_5,SQLSERVER,SYBASE})
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,EXASOL,FIREBIRD_3_0,HSQLDB,MEMSQL,MYSQL,ORACLE,POSTGRES_9_5,SQLSERVER,SYBASE})
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,EXASOL,H2,HSQLDB,INFORMIX,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES_9_5,SNOWFLAKE,SQLITE,SQLSERVER,SYBASE,TERADATA})
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,EXASOL,H2,HSQLDB,INFORMIX,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES_9_5,SNOWFLAKE,SQLITE,SQLSERVER,SYBASE,TERADATA})
<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,EXASOL,H2,HSQLDB,INFORMIX,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES_9_5,SNOWFLAKE,SQLITE,SQLSERVER,SYBASE,TERADATA})
<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,EXASOL,H2,HSQLDB,INFORMIX,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES_9_5,SNOWFLAKE,SQLITE,SQLSERVER,SYBASE,TERADATA})
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()

onConflictWhere

@Support({AURORA_POSTGRES,POSTGRES_9_5,SQLITE})
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_POSTGRES,COCKROACHDB,CUBRID,DERBY,EXASOL,H2,MARIADB,ORACLE,POSTGRES_9_5,SQLITE,SQLSERVER})
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_POSTGRES,COCKROACHDB,CUBRID,DERBY,EXASOL,H2,MARIADB,ORACLE,POSTGRES_9_5,SQLITE,SQLSERVER})
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_POSTGRES,COCKROACHDB,CUBRID,DERBY,EXASOL,H2,MARIADB,ORACLE,POSTGRES_9_5,SQLITE,SQLSERVER})
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_POSTGRES,COCKROACHDB,CUBRID,DERBY,EXASOL,H2,MARIADB,ORACLE,POSTGRES_9_5,SQLITE,SQLSERVER})
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_POSTGRES,COCKROACHDB,CUBRID,DERBY,EXASOL,H2,MARIADB,ORACLE,POSTGRES_9_5,SQLITE,SQLSERVER})
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_POSTGRES,COCKROACHDB,CUBRID,DERBY,EXASOL,H2,MARIADB,ORACLE,POSTGRES_9_5,SQLITE,SQLSERVER})
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({ASE,AURORA_MYSQL,AURORA_POSTGRES,COCKROACHDB,CUBRID,DB2,DERBY,EXASOL,FIREBIRD,H2,HSQLDB,INGRES,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES,SQLDATAWAREHOUSE,SQLITE,SQLSERVER,SYBASE,TERADATA})
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.