Introducing a named parameter breaks jOOQ query

user_13818874 0
Viewed 280

To query a PostgreSQL 10.11 database, I am using jOOQ 3.12.4, which comes bundled with Spring Boot 2.2.

Let's assume I have built a query using jOOQ like this:

final String[] ids = ...;

final var query = dslContext.selectFrom(MY_TABLE).where(MY_TABLE.ID.in(ids));
final Map<String, List<MyTable>> changeDomains = query.fetch().intoGroups(MY_TABLE.ID, MyTable.class);

This code runs fine and produces the expected results. But when I refactor my query and introduce a named parameter (to reuse the query in multiple parts of my code), like this:

final String[] ids = ...;

final var query = dslContext.selectFrom(MY_TABLE).where(MY_TABLE.ID.in(param("ids")));
final Map<String, List<MyTable>> changeDomains = query.bind("ids", ids).fetch().intoGroups(MY_TABLE.ID, MyTable.class);

I suddenly start to get the following error:

org.springframework.jdbc.BadSqlGrammarException: jOOQ; bad SQL grammar ...; nested exception is org.postgresql.util.PSQLException: ERROR: operator does not exist: text = character varying[]
    Hinweis: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Edit: I get the same error when I use

MY_TABLE.ID.in(param("ids", String[].class))

instead.

How can I solve or work around this problem?

jooq postgresql sql java
1 Answers

A better solution to your code reuse approach

But when I refactor my query and introduce a named parameter (to reuse the query in multiple parts of my code)

While you could use jOOQ this way (be careful, when mutating and reusing jOOQ queries in a non-threadsafe way!), it is generally recommended to use jOOQ in a more functional way, see e.g.:

You don't gain much by re-using a jOOQ query, specifically, there's hardly any performance gain.

So, instead of this:

final var query = dslContext.selectFrom(MY_TABLE)
    .where(MY_TABLE.ID.in(param("ids")));
final Map<String, List<MyTable>> changeDomains = query
    .bind("ids", ids).fetch().intoGroups(MY_TABLE.ID, MyTable.class);

Write this:

public ResultQuery<MyTableRecord> query(String[] ids) {
    return dslContext.selectFrom(MY_TABLE).where(MY_TABLE.ID.in(ids));
}

// And then:
final Map<String, List<MyTable>> changeDomains = query(ids)
    .fetch().intoGroups(MY_TABLE.ID, MyTable.class);

The actual problem you ran into:

jOOQ, JDBC, and SQL don't support single bind value IN lists. While it seems useful to write this:

SELECT * FROM t WHERE c IN (:bind_value)

And passing an array or list as a single bind value, this is not supported in SQL. Some APIs might pretend that this is supported (but behind the scenes replace the single bind value by multiple ?, ?, ..., ?

PostgreSQL supports the = ANY (:bind_value) operator with arrays

SELECT * FROM t WHERE c = ANY (:bind_value)

You could use it in jOOQ using

dslContext.selectFrom(MY_TABLE).where(MY_TABLE.ID.eq(any(ids)));

That way, you could call the bind() method to replace the array prior to execution. However, I still recommend you write functions returning queries dynamically.

user_5217990
Related