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.

Answer 1

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.