Module org.jooq

Package org.jooq

Interface UpdatableRecord<R extends UpdatableRecord<R>>

Type Parameters:

R - The record type

All Superinterfaces:

Attachable, Comparable<Record>, Fields, Formattable, QualifiedRecord<R>, Record, Serializable, SQLData, TableRecord<R>

All Known Implementing Classes:

UpdatableRecordImpl

public interface UpdatableRecord<R extends UpdatableRecord<R>>
extends TableRecord<R>

A common interface for records that can be stored back to the database again.

Any Record can be updatable, if

1. it represents a record from a table or view - a TableRecord
2. its underlying table or view has a "main unique key", i.e. a primary key or at least one unique key

The "main unique key" is used by jOOQ to perform the various operations that can be performed on an UpdatableRecord:

• delete() : Deleting the record
• refresh() : Refreshing the records attributes (or loading it for the first time)
• store() : Storing the record to the database. This executes either an INSERT or an UPDATE statement
• merge() : Merging a record to the database. This executes an INSERT … ON DUPLICATE KEY UPDATE statement.

UpdatableRecords are Attachable, which means that they hold an underlying Configuration that they can be detached from. They can also be instantiated without any underlying Configuration, in case of which they have to be attached first, in order to be refreshed, stored, or deleted.

Author:

Lukas Eder

Method Summary

Modifier and Type	Method	Description
<O extends TableRecord<O>> @NotNull Table<O>	children(ForeignKey<O,R> key)	Get a table expression representing the children of this record, given a foreign key.
R	copy()	Duplicate this record (in memory) and reset all fields from the primary key or main unique key, such that a subsequent call to store() will result in an INSERT statement.
int	delete()	Deletes this record from the database, based on the value of the primary key or main unique key.
<O extends TableRecord<O>> O	fetchChild(ForeignKey<O,R> key)	Fetch a child record of this record, given a foreign key.
<O extends TableRecord<O>> @NotNull Result<O>	fetchChildren(ForeignKey<O,R> key)	Fetch child records of this record, given a foreign key.
int	insert()	Store this record back to the database using an INSERT statement.
int	insert(Collection<? extends Field<?>> fields)	Store parts of this record to the database using an INSERT statement.
int	insert(Field<?>... fields)	Store parts of this record to the database using an INSERT statement.
@NotNull Record	key()	A Record copy holding values for the Table.getPrimaryKey().
int	merge()	Store this record back to the database using a MERGE statement.
int	merge(Collection<? extends Field<?>> fields)	Store parts of this record to the database using a MERGE statement.
int	merge(Field<?>... fields)	Store parts of this record to the database using a MERGE statement.
void	refresh()	Refresh this record from the database.
void	refresh(Collection<? extends Field<?>> fields)	Refresh parts of this record from the database.
void	refresh(Field<?>... fields)	Refresh parts of this record from the database.
int	store()	Store this record back to the database.
int	store(Collection<? extends Field<?>> fields)	Store parts of this record to the database.
int	store(Field<?>... fields)	Store parts of this record to the database.
int	update()	Store this record back to the database using an UPDATE statement.
int	update(Collection<? extends Field<?>> fields)	Store parts of this record to the database using an UPDATE statement.
int	update(Field<?>... fields)	Store parts of this record to the database using an UPDATE statement.

Methods inherited from interface org.jooq.Attachable

attach, configuration, detach

Methods inherited from interface org.jooq.Fields

dataType, dataType, dataType, dataTypes, field, field, field, field, field, field, field, field, field, field, fields, fields, fields, fields, fields, fieldsIncludingHidden, fieldsRow, fieldStream, indexOf, indexOf, indexOf, type, type, type, types

Methods inherited from interface org.jooq.Formattable

