org.jooq

Interface InsertQuery<R extends Record>

Type Parameters:

R - The record type of the table being inserted into

All Superinterfaces:

Attachable, AutoCloseable, Insert<R>, Query, QueryPart, Serializable, StoreQuery<R>

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

A query for data insertion

Author:

Lukas Eder

Method Summary

Modifier and Type	Method and Description
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<? extends Field<?>,?> 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.
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 Calling this method will cause subsequent calls to StoreQuery.addValue(Field, Object) (and similar) to fill the next record.
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	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 Field<?>> fields) Configure the INSERT or UPDATE statement to return a list of fields in R.
void	setReturning(Field<?>... 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	setSelect(Field<?>[] fields, Select<?> select) Use a SELECT statement as the source of values for the INSERT statement.

Methods inherited from interface org.jooq.StoreQuery

addValue, addValue, addValues, setRecord

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, detach

Method Detail

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(value=POSTGRES_9_5)
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(value=POSTGRES_9_5)
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()

onDuplicateKeyUpdate

@Support(value={CUBRID,DB2,HSQLDB,INFORMIX,MARIADB,MYSQL,ORACLE,POSTGRES_9_5,SQLSERVER,SYBASE})
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	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] )

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(value={CUBRID,DB2,HSQLDB,INFORMIX,MARIADB,MYSQL,ORACLE,POSTGRES_9_5,SQLSERVER,SYBASE})
<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(value={CUBRID,DB2,HSQLDB,INFORMIX,MARIADB,MYSQL,ORACLE,POSTGRES_9_5,SQLSERVER,SYBASE})
<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(value={CUBRID,DB2,HSQLDB,INFORMIX,MARIADB,MYSQL,ORACLE,POSTGRES_9_5,SQLSERVER,SYBASE})
void addValuesForUpdate(Map<? extends Field<?>,?> 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()

setDefaultValues

@Support(value={ASE,CUBRID,DB2,DERBY,FIREBIRD,H2,HSQLDB,INGRES,MARIADB,MYSQL,ORACLE,POSTGRES,SQLITE,SQLSERVER,SYBASE})
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.

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(Field<?>... 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 Field<?>> 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

@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

@Support
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.

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.