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();
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.
String to the operator method, but I understand that it could be too much extrawork.
org.jooq.Operator type to abstract over this. Not sure about the enum, because users can define their own operators in most databases, so there will be operator code generation, e.g. for Oracle: github.com/jOOQ/jOOQ/issues/3710There 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)
