如何在JOOQ中通过其组合主键选择多行? [英] How to select multiple rows by their composite primary keys in JOOQ?

问题描述

我有一个带有复合主键的表.我想找到带有一些主键的行.

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

我的桌子是:

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

我的SQL查询是:

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

那么，如何在JOOQ的帮助下实现此查询?我不会生成JOOQ Dao，而只有JOOQ表.

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

推荐答案

如何手动执行

您可以使用

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

另请参见关于IN谓词，程度>的jOOQ手册部分. 1

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

或者，您可以从新的jOOQ 3.14

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

或隐式连接看起来像这样:

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

这篇关于如何在JOOQ中通过其组合主键选择多行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！