format, format, format, format, format, format, format, format, format, formatChart, formatChart, formatChart, formatChart, formatChart, formatChart, formatCSV, formatCSV, formatCSV, formatCSV, formatCSV, formatCSV, formatCSV, formatCSV, formatCSV, formatCSV, formatCSV, formatCSV, formatCSV, formatCSV, formatCSV, formatCSV, formatCSV, formatCSV, formatCSV, formatCSV, formatCSV, formatHTML, formatHTML, formatHTML, formatInsert, formatInsert, formatInsert, formatInsert, formatInsert, formatInsert, formatJSON, formatJSON, formatJSON, formatJSON, formatJSON, formatJSON, formatXML, formatXML, formatXML, formatXML, formatXML, formatXML, intoXML, intoXML, intoXML, intoXML

Methods inherited from interface org.jooq.QualifiedRecord

getQualifier, with, with

Methods inherited from interface org.jooq.Record

changed, changed, changed, changed, changed, changed, changed, changed, changed, changed, compareTo, equals, from, from, from, from, from, fromArray, fromArray, fromArray, fromArray, fromArray, fromMap, fromMap, fromMap, fromMap, fromMap, get, get, get, get, get, get, get, get, get, get, get, get, getValue, getValue, getValue, getValue, getValue, getValue, getValue, getValue, getValue, getValue, getValue, getValue, hashCode, into, into, into, into, into, into, into, into, into, into, into, into, into, into, into, into, into, into, into, into, into, into, into, into, into, into, intoArray, intoList, intoMap, intoResultSet, intoStream, map, modified, modified, modified, modified, modified, original, original, original, original, reset, reset, reset, reset, reset, set, set, setValue, setValue, size, touched, touched, touched, touched, touched, touched, touched, touched, touched, touched, valuesRow

Methods inherited from interface java.sql.SQLData

getSQLTypeName, readSQL, writeSQL

Methods inherited from interface org.jooq.TableRecord

fetchParent, getTable, original, parent

Method Details

key

@NotNull
@NotNull Record key()

A Record copy holding values for the Table.getPrimaryKey().

The returned record consists exactly of those fields as returned by the table's primary key: Key.getFields().

Generated subtypes may covariantly override this method to add more record type information. For instance, they may return Record1, Record2, ...

store

@Support
int store()
   throws DataAccessException,
DataChangedException

Store this record back to the database.

Depending on the state of the primary key's value, an insert() or an update() statement is executed.

Statement type

• If this record was created by client code, an INSERT statement is executed
• If this record was loaded by jOOQ and the primary key value was touched, an INSERT statement is executed (unless Settings.isUpdatablePrimaryKeys() is set). jOOQ expects that primary key values will never change due to the principle of normalisation in RDBMS. So if client code changes primary key values, this is interpreted by jOOQ as client code wanting to duplicate this record.
• If this record was loaded by jOOQ, and the primary key value was not touched, an UPDATE statement is executed.

In either statement type, only those fields are inserted/updated, which had been explicitly set by client code, in order to allow for DEFAULT values to be applied by the underlying RDBMS. If no fields were modified, neither an UPDATE nor an INSERT will be executed.

Automatic value generation

Use insert() or update() to explicitly force either statement type.

• IDENTITY columns

  If there is an IDENTITY column defined on the record's underlying table (see Table.getIdentity()), then the auto-generated IDENTITY value is refreshed automatically on INSERT's. Refreshing is done using Statement.getGeneratedKeys(), where this is supported by the JDBC driver. See also InsertQuery.getReturnedRecord() for more details

• VERSION and TIMESTAMP columns

  jOOQ can auto-generate "version" and "timestamp" values that can be used for optimistic locking. If this is an UpdatableRecord and if this record returns fields for either Table.getRecordVersion() or Table.getRecordTimestamp(), then these values are set onto the INSERT or UPDATE statement being executed. On execution success, the generated values are set to this record. Use the code-generation configuration to specify naming patterns for auto-generated "version" and "timestamp" columns.

  Should you want to circumvent jOOQ-generated updates to these columns, you can render an INSERT or UPDATE statement manually using the various DSLContext.insertInto(Table), DSLContext.update(Table) methods.

Optimistic locking

If an UPDATE statement is executed and Settings.isExecuteWithOptimisticLocking() is set to true, then this record will first be compared with the latest state in the database. There are two modes of operation for optimistic locking:

