Java >> Java チュートリアル >  >> Tag >> SQL

すべての子が SQL と Hibernate のフィルター条件に一致する必要がある場合に親行をクエリする方法

はじめに

Hibernate フォーラムは、エンタープライズ アプリケーションの開発時に遭遇する可能性のある現実の問題を解決する際に、尽きることのないインスピレーションの源です。

たとえば、この投稿では、すべての子エンティティが特定のフィルタリング基準に一致する場合に、特定の親エンティティをフェッチする JPQL クエリについて質問しています。

すべての子が SQL と Hibernate のフィルタリング条件に一致する必要がある場合に親行をクエリする方法 @vlad_mihalcea https://t.co/lXIDCJXnL0 pic.twitter.com/SL4N0hvjkF

— Java (@java) 2018 年 7 月 29 日

ドメイン モデル

私たちのデータベースには、多対多の関係を形成する次のテーブルが含まれていると考えてください:

cluster の両方 そして tag テーブルは独立した関係です。この目的のために、それらは cluster_tag を通じて関連付けられています テーブルに参加してください。

データベース テーブルには次のエントリが含まれています:

クラスタ テーブル

id 名前
1 クラスター 1
2 クラスター 2
3 クラスター 3

タグ テーブル

id tag_name tag_value
1 スパーク 2.2
2 Hadoop 2.7
3 スパーク 2.3
4 Hadoop 2.6

cluster_tag テーブル

cluster_id tag_id
1 1
1 2
2 1
2 4
3 3
3 4

JPA エンティティ

この記事で説明したように、多対多テーブルの関係をマッピングする非常に効率的な方法は、結合テーブルを JPA エンティティとしてマッピングすることです。

Tag エンティティは次のようになります:

@Entity(name = "Tag")
@Table(
    name = "tag",
    uniqueConstraints = @UniqueConstraint(
        columnNames = { 
            "tag_name", 
            "tag_value" 
        }
    )
)
public class Tag {

    @Id
    private Long id;

    @Column(name = "tag_name")
    private String name;

    @Column(name = "tag_value")
    private String value;

    //Getters and setters omitted for brevity
}

Cluster エンティティは次のようにマッピングされます:

@Entity(name = "Cluster")
@Table(name = "cluster")
public class Cluster {

    @Id
    private Long id;

    private String name;

    @OneToMany(
        mappedBy = "cluster",
        cascade = CascadeType.ALL,
        orphanRemoval = true
    )
    private List<ClusterTag> tags = new ArrayList<>();

    //Getters and setters omitted for brevity

    public void addTag(Tag tag) {
        tags.add(new ClusterTag(this, tag));
    }
}

ClusterTag エンティティは次のようにマッピングされます:

@Entity(name = "ClusterTag")
@Table(name = "cluster_tag")
public class ClusterTag {

    @EmbeddedId
    private ClusterTagId id;

    @ManyToOne
    @MapsId("clusterId")
    private Cluster cluster;

    @ManyToOne
    @MapsId("tagId")
    private Tag tag;

    private ClusterTag() {}

    public ClusterTag(Cluster cluster, Tag tag) {
        this.cluster = cluster;
        this.tag = tag;
        this.id = new ClusterTagId(
            cluster.getId(), 
            tag.getId()
        );
    }

    //Getters and setters omitted for brevity
}

この記事で説明したように、ClusterTag エンティティには複合識別子があり、ClusterTagId を使用しています 次のような埋め込み可能:

@Embeddable
public class ClusterTagId 
    implements Serializable {

    @Column(name = "cluster_id")
    private Long clusterId;

    @Column(name = "tag_id")
    private Long tagId;

    public ClusterTagId() {}

    public ClusterTagId(
            Long clusterId, 
            Long tagId) {
        this.clusterId = clusterId;
        this.tagId = tagId;
    }

    //Getters omitted for brevity

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) 
            return false;
        ClusterTagId that = (ClusterTagId) o;
        return Objects.equals(clusterId, that.clusterId) &&
               Objects.equals(tagId, that.tagId);
    }

    @Override
    public int hashCode() {
        return Objects.hash(clusterId, tagId);
    }
}

以上です。

問題

