Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17

Optional column expressions

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

A key capability when creating dynamic SQL queries is to be able to provide optional column expressions. For example, imagine you have a condition based on which you want to add an ORDER BY clause and a LIMIT clause. You can do this using DSL.noField():

boolean condition = ...

create.select(BOOK.ID)
      .from(BOOK)
      .orderBy(condition ? BOOK.ID : noField())
      .limit(condition ? val(10) : noField(INTEGER))
      .fetch();

The above query produces:

-- If condition is true
SELECT book.id FROM book ORDER BY book.id LIMIT 10

-- If condition is false
SELECT book.id FROM book

In clauses that do not project columns, the noField() expression will be ignored. If that means the clause is empty, then the entire clause will be omitted. This does not apply to clauses that project a Record type, including the SELECT clause, row value expressions, or nested records, as well as function calls, in case of which a NULL value will be projected.

Using a noField() as a conditional expression, e.g. by wrapping it with DSL.condition(noField()) will produce a DSL.noCondition().

The noField() expression is supported only in the DSL API, not in the model API, where the behaviour of noField() is undefined.

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo