《你不知道的 JAVA 系列博客》💘 失傳已久 SQL JOIN 查詢獨門秘籍詳情 - java,springboot,docker,gradle,springsecurity Chuck1sn 動態日志

工程思維落地

《你不知道的 Java 系列》的理念與思維，已落地成一款全新設計的 Java 腳手架，可與博客配套使用。

從 Left Join 説起

假設你有這樣一個 n2n 的關係表，代表用户和角色之間的關係。

通常通過 left join 去連接這三張表，來查詢出用户及其角色的信息。

SELECT 
    u.id AS user_id,
    u.name AS user_name,
    r.name AS role_name
FROM 
    "user" u
LEFT JOIN 
    "user_role_map" urm ON u.id = urm.user_id
LEFT JOIN 
    "role" r ON urm.role_id = r.id;

user_id	user_name	role_name
1	Alice	Admin
1	Alice	User
2	Bob	User
3	Charlie	Guest

查詢出的結果中，Alice 這個用户出現了兩次。這是顯而易見的，因為這是一個 "Flatten" 的結果。

這樣的結果是無法返回給客户端直接使用的。你需要進行處理，把重複的用户歸納到一起以後再返回給客户端進行展示，比如像下面這樣：

user_id	user_name	user's_role_array	備註
1	Alice	[(Admin),(User<List<Tag>>),(Vip<List<Level>>),...(n)]	試想任意節點都可能嵌套高度為 n 的子樹的情況，各節點需直接返回 List\<Map\> 的形式供前端在 html 的 `<li></li>` 和 `<select></select>` 節點中展示。
2	Bob	User
3	Charlie	Guest

不幸的是這樣的處理非常麻煩，你 join 的表越多這個代碼越不好寫，不相信你可以試試。

Group_contact

看到這裏，你可能會覺得 agg_string 和 group_contact 等聚合函數一定程度上能實現這個需求。但是聚合函數人如其名，作用為「聚合」。
回到上面的例子，不要侷限於例子中樹的高度，試想任意節點都可能嵌套一顆高度為 n 的子樹，並且你的業務邏輯還需要對子樹的節點做數據結構的轉換。顯然，字符串的「聚合」在解決這樣複雜樹結構的問題時顯得力量不足。

這樣的複雜樹結構是否很常見？不，它不常見，但是它也不少見。因為除了互聯網，還有很多行業也在使用數據庫支撐他們的業務。

談談 ORM

有沒有方便的方法來獲取這個「嵌套」的結果呢？使用 Hibernate 這樣的 ORM 框架是個不錯的主意：

@Entity
public class User {
    @Id
    private int id;

    @Column(name = "name", nullable = false)
    private String name;

    @ManyToMany
    @JoinTable(
        name = "user_role_map",
        joinColumns = @JoinColumn(name = "user_id"),
        inverseJoinColumns = @JoinColumn(name = "role_id")
    )
    private Set<Role> roles;
}

@Entity
public class Role {
    @Id
    private int id;

    @Column(name = "name", nullable = false)
    private String name;

    @ManyToMany(mappedBy = "roles")
    private Set<User> users;
}

Hibernate 直接幫你把數據庫的結果映射到了嵌套結果集中。現在你可以直接把 List<User> 返回給客户端了，因為這個結果現在展示為：

user_id	user_name	user's_role_array	備註
1	Alice	[(Admin),(User<List<Tag>>),(Vip<List<Level>>),...(n)]	無論樹的形狀和高度，Hibernate 把各節直接映射為 List\<Map\> 的形式供前端在 html 的 `<li></li>` 和 `<select></select>` 節點中展示。
2	Bob	User
3	Charlie	Guest

Hibernate 的問題

使用 Hibernate 的代價就是你的心智負擔很大。除了要學習很多註解以外，還有很多誇張的概念需要深入理解，才能夠寫出能正常運行的代碼。

那麼，有沒有一種簡單的方法，可以通過 SQL 的方式，直接查詢出這種嵌套的結果集，然後扔給客户端進行處理呢？答案是有的。

全新的解決方案

public static void main(String[] args) {
        UserRoleEntity userRoleEntity = select(
                USER.ID,
                USER.NAME,
                array(select(ROLE.ID, ROLE.NAME)
                        .from(ROLE)
                        .join(USER_ROLE_MAP).on(ROLE.ID.eq(USER_ROLE_MAP.ROLE_ID))
                        .where(USER_ROLE_MAP.USER_ID.eq(USER.ID))
                ).as("roles")
        ).from(USER);
        System.out.println(userRoleEntity);
    }


class UserRoleEntity {
    private Long id;
    private String name;
    private List<Role> roles;
}

是的，如你所見，使用 JOOQ，通過在 Java 的 main 方法裏面用 Java 語言來編寫「類型安全的 SQL」並通過 Array 方法一鍵轉換為嵌套對象，免去了學習 Hibernate 的煩惱。