使用mybatis example 和 java 8的特性來實現多表關聯且帶有查詢條件的查詢

一也者發表於2018-10-10
MyBatisJava

Mybatis Example的好處

使用了物件導向的思想，其關聯資料庫的單表查詢都能自動生成。減少了勞動量，同時將複雜查詢拆分成單表查詢，加快了查詢速度。讓其複雜的關聯在service層中使用程式碼進行拼裝。這樣處理速度會比一個大查詢快得多，因為電腦本身就是幹這個計算的。

     public ServiceResponse doInOperation(ServiceResponse serviceResponse) throws Exception {
                //醫生資訊
                DictUserOrgInfoExample dictUserOrgInfoExample = new DictUserOrgInfoExample();
                DictUserOrgInfoExample.Criteria dictUserOrgInfoExampleCriteria = dictUserOrgInfoExample.createCriteria();
                dictUserOrgInfoExampleCriteria.andActiveEqualTo(Boolean.TRUE)
                        .andAffiliatedDeptEqualTo(request.getDepartmentId())
                        .andOrgIdEqualTo(request.getLoginOrgId());

                List<DictUserOrgInfo> dictUserOrgInfos = dictUserOrgInfoService.selectByExample(dictUserOrgInfoExample);

                //醫生姓名
                List<Long> doctorIds = dictUserOrgInfos.stream().map(DictUserOrgInfo::getUserBaseId).distinct().collect(Collectors.toList());
                DictUserBaseInfoExample dictUserBaseInfoExample = new DictUserBaseInfoExample();
                DictUserBaseInfoExample.Criteria dictUserBaseInfoExampleCriteria = dictUserBaseInfoExample.createCriteria();
                dictUserBaseInfoExampleCriteria.andActiveEqualTo(Boolean.TRUE)
                        .andIdIn(doctorIds);

                List<DictUserBaseInfo> dictUserBaseInfos = dictUserBaseInfoService.selectByExample(dictUserBaseInfoExample);

                //職稱名稱
                List<String> codes = dictUserOrgInfos.stream().map(DictUserOrgInfo::getProfessionalTitle).distinct().collect(Collectors.toList());
                DictDataExample dictDataExample = new DictDataExample();
                DictDataExample.Criteria dictDataExampleCriteria = dictDataExample.createCriteria();
                dictDataExampleCriteria.andActiveEqualTo(Boolean.TRUE).andSubCodeIn(codes);

                List<DictData> dictData = dictDataService.selectByExample(dictDataExample);

                //科室
                List<Long> deptId = dictUserOrgInfos.stream().map(DictUserOrgInfo::getAffiliatedDept).distinct().collect(Collectors.toList());
                DictDepartmentExample dictDepartmentExample = new DictDepartmentExample();
                DictDepartmentExample.Criteria dictDepartmentExampleCriteria = dictDepartmentExample.createCriteria();
                dictDepartmentExampleCriteria.andActiveEqualTo(Boolean.TRUE)
                        .andIdIn(deptId);

                List<DictDepartment> dictDepartments = dictDepartmentService.selectByExample(dictDepartmentExample);
                //號源
                OrdScheduleExample ordScheduleExample = new OrdScheduleExample();
                OrdScheduleExample.Criteria ordScheduleExampleCriteria = ordScheduleExample.createCriteria();
                ordScheduleExampleCriteria.andActiveEqualTo(Constants.FLAG_TRUE).andUserIdIn(doctorIds);

                List<OrdSchedule> ordSchedules = ordScheduleService.selectByExample(ordScheduleExample);

                if (StringUtil.isNotEmpty(request.getQueryStr())) {
                    dictUserOrgInfoExampleCriteria.andJobNumberLike(
                            String.format("%%%s%%", request.getQueryStr()));

                    dictUserBaseInfoExampleCriteria.andUserNameLike(
                            String.format("%%%s%%", request.getQueryStr()));

                    List<DictUserOrgInfo> orgInfos = dictUserOrgInfoService.selectByExample(dictUserOrgInfoExample);
                    List<Long> filterJobList = orgInfos.stream().map(DictUserOrgInfo::getId)
                            .distinct().collect(Collectors.toList());

                    List<DictUserBaseInfo> userBaseInfos = dictUserBaseInfoService.selectByExample(dictUserBaseInfoExample);
                    List<Long> filterNameList = userBaseInfos.stream().map(DictUserBaseInfo::getId)
                            .distinct().collect(Collectors.toList());

                    dictUserOrgInfos = dictUserOrgInfos.stream().filter(d -> filterJobList.contains(d.getId())
                            || filterNameList.contains(d.getUserBaseId()))
                            .collect(Collectors.toList());
                }

                List<ScheduleDoctorListRsp> list = new ArrayList<>();

                for (DictUserOrgInfo dictUserOrgInfo : dictUserOrgInfos) {
                    ScheduleDoctorListRsp scheduleDoctorListRsp = new ScheduleDoctorListRsp();

                    Optional<DictUserBaseInfo> dictUserBaseInfo = dictUserBaseInfos.stream()
                            .filter(d -> d.getId().equals(dictUserOrgInfo.getUserBaseId())).findFirst();

                    Optional<DictData> data = dictData.stream()
                            .filter(d -> d.getSubCode().equals(dictUserOrgInfo.getProfessionalTitle())).findFirst();

                    Optional<DictDepartment> dictDepartment = dictDepartments.stream()
                            .filter(d -> d.getId().equals(dictUserOrgInfo.getAffiliatedDept())).findFirst();

                    Optional<OrdSchedule> ordSchedule = ordSchedules.stream()
                            .filter(s -> s.getUserId().equals(dictUserOrgInfo.getUserBaseId())).findFirst();

                    scheduleDoctorListRsp.setDoctorName(dictUserBaseInfo.isPresent() ? dictUserBaseInfo.get().getUserName() : "");
                    scheduleDoctorListRsp.setDoctorId(dictUserOrgInfo.getUserBaseId());
                    scheduleDoctorListRsp.setDeptId(dictUserOrgInfo.getAffiliatedDept());
                    scheduleDoctorListRsp.setDeptName(dictDepartment.isPresent() ? dictDepartment.get().getDeptName() : "");
                    scheduleDoctorListRsp.setJobNumber(dictUserOrgInfo.getJobNumber());
                    scheduleDoctorListRsp.setJobTitle(data.isPresent() ? data.get().getTypeDesc() : "");
                    scheduleDoctorListRsp.setTotalNumber(ordSchedule.isPresent() ? ordSchedule.get().getTotalNumber() : 0);
                    list.add(scheduleDoctorListRsp);
                }
                return serviceResponse.setBody(list);
            }

