使用mybatis example 和 java 8的特性来实现多表关联且带有查询条件的查询

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集合。进行过滤，（并行条件使用||）这样就能搜索出符合条件的主表数据了，最后在下面遍历。