jOOQ upsert a pojo

Viewed 43

Feeling a bit stupid, but I have a simple architecture where the repositories are the only ones accessing ~Record classes and the services work on POJOs.

So basic flow is

  • repository fetches into POJO
  • service modifies POJO
  • repository receives POJO to update DB
  • repository matches updated POJO to record
  • repository stores (insert or update) the record
  • repository maps updated record (may have received generated values from insert) back to POJO
  • service receives updated POJO

i.e. something like

fun save(set: MySet): MySet {
    set.description = set.description ?: ""
    val record = ctx.newRecord(MY_SET, set).apply {
        store()
    }
    // "When store() performs an INSERT statement, jOOQ attempts to load any generated keys from the database back into the record."
    // cf. https://www.jooq.org/doc/latest/manual/sql-execution/crud-with-updatablerecords/simple-crud/
    return record.into(MySet::class.java)
}

This fails because to quote documentation for newRecord:

Create a new pre-filled Record that can be inserted into the corresponding table. This performs roughly the inverse operation of Record.into(Class) The resulting record will have its internal "changed" flags set to true for all values. This means that UpdatableRecord.store() will perform an INSERT statement. If you wish to store the record using an UPDATE statement, use executeUpdate(UpdatableRecord) instead.

I CAN, of course, check if I have an id, and then either fetch the record from the database or create a new one

fun save(set: MySet): MySet {
    set.description = set.description ?: ""

    val record = when (val setId = set.id) {
        null -> ctx.newRecord(MY_SET, set)
        else -> ctx.selectFrom(MY_SET).where(MY_SET.ID.eq(setId)).fetchSingle()
    }

    //TOOD: update record manually from `set`

    record.store()

    // "When store() performs an INSERT statement, jOOQ attempts to load any generated keys from the database back into the record."
    // cf. https://www.jooq.org/doc/latest/manual/sql-execution/crud-with-updatablerecords/simple-crud/
    return record.into(MySet::class.java)
}

But that kind of is a lot of boilerplate code.

I DO have access to the MySetDao but that one just has insert and update, there's no store or upsert, as far as I can see.

Is there a way to turn a POJO into an UpdatableRecord directly or is this fetch-and-manual-update the way to go?

(Worth noting: the MySet POJO used here was generated by jOOQ.)

0 Answers
Related