最近开发过程中,遇到了一个比较复杂的查询功能,查询条件有五六十个,每个条件可填可不填,要根据用户的输入条件查询结果,并且根据用户选中的字段返回对应的数据。
其中的查询条件里面大部分是 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
)
)