CODEWITHSUNDEEP
javapostgresqljooq
Bas Opers
Bas Opers

Reputation: 287

ANY operator with jOOQ

I am having troubles understanding the following. I have a field and some values:

Field<T> field = ...;
List<T> values = ...;

Now, I want to express the filter field = ANY({... the values ....}) in a WHERE clause. PostgreSQL supports this ANY(array of values) operator. I got this idea from https://blog.jooq.org/2017/03/30/sql-in-predicate-with-in-list-or-with-array-which-is-faster/.

I tried the following to create a condition:

field.equal(PostgresDSL.any(PostgresDSL.array(values)));

The above doesn't work. This compiles:

field.equal((QuantifiedSelect) PostgresDSL.any(PostgresDSL.array(values)));

First question: Why is this additional cast necessary? Should the API be altered? I am not sure on which side (mine vs. jOOQ's) the generics error actually is.

However, after this change, the query itself is not working. It gives the error:

org.jooq.exception.SQLDialectNotSupportedException: Type class java.util.ArrayList is not supported in dialect DEFAULT

Second question: How do I declare/create an array of values? The call PostgresDSL.array(...) above is falling back to DSL.array(...), which is probably root of the second problem?

Upvotes: 2

Views: 3721

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

Use DSL.any(T[]):

// Using varargs
field.equal(DSL.any(1, 2, 3));

// Using an actual array
Integer array = { 1, 2, 3 };
field.equal(DSL.any(array));

Your mistakes are:

  1. PostgresDSL.array(Select) corresponds to PostgreSQL's ARRAY(<SELECT expression>) syntax, which is useful when you want to collect the results of a correlated subquery into an array. You don't need that in your case, because if that's what you wanted, here, you could simply use Field.in(Select). Note, there is no DSL.array(...)
  2. You referenced the DSL.any(T[]) operator from PostgresDSL, which is a subclass of DSL. This works because Java allows it, but might have contributed to the confusion here.

Upvotes: 5

Related Questions