mysql - 在使用 JOOQ 时如何最好地检索具有相关表项的记录？

Question

首先，我为标题道歉，但想不出更好的措辞。
其次我有下表:

Profiles Table:Primary Key: profileName <-----                              |Repositories Table:           |Composite Primary Keys: (profileName, repository_name)

simulating a 1 - n relationship between the profiles table and repositories table.I recently discovered jooq and using it to retrieve and store the data from the db and have this code for retrieving a profile from the db:

profile = db.select().from(Profiles.PROFILES, Repositories.REPOSITORIES).fetch().stream()
                .filter(t -> t.getValue(Profiles.PROFILES.PROFILENAME).equalsIgnoreCase(profileName))
                .limit(1) //limit it to just the one result
                .map(this::convertToProfile)
                .collect(Collectors.toList()).get(0);

工作正常，但我不确定如何改进它以包括检索在存储库表中找到的可能的存储库。也就是说，存储库对于配置文件表不是强制性的，而是可选的。我现在唯一的选择是创建一个“第二个循环逻辑”，以便在解码数据之前使用配置文件名称检索存储库。

Answer 1

将操作推送到数据库

随着数据的增长，您的查询将变得非常缓慢。为什么？因为您的 SQL 查询仅运行 cartesian product在 PROFILES 和 REPOSITORIES 表之间，然后在 Java 内存中应用连接谓词和限制子句。

数据库永远不知道你想用那个叉积做什么，所以它非常愚蠢地运行这个非常慢的查询。如果您通过将谓词“下推”到 jOOQ/SQL 查询中来为数据库提供更多信息，那么整个过程将运行得更快(尽管流结果在技术上是等效的)。所以，改为这样写:

profile = db.select()
            .from(PROFILES, REPOSITORIES)
            // WHERE and LIMIT are moved "up" into the SQL query:
            .where(PROFILES.PROFILENAME.equalIgnoreCase(profileName))
            .limit(1)
            .fetch().stream()
            .map(this::convertToProfile)
            .collect(Collectors.toList()).get(0);

此查询与您的相同(尚未正确)，但速度更快

正确获取连接

上面的查询仍然在两个表之间运行笛卡尔积。相反，您可能想加入他们。 SQL中有两种加入方式:

使用WHERE子句

只需在 where 子句中添加一个 JOIN 谓词即可

profile = db.select()
            .from(PROFILES, REPOSITORIES)
            // Join predicate here:
            .where(PROFILES.PROFILENAME.equal(REPOSITORIES.PROFILENAME))
            .and(PROFILES.PROFILENAME.equalIgnoreCase(profileName))
            .limit(1)
            .fetch().stream()
            .map(this::convertToProfile)
            .collect(Collectors.toList()).get(0);

这也称为 INNER JOIN，可以使用 JOIN 子句编写以提高可读性:

使用 (INNER) JOIN 子句:

大多数人会发现这种语法更具可读性，因为 JOIN 谓词与“普通”谓词明显分开:

profile = db.select()
            .from(PROFILES)
            // Join expression and predicates here:
            .join(REPOSITORIES)
            .on(PROFILES.PROFILENAME.equal(REPOSITORIES.PROFILENAME))
            // Ordinary predicates remain in the where clause:
            .where(PROFILES.PROFILENAME.equalIgnoreCase(profileName))
            .limit(1)
            .fetch().stream()
            .map(this::convertToProfile)
            .collect(Collectors.toList()).get(0);

“可选”JOIN

在 SQL 中，这称为 OUTER JOIN，或者更具体地说是 LEFT (OUTER) JOIN:

profile = db.select()
            .from(PROFILES)
            // LEFT JOIN expression and predicates here:
            .leftJoin(REPOSITORIES)
            .on(PROFILES.PROFILENAME.equal(REPOSITORIES.PROFILENAME))
            .where(PROFILES.PROFILENAME.equalIgnoreCase(profileName))
            .limit(1)
            .fetch().stream()
            .map(this::convertToProfile)
            .collect(Collectors.toList()).get(0);

请注意，REPOSITORIES 列表不会为空，而是包含一个所有值都设置为 NULL 的存储库。这就是 OUTER JOIN 的工作原理