spring-data-jpa 实现子查询和复杂的关联查询总结

最近开发过程中，遇到了一个比较复杂的查询功能，查询条件有五六十个，每个条件可填可不填，要根据用户的输入条件查询结果，并且根据用户选中的字段返回对应的数据。

其中的查询条件里面大部分是 and的关系，有部分是互相之间是or的关系

所以查询逻辑里面牵扯到子查询和关联查询

 

1、spring-data-jpa 要实现 子查询，如下代码所示可以实现

 //代码打印出的sql代码如下
/**
 *    from
 *         cat catdomain0_
 *     where
 *         catdomain0_.id in (
 *             select
 *                 hobby1_.cat_id
 *             from
 *                 hobby hobby1_
 *             where
 *                 (
 *                     hobby1_.cat_id in (
 *                         select
 *                             hobby2_.cat_id
 *                         from
 *                             hobby hobby2_
 *                         where
 *                             hobby2_.chinese=?
 *                     )
 *                 )
 *                 and hobby1_.type=1
 *             )
 */

//具体的实现代码
 public static Specification<CatDomain> listAdvanceSpec() {
        return (Root<CatDomain> root, CriteriaQuery<?> query, CriteriaBuilder builder) -> {
            List<Predicate> predicates = new ArrayList<>();
            //完成子查询

            Subquery subQuery = query.subquery(String.class);
            Root from = subQuery.from(Hobby.class);
            subQuery.select(from.get("catId")).where(builder.equal(from.get("chinese"), "ls"));

            Subquery subQuery11 = query.subquery(String.class);
            Root from1 = subQuery11.from(Hobby.class);
            subQuery11.select(from1.get("catId")).where(from1.get("catId").in(subQuery), builder.equal(from1.get("type"), "1"));
            return builder.and((root.get("id")).in(subQuery11));

        };
    }

2、spring-data-jpa 要实现如下关联查询

select a.*

from table_a a

left join table_b

//主要实现如下所示的自定义条件查询b.status=1 等

on a.id=b.id and b.status=1 

where a.name="zs" and b.age=18

示例代码如下：

  public static Specification<CatDomain> listQuerySpec() {
        return (Root<CatDomain> root, CriteriaQuery<?> query, CriteriaBuilder builder) -> {
            query.distinct(true);
            List<Predicate> predicates = new ArrayList<>();

            Join<Object, Object> hobby1 = root.join("hobby1", JoinType.INNER);
            // 此处相当于 left join table_b b 中 后面的on b.type=1 and b.name="zs"等，可填写多个条件
            hobby1.on(builder.equal(hobby1.get("type"), "1"));


            Join<Object, Object> hobby2 = root.join("hobby2", JoinType.INNER);
            hobby2.on(builder.equal(hobby2.get("type"), "2"));

            predicates.add(builder.equal(hobby2.get("chinese"), "ls"));
            predicates.add(builder.isNotEmpty(root.get("hobby1")));

            predicates.add(builder.equal(hobby2.get("chinese"), "ls"));
            predicates.add(builder.isNotEmpty(root.get("hobby2")));
            // 返回Predicate
            return builder.and(predicates.toArray(new Predicate[predicates.size()]));
        };
    }

打印的sql 如下所示：

  from
        cat catdomain0_ 
    inner join
        hobby hobby1x1_ 
            on catdomain0_.id=hobby1x1_.cat_id 
            and catdomain0_.type1=hobby1x1_.type 
            and (
                hobby1x1_.type=1
            ) 
    inner join
        hobby hobby2x2_ 
            on catdomain0_.id=hobby2x2_.cat_id 
            and catdomain0_.type2=hobby2x2_.type 
            and (
                hobby2x2_.type=2
            ) 
    where
        hobby2x2_.chinese=? 
        and (
            exists (
                select
                    hobby1x3_.id 
                from
                    hobby hobby1x3_ 
                where
                    catdomain0_.id=hobby1x3_.cat_id 
                    and catdomain0_.type1=hobby1x3_.type
            )
        ) 
        and hobby2x2_.chinese=? 
        and (
            exists (
                select
                    hobby2x4_.id 
                from
                    hobby hobby2x4_ 
                where
                    catdomain0_.id=hobby2x4_.cat_id 
                    and catdomain0_.type2=hobby2x4_.type
            )
        )