@Repository
public interface DisplayScreenRepository extends JpaRepository<DisplayScreen, Long>, JpaSpecificationExecutor<DisplayScreen> {
}
public ChiticPageResponse<WorkSheetResponse> getPageWorkSheet(Integer pageNo, Integer pageSize,Integer workSheetState,Long dutyGroup,Long dutyUser,String searchKey) { Long userId = UserUtil.getUserId(); //当前用户所拥有的工单权限 List<WorkSheetGroupUser> byGroupUser = workSheetGroupUserRepository.findByUserId(userId); Set<Integer> set = byGroupUser.stream().map(WorkSheetGroupUser::getRights).collect(Collectors.toSet()); List<User> allByCompanyId = userRepository.findAllByCompanyId(UserUtil.getCompanyId()); //该公司下所有的用户id Set<Long> ids = allByCompanyId.stream().map(x -> x.getId()).collect(Collectors.toSet()); Specification<WorkSheet> specification = (Specification <WorkSheet>) (root, query, cb) -> { List <Predicate> predicates = new ArrayList <>(); //查询删除标识为0 的 predicates.add(cb.equal(root.get("isDelete"), WorkSheetConstant.NOT_DELETED)); predicates.add(cb.notEqual(root.get("workSheetState"), WorkSheetStateEnum.TRUE_CHECK.getCode())); predicates.add(cb.notEqual(root.get("workSheetState"), WorkSheetStateEnum.TRUE_CLOSE.getCode())); //普通管理人员1 if(!set.contains(WorkSheetRightsEnum.HIGN_VIP.getCode()) && set.contains(WorkSheetRightsEnum.GENERAL_VIP.getCode())){ //只能查看自己创建的工单 predicates.add(cb.equal(root.get("createUser"), userId)); } //维修人员2 if(!set.contains(WorkSheetRightsEnum.HIGN_VIP.getCode()) && set.contains(WorkSheetRightsEnum.FIX_USER.getCode())){ //只能查看责任人是自己或者责任组是当前用户的所在组 Set<Long> setGroup = byGroupUser.stream().map(WorkSheetGroupUser::getGroupId).collect(Collectors.toSet()); CriteriaBuilder.In<Object> dutyGroup1 = cb.in(root.get("dutyGroup")).value(setGroup); Predicate dutyUser1 = cb.equal(root.get("dutyUser"), userId); predicates.add(cb.and(cb.or(dutyGroup1,dutyUser1))); predicates.add(cb.equal(root.get("workSheetState"), WorkSheetStateEnum.STAY_ASSIGN.getCode())); } if (null != workSheetState) { predicates.add(cb.equal(root.get("workSheetState"), workSheetState)); } if (null != dutyGroup) { predicates.add(cb.equal(root.get("dutyGroup"), dutyGroup)); } if (null != dutyUser) { predicates.add(cb.equal(root.get("dutyUser"), dutyUser)); } if (StringUtils.isNotBlank(searchKey)){ Predicate workSheetName = cb.like(root.get("workSheetName"), "%" + searchKey + "%"); Predicate workSheetNum = cb.like(root.get("workSheetNum"), "%" + searchKey + "%"); List<WaterHouse> byHouseNameLike = waterHouseRepository.findByHouseNameLike("%" + searchKey + "%"); if(CollectionUtils.isNotEmpty(byHouseNameLike)){ Set<Long> houseIdSet = byHouseNameLike.stream().map(WaterHouse::getId).collect(Collectors.toSet()); CriteriaBuilder.In<Object> objectIn = cb.in(root.get("houseId")); houseIdSet.forEach(x->{ objectIn.value(x); }); predicates.add(cb.and(cb.or(workSheetName,workSheetNum,objectIn))); }else{ predicates.add(cb.and(cb.or(workSheetName,workSheetNum))); } } if (CollectionUtils.isNotEmpty(ids)) { CriteriaBuilder.In<Object> objectIn = cb.in(root.get("createUser")); ids.forEach(x->{ objectIn.value(x); }); predicates.add(objectIn); } query.where(predicates.toArray(new Predicate[predicates.size()])); return query.getRestriction(); }; PageRequest pageRequest = PageRequest.of(pageNo-1, pageSize,Sort.by(Sort.Direction.DESC,"createTime")); Page <WorkSheet> page = workSheetRepository.findAll(specification, pageRequest); List<WorkSheet> content = page.getContent(); List<WorkSheetResponse> workSheetListResponse = CopyUtil.copyList(content, WorkSheetResponse.class); /** * 返回前端组名和用户名,转换id和name */ List<User> listUser = userRepository.findAll(); List<WorkSheetGroup> listGroup = workSheetGroupRepository.findAll(); //查询所有泵房 List<WaterHouse> waterHouseList = waterHouseRepository.findAll(); List<WaterUnit> waterUnitList = waterUnitRepository.findAll(); for(WorkSheetResponse w : workSheetListResponse){ if(null != w.getDutyUser()){ listUser.stream().forEach(u->{ if(w.getDutyUser().equals(u.getId())){ w.setDutyUserName(u.getName()); } }); } if(null != w.getDutyGroup()){ listGroup.stream().forEach(g->{ if(w.getDutyGroup().equals(g.getId())){ w.setDutyGroupName(g.getGroupName()); } }); } if(null != w.getHouseId()){ waterHouseList.stream().forEach(g->{ if(w.getHouseId().equals(g.getId())){ w.setHouseName(g.getHouseName()); } }); } if(null != w.getUnitId()){ waterUnitList.stream().forEach(g->{ if(w.getUnitId().equals(g.getId())){ w.setUnitSn(g.getUnitSn()); } }); } } return ChiticPageResponse.of(workSheetListResponse, page.getTotalElements(), page.getTotalPages()); }
解析出来的SQL
SELECT * FROM work_sheet w WHERE w.is_delete = 0 AND w.work_sheet_state <> 6 AND w.work_sheet_state <> 7 AND ( w.work_sheet_name LIKE ? OR w.work_sheet_num LIKE ? OR w.house_id IN ( 12 ) ) AND ( w.create_user IN ( 2455275655638549504, 2447384607788631040, 2445987138173535232 ) ) ORDER BY w.create_time DESC LIMIT ?
JPA新增、修改的坑
1,新增时,如果数据库有默认值字段,会将其置空。解决办法:在service层再将默认值手动赋值一下,如下:
//工单状态:0 待下达
copy.setWorkSheetState(0);
//验收状态:0未验收
copy.setCheckState(0);
//工单来源默认0:手动输入
copy.setWorkSheetSource(0);
//工单下达次数:0
copy.setAssignCount(0);
workSheetRepository.save(copy);
2,修改时,如果只传部分字段,会将未传的字段置空。解决办法
1,根绝id查询对象的值,在此基础上赋值
Optional<WorkSheet> workSheetOptional = workSheetRepository.findById(request.getId());
if (!workSheetOptional.isPresent()) {
throw ChiticException.of("异常");
}
WorkSheet workSheet = workSheetOptional.get();
//赋值
workSheet.set(前台传过来的值);
if(null != request.getRemarks()){
workSheet.setRemarks(request.getRemarks());
}
workSheetRepository.save(workSheet);
还有一种注解的方式,动态插入、更新,本人测试过,可以实现(只是不知道有没有其他的影响)
@DynamicInsert
@DynamicUpdate