CODEWITHSUNDEEP
javasqljooq
pafede2
pafede2

Reputation: 1704

JOOQ: Update column values on null values

I need to update the value of an integer column associated with a row using JOOQ on Java. In case the value of the column is not NULL I am aware that I could use this code:

context.update(TABLENAME)
    .set(TABLENAME.COUNTER, TABLENAME.COUNTER.add(incrementValue))
    .where(TABLENAME.ID.eq(id))
    .execute();

however if the column value has NULL value I am wondering whether I could do something like the following by setting a default value in presence of NULL:

context.update(TABLENAME)
    .set(TABLENAME.COUNTER, TABLENAME.COUNTER == null ? 0 : TABLENAME.COUNTER.add(incrementValue))
    .where(TABLENAME.ID.eq(id))
    .execute();

Is it possible? How should I perform it?

Thanks a lot!

Upvotes: 1

Views: 4364

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

Why doesn't your approach work?

jOOQ does not translate arbitrary Java expressions to SQL. I.e. your TABLENAME.COUNTER == null check is evaluated in Java prior to passing the expression to jOOQ. It will never be generated in SQL. And since TABLENAME.COUNTER, being generated code, will never be null, the conditional expression is really meaningless.

Solutions

You could use DSL.coalesce() or DSL.nvl() or DSL.ifnull()

.set(TABLENAME.COUNTER, coalesce(TABLENAME.COUNTER, inline(-1)).add(incrementValue))

There's also the Oracle style DSL.nvl2() function you could use:

.set(TABLENAME.COUNTER, 
     nvl2(TABLENAME.COUNTER, TABLENAME.COUNTER.add(incrementValue), inline(0)))

Alternatively, write a CASE expression

.set(TABLENAME.COUNTER, 
     when(TABLENAME.COUNTER.isNull(), inline(0))
     .otherwise(TABLENAME.COUNTER.add(incrementValue)))

As always, the above is assuming you have the following static import:

import static org.jooq.impl.DSL.*;

Upvotes: 2

Related Questions