Exclude null columns in an update statement - JOOQ

user_17318549 0
Viewed 145

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?

jooq kotlin postgresql
3 Answers

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.

user_1813360

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();
user_25645090

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().

user_5217990
Related