• With VERSION and/or TIMESTAMP columns configured

  This is the preferred way of using optimistic locking in jOOQ. If this is an UpdatableRecord and if this record returns fields for either Table.getRecordVersion() or Table.getRecordTimestamp(), then these values are compared to the corresponding value in the database in the WHERE clause of the executed DELETE statement.

• Without any specific column configurations

  In order to compare this record with the latest state, the database record will be locked pessimistically using a SELECT … FOR UPDATE statement. Not all databases support the FOR UPDATE clause natively. Namely, the following databases will show slightly different behaviour:

  • SQLDialect.SQLSERVER: jOOQ will try to lock the database record using WITH (ROWLOCK, UPDLOCK) hints.
  • SQLDialect.SQLITE: No pessimistic locking is possible. Client code must assure that no race-conditions can occur between jOOQ's checking of database record state and the actual UPDATE

  See SelectQuery.setForUpdate(boolean) for more details

Statement examples

Possible statements are

•  
   INSERT INTO [table] ([modified fields, including keys])
   VALUES ([modified values, including keys])
   

•  
   UPDATE [table]
   SET [modified fields = modified values, excluding keys]
   WHERE [key fields = key values]
   AND [version/timestamp fields = version/timestamp values]
   

Statement execution enforcement

If you want to control statement re-execution, regardless if the values in this record were Record.modified(), you can explicitly set the touched flags for all values with Record.touched(boolean) or for single values with Record.touched(Field, boolean), prior to storing, if Settings.getRecordDirtyTracking() is set to RecordDirtyTracking.TOUCHED. Consider also setting the flags Settings.getUpdateUnchangedRecords() and/or Settings.isInsertUnchangedRecords() appropriately to control if the record should be "touched" without any changes (UPDATE) or inserted with default values (INSERT).

This is the same as calling record.store(record.fields())

Effects on this record

After a successful store() operation, this record will have:

• Its Record.valuesRow() unchanged
• Its TableRecord.original() values set to the same values as Record.valuesRow().
• Its Record.touched() flags set to false
• Its Record.modified() flags set to false

Returns:

1 if the record was stored to the database. 0 if storing was not necessary.

Throws:

DataAccessException - if something went wrong executing the query

DataChangedException - If optimistic locking is enabled and the record has already been changed/deleted in the database

See Also:

• insert()
• update()

store

@Support
int store(Field<?>... fields)
   throws DataAccessException,
DataChangedException

Store parts of this record to the database.

Returns:

1 if the record was stored to the database. 0 if storing was not necessary.

Throws:

DataAccessException - if something went wrong executing the query

DataChangedException - If optimistic locking is enabled and the record has already been changed/deleted in the database

See Also:

• store()
• insert(Field...)
• update(Field...)

store

@Support
int store(Collection<? extends Field<?>> fields)
   throws DataAccessException,
DataChangedException

Store parts of this record to the database.

Returns:

1 if the record was stored to the database. 0 if storing was not necessary.

Throws:

DataAccessException - if something went wrong executing the query

DataChangedException - If optimistic locking is enabled and the record has already been changed/deleted in the database

See Also:

• store()
• insert(Field...)
• update(Field...)

insert

@Support
int insert()
    throws DataAccessException

Store this record back to the database using an INSERT statement.

This is the same as store(), except that an INSERT statement (or no statement) will always be executed.

If you want to enforce re-insertion this record's values, regardless if the values in this record were touched, you can explicitly set the touched flags for all values with Record.touched(boolean) or for single values with Record.touched(Field, boolean), prior to insertion, if Settings.getRecordDirtyTracking() is set to RecordDirtyTracking.TOUCHED

This is the same as calling record.insert(record.fields())

Effects on this record

After a successful insert() operation, this record will have:

• Its Record.valuesRow() unchanged
• Its TableRecord.original() values set to the same values as Record.valuesRow().
• Its Record.touched() flags set to false
• Its Record.modified() flags set to false

Specified by:

insert in interface TableRecord<R extends UpdatableRecord<R>>

Returns:

1 if the record was stored to the database. 0 if storing was not necessary and Settings.isInsertUnchangedRecords() is set to false.

