JPA使用Specification动态where条件查询

1. 想要的效果SQL

select * from XXX
where  column_1 ='字段1的值'
     and column_2 ='字段2的值'
     and (column_3= '字段3的值' or column_3  ='字段3的第二个值')
     and LOCATE('字段4的值', column_4)>0

2.Specification 动态where查询

    @ApiOperation(value = " 测试动态where条件查询", notes = "测试动态where条件查询")
    @GetMapping(value = "specification")
    public List<BugData> whereTiaojian() {
        BugRequestParam bugRequestParam = new BugRequestParam();
        bugRequestParam.setBugLevel("mid");
        bugRequestParam.setBugName("CVE-2019-8331");
        bugRequestParam.setLimit(100);
        bugRequestParam.setPage(0);
        List<String> bugTypeList = new ArrayList<>(4);
        bugTypeList.add("CWE-79");
        bugTypeList.add("CWE-502");
        bugTypeList.add("CWE-284");
        bugRequestParam.setBugTypeList(bugTypeList);

        Pageable pageable = PageRequest.of(bugRequestParam.getPage(), bugRequestParam.getLimit());

        Specification<BugData> bugDataSpecification = new Specification<BugData>() {
            @Override
            public Predicate toPredicate(Root<BugData> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                Path<Object> bugName = root.get("bugName");
                Path<Object> bugType = root.get("bugType");
                Path<Object> bugLevel = root.get("bugLevel");
                List<Predicate> predicateListAnd = new ArrayList<>();
                List<Predicate> predicateListOr = new ArrayList<>();

                if (!StringUtils.isNullOrEmpty(bugRequestParam.getBugLevel())) {
                    Predicate pLevel = cb.equal(bugLevel, bugRequestParam.getBugLevel());
                    predicateListAnd.add(pLevel);
                }
                for (int i = 0; i < bugRequestParam.getBugTypeList().size(); i++) {

                    Predicate p1 = cb.equal(bugType, bugRequestParam.getBugTypeList().get(i));
                    predicateListOr.add(p1);
                }

                if (!StringUtils.isNullOrEmpty(bugRequestParam.getBugName())) {
                    /**
                     * 如果存在，添加，等价于  LOCATE(?1, bug_name)>0
                     */
                    Expression<Integer> findStr = cb.locate(bugName.as(String.class), bugRequestParam.getBugName());
                    Predicate pName = cb.greaterThan(findStr, 0);
                    predicateListAnd.add(pName);
                }
                Predicate resultAnd[] = predicateListAnd.toArray(new Predicate[predicateListAnd.size()]);
                Predicate resultOr[] = predicateListOr.toArray(new Predicate[predicateListOr.size()]);
                Predicate end = cb.and(cb.and(resultAnd), cb.or(resultOr));//以与的形式拼接多个查询条件
                return end;
            }
        };
        Page<BugData> bugDataPage = bugDataVersionFiveDao.findAll(bugDataSpecification, pageable);
        return bugDataPage.getContent();

    }

最后查询的结果如下：

select * from bugdata0   where
	bugdata0_.bug_level=? and locate(?, bugdata0_.bug_name)>0 
	and (bugdata0_.bug_type=? or bugdata0_.bug_type=? or bugdata0_.bug_type=?) 

2.Specification 动态between查询

 Predicate influence = cb.between(root.get("projectId"), 1, 3);

2.1 其中BugRequestParam是这样的

import java.io.Serializable;
import java.util.List;


@Getter
@Setter
public class BugRequestParam implements Serializable {
    private static final long serialVersionUID = -3018468790740380259L;

    private String bugName;
    private List<String> bugTypeList;
    private String bugLevel;
    private Integer limit;
    private Integer page;
}

2.2 bugDataVersionFiveDao

import cn.net.conet.dao.support.IBaseDao;
import cn.net.conet.entity.BugData;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

@Repository
public interface IBugDataVersionFiveDao extends IBaseDao<BugData, Integer> {

}

2.3 BugData就是一个实体类，里面包括一些字段

import java.io.Serializable;
import java.util.List;


@Getter
@Setter
public class BugData implements Serializable {
    private static final long serialVersionUID = -3018468790740381259L;

	private Integer id;
    private String bugName;
    private String BugType;
    private String bugLevel;
}

3.Specification 动态groupBy查询

要实现的效果

select * from XXX where XXXX=xxx group by XXX

找到下面的代码

 Predicate end = cb.and(cb.and(resultAnd), cb.or(resultOr));//以与的形式拼接多个查询条件
 return end;

然后改成：

 Predicate end = cb.and(cb.and(resultAnd), cb.or(resultOr));//以与的形式拼接多个查询条件

 query.where(end).groupBy(root.get("bugName"));
 return query.getRestriction();

4.Specification 动态having查询

要实现的效果

select * from XXX where XXXX=xxx group by XXX HAVING
(project_id)>1 and (project_id)<3

 Predicate influence = cb.between(root.get("projectId"), 1, 3);
 Predicate end = cb.and(cb.and(resultAnd), cb.or(resultOr));//以与的形式拼接多个查询条件

query.where(end).groupBy(root.get("bugName")).having(influence);
 return query.getRestriction();

4.Specification 动态having(count)查询

比如要实现的功能是

SELECT
	bug_name,
	project_id,
	user_id,
	COUNT(project_id) nun
FROM
	bug_data
WHERE
	bug_level = 'mid'
GROUP BY
	bug_name
HAVING
	count(project_id)  BETWEEN 5 AND 10

 Expression a = cb.count(root.get("projectId"));
 Predicate influence = cb.between(a, 5, 10);
 query.having(influence);
 return query.getRestriction();