How to write OPTIONAL where clauses in JOOQ

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.

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.