How to select multiple rows by their composite primary keys in JOOQ?

Question

I have a table with composite primary key. I want to find rows with some set of primary keys.

My table is:

create table test_tbl
(
    id_part_1 varchar(36) not null,
    id_part_2 varchar(36) not null,
    some_data text not null,
    constraint test_tbl_pkey
        primary key (id_part_1, id_part_2)
);

My SQL query is:

SELECT * FROM test_tbl
    WHERE (id_part_1, id_part_2) IN (('id_part_1_1', 'id_part_2_1'), ('id_part_1_2', 'id_part_2_2'));

So, how to implement this query with help of JOOQ? I don't generate JOOQ Dao, I just have JOOQ tables.

Answer 1

How to do this manually

You can translate your SQL query directly to jOOQ using DSL.row() to construct a row value expression, and then:

row(TEST_TBL.ID_PART_1, TEST_TBL.ID_PART_2).in(
  row("id_part_1_1", "id_part_2_1"),
  row("id_part_1_2", "id_part_2_2")
);

See also the jOOQ manual section about the IN predicate, degree > 1

Use embeddable keys

Alternatively, you can profit from the additional type safety offered by the new jOOQ 3.14 <embeddablePrimaryKeys/> feature, which allows you to generate record types for all primary keys and their referencing foreign keys. Your query would then read:

ctx.select()
   .from(TEST_TBL)
   .where(TEST_TBL.TEST_TBL_PKEY.in(
      new TestTblPkeyRecord("id_part_1_1", "id_part_2_1"),
      new TestTblPkeyRecord("id_part_1_2", "id_part_2_2")
   ))
   .fetch();

This produces the same query as your original one, but does so type safely, and you'll never forget a key column again. Not only when you query the primary key, but also when you join it! Changing the key will result in a compilation error:

ctx.select()
   .from(TEST_TBL)
   .join(OTHER_TEST_TBL)
   .on(TEST_TBL.TEST_TBL_PKEY.eq(OTHER_TEST_TBL.TEST_TBL_PKEY.TEST_TBL_FKEY))
   .fetch();

Or an implicit join would look like this:

ctx.select(OTHER_TEST_TBL.testTbl().fields(), OTHER_TEST_TBL.fields())
   .from(OTHER_TEST_TBL)
   .fetch();