In jooq, how do I add an "and" condition when don't know whether there is a "where" prior to it or not?

Question

I have the following Jooq code:

if (...) {
    query = query.where(...);
}

query = query.and(...);

The problem is that I don't know whether the where() statement will be added or not.

If not, the and() part will be added without a prior where(), which should be wrong.

I am wondering how to solve that.

Must I write it as follows?

if (...) {
    query = query.where(...);
}
else {
    query = query.where(true);    // This will make sure there is always a where statement, but it looks hacky...
}

query = query.and(...);

Or, must I adjust the statements' order?

What if I want to keep the order?

Thanks!

Answer 1

Don't create dynamic queries. Create dynamic conditions.

As always, assuming this static import:

import static org.jooq.impl.DSL.*;

Imperative style

Condition condition = noCondition();

// And then
if (something1)
    condition = condition.and(c1);

if (something2)
    condition = condition.and(c2);

ctx.select(...)
   .from(...)
   .where(condition)
   .fetch();

Expression style

ctx.select(...)
   .from(...)
   .where(something1 ? c1 : noCondition())
   .and(something2 ? c2 : noCondition())

Functional style

ctx.select(...)
   .from(...)
   .where(Stream
       .of(1, 2, 3)
       .map(BOOK.ID::eq)
       .reduce(noCondition(), Condition::or))
   .fetch();

There are probably many other styles. See also:

• https://www.jooq.org/doc/latest/manual/sql-building/dynamic-sql/
• https://www.jooq.org/doc/latest/manual/sql-building/conditional-expressions/true-false-no-condition/
• https://blog.jooq.org/create-empty-optional-sql-clauses-with-jooq/