List<AppRegisterPO > 返回到结果, AppRegisterVO前端传来到数据,比AppRegisterPO多startTime和endTime,
CriteriaBuilder:标准指定者,设置动态查询的条件
Root<AppRegisterPO> root,从DB中查寻的条件
predicates.add(cb.equal(root.<String>get("appName"), appRegisterVO.getAppName())
predicates.add(cb.between(root.get("createDate"), appRegisterVO.getStartTime(),appRegisterVO.getEndTime()));
@Override
public List<AppRegisterPO> search(AppRegisterVO appRegisterVO) {
List<AppRegisterPO> appRegisterPOS = appRegisterDao.findAll(new Specification<AppRegisterPO>() {
@Override
public Predicate toPredicate(Root<AppRegisterPO> root, CriteriaQuery<?> query,
CriteriaBuilder cb) {
// 本集合用于封装查询条件
List<Predicate> predicates = new ArrayList<Predicate>();
if (appRegisterVO != null && !appRegisterVO.equals("")) {
/** 是否传入了应用名来查询 */
if (!StringUtils.isEmpty(appRegisterVO.getAppName())) {
predicates.add(cb.equal(root.<String>get("appName"), appRegisterVO.getAppName()));
}
/** 是否传入了appKey来查询 */
if (!StringUtils.isEmpty(appRegisterVO.getApiKey())) {
predicates.add(cb.equal(root.<String>get("apiKey"), appRegisterVO.getApiKey()));
}
// between
if(!StringUtils.isEmpty(appRegisterVO.getStartTime())&&!StringUtils.isEmpty(appRegisterVO.getEndTime())){
predicates.add(cb.between(root.get("createDate"), appRegisterVO.getStartTime(),appRegisterVO.getEndTime()));
}
}
return query.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction();
}
});
return appRegisterPOS;
}
/**查询Demo结果集
* @return
*/
public List<Demo> findDemoList(ParamVO param,Pageable pageable,List<Integer> userIds){
List<Demo> result = demoRepository.findAll(new Specification<Demo>(){
@Override
public Predicate toPredicate(Root<Demo> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
List<Predicate> predicates = new ArrayList<Predicate>();
// is null
predicates.add(root.get("fId").isNull());
// =
if(StringUtils.isNoneBlank(param.getPoName())){
predicates.add(cb.equal(root.get("poName"), "%"+param.getPoName()+"%"));
}
// like
if(StringUtils.isNoneBlank(param.getPoName())){
predicates.add(cb.like(root.get("poName"), "%"+param.getPoName()+"%"));
}
// between
if(StringUtils.isNoneBlank(param.getBeginDate())&&StringUtils.isNoneBlank(param.getEndDate())){
predicates.add(cb.between(root.get("date"),
param.getBeginDate(),param.getEndDate()));
}
//in
predicates.add(root.get("userId").in(userIds));
return query.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction();
}
},pageable).getContent();
return result;
}
多表关联查询:1.左连接
JPA多对多的关联字段:roleList
@ManyToMany
@JoinTable(name = "user_role_r", joinColumns = { @JoinColumn(name = "userId") }, inverseJoinColumns ={@JoinColumn(name = "roleId") })
private List<Role> roleList;
JPA左连接查询在上面的模板上添加下面条件。
if(null!=param.getRole()){
//左连接
Join<User,Role> join = root.join("roleList",JoinType.LEFT);
predicates.add(cb.equal(join.get("id"),param.getRole()));
}
数据条数(count)
public Integer findCountReport(Integer orgId){
//多条件查询
Long count= demoRepository.count(new Specification<Demo>(){
@Override
public Predicate toPredicate(Root<Demo> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
List<Predicate> predicates = new ArrayList<Predicate>();
if(null!=orgId&&orgId.intValue()!=0){
predicates.add(cb.equal(root.get("orgId"), orgId));
}
return query.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction();
}
});
return count.intValue();
}