CODEWITHSUNDEEP
postgresqlkotlinjooq
NoraL
NoraL

Reputation: 23

Exclude null columns in an update statement - JOOQ

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?

Upvotes: 2

Views: 828

Answers (3)

Lukas Eder
Lukas Eder

Reputation: 221155

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

Upvotes: 0

Adriaan Koster
Adriaan Koster

Reputation: 16209

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.

Upvotes: 3

djm.im
djm.im

Reputation: 3323

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

Upvotes: 1

Related Questions