總體思路就是根據介面需要的返回欄位，查出返回欄位所在表的實體集合。根據關聯條件類似下面程式碼

  //職稱名稱
                List<String> codes = dictUserOrgInfos.stream().map(DictUserOrgInfo::getProfessionalTitle).distinct().collect(Collectors.toList());
                DictDataExample dictDataExample = new DictDataExample();
                DictDataExample.Criteria dictDataExampleCriteria = dictDataExample.createCriteria();
                dictDataExampleCriteria.andActiveEqualTo(Boolean.TRUE).andSubCodeIn(codes);

                List<DictData> dictData = dictDataService.selectByExample(dictDataExample);

主表裡存的是字典表中的編碼 需要查編碼對應的名稱。所以根據主表的list集合拿到編碼的集合 此處用到了java 8中的流特性。

這樣就能查出所有的需要的字典表物件實體了。

下面這段程式碼是在迴圈拼裝資料的時候使用。迴圈的list物件是主表。每次拿出迴圈當中一個物件，則拿主表中的編碼欄位和字典

表list中的一個相關聯的欄位(迴圈) 若這個字典表中有物件的欄位和主表物件關聯到。則在下面封裝資料的時候拿此過濾的物件取

需要的屬性就行了。

 Optional<DictData> data = dictData.stream()
                            .filter(d -> d.getSubCode().equals(dictUserOrgInfo.getProfessionalTitle())).findFirst();

 

需要模糊查詢

模糊查詢條件的話也是過濾主表list資料 在for迴圈中將不符合的過濾就行了

 if (StringUtil.isNotEmpty(request.getQueryStr())) {
                    dictUserOrgInfoExampleCriteria.andJobNumberLike(
                            String.format("%%%s%%", request.getQueryStr()));

                    dictUserBaseInfoExampleCriteria.andUserNameLike(
                            String.format("%%%s%%", request.getQueryStr()));

                    List<DictUserOrgInfo> orgInfos = dictUserOrgInfoService.selectByExample(dictUserOrgInfoExample);
                    List<Long> filterJobList = orgInfos.stream().map(DictUserOrgInfo::getId)
                            .distinct().collect(Collectors.toList());

                    List<DictUserBaseInfo> userBaseInfos = dictUserBaseInfoService.selectByExample(dictUserBaseInfoExample);
                    List<Long> filterNameList = userBaseInfos.stream().map(DictUserBaseInfo::getId)
                            .distinct().collect(Collectors.toList());

                    dictUserOrgInfos = dictUserOrgInfos.stream().filter(d -> filterJobList.contains(d.getId())
                            || filterNameList.contains(d.getUserBaseId()))
                            .collect(Collectors.toList());
                }

拿出模糊搜尋的表與主表關聯的id集合。進行過濾，（並行條件使用||）這樣就能搜尋出符合條件的主表資料了，最後在下面遍歷。

相關文章