JPA多条件组合查询,多表关联查询,数据条数<count>（CriteriaQuery）

JPA多条件组合查询（CriteriaQuery）

    /**查询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();
    }

 

2018-07-24

     添加  JPA时间比较方法

1.between - findByStartDateBetween … where x.startDate between ?1 and ?2

2.lessThan/lessThanEqual

lessThan - findByEndLessThan … where x.start< ?1

lessThanEqual findByEndLessThanEqual … where x.start <= ?1

3.greaterThan/greaterThanEqual

greaterThan - findByStartGreaterThan … where x.end> ?1

greaterThanEqual - findByStartGreaterThanEqual … where x.end>= ?1