hibernate - 如何在 JPA 中使用规范进行左连接、分组、计数和 IN 子句？

Question

@Query(value = "select * from SalesLeadsSearch s " +
        "left join SalesLeadsCustomerOpportunities so on s.id = so.userId and (so.opportunityId in:opportunities) " +
        "left join SalesLeadsPurchasedCourse sp on s.id = sp.userId and (sp.courseId in:purchasedCourses)" +
        "left join SalesLeadsCustomerLostReason sl on s.id = sl.userId and (sl.lostReasonId in:lostReasons)" +
        "where s.id in:ids group by s.id having count(so.id) >=:len1 and count(sp.id) >=:len2 and count (sl.id) >=:len3")
    List<SalesLeadsVM> findSalesLeadsByOpportunitiesAndPurchasedCoursesAndLostReasons(List<Long> ids, List<Long> opportunities, Long len1, List<Long> purchasedCourses, Long len2, List<Long> lostReasons, Long len3);

这是我的查询子句，现在我希望它可以与动态查询相结合。

 private Specification<SalesLeadsSearch> getSalesLeads(SalesLeadsSearchVM salesLeadsSearchVM) {
        Specification<SalesLeadsSearch> specification = new Specification<SalesLeadsSearch>() {
            @Override
            public Predicate toPredicate(Root<SalesLeadsSearch> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                Predicate predicate = criteriaBuilder.conjunction();
                if(!StringUtils.isBlank(salesLeadsSearchVM.getLastName())) {
                    predicate.getExpressions().add(criteriaBuilder.like(root.get("lastName"), '%'+salesLeadsSearchVM.getLastName()+'%'));
                }
                if(!StringUtils.isBlank(salesLeadsSearchVM.getFirstName())) {
                    predicate.getExpressions().add(criteriaBuilder.like(root.get("firstName"),'%'+salesLeadsSearchVM.getFirstName()+'%'));
                }
                if(!StringUtils.isBlank(salesLeadsSearchVM.getWechatName())) {
                    predicate.getExpressions().add(criteriaBuilder.like(root.get("wechatName"),'%'+salesLeadsSearchVM.getWechatName()+'%'));
                }
                if(!StringUtils.isBlank(salesLeadsSearchVM.getEmail())) {
                    predicate.getExpressions().add(criteriaBuilder.like(root.get("email"), '%'+salesLeadsSearchVM.getEmail()+'%'));
                }
                if(!CollectionUtils.isEmpty(salesLeadsSearchVM.getSalesLeadsPurchasedCourses())) {
                    Join<SalesLeadsSearch, Course> joinCourses = root.join("purchasedCourses", JoinType.LEFT);
                // how to add criteriabuilder at here ?
                //
                }
                return predicate;
            }
        };
        return specification;
    }

这是我的实体 SalesLeadsSearch：

    @OneToMany(fetch = FetchType.EAGER)
    @JoinTable(
        name = "sales_leads_purchased_course",
        joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"),
        inverseJoinColumns = @JoinColumn(name = "course_id", referencedColumnName = "id")
    )
    private Set<Course> purchasedCourses;

    @OneToMany(fetch = FetchType.EAGER)
    @JoinTable(
        name = "sales_leads_customer_opportunities",
        joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"),
        inverseJoinColumns = @JoinColumn(name = "opportunity_id", referencedColumnName = "id")
    )
    private Set<SalesLeadsOpportunity> opportunities;

    @OneToMany(fetch = FetchType.EAGER)
    @JoinTable(
        name = "sales_leads_customer_lost_reason",
        joinColumns = @JoinColumn(name = "user_id", referencedColumnName = "id"),
        inverseJoinColumns = @JoinColumn(name = "lost_reason_id", referencedColumnName = "id")
    )
    private Set<SalesLeadsLostReason> lostReasons;

由于 Courses、Opportunites、lost Reasons 实体过于复杂，无法在此处使用 joinColumn，而是在此处使用 joinTable。

我想知道如何将查询子句添加到规范 getSalesLeads 中的语法，该子句具有左联接、分组依据、计数和 In cluase。太感谢了。

标签： hibernatejpqlspecificationshibernate-criteriacriteria-api