3

Please suggest JOOQ DSL to query for result of boolean expression
In SQL I would write:

SELECT sum(apples.quantity) > sum(bananas.quantity)
FROM ...
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
snp0k
  • 398
  • 1
  • 5
  • 12
  • Possible duplicate of [How to perform an IF...THEN in an SQL SELECT?](http://stackoverflow.com/questions/63447/how-to-perform-an-if-then-in-an-sql-select) – APC Jan 24 '16 at 11:01

1 Answers1

5

In order to get a jOOQ Condition for your sum comparison, simply write:

Condition condition = sum(apples.quantity).gt(sum(bananas.quantity));

Now, Condition types currently cannot be put into the SELECT clause in jOOQ, but you can wrap them using DSL.field(Condition):

Field<Boolean> field = field(sum(apples.quantity).gt(sum(bananas.quantity)));

jOOQ will take care of deciding whether your SQL dialect allows for using predicates as column expressions (e.g. MySQL, PostgreSQL, SQLite), or whether this needs to be emulated using an equivalent CASE expression (e.g. DB2, HANA, Oracle, SQL Server).

Which leads to:

Record1<Boolean> result =
DSL.using(configuration)
   .select(field(sum(apples.quantity).gt(sum(bananas.quantity))))
   .from(...)
   .fetchOne();
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • jOOQ is like donuts, is there anything it can't do? Now a possible improvement would be to have an asField() method on Condition so it would be more discoverable when using an IDE.e.g. sum(apples.quantity).gt(sum(bananas.quantity)).asField() – Aner Apr 06 '17 at 18:55
  • @Aner: Indeed, having such a method would be consistent with other API (e.g. `FieldLike.asField()`), but in fact, the `asField()` (and `asTable()`) methods suffer from one big drawback: They cannot be made generic, like the `field()` method shown here. – Lukas Eder Apr 06 '17 at 20:49