1.项目中之前的"我关注的拍品列表"需要添加筛选功能，因为目前显示的关注的拍品太多没有进行分类，用户体验差。
2.添加筛选条件之后，可以筛选出“未开始”“进行中”“已结束”三种情况的拍品。
其中
“未开始”--->状态为 1
“进行中”---->状态为 2
“已结束”---->状态为 3 or 4 or 5 or 6 or 7
3.优化之前，每一个条件都书写了一个SQL 语句。 功能能够实现，但是代码比较冗余。
//未开始状态
@Query(value="SELECT a.id,a.auc_id,a.crt_time,a.crt_user_id,a.crt_user_name,a.customer_id,a.upd_time,a.upd_user_id,a.upd_user_name FROM auc_attention a,auc_lot b WHERE a.auc_id = b.id " + "AND a.customer_id = ?1 AND ( b.STATUS = 1) order by a.crt_time desc limit ?2,?3",nativeQuery=true) List<AucAttention> findAllByCustomerIdAndAucLotStatus1(Long customerId, int i, int rows); @Query(value="select count(*) from (SELECT a.id FROM auc_attention a,auc_lot b WHERE a.auc_id = b.id " + "AND a.customer_id = ?1 AND ( b.STATUS = 1)) as temp",nativeQuery=true) int findAllCountByCustomerIdAndAucLotStatus1(Long customerId); //进行中状态 @Query(value="SELECT a.id,a.auc_id,a.crt_time,a.crt_user_id,a.crt_user_name,a.customer_id,a.upd_time,a.upd_user_id,a.upd_user_name FROM auc_attention a,auc_lot b WHERE a.auc_id = b.id " + "AND a.customer_id = ?1 AND ( b.STATUS = 2) order by a.crt_time desc limit ?2,?3",nativeQuery=true) List<AucAttention> findAllByCustomerIdAndAucLotStatus2(Long customerId, int i, int rows); @Query(value="select count(*) from (SELECT a.id FROM auc_attention a,auc_lot b WHERE a.auc_id = b.id " + "AND a.customer_id = ?1 AND ( b.STATUS = 2)) as temp",nativeQuery=true) int findAllCountByCustomerIdAndAucLotStatus2(Long customerId); //已结束状态 @Query(value="SELECT a.id,a.auc_id,a.crt_time,a.crt_user_id,a.crt_user_name,a.customer_id,a.upd_time,a.upd_user_id,a.upd_user_name FROM auc_attention a,auc_lot b WHERE a.auc_id = b.id " + "AND a.customer_id = ?1 AND ( b.STATUS != 1 AND b.STATUS != 2) order by a.crt_time desc limit ?2,?3",nativeQuery=true) List<AucAttention> findAllByCustomerIdAndAucLotStatus3(Long customerId, int i, int rows); @Query(value="select count(*) from (SELECT a.id FROM auc_attention a,auc_lot b WHERE a.auc_id = b.id " + "AND a.customer_id = ?1 AND ( b.STATUS != 1 AND b.STATUS != 2)) as temp",nativeQuery=true) int findAllCountByCustomerIdAndAucLotStatus3(Long customerId); //默认状态 @Query(value="SELECT a.id,a.auc_id,a.crt_time,a.crt_user_id,a.crt_user_name,a.customer_id,a.upd_time,a.upd_user_id,a.upd_user_name FROM auc_attention a,auc_lot b WHERE a.auc_id = b.id " + "AND a.customer_id = ?1 order by a.crt_time desc limit ?2,?3",nativeQuery=true) List<AucAttention> findAllByCustomerIdAndAucLotStatus(Long customerId, int i, int rows); @Query(value="select count(*) from (SELECT a.id FROM auc_attention a,auc_lot b WHERE a.auc_id = b.id " + "AND a.customer_id = ?1 ) as temp",nativeQuery=true) int findAllCountByCustomerIdAndAucLotStatus(Long customerId);
4.优化之后，公用一个SQL语句即可。
控制器方法：
/** * 返回所有的我关注的拍品列表 筛选条件 未开始,进行中,已结束 * * 1 未开始 2 进行中 3 已结束 * * @param request * @return */ @RequestMapping(value = "/auctionFocusOnList", method = RequestMethod.POST) @ResponseBody public ResultDTO<Map<String, Object>> findAllMyFocusOn(Integer checkStatus, int page, int rows, HttpServletRequest request) { if (checkStatus == null) { checkStatus = 0; } Map<String, Object> retMap = null; List<Map<String, Object>> focusOnList = new ArrayList<>(); // 获取session中的登陆对象 Customer user = (Customer) request.getSession().getAttribute("user"); List<AucAttention> aucAttentionList = null; // 获取所有的关注的拍品的条件 if (null != user) { Long customerId = Long.valueOf(user.id()); int total = 0; if (checkStatus == 1) {// 未开始 aucAttentionList = aucAttentionRepository.findAllByCustomerIdAndAucLotStatus(customerId, (page - 1) * rows, rows, 1, 0, 0, 0, 0, 0, 0); total = aucAttentionRepository.findAllCountByCustomerIdAndAucLotStatus(customerId, 1, 0, 0, 0, 0, 0, 0); } else if (checkStatus == 2) {// 进行中 aucAttentionList = aucAttentionRepository.findAllByCustomerIdAndAucLotStatus(customerId, (page - 1) * rows, rows, 2, 0, 0, 0, 0, 0, 0); total = aucAttentionRepository.findAllCountByCustomerIdAndAucLotStatus(customerId, 2, 0, 0, 0, 0, 0, 0); } else if (checkStatus == 3) {// 已结束 aucAttentionList = aucAttentionRepository.findAllByCustomerIdAndAucLotStatus(customerId, (page - 1) * rows, rows, 3, 4, 5, 6, 7, 0, 0); total = aucAttentionRepository.findAllCountByCustomerIdAndAucLotStatus(customerId, 3, 4, 5, 6, 7, 0, 0); } else {// 默认情况 aucAttentionList = aucAttentionRepository.findAllByCustomerIdAndAucLotStatus(customerId, (page - 1) * rows, rows, 1, 2, 3, 4, 5, 6, 7); total = aucAttentionRepository.findAllCountByCustomerIdAndAucLotStatus(customerId, 1, 2, 3, 4, 5, 6, 7); } Map<String, Object> map = null; AucLot aucLot = null; int auctionStatus = 4; String startTime = null; String endTime = null; String aucLotTime = null; SimpleDateFormat dateFormater = new SimpleDateFormat("yyyy.MM.dd");// 格式化时间 for (AucAttention aucAttention : aucAttentionList) { map = new HashMap<String, Object>(); aucLot = aucLotRepository.findOne(aucAttention.aucId()); if (aucLot != null) { startTime = dateFormater.format(aucLot.startTime());// 拍卖开始时间 endTime = dateFormater.format(aucLot.endTime());// 拍卖结束时间 // 定义拍卖时间,开始时间和结束时间的拼接 if (startTime.equals(endTime)) { aucLotTime = startTime; } else { aucLotTime = startTime + "-" + endTime; } map.put("aucLotId", aucLot.id());// 拍卖id map.put("goodsName", aucLot.goodsName());// 拍品名称 map.put("aucLotTime", aucLotTime);// 拍品时间 map.put("status", aucLot.status());// 拍卖状态 // 关注的拍品的拍卖资质对象的条件 Specification<AucBrand> aucBrandSpec = (root, query, cb) -> { List<Predicate> predicates = new ArrayList<Predicate>(); if (null != aucAttention) { Predicate predicate = cb.equal(root.get(AucBrand_.customerId), aucAttention.customerId());// 竞买人id predicates.add(predicate); } if (null != aucAttention) { Predicate predicate = cb.equal(root.get(AucBrand_.aucLot).get(AucLot_.id), aucAttention.aucId());// 拍品id predicates.add(predicate); } if (!predicates.isEmpty()) { return cb.and(predicates.toArray(new Predicate[0])); } else { return null; } }; List<AucBrand> aucBrandList = aucBrandRepository.findAll(aucBrandSpec); if (aucBrandList != null && aucBrandList.size() > 0) { AucBrand aucBrand = aucBrandList.get(0); if (aucBrand != null) { boolean bailPayed = aucBrand.isBailPayed();// 保证金是否支付 int isDeal = aucBrand.isDeal();// 成交状态 int auditType = aucBrand.auditType();// 审核状态 map.put("bailPayed", bailPayed); map.put("auditType", auditType); // 获取保证金主键id值 List<SettlementBail> settlementBails = settlementBailRepository .findAllByBrandId(aucBrand.id()); if (settlementBails != null && settlementBails.size() > 0) { SettlementBail settlementBail = settlementBails.get(0); if (settlementBail != null) { map.put("settlementBailId", settlementBail.id()); } else { map.put("settlementBailId", null); } } // 判断竞拍情况 if (bailPayed == true) { // 成交 if (isDeal == 1) { auctionStatus = 1;// "已成交" } else { auctionStatus = 2;// "已参拍" } } else { // 保证金未支付 auctionStatus = 3;// "已报名" } map.put("auctionStatus", auctionStatus); } else { // 没有参拍资质 auctionStatus = 4;// "未报名" map.put("bailPayed", null); map.put("auditType", null); map.put("settlementBailId", null); map.put("auctionStatus", auctionStatus);// 竞拍情况 } } else { auctionStatus = 4;// "未报名" map.put("bailPayed", null); map.put("auditType", null); map.put("settlementBailId", null); map.put("auctionStatus", auctionStatus);// 竞拍情况 } focusOnList.add(map); } else { continue; } } retMap = new HashMap<String, Object>(); retMap.put("focusOnList", focusOnList); retMap.put("total", total); return new ResultDTO<Map<String, Object>>(retMap); } else { return new ResultDTO<Map<String, Object>>(700, "用户未登录", null); } }
SQL语句：
@Query(value="SELECT a.id,a.auc_id,a.crt_time,a.crt_user_id,a.crt_user_name,a.customer_id,a.upd_time,a.upd_user_id,a.upd_user_name FROM auc_attention a,auc_lot b WHERE a.auc_id = b.id "
+ "AND a.customer_id = ?1 AND ( b.STATUS = ?4 or b.STATUS = ?5 or b.STATUS = ?6 or b.STATUS = ?7 or b.STATUS = ?8 or b.STATUS = ?9 or b.STATUS = ?10) order by a.crt_time desc limit ?2,?3",nativeQuery=true)
List<AucAttention> findAllByCustomerIdAndAucLotStatus(Long customerId,
int i, int rows,int checkStatus1,int checkStatus2,int checkStatus3,int checkStatus4,int checkStatus5,int checkStatus6,int checkStatus7);
@Query(value="select count(*) from (SELECT a.id FROM auc_attention a,auc_lot b WHERE a.auc_id = b.id "
+ "AND a.customer_id = ?1 AND ( b.STATUS = ?2 or b.STATUS = ?3 or b.STATUS = ?4 or b.STATUS = ?5 or b.STATUS = ?6 or b.STATUS = ?7 or b.STATUS = ?8)) as temp",nativeQuery=true)
int findAllCountByCustomerIdAndAucLotStatus(Long customerId,int checkStatus1,int checkStatus2,int checkStatus3,int checkStatus4,int checkStatus5,int checkStatus6,int checkStatus7);