Querying a composite key with multiple IN values with jOOQ

I have the following query:

SELECT *
FROM table
WHERE (id, other_id, status)
IN (
    (1, 'XYZ', 'OK'),
    (2, 'ZXY', 'OK') -- , ...
);

Is it possible to construct this query in a type-safe manner using jOOQ, preferably without generating composite keys? Is it possible to do this using jOOQ 3.11?

Solution

Your own answer already shows how to do this with a 1:1 translation from SQL to jOOQ using the IN predicate for degrees > 1.

Starting from jOOQ 3.14, there is also the option of using the new <embeddablePrimaryKeys/> flag in the code generator, which will produce embeddable types for all primary keys (and foreign keys referencing them). This will help never forget a key column on these queries, which is especially useful for joins.

Your query would look like this:

ctx.selectFrom(TABLE)
   .where(TABLE.PK_NAME.in(
      new PkNameRecord(1, "XYZ", "OK"), 
      new PkNameRecord(2, "ZXY", "OK")))
   .fetch();

The query generated behind the scenes is the same as yours, using the 3 constraint columns for the predicate. If you add or remove a constraint from the key, the query will no longer compile. A join would look like this:

ctx.select()
   .from(TABLE)
   .join(OTHER_TABLE)
   .on(TABLE.PK_NAME.eq(OTHER_TABLE.FK_NAME))
   .fetch();

Or an implicit join would look like this:

ctx.select(OTHER_TABLE.table().fields(), OTHER_TABLE.fields())
   .from(OTHER_TABLE)
   .fetch();