循环中套查询优化

之前的想法是赋值的话是（无论是循环还是流式操作）在循环里面搜索完赋值，这是新手范的通病，建议不要再循环里面搜索赋值，这样的性能很差劲。 优化后的方法：结合本次业务 利用map集合 key存储主键 value存储数量

Repository:

/** * 查询创建人创建每个分组的人数集合 * @param usrPid 创建人pid * @return */ @Query("SELECT\n" + "model.pid,\n" + "COUNT(guser.groupDetailsPid)\n" + "FROM\n" + "GroupDetails model\n" + "LEFT JOIN GroupDetailsUser guser ON model.pid = guser.groupDetailsPid \n" + "WHERE\n" + "model.userPid = :usrPid\n" + "GROUP BY\n" + "model.pid") Stream<Object[]> userNum(String usrPid);

Service层

/** * 查询创建人创建每个分组的人数Map集合 String分组主键 * @param usrPid 创建人pid * @return */ @Transactional(readOnly = true) public Map<String,Long> userNum(String usrPid) { Map<String, Long> map = repository.userNum(usrPid) .collect(Collectors.toMap(arr->(String) arr[0], arr -> (long) arr[1])); return map; }

Controller层

涉及的代码：

Map<String, Long> countUsrs = service.userNum(getOperatorId()); Page<GroupDetailsDTO> dataDto = data.map(groupDetails -> new GroupDetailsDTO( groupDetails.getPid(), groupDetails.getOrgPid(), groupDetails.getUserPid(), groupDetails.getGroupName(), groupDetails.getCreateTime(), groupDetails.getGroupDesc(), countUsrs.get(groupDetails.getPid()) ));

全部代码

/** * 分页显示教师创建的分组 * @param page * @param title 分组名称,默认值为"" * @param startTime 起始时间 * @param endTime 结束时间 * @return */ @GetMapping({"/group/details"}) public ResponseEntity findAllByPage(@PageableDefault Pageable page, @RequestParam(required = false) String title, @RequestParam(required = false) @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate startTime, @RequestParam(required = false) @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate endTime) { if (startTime != null && endTime != null && startTime.isAfter(endTime)) { return ResponseEntity.status(HttpStatus.BAD_REQUEST).body(Translator.toLocale("group.time.Failed.message")); } LocalDateTime startLocalDateTime = null; LocalDateTime endLocalDateTime = null; if (startTime != null || endTime != null) { startLocalDateTime = LocalDateTime.of(startTime, LocalTime.of(0, 0)); endLocalDateTime = LocalDateTime.of(endTime, LocalTime.of(23, 59)); } Map<String, Long> countUsrs = service.userNum(getOperatorId()); Page<GroupDetails> data = service.findAllByPage(getOperatorId(), title, startLocalDateTime, endLocalDateTime, page); Page<GroupDetailsDTO> dataDto = data.map(groupDetails -> new GroupDetailsDTO( groupDetails.getPid(), groupDetails.getOrgPid(), groupDetails.getUserPid(), groupDetails.getGroupName(), groupDetails.getCreateTime(), groupDetails.getGroupDesc(), countUsrs.get(groupDetails.getPid()) )); return ResponseEntity.ok(dataDto); }