Querying a composite key with multiple IN values with jOOQ

My apologies, it seems my Google-fu was not up to par. The opposite of this question can be found here: Use JOOQ to do a delete specifying multiple columns in a "not in" clause

For completeness' sake, so that other Google searches might be more immediately helpful, the solution is:

// can be populated using DSL.row(...); for each entry
Collection<? extends Row3<Long, String, String>> values = ... 

dslContext.selectFrom(TABLE)
 .where(DSL.row(ID, OTHER_ID, STATUS).in(values))
 .fetch();

Relevant jOOQ documentation: https://www.jooq.org/doc/3.14/manual/sql-building/conditional-expressions/in-predicate-degree-n/

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();