Writing dynamic queries

Commonly, a dynamic query contains no or some fixed parts and some other parts that can be appended at runtime to form a query that corresponds to a certain scenario or use case.

Important Note

In jOOQ, even when they look like static queries (due to jOOQ's API design), every SQL is dynamic; therefore, it can be broken up into query parts that can be fluently glued back in any valid jOOQ query. We already have covered this aspect in Chapter 3, jOOQ Core Concepts, in the Understanding the jOOQ fluent API section.

Dynamically creating an SQL statement on the fly is one of the favorite topics of jOOQ, so let's try to cover some approaches that can be useful in real applications.

Using the ternary operator

The Java ternary operator (?) is probably the simplest approach for shaping a query at runtime. Check out this sample:

public List<ProductRecord> fetchCarsOrNoCars(
       float buyPrice, boolean...