CODEWITHSUNDEEP
javaarrayspostgresqllistjooq
Vishal Gidwani
Vishal Gidwani

Reputation: 33

java - jooq - how to bind array as param in jooq - PostgreSQL

how to bind an array as param in the query of jooq?

This is where i'm adding a named parameter with name 'someIds'

    Query query = selectJoinStep.where(field("some.id").in(param("someIds")));

I tried the line below to bind the an array to the param

    query.bind("someIds", someIds);

but in the generated sql i'm getting the reference of the object and not the actual values as shown below

some.id in ('[Ljava.lang.Object;@1fee4fe3')

Upvotes: 3

Views: 1653

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220877

Why doesn't your approach work?

It is a common misconception that the SQL IN (?) predicate can somehow support array bind variables. This isn't possible in any SQL dialect I'm aware of, and neither can you do it this way with jOOQ. By "this way", I mean specifying a single bind variable marker (param) and bind several IDs to it.

The reason why you're getting that particular error message is that jOOQ tries to bind an actual array bind variable to the single parameter. As you probably know, PostgreSQL supports arrays, and if you had bound an Integer[], then jOOQ would have passed that on to the PostgreSQL JDBC driver. In fact, maybe that's what you really want to do here by using the PostgreSQL specific = ANY(?) predicate

field("some.id").eq(any(someIds))

Dynamic IN lists

In order to have dynamic IN-lists, you have to pass a dynamic list of params, e.g.

field("some.id").in(IntStream.range(0, someIds.length)
                             .mapToObj(i -> param("someId" + i))
                             .collect(Collectors.toList()))

And then, you will have to bind your ids individually using

for (int i = 0; i < someIds.length; i++)
    query.bind("someId" + i, someIds[i]);

Or, of course, you bind them directly to the in-predicate:

field("some.id").in(IntStream.range(0, someIds.length)
                             .mapToObj(i -> param("someId" + i, i))
                             .collect(Collectors.toList()))

Upvotes: 4

Related Questions