Search code examples
javasqljooq

How do I create a Field<T> from a value T in jOOQ, explicitly?

In jOOQ, it is easy to use bind variables in expressions like:

MY_TABLE.COL.eq(1);
MY_TABLE.COL.in(1, 2, 3);
MY_TABLE.COL.between(1).and(10);

All of the above methods accept both T and Field<T> types, e.g. Field.eq(T) or Field.eq(Field<T>). But what if I want to put the bind variable on the left side of the expression? These obviously don't work:

1.eq(MY_TABLE.COL);
1.in(MY_TABLE.COL1, MY_TABLE.COL2, MY_TABLE.COL3);
1.between(MY_TABLE.COL1).and(MY_TABLE.COL2);

How can I turn those 1 or "xyz" into Field<Integer> or Field<String>, etc.?

(This is such a popular question on the jOOQ mailing list, etc., so I'm documenting it here)

Solution

  • The methods accepting T are just convenience methods. Behind the scenes, they all wrap the T argument in DSL.val(T)

    // This
MY_TABLE.COL.eq(1);

// Is just convenience for this:
MY_TABLE.COL.eq(val(1));

// Or rather, this one to be precise
MY_TABLE.COL.eq(val(1, MY_TABLE.COL.getDataType()));

    Because wrapping everything in val() would be very verbose and cumbersome, all of jOOQ's API accepting Field<T> also accepts T for convenience. If Java had untagged union types, the parameter types of such methods would be T|Field<T>.

    In rare cases, users need to explicitly wrap their bind values in val(), including:

    • When the bind value is on the left hand side of an expression
    • When the bind value is used in a clause that doesn't support such overloads, e.g. in select()
    • When functions with many overloads require a Field<T> type for one of the parameters

    So, just write:

    val(1).eq(MY_TABLE.COL);
val(1).in(MY_TABLE.COL1, MY_TABLE.COL2, MY_TABLE.COL3);
val(1).between(MY_TABLE.COL1).and(MY_TABLE.COL2);

    Note there's also DSL.inline(T) to create "inline values", or also "constants", "literals", instead of bind values.

    See also: