JPA Criteria Builder一对多限制

问题描述

我有一个与一个子表具有OneToMany关联的父母.

I have a Parent with a OneToMany associations with a Child Table.

我正在尝试使用CriteriaBuilder编写查询，以限制从Child表返回的结果.

I'm trying to write a query with CriteriaBuilder to restrict the results returned from the Child table.

我要添加一个谓词，例如

I'm adding a Predicate, something like

cb.equal(parent.get("children").get("sex"), "MALE")

如果父母有儿子，儿子和女儿，则是在返还该父母，而且还返还了他们所有的孩子.

If the Parent has a son or SON and Daughter it's returning that parent but also returning all the children they have.

Hibernate使用我的谓词触发第一个查询，但是第二个获取子项的查询仅在不包含where子句的

Hibernate fires off the first query with my predicates but the second query to get the children only uses the JoinColumn in the where clause it doesn't include

cb.equal(parent.get("children").get("sex"), "MALE").

有想法吗?

我正在使用SetJoin

I am using a SetJoin

children = parent.joinSet("children", JoinType.LEFT)

澄清:

public static Specification<Parent> findPlanBenefits(Integer parentId) {
    return (parent, query, cb) -> {
        Predicate predicates = cb.conjunction();
        List<Expression<Boolean>> expressions = predicates.getExpressions();

        //Parent Criteria
        expressions.add(cb.equal(parent.get("parentId"), parentId));

        //Children Criteria
        SetJoin<Parent, Children> children = parent.joinSet("children", JoinType.LEFT);

        Predicate sex = cb.equal(children.get("sex"), "MALE");
        children.on(sex);

        return predicates;
    };
}

推荐答案

恐怕JOIN ON不能按您期望的那样工作. JOIN ON仅告诉如何加入，而不告诉如何加载关系.

I am afraid, the JOIN ON does not work as you expect in your answer. JOIN ON only tells how to join, and NOT how relationships are loaded.

因此，为了解决您的问题，您必须在加载后的子项中对其进行过滤，或者通过单独的查询手动获取所有男子项.

So, in order to solve your problem you will have to filter the children after they are loaded, or fetch manually all male children with a separate query.

为了检查JOIN ON的工作方式，您还可以尝试相应的JPQL查询.

In order to check how JOIN ON works, you could try also the corresponding JPQL query.

更新

OP告知JPQL查询select p from parent p join fetch children c where p.parentId = :parentId and c.sex = "MALE"有效.

OP told that the JPQL queryselect p from parent p join fetch children c where p.parentId = :parentId and c.sex = "MALE" works.

相应的CriteriaQuery看起来像:

The corresponding CriteriaQuery would look like:

CriteriaQuery<Parent> criteria = cb.createQuery((Class<Parent>) Parent.class);
Root<Parent> parent = criteria.from(Parent.class);

criteria.select((Selection<T>) parent);
SetJoin<Parent, Children> children = parent.joinSet("children", JoinType.LEFT);

Predicate sexPredicate = cb.equal(children.get("sex"), "MALE");
parent.fetch(children);
//parent.fetch("children");//try also this

criteria.where(sexPredicate);

这篇关于JPA Criteria Builder一对多限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！