Как запрашивать родительские строки, когда все дочерние должны соответствовать критериям фильтрации с помощью SQL и гибернации

Автор оригинала: Vlad Mihalcea.

Форум Hibernate – это неиссякаемый источник вдохновения, когда речь заходит о решении реальных проблем, с которыми вы можете столкнуться при разработке корпоративного приложения.

Например, в этом посте запрашивается запрос JPQL, который должен извлекать заданную родительскую сущность, когда все ее дочерние сущности соответствуют заданным критериям фильтрации.

Как запросить родительские строки, когда все дочерние должны соответствовать критериям фильтрации с помощью SQL и гибернации @vlad_mihalcea https://t.co/lXIDCJXnL0 pic.twitter.com/SL4N0hvjkF

Учтите, что наша база данных содержит следующие таблицы, которые образуют взаимосвязь “многие ко многим”:

И кластер , и тег таблицы являются независимыми отношениями. Для этой цели они связаны через таблицу cluster_tag join.

Наши таблицы базы данных содержат следующие записи:

Таблица кластеров

1	Кластер 1
2	Кластер 2
3	Кластер 3

Таблица тегов

1	Искра	2.2
2	Hadoop	2.7
3	Искра	2.3
4	Hadoop	2.6

Таблица cluster_tag

1	1
2	1
1	2
4	2
3	3
4	3

Организации 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
}

Объект Кластер отображается следующим образом:

@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 tags = new ArrayList<>();

    //Getters and setters omitted for brevity

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

Объект Кластерный тег отображается следующим образом:

@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
}

Как объяснено в этой статье , поскольку Кластерный тег сущности имеет составной идентификатор, мы используем Идентификатор кластерного тега встраиваемый, который выглядит следующим образом:

@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);
    }
}

Вот и все.

Теперь мы хотим получить кластер записей, имеющих теги записи, соответствующие следующим двум условиям:

Тег имя является Искрой и значение является 2.2 или
Тег имя является Hadoop и значение является 2.7

Теперь давайте попробуем различные способы решения этой проблемы.

Один из первых запросов, который может прийти вам в голову, – это просто объединить все ассоциации и отфильтровать по требуемым критериям:

List 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 результата: Cluster1 и Cluster2 , поскольку у обоих из них есть тег строка, соответствующая одному из двух условий.

Но мы этого не хотим! Нам нужны записи кластера , для которых все связанные теги соответствуют либо первому, либо второму предикату.

Прежде чем выяснять, как решить эту проблему в JPQL, лучше попробовать это с помощью обычного SQL.

Одним из решений было бы выбрать записи cluster_tag , связанные строки тега которых соответствуют критериям фильтрации, и, поскольку мы ожидаем 2 совпадения, подсчитайте количество совпадений, чтобы отфильтровать тег , не соответствующий всем условиям.

Соединив таблицу cluster с результатом таблицы внутреннего запроса cluster_tag , мы можем получить желаемый результат:

List 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();

Однако у этого запроса есть один большой недостаток. Нам просто нужна сущность Кластер , поэтому самое верхнее соединение создаст больше работы на стороне базы данных (особенно если базовая база данных поддерживает только вложенные циклы), чего можно было бы избежать, если бы мы переписали запрос, чтобы вместо этого использовать полу-соединение. Более того, приведенный выше SQL-запрос не может быть выражен в JPQL, что может быть проблемой, если мы хотим построить запрос динамически с помощью API критериев.

Запрос на ПОЛУСОЕДИНЕНИЕ выглядит следующим образом:

List 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();

Мало того, что это более эффективно, поскольку, в конечном счете, мы только выбираем и проецируем записи кластера , но запрос еще проще для чтения и может быть адаптирован к JPQL или API критериев.

Как уже говорилось, запрос ПОЛУ-СОЕДИНЕНИЯ может быть переписан в JPQL следующим образом:

List 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 и кластер во внутреннем запросе:

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
)

Обратите внимание на ВНУТРЕННИЙ кластер СОЕДИНЕНИЯ ctc ON.id избыточное соединение, которого мы хотели бы избежать.

Переписать предыдущий запрос для использования неявного соединения для тега Cluster.cluster можно следующим образом:

List 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 или API критериев для извлечения объектов, которые мы планируем изменить.

Таблица кластеров

Таблица тегов

Таблица cluster_tag

Организации JPA

Читайте ещё по теме: