Create Empty Optional SQL Clauses with jOOQ

Posted on by lukaseder

When using jOOQ to create dynamic SQL statements (one of jOOQ’s core value propositions), it is often necessary to add query elements conditionally, with a default “No-op” behaviour. For first time users, this default “no-op” behaviour is not always obvious as the jOOQ API is vast, and as with any vast API, there are many different options to do similar things.

How not to do it

A common pitfall is to be tempted to work with the many XYZStep types. What types are these? They are usually invisible to the developer as developers use jOOQ’s DSL API in a fluent fashion, just like the JDK Stream API. For example:

DSLContext ctx = ...;

Result<?> result =
ctx.select(T.A, T.B)
   .from(T)
   .where(T.C.eq(1))
   .and(T.D.eq(2))
   .fetch();

Let’s decompose the above query to see what happens in the API. We could assign every method result to a local variable:

SelectFromStep<?> s1 = ctx.select(T.A, T.B);
SelectWhereStep<?> s2 = s1.from(T);
SelectConditionStep<?> s3 = s2.where(T.C.eq(1));
SelectConditionStep<?> s4 = s3.and(T.D.eq(2))

Result<?> result = s4.fetch();

Our previous fluent API design blog post explains this API design technique.

This is not what people usually do with “static SQL” statements, but they might be tempted to do this if they wanted to add the last predicate (T.D = 2) conditionally, e.g:

DSLContext ctx = ...;

SelectConditionStep<?> c =
ctx.select(T.A, T.B)
   .from(T)
   .where(T.C.eq(1));

if (something)
    c = c.and(T.D.eq(2));

Result<?> result = c.fetch();

This is perfectly valid API usage, but we do not recommend it because it is very messy and leads to difficult to maintain client code. Also, it is absolutely unnecessary, because there is a better way:

Composing queries from its parts

The problem with the above approach is that it is trying to use an imperative approach of adding things to a query step by step. This is how many developers tend to structure their code, but with SQL (and by consequence, jOOQ) that can turn out to be difficult to get right. A functional approach tends to work better.

Notice that not only the entire DSL structure could be assigned to local variables, but also the individual SELECT clause arguments. For example:

DSLContext ctx = ...;

List<SelectField<?>> select = Arrays.asList(T.A, T.B);
Table<?> from = T;
Condition where = T.C.eq(1).and(T.D.eq(2));

Result<?> result =
ctx.select(select)
   .from(from)
   .where(where)
   .fetch();

In fact, every jOOQ query is a dynamic SQL query. Many queries just happen to look like static queries, due to jOOQ’s API design.

Again, we wouldn’t be assigning every SELECT clause argument to a local variable, only the truly dynamic ones. For example:

DSLContext ctx = ...;

Condition where = T.C.eq(1);

if (something)
    where = where.and(T.D.eq(2));

Result<?> result =
ctx.select(T.A, T.B)
   .from(T)
   .where(where)
   .fetch();

This already looks quite decent.

Avoid breaking readability

A lot of people aren’t happy with this approach either, because it breaks a query’s readability by making its components non-local. The predicates in the query are declared up front, away from the query itself. This isn’t how many people like to reason about SQL.

And you don’t have to! It is totally possible to embed the condition directly in the WHERE clause like this:

DSLContext ctx = ...;

Result<?> result =
ctx.select(T.A, T.B)
   .from(T)

   // We always need this predicate
   .where(T.C.eq(1))

   // This is only added conditionally
   .and(something
      ? T.D.eq(2)
      : DSL.noCondition())
   .fetch();

The magic is in the above usage of DSL.noCondition, which is a pseudo predicate that does not generate any content. It is a placeholder where an org.jooq.Condition type is required without actually materialising one.

There is also:

… but that requires having to think about these identities and the reductions all the time. Also, if you append many of these trueCondition() or falseCondition() to a query, the resulting SQL tends to be quite ugly, for example for people having to analyse performance in production. noCondition() just never generates any content at all.

