JPA query學習

1.JPA query   統計 

@Query("select count(bo) from BaseOrder as bo where bo.gGameId=?1 and bo.gRoleId=?2 and bo.createdAt >= ?3 and bo.status=2")     Integer countByGameIdAndRoleIdAndStartDate(String gameId, String roleId, Date startDate);


    @Query("select count(bo) from BaseOrder as bo where bo.gGameId=?1 and bo.gRoleId=?2 and bo.status=2")
    Integer countByGameIdAndRoleId(String gameId, String roleId);


    @Query("select new Map(sum(bo.orderAmount) as amount,bo.channel as channel,bo.gGameId as gameId,bo.tradeType as tradeType,bo.currency as currency) from BaseOrder as bo where bo.channel in(?1) and bo.createdAt between ?2 and ?3 and bo.status =2 group by bo.channel,bo.gGameId,bo.tradeType,bo.currency")

    List<Object[]> getTotalAmountByChannel(List<String> channel, Date startTime , Date endTime);

2.distinct

 @Query("select DISTINCT new com.kz.balance.response.RepSelection(b.gameName,b.gameCode)  from Bill b")
List<RepSelection> getGameList();

@Query("select DISTINCT new com.kz.balance.response.RepSelection(b.mstChannelName,b.mstChannelCode)  from Bill b")
List<RepSelection> getmstChannelList();

@Query("select DISTINCT new com.kz.balance.response.RepSelection(b.companyName,b.companyCode)  from Bill b")
List<RepSelection> getcompanyNameList();

@Query("select DISTINCT new com.kz.balance.response.RepSelection(b.payPartnerName,b.payPartnerCode)  from Bill b")
List<RepSelection> getpayTypeList();

3.多條件查詢

public interface BillDao extends CrudRepository<Bill,Long>, JpaSpecificationExecutor<Bill> {


//擷取賬單(最新20條資料)
@Query("select b from Bill b order by b.billDate desc")
List<Bill> findAll(Pageable pageable);

-----------------------------------------------------------------------------------------

@Override
public List<Bill> getBillBySelection(ReqSelection selection) {

String billNo = selection.getBillNo();
if(billNo!=null && !billNo.equals("")) {
List<Bill> lists = new ArrayList<Bill>();
Bill b = billDao.findByBillNo(billNo);
lists.add(b);
return lists;
}
String checkNo = selection.getCheckNo();
if(checkNo!=null && !checkNo.equals("")) {
return billDao.findByCheckNo(checkNo);
}

if(selection.getBillDate()==null&&selection.getBillSate()==null&&selection.getCompanyCode()==null
&&selection.getGameCode()==null&&selection.getMstChannelCode()==null&&selection.getPayPartnerCode()==null) {
return billDao.findAll(new PageRequest(0, 5));
}

        return billDao.findAll(getWhereClause(selection));
}

private Specification<Bill> getWhereClause(ReqSelection selection){
       return new Specification<Bill>() {
           @Override
           public Predicate toPredicate(Root<Bill> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
               List<Predicate> predicates = new ArrayList<>();
               String billDate= selection.getBillDate();
               if(billDate != null && !billDate.equals("")){
                   predicates.add(criteriaBuilder.equal(root.get("billDate"), billDate));
               }
               List<String> billState= selection.getBillSate();
               if(billState != null && billState.size()>0){
                   predicates.add(root.get("billState").in(billState));
               }
               String mstChannelCode= selection.getMstChannelCode();
               if(mstChannelCode != null && !mstChannelCode.equals("")){
                   predicates.add(criteriaBuilder.equal(root.get("mstChannelCode"), mstChannelCode));
               }
               
               String payPartnerCode= selection.getPayPartnerCode();
               if(payPartnerCode != null && !payPartnerCode.equals("")){
                   predicates.add(criteriaBuilder.equal(root.get("payPartnerCode"), payPartnerCode));
               }
               String companyCode= selection.getCompanyCode();
               if(companyCode != null && !companyCode.equals("")){
                   predicates.add(criteriaBuilder.equal(root.get("companyCode"), companyCode));
               }
               String gameCode= selection.getGameCode();
               if(gameCode != null && !gameCode.equals("")){
                   predicates.add(criteriaBuilder.equal(root.get("gameCode"), gameCode));
               }
               Predicate[] pre = new Predicate[predicates.size()];
               return query.where(predicates.toArray(pre)).getRestriction();
           }
       };
   }