Exclude null columns in an update statement - JOOQ

Issue

I have a POJO that has the fields that can be updated. But sometimes only a few fields will need to be updated and the rest are null. How do I write an update statement that ignores the fields that are null? Would it be better to loop through the non missing ones and dynamically add to a set statement, or using coalesce?

I have the following query:

jooqService.using(txn)
        .update(USER_DETAILS)
        .set(USER_DETAILS.NAME, input.name)
        .set(USER_DETAILS.LAST_NAME, input.lastName)
        .set(USER_DETAILS.COURSES, input.courses)
        .set(USER_DETAILS.SCHOOL, input.school)
        .where(USER_DETAILS.ID.eq(input.id))
        .execute()

If there is a better practice?

Solution

I don't know Jooq but it looks like you could simply do this:

val jooq = jooqService.using(txn).update(USER_DETAILS)
input.name.let {jooq.set(USER_DETAILS.NAME, it)}
input.lastName.let {jooq.set(USER_DETAILS.LAST_NAME, it)}

etc...

EDIT: Mapping these fields explicitly as above is clearest in my opinion, but you could do something like this:

val fields = new Object[] {USER_DETAILS.NAME, USER_DETAILS.LAST_NAME}
val values = new Object[] {input.name, input.lastName}
val jooq = jooqService.using(txn).update(USER_DETAILS)
values.forEachIndexed { i, value ->
    value.let {jooq.set(fields[i], value)}
}

You'd still need to enumerate all the fields and values explicitly and consistently in the arrays for this to work. It seems less readable and more error prone to me.

Answered By - Adriaan Koster