Search code examples
javamysqlsqljooq

Set NULL value in jooq

I have an big query and my problem is setting NULL values using jooq. For example, I have this piece of sql query:

IF(t.PANEL_ID IS NULL, t.ID, NULL) AS testId

If transform this into jooq implementation something like this will come out:

when(TEST.PANEL_ID.isNull(), TEST.ID).otherwise(null)).as("testId")

but this is ambiguous method call.

I made some research, and find this snippet:

DSL.val((String) null)

but it didn't work, because it cannot resolve method with jooq.Param<String>.

How should I proceed?

Solution

  • Your NULL expression must be of the same type as your TEST.ID column. I would imagine this is not a String column, but some numeric one. Irrespective of the actual data type, you can always create a bind value using the data type of another expression, e.g.

    // Bind variable:
DSL.val(null, TEST.ID)

// Inline value / NULL literal
DSL.inline(null, TEST.ID)

    If you're doing this a lot, you could also extract your own utility like this:

    public static <T> util(Field<?> nullable, Field<T> other) {
    return when(nullable.isNull(), other).otherwise(inline(null, other));
}

    Notice, jOOQ has a built-in method NVL2 for this purpose:

    nvl2(TEST.PANEL_ID, inline(null, TEST.ID), TEST.ID).as("testId")