JPA query学习

最新推荐文章于 2024-09-03 21:30:00 发布

发疯鸟aaa

最新推荐文章于 2024-09-03 21:30:00 发布

阅读量641

点赞数 1

分类专栏： sql

本文链接：https://blog.csdn.net/u014717807/article/details/79268087

版权

sql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

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