- 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>
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 TypeMethodDescriptionvoid
addConditions
(Collection<? extends Condition> conditions) Adds new conditions to the query, connecting them to existing conditions withOperator.AND
.void
addConditions
(Condition condition) Adds a new condition to the query, connecting it to existing conditions withOperator.AND
.void
addConditions
(Condition... conditions) Adds new conditions to the query, connecting them to existing conditions withOperator.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
Short for callingnewRecord()
andStoreQuery.setRecord(Record)
.<T> void
addValueForUpdate
(Field<T> field, Field<T> value) Add a value to theON DUPLICATE KEY UPDATE
clause of thisINSERT
statement, where this is supported.<T> void
addValueForUpdate
(Field<T> field, T value) Add a value to theON DUPLICATE KEY UPDATE
clause of thisINSERT
statement, where this is supported.void
addValuesForUpdate
(Map<?, ?> map) Add multiple values to theON DUPLICATE KEY UPDATE
clause of thisINSERT
statement, where this is supported.The record holding returned values as specified by any of theStoreQuery.setReturning()
methods.The records holding returned values as specified by any of theStoreQuery.setReturning()
methods.void
Adds a new Record to the insert statement for multi-record insertsvoid
onConflict
(Collection<? extends Field<?>> fields) Whether aON CONFLICT
clause should be added to thisINSERT
statement.void
onConflict
(Field<?>... fields) Whether aON CONFLICT
clause should be added to thisINSERT
statement.void
onConflictOnConstraint
(Constraint constraint) Whether use aON CONFLICT
orON CONFLICT ON CONSTRAINT
clause in thisINSERT
statement.void
onConflictOnConstraint
(Name constraint) Whether use aON CONFLICT
orON CONFLICT ON CONSTRAINT
clause in thisINSERT
statement.void
onConflictOnConstraint
(UniqueKey<R> constraint) Whether use aON CONFLICT
orON CONFLICT ON CONSTRAINT
clause in thisINSERT
statement.void
onConflictWhere
(Condition condition) Adds a new condition theonConflict(Field...)
clause.void
onDuplicateKeyIgnore
(boolean flag) Whether anON DUPLICATE KEY IGNORE
clause should be added to thisINSERT
statement.void
onDuplicateKeyUpdate
(boolean flag) Whether aON DUPLICATE KEY UPDATE
clause should be added to thisINSERT
statement.void
Set an empty record with theDEFAULT VALUES
clause.void
setRecordForUpdate
(R record) Add multiple values to theON DUPLICATE KEY UPDATE
clause of thisINSERT
statement, where this is supported.void
Configure theINSERT
orUPDATE
statement to return all fields inR
.void
setReturning
(Collection<? extends SelectFieldOrAsterisk> fields) Configure theINSERT
orUPDATE
statement to return a list of fields inR
.void
setReturning
(Identity<R, ?> identity) Configure theINSERT
orUPDATE
statement to return the generated identity value.void
setReturning
(SelectFieldOrAsterisk... fields) Configure theINSERT
orUPDATE
statement to return a list of fields inR
.void
setSelect
(Collection<? extends Field<?>> fields, Select<?> select) Use aSELECT
statement as the source of values for theINSERT
statement.void
Use aSELECT
statement as the source of values for theINSERT
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.reactivestreams.Publisher
subscribe
Methods inherited from interface org.jooq.Query
bind, bind, cancel, execute, executeAsync, executeAsync, isExecutable, keepStatement, poolable, queryTimeout
-
Method Details
-
newRecord
Adds a new Record to the insert statement for multi-record insertsCalling 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
Short for callingnewRecord()
andStoreQuery.setRecord(Record)
.- Parameters:
record
- The record to add to this insert statement.
-
onConflict
Whether aON CONFLICT
clause should be added to thisINSERT
statement.When setting this flag to
true
, be sure to also add values "for update" using theaddValueForUpdate(Field, Field)
methods.- See Also:
-
onConflict
Whether aON CONFLICT
clause should be added to thisINSERT
statement.When setting this flag to
true
, be sure to also add values "for update" using theaddValueForUpdate(Field, Field)
methods.- See Also:
-
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 aON CONFLICT
orON CONFLICT ON CONSTRAINT
clause in thisINSERT
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 aON CONFLICT
orON CONFLICT ON CONSTRAINT
clause in thisINSERT
statement. -
onConflictOnConstraint
Whether use aON CONFLICT
orON CONFLICT ON CONSTRAINT
clause in thisINSERT
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 aON DUPLICATE KEY UPDATE
clause should be added to thisINSERT
statement.When setting this flag to
true
, be sure to also add values "for update" using theaddValueForUpdate(Field, Field)
methods.The
ON DUPLICATE KEY UPDATE
flag is mutually exclusive with theON DUPLICATE KEY IGNORE
flag (seeonDuplicateKeyIgnore(boolean)
. Setting one will unset the other- See Also:
-
onDuplicateKeyIgnore
Whether anON DUPLICATE KEY IGNORE
clause should be added to thisINSERT
statement.This clause is not actually supported in this form by any database, but can be emulated as such:
Dialect Emulation SQLDialect.MYSQL
andSQLDialect.MARIADB
INSERT IGNORE INTO …
SQLDialect.POSTGRES_9_5
andSQLDialect.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 theON DUPLICATE KEY IGNORE
flag (seeonDuplicateKeyIgnore(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 theON DUPLICATE KEY UPDATE
clause of thisINSERT
statement, where this is supported.- See Also:
-
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 theON DUPLICATE KEY UPDATE
clause of thisINSERT
statement, where this is supported.- See Also:
-
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 theON DUPLICATE KEY UPDATE
clause of thisINSERT
statement, where this is supported.Please assure that key/value pairs have matching
<T>
types. Values can either be of type<T>
orField<T>
- See Also:
-
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 theON DUPLICATE KEY UPDATE
clause of thisINSERT
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 theonConflict(Field...)
clause.This is for use with
SQLDialect.POSTGRES
'sonConflict(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 withOperator.AND
.This is for use with
SQLDialect.POSTGRES
'sonConflict(Field...)
clause.- Specified by:
addConditions
in interfaceConditionProvider
- 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 withOperator.AND
.This is for use with
SQLDialect.POSTGRES
'sonConflict(Field...)
clause.- Specified by:
addConditions
in interfaceConditionProvider
- 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 withOperator.AND
.This is for use with
SQLDialect.POSTGRES
'sonConflict(Field...)
clause.- Specified by:
addConditions
in interfaceConditionProvider
- 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
'sonConflict(Field...)
clause.- Specified by:
addConditions
in interfaceConditionProvider
- 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
'sonConflict(Field...)
clause.- Specified by:
addConditions
in interfaceConditionProvider
- 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
'sonConflict(Field...)
clause.- Specified by:
addConditions
in interfaceConditionProvider
- Parameters:
conditions
- The condition
-
setDefaultValues
@Support({ACCESS,ASE,AURORA_MYSQL,AURORA_POSTGRES,BIGQUERY,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 theDEFAULT VALUES
clause. -
setSelect
Use aSELECT
statement as the source of values for theINSERT
statement. -
setSelect
Use aSELECT
statement as the source of values for theINSERT
statement. -
setReturning
Configure theINSERT
orUPDATE
statement to return all fields inR
.This feature works with
INSERT
statements for all SQL dialects- Specified by:
setReturning
in interfaceStoreQuery<R extends Record>
- See Also:
-
setReturning
Configure theINSERT
orUPDATE
statement to return the generated identity value.This feature works with
INSERT
statements for all SQL dialects- Specified by:
setReturning
in interfaceStoreQuery<R extends Record>
- Parameters:
identity
- The table's identity- See Also:
-
setReturning
Configure theINSERT
orUPDATE
statement to return a list of fields inR
.This feature works with
INSERT
statements for all SQL dialects- Specified by:
setReturning
in interfaceStoreQuery<R extends Record>
- Parameters:
fields
- Fields to be returned- See Also:
-
setReturning
Configure theINSERT
orUPDATE
statement to return a list of fields inR
.This feature works with
INSERT
statements for all SQL dialects- Specified by:
setReturning
in interfaceStoreQuery<R extends Record>
- Parameters:
fields
- Fields to be returned- See Also:
-
getReturnedRecord
The record holding returned values as specified by any of theStoreQuery.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
andUPDATE … 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
orlast_inserted_rowid()
values. Those values are fetched in a separateSELECT
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 interfaceStoreQuery<R extends Record>
- Returns:
- The returned value as specified by any of the
StoreQuery.setReturning()
methods. This may returnnull
in case jOOQ could not retrieve any generated keys from the JDBC driver. - See Also:
- Firebird and Postgres have native support for
-
getReturnedRecords
The records holding returned values as specified by any of theStoreQuery.setReturning()
methods.This implemented differently for every dialect:
- Firebird and Postgres have native support for
INSERT … RETURNING
andUPDATE … 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
orlast_inserted_rowid()
values. Those values are fetched in a separateSELECT
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 typeRecord
. This means that only actual columns inR
can be returned. For a more generic set of column expressions, useStoreQuery.getResult()
instead.This feature works with
INSERT
statements for all SQL dialects- Specified by:
getReturnedRecords
in interfaceStoreQuery<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.
- Firebird and Postgres have native support for
-