JOOQ save POJO with optimistic locking

Alexander Golub 0
Viewed 58

I need to use optimistic locking for my record. But also in our system, we use mapping logic between layers, records to POJO, and vice versa. And if I use store() on new records it works, but I want to store() like update statement on the existing record, but when I convert it from POJO, it works like for new record and I got duplicate id exception.

A know that I can save records with context.update(), but I need optimistic locking and as I know it works only with UpdatableRecords (store() method). In the documentation I found :

`When loading records from POJOs, jOOQ will assume the record is a new record. It will hence attempt to INSERT it.

and it means that I can't update existing records from POJO with method store() and should use the update from context, but it can't work with the optimistic lock.

Hence, is there any chance to get it at the same time optimistic lock and work with POJOs?

UPDATE : Thx Lucas. I've started using record.update(), but I ran into another problem :

 UserRecord userRecord = context.fetchOne(...);
 UserPojo userPojo = mapper.toPojo(userRecord);
 userPojo.setName("new_name")
 UserRecord userRecordForSave = context.newRecord(USER,userPojo);
 userRecordForSave.update()
 got DataChangedException

But I turned on optimistic locking and inner JOOQ method checkIfChanged() - validate originals[] fields, but I don't have them after mapping POJO to record, hence I got DataChangedException: Database record has been changed. What is the correct method of mapping POJO in this case?

jooq
1 Answers

Updating explicitly

UpdatableRecord.store() decides whether to insert() or update(), depending on whether you created the record, or whether it was fetched from the database via jOOQ. See the Javadoc:

  • 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 changed, 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 changed, an UPDATE statement is executed.

But you don't have to use store(), you can use insert() or update() directly.

Optimistic locking

There are 2 ways to implement optimistic locking in jOOQ:

The former works purely based on the value in the record, but in order to support the latter, you have to make sure jOOQ knows both:

  • The previous state of your record (Record.original())
  • The new state of your record (Record itself)

There's no way jOOQ can implement this functionality without knowing both states, so you can't implement your logic without keeping a hold of the previous state somehow.

But in most cases, the version based optimistic locking approach is the most desirable one.

Lukas Eder0
Related