JOOQ:在空值上更新列值 [英] JOOQ: Update column values on null values

问题描述

我需要在Java上使用JOOQ更新与行关联的整数列的值.如果该列的值不为NULL，我知道我可以使用以下代码:

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

但是，如果列值具有NULL值，我想知道是否可以通过在存在NULL的情况下设置默认值来执行以下操作:

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?

非常感谢！

推荐答案

您的方法为什么行不通?

jOOQ不会将任意Java表达式转换为SQL. IE.在将表达式传递给jOOQ之前，将使用Java对您的TABLENAME.COUNTER == null检查进行评估.它永远不会在SQL中生成.而且由于TABLENAME.COUNTER作为生成的代码将永远不会是null，因此条件表达式实际上是毫无意义的.

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.

您可以使用

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

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

还有Oracle风格

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

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

或者，编写一个> 表达式

.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.*;

这篇关于JOOQ:在空值上更新列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！