Best way to use dynamic SQL with jOOQ

user_5743485 0
Viewed 45

Currently I'm using RQL (https://github.com/persvr/rql) as rest query language. Previously, Query DSL was used with the meta model for the SQL queries.

It is very easy to navigate over the metamodel to create a Predicate. It is enough to use the root entity and was able to work your way through all the relationships and find out the Predicate

Example:

class Foo{
  private Set<Bar> bars
}

class Bar{
  private String name;
}

rql: filter=eq(bars.name, "test)

Query DSL is: QFoo.bars --> SetPath and then navigate to bars.name is a SimpleExpression then you can call SimpleExpression.eq returns the Predicate.

Is there also this possibility with jOOQ to create the condition dynamically?

I can't find way with the statically generated fields because they don't contain any relationships, do they?

What is the best way to create the condition using the root resource? Hints?

Or can I simply create a condition from the predicate?

jooq sql java
1 Answers

As of jOOQ 3.17, to-many path expressions aren't yet available, see https://github.com/jOOQ/jOOQ/issues/13639

But you don't need them anyway, they're just syntax sugar for an IN or EXISTS predicate. E.g.

FOO.ID.in(select(BAR.FOO_ID).from(BAR).where(BAR.NAME.eq("test")))

It's a bit more verbose, but straightforward, I think? Note that every jOOQ query is a dynamic SQL query. You're not required to write the predicate like this. You can compose it from its individual parts completely dynamically.

user_5217990
Related