Throws:

DataAccessException - if something went wrong executing the query

See Also:

• store()

insert

@Support
int insert(Field<?>... fields)
    throws DataAccessException

Store parts of this record to the database using an INSERT statement.

Specified by:

insert in interface TableRecord<R extends UpdatableRecord<R>>

Returns:

1 if the record was stored to the database. 0 if storing was not necessary.

Throws:

DataAccessException - if something went wrong executing the query

See Also:

• insert()

insert

@Support
int insert(Collection<? extends Field<?>> fields)
    throws DataAccessException

Store parts of this record to the database using an INSERT statement.

Specified by:

insert in interface TableRecord<R extends UpdatableRecord<R>>

Returns:

1 if the record was stored to the database. 0 if storing was not necessary.

Throws:

DataAccessException - if something went wrong executing the query

See Also:

• insert()

update

@Support
int update()
    throws DataAccessException,
DataChangedException

Store this record back to the database using an UPDATE statement.

This is the same as store(), except that an UPDATE statement (or no statement) will always be executed.

If you want to enforce statement execution, regardless if the values in this record were Record.modified(), you can explicitly set the touched flags for all values with Record.touched(boolean) or for single values with Record.touched(Field, boolean), prior to updating, if Settings.getRecordDirtyTracking() is set to RecordDirtyTracking.TOUCHED, or alternatively, use Settings.getUpdateUnchangedRecords().

This is the same as calling record.update(record.fields())

Effects on this record

After a successful update() operation, this record will have:

• Its Record.valuesRow() unchanged
• Its TableRecord.original() values set to the same values as Record.valuesRow().
• Its Record.touched() flags set to false
• Its Record.modified() flags set to false

Returns:

1 if the record was stored to the database. 0 if storing was not necessary.

Throws:

DataAccessException - if something went wrong executing the query

DataChangedException - If optimistic locking is enabled and the record has already been changed/deleted in the database

See Also:

• store()

update

@Support
int update(Field<?>... fields)
    throws DataAccessException,
DataChangedException

Store parts of this record to the database using an UPDATE statement.

Returns:

1 if the record was stored to the database. 0 if storing was not necessary.

Throws:

DataAccessException - if something went wrong executing the query

DataChangedException - If optimistic locking is enabled and the record has already been changed/deleted in the database

See Also:

• update()

update

@Support
int update(Collection<? extends Field<?>> fields)
    throws DataAccessException,
DataChangedException

Store parts of this record to the database using an UPDATE statement.

Returns:

1 if the record was stored to the database. 0 if storing was not necessary.

Throws:

DataAccessException - if something went wrong executing the query

DataChangedException - If optimistic locking is enabled and the record has already been changed/deleted in the database

See Also:

• update()

merge

@Support({AURORA_MYSQL,AURORA_POSTGRES,COCKROACHDB,CUBRID,DB2,DERBY,FIREBIRD_3_0,H2,HANA,HSQLDB,INFORMIX,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES_9_5,SQLITE,SQLSERVER,SYBASE,TERADATA,YUGABYTEDB})
int merge()
   throws DataAccessException

Store this record back to the database using a MERGE statement.

Unlike store(), the statement produced by this operation does not depend on whether the record has been previously fetched from the database or created afresh. It implements the semantics of an INSERT … ON DUPLICATE KEY UPDATE statement, which will update the row regardless of which (unique) key value is already present. See InsertOnDuplicateStep.onDuplicateKeyUpdate().

When optimistic locking is active for this record, then this operation will execute insert() or update() explicitly, depending on whether the lock values are present already in the record.

If you want to enforce statement execution, regardless if the values in this record were Record.modified(), you can explicitly set the touched flags for all values with Record.touched(boolean) or for single values with Record.touched(Field, boolean), prior to merging, if Settings.getRecordDirtyTracking() is set to RecordDirtyTracking.TOUCHED.

This is the same as calling record.merge(record.fields())

Effects on this record

After a successful merge() operation, this record will have:

• Its Record.valuesRow() unchanged
• Its TableRecord.original() values set to the same values as Record.valuesRow().
• Its Record.touched() flags set to false
• Its Record.modified() flags set to false

Returns:

1 if the record was merged to the database. 0 if merging was not necessary.

Throws:

DataAccessException - if something went wrong executing the query

See Also:

• store()
• InsertOnDuplicateStep.onDuplicateKeyUpdate()

merge

@Support({AURORA_MYSQL,AURORA_POSTGRES,COCKROACHDB,CUBRID,DB2,DERBY,FIREBIRD_3_0,H2,HANA,HSQLDB,INFORMIX,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES_9_5,SQLITE,SQLSERVER,SYBASE,TERADATA,YUGABYTEDB})
int merge(Field<?>... fields)
   throws DataAccessException

Store parts of this record to the database using a MERGE statement.

Returns:

1 if the record was merged to the database. 0 if merging was not necessary.

Throws:

DataAccessException - if something went wrong executing the query

See Also:

• merge()

merge

@Support({AURORA_MYSQL,AURORA_POSTGRES,COCKROACHDB,CUBRID,DB2,DERBY,FIREBIRD_3_0,H2,HANA,HSQLDB,INFORMIX,MARIADB,MEMSQL,MYSQL,ORACLE,POSTGRES_9_5,SQLITE,SQLSERVER,SYBASE,TERADATA,YUGABYTEDB})
int merge(Collection<? extends Field<?>> fields)
   throws DataAccessException

Store parts of this record to the database using a MERGE statement.

Returns:

1 if the record was merged to the database. 0 if merging was not necessary.

Throws:

DataAccessException - if something went wrong executing the query

See Also:

• merge()

delete

@Support
int delete()
    throws DataAccessException,
DataChangedException

Deletes this record from the database, based on the value of the primary key or main unique key.

Optimistic locking

If a DELETE statement is executed and Settings.isExecuteWithOptimisticLocking() is set to true, then this record will first be compared with the latest state in the database. There are two modes of operation for optimistic locking:

• With VERSION and/or TIMESTAMP columns configured

  This is the preferred way of using optimistic locking in jOOQ. If this is an UpdatableRecord and if this record returns fields for either Table.getRecordVersion() or Table.getRecordTimestamp(), then these values are compared to the corresponding value in the database in the WHERE clause of the executed DELETE statement.

• Without any specific column configurations

  In order to compare this record with the latest state, the database record will be locked pessimistically using a SELECT … FOR UPDATE statement. Not all databases support the FOR UPDATE clause natively. Namely, the following databases will show slightly different behaviour:

  • SQLDialect.SQLSERVER: jOOQ will try to lock the database record using WITH (ROWLOCK, UPDLOCK) hints.
  • SQLDialect.SQLITE: No pessimistic locking is possible. Client code must assure that no race-conditions can occur between jOOQ's checking of database record state and the actual DELETE

  See SelectQuery.setForUpdate(boolean) for more details

Effects on this record

After a successful delete() operation, this record will have:

• Its Record.valuesRow() unchanged
• Its TableRecord.original() values reset to null
• Its Record.touched() flags set to true
• Its Record.modified() flags set to true

Statement examples

The executed statement is

 
 DELETE FROM [table]
 WHERE [key fields = key values]
 AND [version/timestamp fields = version/timestamp values]
 

This is in fact the same as calling delete(getTable().getPrimaryKey().getFieldsArray())

Returns:

1 if the record was deleted from the database. 0 if deletion was not necessary.

Throws:

DataAccessException - if something went wrong executing the query

DataChangedException - If optimistic locking is enabled and the record has already been changed/deleted in the database

refresh

@Support
void refresh()
      throws DataAccessException

Refresh this record from the database.

A successful refresh results in the following:

• Record.valuesRow() will have been restored to the respective values from the database
• TableRecord.original() will match this record
• Record.touched() will be false
• Record.modified() will be false

Refreshing can trigger any of the following actions:

• Executing a new SELECT statement, if this is an UpdatableRecord.
• Failing, otherwise

This is the same as calling record.refresh(record.fields())

