How not to do itA common pitfall is to be tempted to work with the many
XYZSteptypes. 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:
Let’s decompose the above query to see what happens in the API. We could assign every method result to a local variable:
DSLContext ctx = ...; Result<?> result = ctx.select(T.A, T.B) .from(T) .where(T.C.eq(1)) .and(T.D.eq(2)) .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 (
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();
T.D = 2) conditionally, e.g:
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:
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();
Composing queries from its partsThe 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
SELECTclause arguments. For example:
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
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();
SELECTclause argument to a local variable, only the truly dynamic ones. For example:
This already looks quite decent.
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();
Avoid breaking readabilityA 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
WHEREclause like this:
The magic is in the above usage of
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();
DSL.noCondition, which is a pseudo predicate that does not generate any content. It is a placeholder where an
org.jooq.Conditiontype is required without actually materialising one. There is also:
1 = 1in SQL, the identity for
1 = 0in SQL, the identity for
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.
noCondition()does not act as an identity! If your
noCondition()is the only predicate left in a
WHEREclause, there will not be any
WHEREclause, regardless if you work with
No-op expressions in jOOQWhen 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
WHEREclause (the same approach would obviously work with
HAVINGand all other clauses that work with boolean expressions). The three most important jOOQ query types are:
org.jooq.Field: for column expressions
org.jooq.Condition: for boolean column expressions / predicates
org.jooq.Table: for table expressions
org.jooq.Condition. org.jooq.Field What about dynamic column expressions in the projection (the
SELECTclause)? Assuming you want to project columns only in certain cases. In our example, the
T.Bcolumn is something we don’t always need. That’s easy! The same approach can be used (assuming
T.Bis a string column):
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 = 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();
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, 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();
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<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();
There isn’t a more simple way to produce the conditional
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();
ONneed 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.
ConclusionThere are two important messages here in this post:
- 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.
- 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
CASEexpression, some SQL statement parts can be constructed dynamically in the client, prior to passing them to the server.