JOOQ获取外键表

Question

我有三张桌子：

Users
Keys
UserKeys

用户键表具有来自用户和键表的主键，以建立用户和键之间的关系。

如何使用所有相关键获取用户？

如果存在其他表（例如，UserRoles）等，该怎么办？通常，如何获取用户和所有通过外键表相关的相关行？

Answer 1

我假设您正在使用jOOQ的代码生成器。编写连接就像在SQL中编写连接一样：

ctx.select() // Optionally, list columns here, explicitly
   .from(USERS)
   .join(USER_KEYS).on(USERS.ID.eq(USER_KEYS.USER_ID))
   .join(KEYS).on(USER_KEYS.KEY_ID.eq(KEYS.ID))
   .where(USERS.NAME.eq("something"))
   .fetch();

如果存在其他表（例如UserRoles）等怎么办？通常，如何获取用户和所有关联的行，通过外键表关联？

我不确定这是否还是同一个问题。上述内容可能是关于如何进行连接的，这一部分似乎更具体地介绍了如何获取嵌套集合？

因为一旦连接多条路径，连接总是会产生笛卡尔积，这是不可取的。从jOOQ 3.14开始，如果您的数据库支持，您可以使用SQL/XML或SQL/JSON作为解决方法。从jOOQ 3.15开始，可以使用MULTISET。例如，JSON解决方案可能如下所示：

List<User> users =
ctx.select(jsonObject(
     jsonEntry("id", USERS.ID),
     jsonEntry("name", USERS.NAME),
     jsonEntry("keys", field(
       select(jsonArrayAgg(jsonObject(KEYS.NAME, KEYS.ID)))
       .from(KEYS)
       .join(USER_KEYS).on(KEYS.ID.eq(USER_KEYS.KEY_ID))
       .where(USER_KEYS.USER_ID.eq(USER.ID))
     )),
     jsonEntry("roles", field(
       select(jsonArrayAgg(jsonObject(ROLES.NAME, ROLES.ID)))
       .from(ROLES)
       .join(USER_ROLES).on(ROLES.ID.eq(USER_ROLES.ROLE_ID))
       .where(USER_ROLES.USER_ID.eq(USER.ID))
     ))
   ))
   .from(USERS)
   .where(USERS.NAME.eq("something"))
   .fetchInto(User.class);

假设用户类看起来像这样，并且类路径上有Gson或Jackson，可以从JSON映射到Java数据结构：

class Key {
  long id;
  String name;
}

class Role {
  long id;
  String name;
}

class User {
  long id;
  String name;

  List<Key> keys;
  List<Role> roles;
}

当然，您不必映射到Java数据结构并直接生成JSON结果，而无需进一步映射。有关更多详细信息，请参阅这篇博客文章，或者这篇文章解释了如何使用MULTISET。

请注意，JSON_ARRAYAGG（）将空集聚合到NULL中，而不是空[]中。如果有问题，请使用COALESCE（）

MULTISET解决方案如下所示：

List<User> users =
ctx.select(
     USERS.ID,
     USERS.NAME,
     multiset(
       select(KEYS.NAME, KEYS.ID)
       .from(KEYS)
       .join(USER_KEYS).on(KEYS.ID.eq(USER_KEYS.KEY_ID))
       .where(USER_KEYS.USER_ID.eq(USER.ID))
     ).as("keys").convertFrom(r -> r.map(Records.mapping(Key::new))),
     multiset(
       select(ROLES.NAME, ROLES.ID)
       .from(ROLES)
       .join(USER_ROLES).on(ROLES.ID.eq(USER_ROLES.ROLE_ID))
       .where(USER_ROLES.USER_ID.eq(USER.ID))
     ).as("roles").convertFrom(r -> r.map(Records.mapping(Role::new)))
   )
   .from(USERS)
   .where(USERS.NAME.eq("something"))
   .fetch(Records.mapping(User::new));

使用各种Records.mapping（）重载以及ad-hoc数据类型转换的上述方法假定存在不可变构造函数，例如如果您的类Java16条记录，您会得到：

record Key (int id, String name) {}
record Role (int id, String name) {}
record User (int id, String name, List<Key> keys, List<Role> roles) {}