文章目录
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();