cluster を取得したい tag を持つレコード 次の 2 つの条件に一致するエントリ:

<オール>
  • tag name Spark です そして value 2.2 です または
  • tag name Hadoop です そして value 2.7 です
  • それでは、この問題を解決するためにさまざまな方法を試してみましょう。

    失敗した最初の試み

    最初に頭に浮かぶクエリの 1 つは、単純にすべての関連付けを結合し、必要な条件でフィルタリングすることです:

    List<Cluster> clusters = entityManager
    .createQuery(
        "select distinct c " +
        "from ClusterTag ct " +
        "join ct.cluster c " +
        "join ct.tag t " +
        "where " +
        "    (t.name = :tagName1 and t.value = :tagValue1) or " +
        "    (t.name = :tagName2 and t.value = :tagValue2) "
        , Cluster.class)
    .setParameter("tagName1", "Spark")
    .setParameter("tagValue1", "2.2")
    .setParameter("tagName2", "Hadoop")
    .setParameter("tagValue2", "2.7")
    .getResultList();
    

    ただし、このクエリは 2 つの結果を返します:Cluster1Cluster2 どちらも tag 2 つの条件のいずれかに一致する行。

    しかし、私たちはそれを望んでいません! cluster が必要です 関連するすべてのレコード tag 最初または 2 番目の述語のいずれかに一致します。

    ネイティブ SQL – JOIN ソリューション

    JPQL でこの問題を解決する方法を理解する前に、プレーン SQL で試してみることをお勧めします。

    1 つの解決策は、cluster_tag を選択することです。 関連する tag のエントリ 行はフィルタリング基準に一致し、2 つの一致が予想されるため、一致の数を数えて tag を除外します。 すべての条件に一致しません。

    cluster に参加することで cluster_tag のテーブル結果を含むテーブル 内部クエリで、目的の結果を得ることができます:

    List<Cluster> clusters = entityManager
    .createNativeQuery(
        "SELECT * " +
        "FROM cluster c " +
        "JOIN (" +
        "   SELECT ct.cluster_id AS c_id " +
        "   FROM cluster_tag ct " +
        "   JOIN tag t ON ct.tag_id = t.id " +
        "   WHERE " +
        "       (t.tag_name = :tagName1 AND t.tag_value = :tagValue1) OR " +
        "       (t.tag_name = :tagName2 AND t.tag_value = :tagValue2) " +
        "   GROUP BY ct.cluster_id " +
        "   HAVING COUNT(*) = 2" +
        ") ct1 on c.id = ct1.c_id ", Cluster.class)
    .setParameter("tagName1", "Spark")
    .setParameter("tagValue1", "2.2")
    .setParameter("tagName2", "Hadoop")
    .setParameter("tagValue2", "2.7")
    .getResultList();
    

    ただし、このクエリには 1 つの大きな欠点があります。 Cluster だけが欲しい そのため、最上位の結合はデータベース側でより多くの作業を生成します (特に、基になるデータベースが入れ子になったループのみをサポートしている場合)。これは、代わりに半結合を使用するようにクエリを書き直せば回避できます。さらに、上記の SQL クエリは JPQL で表現できないため、Criteria API を使用してクエリを動的に構築する場合に問題になる可能性があります。

    ネイティブ SQL – SEMI-JOIN ソリューション

    SEMI-JOIN クエリは次のようになります:

    List<Cluster> clusters = entityManager
    .createNativeQuery(
        "SELECT * " +
        "FROM cluster c " +
        "WHERE EXISTS (" +
        "   SELECT ct.cluster_id as c_id " +
        "   FROM cluster_tag ct " +
        "   JOIN tag t ON ct.tag_id = t.id " +
        "   WHERE " +
        "       c.id = ct.cluster_id AND ( " +
        "           (t.tag_name = :tagName1 AND t.tag_value = :tagValue1) OR " +
        "           (t.tag_name = :tagName2 AND t.tag_value = :tagValue2) " +
        "       )" +
        "   GROUP BY ct.cluster_id " +
        "   HAVING COUNT(*) = 2 " +
        ") ", Cluster.class)
    .setParameter("tagName1", "Spark")
    .setParameter("tagValue1", "2.2")
    .setParameter("tagName2", "Hadoop")
    .setParameter("tagValue2", "2.7")
    .getResultList();
    

    最終的には cluster を選択して投影するだけなので、これはより効率的です。 レコードですが、クエリはさらに読みやすくなり、JPQL または Criteria API にも適用できます。

    JPQL – 明示的な関連付け結合による SEMI-JOIN ソリューション

    既に述べたように、SEMI-JOIN クエリは次のように JPQL に書き換えることができます。

    List<Cluster> clusters = entityManager.createQuery(
        "select c " +
        "from Cluster c " +
        "where exists (" +
        "    select ctc.id " +
        "    from ClusterTag ct " +
        "    join ct.cluster ctc " +
        "    join ct.tag ctt " +
        "    where " +
        "        c.id = ctc.id and ( " +
        "            (ctt.name = :tagName1 and ctt.value = :tagValue1) or " +
        "            (ctt.name = :tagName2 and ctt.value = :tagValue2) " +
        "        )" +
        "    group by ctc.id " +
        "    having count(*) = 2" +
        ") ", Cluster.class)
    .setParameter("tagName1", "Spark")
    .setParameter("tagValue1", "2.2")
    .setParameter("tagName2", "Hadoop")
    .setParameter("tagValue2", "2.7")
    .getResultList();
    

    通常、JPQL クエリを記述するときは明示的な結合が好まれますが、今回は、Hibernate が cluster_tag 間の役に立たない JOIN を発行しているようです。 と cluster 内部クエリ:

    SELECT c.id   AS id1_0_, 
           c.NAME AS name2_0_ 
    FROM   cluster c 
    WHERE  EXISTS (
        SELECT ctc.id 
        FROM   cluster_tag ct 
        INNER JOIN cluster ctc ON ct.cluster_id = ctc.id 
        INNER JOIN tag ctt ON ct.tag_id = ctt.id 
        WHERE c.id = ctc.id AND ( 
                ctt.tag_name = ? AND ctt.tag_value = ? OR 
                ctt.tag_name = ? AND ctt.tag_value = ? 
              ) 
        GROUP  BY ctc.id 
        HAVING COUNT(*) = 2
    )
    

    INNER JOIN cluster ctc ON ct.cluster_id = ctc.id に注意してください 避けたい冗長結合。

    JPQL – 暗黙的な関連結合による SEMI-JOIN ソリューション

    ClusterTag.cluster の暗黙的な結合を使用するように前のクエリを書き直す 次のように実行できます:

    List<Cluster> clusters = entityManager
    .createQuery(
        "select c " +
        "from Cluster c " +
        "where exists (" +
        "    select ct.cluster.id " +
        "    from ClusterTag ct " +
        "    join ct.tag ctt " +
        "    where " +
        "        c.id = ct.cluster.id and ( " +
        "            (ctt.name = :tagName1 and ctt.value = :tagValue1) or " +
        "            (ctt.name = :tagName2 and ctt.value = :tagValue2) " +
        "        )" +
        "    group by ct.cluster.id " +
        "    having count(*) = 2" +
        ") ", Cluster.class)
    .setParameter("tagName1", "Spark")
    .setParameter("tagValue1", "2.2")
    .setParameter("tagName2", "Hadoop")
    .setParameter("tagValue2", "2.7")
    .getResultList();
    

    上記の JPQL クエリを実行すると、Hibernate は次の SQL クエリを生成します:

    SELECT c.id   AS id1_0_, 
           c.NAME AS name2_0_ 
    FROM   cluster c 
    WHERE  EXISTS (
        SELECT ct.cluster_id 
        FROM   cluster_tag ct 
        INNER JOIN tag ctt ON ct.tag_id = ctt.id 
        WHERE 
            c.id = ct.cluster_id AND ( 
                ctt.tag_name = ? AND ctt.tag_value = ? OR 
                ctt.tag_name = ? AND ctt.tag_value = ? 
            ) 
        GROUP  BY ct.cluster_id 
        HAVING COUNT(*) = 2
    ) 
    

    すばらしい!

    結論

    データのフェッチに関しては、最初に SQL クエリを想定し、その後で JPQL または Criteria API クエリを使用して、変更する予定のエンティティをフェッチするのが最善です。


    Java タグ