Throws:

DataAccessException - This exception is thrown if something went wrong executing the refresh SELECT statement

NoDataFoundException - If the record does not exist anymore in the database

refresh

@Support
void refresh(Field<?>... fields)
      throws DataAccessException,
NoDataFoundException

Refresh parts of this record from the database.

A successful refresh results in the following:

• Record.valuesRow() will have been restored to the respective values from the database
• TableRecord.original() will match this record
• Record.touched() will be false
• Record.modified() will be false

Refreshing can trigger any of the following actions:

• Executing a new SELECT statement, if this is an UpdatableRecord.
• Failing, otherwise

This is the same as calling record.refresh(record.fields())

Throws:

DataAccessException - This exception is thrown if something went wrong executing the refresh SELECT statement

NoDataFoundException - If the record does not exist anymore in the database

refresh

@Support
void refresh(Collection<? extends Field<?>> fields)
      throws DataAccessException,
NoDataFoundException

Refresh parts of this record from the database.

A successful refresh results in the following:

• Record.valuesRow() will have been restored to the respective values from the database
• TableRecord.original() will match this record
• Record.touched() will be false
• Record.modified() will be false

Refreshing can trigger any of the following actions:

• Executing a new SELECT statement, if this is an UpdatableRecord.
• Failing, otherwise

This is the same as calling record.refresh(record.fields())

Throws:

DataAccessException - This exception is thrown if something went wrong executing the refresh SELECT statement

NoDataFoundException - If the record does not exist anymore in the database

copy

@NotNull
R copy()

Duplicate this record (in memory) and reset all fields from the primary key or main unique key, such that a subsequent call to store() will result in an INSERT statement.

Effects on this record

After a copy() operation, the resulting record will have:

• Its Record.valuesRow() just like the source record
• Its TableRecord.original() values set null.
• Its Record.touched() flags set to true
• Its Record.modified() flags set to false

Returns:

A new record, distinct from this record.

fetchChild

@Nullable
@Support
@Blocking
<O extends TableRecord<O>> O fetchChild(ForeignKey<O,R> key)
                                 throws TooManyRowsException,
DataAccessException

Fetch a child record of this record, given a foreign key.

This returns a child record referencing this record through a given foreign key, as if fetching from children(ForeignKey).. If no child record was found, this returns null.

A separate roundtrip is created by this operation. It is often much better to include parent records using ordinary JOIN mechanisms in a single query, or using nested records, or the MULTISET or MULTISET_AGG operators, see https://www.jooq.org/doc/latest/manual/sql-building/column-expressions/nested-records/, or the https://www.jooq.org/doc/latest/manual/sql-building/column-expressions/multiset-value-constructor/.

Throws:

DataAccessException - if something went wrong executing the query

TooManyRowsException - if the query returned more than one record

See Also:

• ForeignKey.fetchChildren(java.util.Collection)
• ForeignKey.fetchChildren(Record)
• ForeignKey.fetchChildren(Record...)

fetchChildren

@NotNull
@Support
@Blocking
<O extends TableRecord<O>> @NotNull Result<O> fetchChildren(ForeignKey<O,R> key)
                                                     throws DataAccessException

Fetch child records of this record, given a foreign key.

This returns child records referencing this record through a given foreign key, as if fetching from children(ForeignKey).

A separate roundtrip is created by this operation. It is often much better to include parent records using ordinary JOIN mechanisms in a single query, or using nested records, or the MULTISET or MULTISET_AGG operators, see https://www.jooq.org/doc/latest/manual/sql-building/column-expressions/nested-records/, or the https://www.jooq.org/doc/latest/manual/sql-building/column-expressions/multiset-value-constructor/.

Throws:

DataAccessException - if something went wrong executing the query

See Also:

• ForeignKey.fetchChildren(java.util.Collection)
• ForeignKey.fetchChildren(Record)
• ForeignKey.fetchChildren(Record...)

children

@NotNull
@Support
<O extends TableRecord<O>> @NotNull Table<O> children(ForeignKey<O,R> key)

Get a table expression representing the children of this record, given a foreign key.