JPA複合クエリー、複数テーブル関連クエリー、データ・バー数<br>(CriteriaQuery)

JPAマルチコンディションクエリ(CriteriaQuery)

    /**  Demo   
     * @return
     */
    public List findDemoList(ParamVO param,Pageable pageable,List userIds){
        List result = demoRepository.findAll(new Specification(){
            @Override
            public Predicate toPredicate(Root root, CriteriaQuery> query, CriteriaBuilder cb) {
                List predicates = new ArrayList();
                // 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 roleList;

JPA左接続クエリ上のテンプレートに次の条件を追加します.

                if(null!=param.getRole()){
                    //   
                    Join join = root.join("roleList",JoinType.LEFT);
                    predicates.add(cb.equal(join.get("id"),param.getRole()));
                }

 
 
データ・バー数

 public Integer findCountReport(Integer orgId){
        //     
         Long count= demoRepository.count(new Specification(){
            @Override
            public Predicate toPredicate(Root root, CriteriaQuery> query, CriteriaBuilder cb) {
                List predicates = new ArrayList();
                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