Note that noCondition() does not act as an identity! If your noCondition() is the only predicate left in a WHERE clause, there will not be any WHERE clause, regardless if you work with AND predicates or OR predicates.

No-op expressions in jOOQ

When using dynamic SQL like this, and adding things conditionally to queries, such “no-op expressions” become mandatory. In the previous example, we’ve seen how to add a “no-op predicate” to a WHERE clause (the same approach would obviously work with HAVING and all other clauses that work with boolean expressions).

The three most important jOOQ query types are:

Users may want to add all of these conditionally to queries.

org.jooq.Condition

We’ve already seen how to do this with org.jooq.Condition.

org.jooq.Field

What about dynamic column expressions in the projection (the SELECT clause)? Assuming you want to project columns only in certain cases. In our example, the T.B column is something we don’t always need. That’s easy! The same approach can be used (assuming T.B is a string column):

DSLContext ctx = ...;

Result<Record2<String, String>> result =
ctx.select(T.A, something ? T.B : DSL.inline("").as(T.B))
   .from(T)
   .where(T.C.eq(1))
   .and(T.D.eq(2))
   .fetch();

Using inlined parameters via DSL.inline(), you can easily produce a no-op value in your projection if you don’t want to modify the projection’s row type. The advantage is that you can now use this subquery in a union that expects two columns:

DSLContext ctx = ...;

Result<Record2<String, String>> result =

// First union subquery has a conditionally projected column
ctx.select(T.A, something ? T.B : DSL.inline("").as(T.B))
   .from(T)
   .where(T.C.eq(1))
   .and(T.D.eq(2))

   .union(

// Second union subquery has no such conditions
    select(U.A, U.B)
   .from(U))
   .fetch();

You can take this one step further, and make an entire union subquery conditional this way!

DSLContext ctx = ...;

Result<Record2<String, String>> result =

// First union subquery has a conditionally projected column
ctx.select(T.A, T.B)
   .from(T)
   .union(
      something
        ? select(U.A, U.B).from(U)
        : select(inline(""), inline("")).where(falseCondition())
   )
   .fetch();

This is a bit more syntactic work, but it’s nice to see how easy it is to add something conditionally to a jOOQ query without making the query completely unreadable. Everything is local to where it is being used. No local variables are needed, no imperative control flow is invoked.

And because everything is now an expression (and not a statement / no control flow), we can factor out parts of this query into auxiliary methods, that can be made reusable.

org.jooq.Table

Conditional table expressions usually appear when doing conditional joins. This is usually not done in isolation, but together with other conditional elements in a query. E.g. if some columns are projected conditionally, those columns may require an additional join, as they originate from another table than the tables that are used unconditionally. For example:

DSLContext ctx = ...;

Result<?> result =
ctx.select(
      T.A, 
      T.B, 
      something ? U.X : inline(""))
   .from(
      something
      ? T.join(U).on(T.Y.eq(U.Y))
      : T)
   .where(T.C.eq(1))
   .and(T.D.eq(2))
   .fetch();

There isn’t a more simple way to produce the conditional JOIN expression, because JOIN and ON need to be provided independently. For simple cases as shown above, this is perfectly fine. In more complex cases, some auxiliary methods may be needed, or views.

Conclusion

There are two important messages here in this post:

  1. The XYZStep types are auxiliary types only. They are there to make your dynamically constructed SQL statement look like static SQL. But you should never feel the need to assign them to local variables, or return them from methods. While it is not wrong to do so, there is almost always a better way to write dynamic SQL.
  2. In jOOQ, every query is a dynamic query. This is the benefit of composing SQL queries using an expression tree like the one that is used in jOOQ’s internals. You may not see the expression tree because the jOOQ DSL API mimicks static SQL statement syntax. But behind the scenes, you’re effectively building this expression tree. Every part of the expression tree can be produced dynamically, from local variables, methods, or expressions, such as conditional expressions. I’m looking forward to using the new JEP 361 switch expressions in dynamic SQL. Just like a SQL CASE expression, some SQL statement parts can be constructed dynamically in the client, prior to passing them to the server.

