JOOQ获取外键表 [英] JOOQ fetch over foreign keys table

问题描述

我有三个表:

Users
Keys
UserKeys

UserKeys表同时具有Users和Keys表中的主键，以建立用户和键之间的关系.

The UserKeys table has both primary keys from Users and Keys tables to establish the relation between users and keys.

如何获取具有所有相关键的User?

How to fetch a User with all it's related keys?

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

What if additional tables exist (for instance UserRoles), etc. In general, how to fetch a user and all associated rows, related via foreign keys tables?

推荐答案

使用标准SQL JOIN

我假设您正在使用jOOQ的代码生成器.您可以像在SQL中编写联接一样编写联接:

Using standard SQL JOIN

I'm assuming you're using jOOQ's code generator. You write a join just like you would write a join in 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)等怎么办?通常，如何获取通过外键表关联的用户和所有关联的行?

What if additional tables exist (for instance UserRoles), etc. In general, how to fetch a user and all associated rows, related via foreign keys tables?

我不确定这是否还是同样的问题.上面的内容大概是关于如何进行联接的，这似乎是关于如何获取嵌套集合的更具体的信息?

I'm not sure if this is still the same question. The above may have been about how to do joins in general, this one seems to be more specific about how to fetch nested collections?

因为JOIN总是会产生笛卡尔积，一旦您加入了多对多路径，这是不希望的.从即将发布的jOOQ 3.14开始，如果数据库支持，则可以使用SQL/XML或SQL/JSON作为解决方法.例如:

Because a JOIN will always produce cartesian products, which are undesirable, once you're joining several to-many paths. Starting from the upcoming jOOQ 3.14, you can use SQL/XML or SQL/JSON as a workaround for this, if your database supports that. For example:

List<Student> students =
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);

假设User类看起来像这样，并且您的类路径上有Gson或Jackson来从JSON映射到Java数据结构:

Assuming the User class looks like this, and that you have Gson or Jackson on your classpath to map from JSON to your Java data structures:

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结果，而无需进一步映射.

Of course, you don't have to map to Java data structures and produce a JSON result directly, without further mapping. See also this blog post for more details.

如果不能使用上述方法，或者由于无法使用jOOQ 3.14，或者因为RDBMS不支持SQL/XML或SQL/JSON，则可以运行多个查询并手动组合结果在你的末端.

If you cannot use the above approach, because you can't work with jOOQ 3.14 (yet), or because your RDBMS doesn't support SQL/XML or SQL/JSON, you can run several queries and assemble the results manually on your end.

这篇关于JOOQ获取外键表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！