mongoDB 多表关联查询_mongodb4.x多表关联查询后条件查询

    @Override
    public List<InspectionResultInfo> inspectionList(InspectionResultInfo inspectionResultInfo) throws ParseException {

        List<InspectionResultInfo> infoList = new ArrayList<InspectionResultInfo>();
        //关联查询 关联条件定义
        LookupOperation schedule = LookupOperation.newLookup()
                .from(CollectionNameConstants.NAT_TUBE)
                .localField(NatAppoCheck.TUBE_ID)
                .foreignField(ModuleConstant.ID)
                .as(CollectionNameConstants.NAT_TUBE);//定义别名
        //创建一个操作聚合operations
        List<AggregationOperation> operations = new ArrayList<>();
        //创建一个条件类criteria
        Criteria criteria = new Criteria();
        operations.add(schedule);
        //设置分页属性checkedPersonName
        operations.add(Aggregation.limit(inspectionResultInfo.getLimit()));
        operations.add(Aggregation.skip(inspectionResultInfo.getPage()-1));
        //受检人姓名模糊查询条件
        if (StringUtils.isNotBlank(inspectionResultInfo.getCheckedPersonName())){
            Pattern pattern=Pattern.compile("^.*"+inspectionResultInfo.getCheckedPersonName()+".*$", Pattern.CASE_INSENSITIVE);
            Criteria criteriaToProject = new Criteria().and(NatAppoCheck.CHECKED_PERSON_NAME).regex(pattern);
            AggregationOperation project_matchToLots = Aggregation.match(criteriaToProject);
            operations.add(project_matchToLots);

        }
        //受检人联系方式模糊查询条件
        if (StringUtils.isNotBlank(inspectionResultInfo.getPersonTelphone())){
            Pattern pattern=Pattern.compile("^.*"+inspectionResultInfo.getPersonTelphone()+".*$", Pattern.CASE_INSENSITIVE);
            operations.add(Aggregation.match(criteria.and(NatAppoCheck.CHECKED_PERSON_TELEPHONE).regex(pattern)));
        }
        //检测结果查询条件
        if (!Objects.isNull(inspectionResultInfo.getCheckResult())){
            Criteria criteriaToProject = new Criteria().and(NatAppoCheck.CHECK_RESULT).is(Integer.parseInt(inspectionResultInfo.getCheckResult()));
            AggregationOperation project_matchToLots = Aggregation.match(criteriaToProject);
            operations.add(project_matchToLots);

        }
        //检测点名称模糊查询，not yet
        if (inspectionResultInfo.getCheckPointIds()!=null && !inspectionResultInfo.getCheckPointIds().isEmpty()){
            Criteria criteriaToProject = new Criteria().where(CollectionNameConstants.NAT_TUBE +"."+ModuleConstant.TubeModule.CHECK_POINT_ID).in(inspectionResultInfo.getCheckPointIds());
            AggregationOperation project_matchToLots = Aggregation.match(criteriaToProject);
            operations.add(project_matchToLots);
        }
        //采样点名称模糊查询，ditto
        if (inspectionResultInfo.getSamIds()!=null && !inspectionResultInfo.getSamIds().isEmpty()){
            Criteria criteriaToProject = new Criteria().and(CollectionNameConstants.NAT_TUBE +"."+ModuleConstant.TubeModule.SAM_ID).in(inspectionResultInfo.getSamIds());
            AggregationOperation project_matchToLots = Aggregation.match(criteriaToProject);
            operations.add(project_matchToLots);
        }
        //检测时间查询条件
        if(!CollectionUtils.isEmpty(inspectionResultInfo.getSearchCheckTime())){
            DateFormat format= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String startTime = format.format(inspectionResultInfo.getSearchCheckTime().get(0));
            String endTime = format.format(inspectionResultInfo.getSearchCheckTime().get(1));
            Criteria criteriaToProject = new Criteria().and(CollectionNameConstants.NAT_TUBE +"."+ModuleConstant.TubeModule.CHECK_TIME).gte(startTime).lte(endTime);
            AggregationOperation project_matchToLots = Aggregation.match(criteriaToProject);
            operations.add(project_matchToLots);
        }

        Aggregation aggregation = Aggregation.newAggregation(operations);

        AggregationResults<NatAppoCheck> results = mongoTemplate.aggregate(aggregation,CollectionNameConstants.NAT_APPO_CHECK , NatAppoCheck.class);
        //多表管理只是为了条件
        //List<NatAppoCheck> list = mongoTemplate.aggregate(aggregation,CollectionNameConstants.NAT_APPO_CHECK , NatAppoCheck.class).getMappedResults();
        //整理查询结果
        Document document = results.getRawResults();
        //获取结果集
        Object o = document.get("results");
        List<Object> list = new ArrayList<>();

        for (Object item : (List<?>) o) {
            list.add(Object.class.cast(item));
        }
        list.stream().forEach(item -> {
            InspectionResultInfo info = new InspectionResultInfo();
            Map m = (Map)item;
            info.setId(m.get(NatAppoCheck.ID).toString());
            //受检人姓名
            info.setCheckedPersonName(m.get(NatAppoCheck.CHECKED_PERSON_NAME).toString());
            //受检人联系方式
            info.setPersonTelphone(m.get(NatAppoCheck.CHECKED_PERSON_TELEPHONE).toString());

            //检测结果
            info.setCheckResult(CheckResultEnum.getName(Integer.parseInt(m.get(NatAppoCheck.CHECK_RESULT).toString())));

            //-------------------试管表信息----------------------
            //通过定义好的从表别名获取数据
            Object tube = m.get(CollectionNameConstants.NAT_TUBE);

            List<Object> tubeList = new ArrayList<>();
            for (Object temp : (List<?>) tube) {
                tubeList.add(Object.class.cast(temp));
            }
            if(!tubeList.isEmpty()){
                Map tubeMap = (Map)tubeList.get(0);

                //检测点ID
                //检测点名称去redis通过检测点ID查询
                JSONObject pointBasic= redisCache.getCacheObject(RedisKey.CHECK_POINT_REDIS_KEY+tubeMap.get(ModuleConstant.TubeModule.CHECK_POINT_ID).toString());
                if(pointBasic!=null){
                   CheckPoint CheckPoint = JSONObject.parseObject(pointBasic.toJSONString(),CheckPoint.class);
                   if(CheckPoint!=null){
                       info.setCheckPointId(CheckPoint.getCheckPointName());
                   }
                }

                // 采样点id
                //采样点名称去redis通过采样点ID查询
                JSONObject samname= redisCache.getCacheObject(RedisKey.SAMPLEPOINTBASIC+tubeMap.get(ModuleConstant.TubeModule.SAM_ID).toString());
                if(samname!=null) {
                    NatInstSamplePoint natInstSamplePoint = JSONObject.parseObject(samname.toJSONString(),NatInstSamplePoint.class);
                    if(natInstSamplePoint!=null){
                        info.setSamId(natInstSamplePoint.getSamplePointName());
                    }
                }
                // 检测时间
                info.setCheckTime(tubeMap.get(ModuleConstant.TubeModule.CHECK_RESULT_TIME).toString());
                //检测人姓名
                info.setInstName(tubeMap.get(ModuleConstant.TubeModule.CHECK_USER_NAME).toString());
                //检测人联系方式  暂时存检测人ID 联系方式从redis获取
                info.setInstTelphone("");
                // 试管码
                info.setTubeCode(tubeMap.get(ModuleConstant.TubeModule.TUBE_CODE).toString());
            }
            infoList.add(info);
        });
        return infoList;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129