6

I have a JSONB column which holds arrays of strings, eg: ["foo", "bar"]

I want to write the jOOQ equivalent of:

SELECT * FROM sometable WHERE somecolumn ?| <mylist>

...where should be bound to a java List of string tag names.

There doesn't appear to be any direct support for ?| in jOOQ 3.8. I have looked at binding to raw sql in a condition but I'm not quite sure the syntax; it gets even worse if trying to use the ? postgres operator which conflicts with the binding expression.

UPDATE: the stacktrace with 3.8.3

I stripped this down to a minimal test. When adding a condition like this using jOOQ 3.8.3:

query.addConditions(DSL.condition("sometable.tags ?| array['sometag']"));

Produces a stacktrace like this:

Caused by: org.postgresql.util.PSQLException: No value specified for parameter 1.
    at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:228)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:163)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:622)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:472)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:465)
    at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:194)
    at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:269)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:348)
    ... 36 more
stickfigure
  • 13,458
  • 5
  • 34
  • 50

1 Answers1

3

An issue related to jOOQ parsing ?| and similar operators as bind variables has been addressed in jOOQ 3.8.3: https://github.com/jOOQ/jOOQ/issues/5307

JDBC limitation

Note, in addition to the above, there is also a JDBC limitation that I have documented in a separate question. In jOOQ, you can work around this JDBC limitation by specifying:

Settings settings = new Settings().withStatementType(StatementType.STATIC_STATEMENT);

See also: http://www.jooq.org/doc/latest/manual/sql-execution/statement-type

Or, alternatively, by falling back to using the jsonb_exists_any() function instead of the ?| operator.

Community
  • 1
  • 1
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Thanks -- however, this doesn't seem to work with 3.8.3. Maybe because i'm using `SelectQuery.addCondition()`? Something seems to be passing a bind variable down to the postgres driver. I've updated the question with the relevant error message and stacktrace. – stickfigure Jul 13 '16 at 21:35
  • Thanks for the hint. I've indeed only fixed jOOQ's parser in order not to inline any bind variables at the wrong positions. There's also a JDBC bug / limitation, which [I've documented in a separate question](http://stackoverflow.com/q/38370972/521799) – Lukas Eder Jul 14 '16 at 09:52