CODEWITHSUNDEEP
javasqljooq
mat_boy
mat_boy

Reputation: 13666

jOOQ how to execute Postgresql functions and operators

I'm using jooq 3.9.1. I would like to write queries by adding conditions based on PostgreSQL operators and functions.

E.g., for arrays there are a lot of operators, like && or functions like array_prepend that I would like to use.

What is the best way to achieve this?

I believe there should be something like

int[] array_of_values = {1,2,3};

selectOne()
  .from(TABLE)
  .where(TABLE.COL_A.eq("Hello"))
  .and(TABLE.COL_B.operator("&&").of(array_of_values))
  .fetch();

Upvotes: 4

Views: 2334

Answers (2)

Chris
Chris

Reputation: 8968

There currently is a arrayOverlap() function for the operator in Postgres. https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/util/postgres/PostgresDSL.html#arrayOverlap(org.jooq.Field,org.jooq.Field)

Upvotes: 0

Lukas Eder
Lukas Eder

Reputation: 220762

In jOOQ 3.9, the standard way to go forward here is to use the plain SQL API

selectOne()
  .from(TABLE)
  .where(TABLE.COL_A.eq("Hello"))
  .and("{0} && {1}", TABLE.COL_B, val(array_of_values))
  .fetch();

This is using the SelectConditionStep.and(String, QueryPart...) method for convenience, but there are other ways, including using DSL.condition()

Your idea is a very good one. I've registered a feature request for jOOQ 3.10.

Upvotes: 5

Related Questions