Exclude null columns in an update statement

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?

CodePudding user response：

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.

CodePudding user response：

In Java, it would be somthing like this

var jooqQuery = jooqService.using(txn)
        .update(USER_DETAILS);

if (input.name != null) {
    jooqQuery.set(USER_DETAILS.NAME, input.name);
}
if (input.lastName != null) {
    jooqQuery.set(USER_DETAILS.LAST_NAME, input.lastName);
}

// ...

jooqQuery.where(USER_DETAILS.ID.eq(input.id))
        .execute();

CodePudding user response：

Another option rather than writing this UPDATE statement is to use UpdatableRecord:

// Load a POJO into a record using a RecordUnmapper
UserDetailsRecord r =
jooqService.using(txn)
    .newRecord(USER_DETAILS, input)
    
(0 .. r.size() - 1).forEach { if (r[it] == null) r.changed(it, false) }
r.update();

You can probably write an extension function to make this available for all jOOQ records, globally, e.g. as r.updateNonNulls().