How to write OPTIONAL where clauses in JOOQ

Question

List<MyTable> result = DSL.using(configuration())
                .select()
                .from(MY_TABLE)
                .where(MY_TABLE.ID1.equal(pk_id1))
                        .and(MY_TABLE.ID2.equal(fk_id2))
                        .and(MY_TABLE.ID3.equal(fk_id3))
                .orderBy(MY_TABLE.ID.asc())
                .limit(limit)
                .fetchInto(MY_TABLE)
                .map(mapper());

I'm trying to write some code that will allow my query to take three OPTIONAL id's for example I would like the query to ultimately be

select * from my_table where ID1=5 and ID2=6 and ID3=7 .... etc

However, I would also like the option of being able to exclude any of the id's

select * from my_table where ID2=6 and ID3=7

or

select * from my_table where ID3=7

The problem with this is that the first "where" clause belongs to id one and the rest are ands so if I did an if statement and I removed the where then I would just be left with

List<MyTable> result = DSL.using(configuration())
                .select()
                .from(MY_TABLE)
                        .and(MY_TABLE.ID2.equal(fk_id2))
                        .and(MY_TABLE.ID3.equal(fk_id3))
                .orderBy(MY_TABLE.ID.asc())
                .limit(limit)
                .fetchInto(MY_TABLE)
                .map(mapper());

and it wouldn't work.

I tried to look for something like where id = * where * is essentianlly no filter but I couldn't find anything like that.

Lukas Eder · Accepted Answer · 2020-10-01T07:49:39.077

jOOQ makes writing SQL feel as though it is static, embedded SQL. But it isn't. Every jOOQ query is a dynamic SQL query composed of an expression tree - you just don't notice it.

The SelectWhereStep.where(Condition) method takes a Condition argument, which you don't have to put right there with the WHERE clause. You can construct it before the query:

Condition condition = DSL.noCondition(); // Alternatively, use trueCondition()
if (something)
    condition = condition.and(MY_TABLE.ID1.equal(pk_id1));
if (somethingElse)
    condition = condition.and(MY_TABLE.ID2.equal(fk_id2));
if (somethingOther)
    condition = condition.and(MY_TABLE.ID3.equal(fk_id3));

You can now pass that to your query:

List<MyTable> result = 
DSL.using(configuration())
   .select()
   .from(MY_TABLE)
   .where(condition)
   .orderBy(MY_TABLE.ID.asc())
   .limit(limit)
   .fetchInto(MY_TABLE)
   .map(mapper());

There are also utility methods in DSL, such as:

DSL.condition(Map<Field<?>,?>) constructing a condition from a field / value comparison map
DSL.condition(Record) for using the Query By Example pattern
DSL.condition(Operator, Condition...) or simply DSL.and(Condition...) for constructing AND connected conditions from an array (or collection)

This is also documented here in the manual: http://www.jooq.org/doc/latest/manual/sql-building/dynamic-sql/

Notice, there really isn't any need, usually, to reference the XYZStep types directly. You should mostly be able to write dynamic SQL through more elegant ways, as I've shown in this blog post.

I'm trying it out but for some reason trueCondition always evaluates to true so my query just returns everything. Is there another condition to use? — Luke Xu, May 15 '16 at 23:37
@LukeXu: Yes that's the purpose of the trueCondition... :) are you sure you're assigning each new condition back to the variable? The and() operation creates a new condition. It doesn't modify the left hand side — Lukas Eder, May 16 '16 at 05:48

score 3 · Answer 2 · answered May 15 '16 at 13:42

What you are asking for is sometimes refereed to as "SQL 'number' wildcarding" or at least you can find comments on the web if you search for that.

SQL does not allow you to write "where id = *" instead if you need your DDL to remain static you can emulate it by doing a range check like

select * from table
  where
    (my_table.id1 >= fk_id1_low and my_table.id1 <= fk_id1_high) and
    (my_table.id2 >= fk_id2_low and my_table.id2 <= fk_id2_high) and
    (my_table.id3 >= fk_id3_low and my_table.id3 <= fk_id3_high)

So now you are passing a total of 6 parameters to the query, if you want to do an match against id1 you will set both fk_id1_low and fk_id1_high to the value you want to match. If you do not want to match against id1 you will set fk_id1_low to the minimum possible value and fk_id1_high to the maximum possible value. One thing you must consider doing this is how the resulting query will be processed by the database engine as large amounts of extra work maybe carried out.

With JOOQ another possible answer is to move away from the fluent interface so that you can build the query using if..then conditional parts.

How to write OPTIONAL where clauses in JOOQ

2 Answers2