jOOQ how to execute Postgresql functions and operators

Question

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();

Answer 1

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.