Once these two things are internalised, you can write very fancy dynamic SQL, including using FP approaches to constructing data structures, such as a jOOQ query object.

Published by lukaseder

I made jOOQ

6 thoughts on “Create Empty Optional SQL Clauses with jOOQ”

  1. I prefer “Composing queries from its parts” approach. It took me some time to figure out that “if” statements would not work for dynamic search queries. This post is a must-read for every new JOOQ user.

    Reply

    1. By “if statements”, you mean assigning the Step types to local variables and appending stuff in an “if statement”, right?

      I did write this post with it being such a must-read in mind. However, I’d also like to learn what could be improved so that users don’t have to read it first. In hindsight, do you think we could have changed something in the API to help you come to this conclusion yourself, and earlier?

      Reply

      1. Fluent API makes “if” appending logical. One approach would be to always return the same object, but that would ruin autocomplete offering only the possible methods. Another solution would be to prohibit building everything in one big fluent chain; for example, rather than calling “and” after “where”, a user could only call “groupingBy” and similar; “where” would be completely built inside the parenthesis or passed as a list of conditions. Example:

        select()
  .from(TABLE)
  .where(a.eq.(x).and(b.eq(y)))
  .groupingBy(1)

        This would be deprecated:

        select()
  .from(TABLE)
  .where(a.eq.(x))
  .and(b.eq(y))
  .groupingBy(1)
        Reply

        1. Thanks for the clarifications.

          Ah yes, those convenience methods. A more “pure” API would indeed not need those optional SelectConditionStep and similar types. The API surface would be smaller, and the maintenance easier, etc. But those methods are very convenient in jOOQ. The parentheses tend to become prohibitive in the first example. Look how the second example reads more nicely…

          I agree that the dynamic SQL confusion would have been prevented this way, though. Returning always the same object is not an option. QueryDSL is doing that, and the queries written by beginners as I’ve seen on their mailing list, github, and stack overflow are horrible. People will write things like:

          select()
  .where(x)
  .from(y)
  .groupBy(z)
  .from(s);

          There’s no benefit in doing this, yet it leads to major confusion, and subtle edge cases that produce invalid SQL.

          Reply

  2. I think jOOQ should have some interfaces we can reference safely.
    For example:

    Is pretty common to have a “Base Query”, which is dinamic based on filters and sorts that the user inputs, and using that base-query we need to add: Pagination and do a Count of all the results.

    What i tipically use is a base QueryBuilder class, which does this common addPagination and count for all queries (among other stuff that is common for query building).
    This way, sub classes only need to implement a “getQuery(Filter f)” method and all common stuff is handled.

    In order to do that, i NEED to reference those interfaces that jOOQ don’t recomend doing so.
    My QueryBuilder class would have somehting this:

    public QueryResponse build(F filter) {
    var query = createQuery(filter);
    var total = dsl.fetchCount(query);
    var sortedQuery = addOrderBy(filter, query);
    var sortedAndPagedQuery = addPaging(filter, sortedQuery);
    var queryResult = new QueryResponse(total, sortedAndPagedQuery.fetchInto(clazz));
    postProcessResults(queryResult, filter);
    return queryResult;
    }

    In order to use this, the createQuery(filter) has to reference one of those “Steps” interfaces. I’m currently using SelectGroupByStep which suits most of my cases.

    Here’s the full example for my QueryBuilder class:

    import com.elm.converge.core.domain.Filter;
    import com.elm.converge.core.domain.QueryResponse;
    import com.opencsv.bean.HeaderColumnNameMappingStrategy;
    import com.opencsv.bean.StatefulBeanToCsvBuilder;
    import com.opencsv.exceptions.CsvDataTypeMismatchException;
    import com.opencsv.exceptions.CsvRequiredFieldEmptyException;
    import com.querydsl.core.types.*;
    import java.lang.reflect.ParameterizedType;
    import java.util.*;
    import java.util.Comparator;
    import java.util.function.Supplier;
    import javax.sql.DataSource;
    import org.apache.commons.io.output.StringBuilderWriter;
    import org.apache.commons.lang3.StringUtils;
    import org.jooq.*;
    import org.springframework.beans.factory.annotation.Autowired;

    import static org.apache.commons.lang3.ObjectUtils.isNotEmpty;
    import static org.apache.logging.log4j.util.Strings.isNotBlank;

    @SuppressWarnings({“rawtypes”, “unchecked”})
    public abstract class QueryBuilder {

    private final Class<T> clazz;
    protected DataSource dataSource;
    protected DSLContext dsl;

    protected QueryBuilder() {
    this.clazz = (Class<T>) ((ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[0];
    }

    @Autowired
    public final void setDataSource(DataSource dataSource) {
    this.dataSource = dataSource;
    }

    @Autowired
    public final void setDsl(DSLContext dsl) {
    this.dsl = dsl;
    }

    public abstract TableField getOrderByExpression(String sortColumn);

    public abstract SelectGroupByStep createQuery(F filter);

    public QueryResponse<T> build(F filter) {
    var query = createQuery(filter);
    var total = dsl.fetchCount(query);
    var sortedQuery = addOrderBy(filter, query);
    var sortedAndPagedQuery = addPaging(filter, sortedQuery);
    var queryResult = new QueryResponse<T>(total, sortedAndPagedQuery.fetchInto(clazz));
    postProcessResults(queryResult, filter);
    return queryResult;
    }

    public byte[] buildCsv(F filter) throws CsvRequiredFieldEmptyException, CsvDataTypeMismatchException {
    var query = createQuery(filter);
    var sortedQuery = addOrderBy(filter, query);
    var results = sortedQuery.fetchInto(clazz);
    try (var writer = new StringBuilderWriter()) {
    var strategy = new HeaderColumnNameMappingStrategy<T>();
    strategy.setType(clazz);
    var declaredFields = Arrays.stream(clazz.getDeclaredFields())
    .map(field -> StringUtils.upperCase(field.getName()))
    .toList();
    strategy.setColumnOrderOnWrite(Comparator.comparingInt(declaredFields::indexOf));
    var beanToCsv = new StatefulBeanToCsvBuilder(writer)
    .withMappingStrategy(strategy)
    .build();
    beanToCsv.write(results);
    return writer.toString().getBytes();
    }
    }

    protected void postProcessResults(QueryResponse<T> queryResponse, F filter) {
    // Override this method if you need extra behavior after fetching the results
    }

    protected static <C> void filterIfNotEmpty(
    SelectJoinStep query, Collection<C> filter, Supplier<Condition> whereLambda) {
    if (isNotEmpty(filter)) {
    query.where(whereLambda.get());
    }
    }

    protected static void filterIfPresent(SelectJoinStep query, String filterItem, Supplier<Condition> whereLambda) {
    if (isNotBlank(filterItem)) {
    query.where(whereLambda.get());
    }
    }

    protected static void filterIfPresent(SelectJoinStep query, Object filterItem, Supplier<Condition> whereLambda) {
    if (filterItem != null) {
    query.where(whereLambda.get());
    }
    }

    protected SelectForUpdateStep addPaging(F filter, SelectSeekStep1 query) {
    if (filter.getPageSize() >= 0) {
    return query.limit(filter.getPageSize()).offset(filter.getOffset());
    }
    return query;
    }

    public long count(F filter) {
    return dsl.fetchCount(createQuery(filter));
    }

    @SuppressWarnings({"unchecked"})
    protected SelectSeekStep1 addOrderBy(F filter, SelectGroupByStep query) {
    var orderField = getOrderByExpression(filter.getSortColumn());
    if (Order.DESC == filter.getSortOrder()) {
    return query.orderBy(orderField.desc());
    } else {
    return query.orderBy(orderField.asc());
    }
    }

    }

    Reply

    1. Thanks for your message.

      Have you read the article? Why do you prefer the imperative, mutable style over the one from the article? In any case, we’re not going to go in this direction…

      Reply

Leave a Reply