CODEWITHSUNDEEP
javasqljooq
Appelsien Sap
Appelsien Sap

Reputation: 383

jOOQ - update record based on unique key (not primary key)

I am using jOOQ to generate POJOs for my database tables. This works great.

I have a table with a primary key (identifier) and a unique key (name). When updating the record, jOOQ uses the primary key.

I would like to update the record by using the unique key instead of the primary key.

https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/impl/UpdatableRecordImpl.java

@Override
public final int update() {
    return update(fields.fields.fields);
}

@Override
public int update(Field<?>... storeFields) throws DataAccessException, DataChangedException {
    return storeUpdate(storeFields, getPrimaryKey().getFieldsArray());
}

In essence, I want to call storeUpdate with another key (second parameter). I tried extending the generated record, but storeUpdate is private.

Is there another way to update a record? I could first select the identifier before update(), but it introduces an extra query, which I would like to avoid.

Upvotes: 2

Views: 3171

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

From the comments, I understand that you want to:

  • Use the generated records as "ActiveRecords" holding data that is going to be stored / updated into a table
  • Use arbitrary "key" information as selective criteria for your update statement

There are two ways you can do this with jOOQ:

1. Override the primary key information in the code generator

You can specify a regular expression matching unique key names in your database, which should override primary keys in generated code:

  <!-- All (UNIQUE) key names that should be used instead of primary keys on
       generated UpdatableRecords, to be used with

        - UpdatableRecord.store()
        - UpdatableRecord.update()
        - UpdatableRecord.delete()
        - UpdatableRecord.refresh()

        If several keys match, a warning is emitted and the first one encountered 
        will be used.

        This flag will also replace synthetic primary keys, if it matches. -->
  <overridePrimaryKeys>MY_UNIQUE_KEY_NAME</overridePrimaryKeys>

Note that this solution will affect all the calls to store(), update(), etc. From your comments, this might not be the desired behaviour... For more information, see the jOOQ manual

2. Use a regular UPDATE statement

You can pass the whole UpdatableRecord to an UPDATE statement and specify the selection criteria explicitly, such as:

MyTableRecord record = ...;
DSL.using(configuration)
   .update(MY_TABLE)
   .set(record)
   .where(MY_TABLE.NAME.eq(record.getName())
   .execute();

Upvotes: 3

Related Questions