JPA 根据动态条件查询

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();
    }