The jOOQ User Manual SQL building Conditional expressions TRUE and FALSE condition

previous : next

TRUE and FALSE condition

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

When a conditional expression is mandatory, or when using dynamic SQL, it may be required to provide a "dummy" condition that always evaluates to TRUE or FALSE. For this purpose, you can use DSL.trueCondition(), DSL.falseCondition(), or DSL.noCondition(). For example:

TRUE

TRUE is the identity value of the AND boolean operator, and can be used for procedural or functional reduction of a set of values to a condition:

TRUE
AND
  ID = 1
AND
  TITLE = 'Animal Farm'

Condition condition = trueCondition();
if (id != null)
    condition = condition.and(BOOK.ID.eq(id));
if (title != null)
    condition = condition.and(BOOK.TITLE.eq(title));

If a dialect does not support boolean column types, jOOQ will simply generate 1 = 1.

FALSE

FALSE is the identity value of the OR boolean operator, and can be used for procedural or functional reduction of a set of values to a condition:

FALSE
OR
  ID = 1
OR
  ID = 7

List<Integer> list = List.of(1, 7);
Condition condition = list
    .stream()
    .map(BOOK.ID::eq)
    .reduce(falseCondition(), Condition::or)

If a dialect does not support boolean column types, jOOQ will simply generate 1 = 0.

NO condition

If you think that the "left-over" identity that is generated from the above reductions is ugly, you can just use the auxiliary DSL.noCondition(), which acts as a pseudo-identity for both AND and OR, not generating any SQL, except if the reduction produces nothing (from an empty set), in case of which it will behave like TRUE, which is what you usually want when placing a dynamic condition in a WHERE clause.

If used with actual data:

ID = 1
OR
  ID = 7

List<Integer> list = List.of(1, 7);
Condition condition = list.stream().map(BOOK.ID::eq)
    .reduce(noCondition(), Condition::or)

If used with empty sets:

TRUE
 
 

List<Integer> list = List.of(1, 7);
Condition condition = list.stream().map(BOOK.ID::eq)
    .reduce(noCondition(), Condition::or)

Some additional information about the noCondition() and related topics can be found in the section about dynamic SQL.

The jOOQ User Manual SQL building Conditional expressions TRUE and FALSE condition

previous : next