《あなたが知らないJAVA》💘 失われた SQL JOIN クエリの秘伝
ブログ対応のプロジェクトテンプレート
Left Joinから始める
ユーザーとロールの関係を表すn2nのリレーションテーブルがあるとします。
通常、left joinを使用してこれら3つのテーブルを結合し、ユーザーとそのロールの情報をクエリします。
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というユーザーが2回出現しています。これは明らかに「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 |
残念ながら、この処理は非常に面倒です。結合するテーブルが多ければ多いほど、コードは書きにくくなります。試してみるとわかります。
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を学ぶ煩わしさから解放されます。
このコードの例は、https://github.com/ccmjga/mjga-scaffold/blob/main/src/main/java/com/zl/mjga/repository/UserRepository.java で見つけることができます。
また、関連するユニットテストも参考にしてください：https://github.com/ccmjga/mjga-scaffold/blob/main/src/test/java/com/zl/mjga/integration/persistence/UserRolePermissionDALTest.java
最後に
- 私はChuck1snです。現代のJvmエコシステムの普及に長年尽力している開発者です
- あなたの返信、いいね、ブックマークが、私が更新を続ける原動力です
- 私のアカウントをフォローして、記事の更新をいち早く受け取ってください
PS：上記のすべてのコード例は、Githubリポジトリで見つけることができます。役に立ったら、ぜひStarを付けてください。私にとって大きな励みになります。ありがとうございます！
Discussion