Compile error when trying to use jooq any operator in kotlin

Question

I have problems using jooq with kotlin and the any clause.

Given the following:

• I have a Database field in a postgreSQL database which is an array
• I have search parameters which are a List of Strings
• I want to use jooq any operator to search in the array

I have the following code which is not working:

fun findAll(
        someArrayListOfStrings: List<String>?
    ): List<SomeDTO> {
    val filters = ArrayList<Condition>()
    filters.add(TABLE.SOME_FIELD.eq(DSL.any(someArrayListOfStrings)))
}

Here I want to dynamically create filters (jooq Conditions) to be added to some SQL statement. It should work to look if SOME_FIELD (PostgreSQL Array Type) contains one of the following strings using the ANY clause (PostgreSQL jooq binding). However I get the following compile-time error:

None of the following functions can be called with the arguments supplied:

public abstract fun eq(p0: Array<(out) String!>!): Condition defined in org.jooq.TableField

public abstract fun eq(p0: Field<Array<(out) String!>!>!): Condition defined in org.jooq.TableField

public abstract fun eq(p0: QuantifiedSelect<out Record1<Array<(out) String!>!>!>!): Condition defined in org.jooq.TableField

public abstract fun eq(p0: Select<out Record1<Array<(out) String!>!>!>!): Condition defined in org.jooq.TableField

But my function call should match the third type where QuantifiedSelect is used.

I looked for hours on the internet but was not able to find any solution. Any site I found told me to try the solution I already have. Does anyone have an idea what I could try and why it does not work?

Thank you!

Answer 1

The method you're calling here is DSL.any(T...), which takes a generic varargs array (in Java). You're passing a List<String>, so this binds T = List<String>, which doesn't satisfy the type constraint on the eq() method.

But even if you changed that to an Array<String>, it wouldn't work because the jOOQ ANY operator doesn't do the exact same thing as the PostgreSQL any(array) operator. So, just resort to either plain SQL templating:

condition("{0} = any({1})", TABLE.SOME_FIELD, 
    DSL.value(someArrayListOfStrings.toTypedArray()))

Or just use the IN predicate

TABLE.SOME_FIELD.in(someArrayListOfStrings)