“Java, SQL, and jOOQ as Fields for Defining a Condition”

Starting with jOOQ 3.17, the Condition type extends the Field<Boolean> type. Because, that’s what the SQL standard thinks it is, in sorts:

      <boolean value expression> ::=

The exact definition contains intermediate rules, but you get the idea. A <predicate> (which is a Condition in jOOQ) can be used wherever a <boolean value expression> can be used, which again can be used in projections, predicates, and elsewhere.

Not all SQL dialects work this way, and in fact, before SQL:1999 standardised on the BOOLEAN data type, SQL itself didn’t work this way. SQL-92, for example, listed <predicate> as a possible substitute for the <search condition> only, which is used for example in <where clause>, but not in any ordinary <value expression>.

Hence, while this works in PostgreSQL, which supports standard SQL BOOLEAN types:

      SELECT id, id > 2 AS big_id
      FROM book
      ORDER BY id


    |id |big_id|
    |1  |false |
    |2  |false |
    |3  |true  |
    |4  |true  |

It doesn’t work in Oracle, for example, which delights us with the usual useful error message:

SQL Error [923] [42000]: ORA-00923: FROM keyword not found where expected

How this used to work in jOOQ 3.16 or less

jOOQ has always supported a way to use Condition and Field<Boolean> exchangeably. There are the two wrapper methods:

  • DSL.field(Condition) returns Field<Boolean>
  • DSL.condition(Field<Boolean>) returns Condition

This is documented here. As such, the previous query could have been written as follows:

      Result<Record2<Integer, Boolean>> result =
      ctx.select(BOOK.ID, field(BOOK.ID.gt(2)).as("big_id"))
      //                  ^^^^^^^^^^^^^^^^^^^^ wrapping condition with field()

The generated SQL looks like this, for PostgreSQL:

        (book.id > 2) AS big_id
      FROM book
      ORDER BY book.id

And for Oracle, this is the emulation of the feature:

          WHEN book.id > 2 THEN 1
          WHEN NOT (book.id > 2) THEN 0
        END big_id
      FROM book
      ORDER BY book.id

The emulation preserves our beloved three valued logic, i.e. the BOOLEAN value is NULL in case BOOK.ID is NULL.

How this works in jOOQ 3.17, now

Starting from jOOQ 3.17 and #11969, this manual wrapping of field(Condition) is no longer necessary, and you can just project the Condition directly:

      Result<Record2<Integer, Boolean>> result =
      ctx.select(BOOK.ID, BOOK.ID.gt(2).as("big_id"))
      //               ^^^^^^^^^^^^^ no more wrapping necessary

The behaviour is exactly the same as if you had wrapped the condition (including the result type), and the emulation still kicks in also for Oracle and other dialects that don’t support BOOLEAN value expressions. This means you can also use Condition in other clauses that take Field types, including, e.g.:


Time to upgrade your jOOQ version!

Source link

Leave a Reply