MongoDB多表联查

Oracle数据库： select * from (select t1.name,t2.name from student t1, class t2 where t1.id=t2.id) F where 1=1;

MongoDB: 什么鬼？

非关系型数据库多表联查让人崩溃

@Autowired
    private MongoTemplate mongoTemplate;

@RequestMapping(value = "/queryByMap.html", method = RequestMethod.GET)
    public Map<String, Object> queryByMap() {
        //定义分组字段
        String[] groupIds = new String[] {"$_id","$name","$msg","$itemList",
                "$instanceItem.elementName",
                "$instanceItem"};
        //定义查询条件
        Criteria criteria = new Criteria();
        //相当于where username = "zhangsan"
        criteria.and("name").is("name0");
        //相当于 where age not in("15","20")
        //criteria.and("age").nin("15","20");
        criteria.and("msg").is("message0");
        //in操作对应的语句
        //criteria.and("").in();
        //定义排序条件
        List<Sort.Order> orders = new ArrayList();
        orders.add(new Sort.Order(Sort.Direction.DESC, "name"));
        Sort sort = Sort.by(orders);

        //联合查询条件
        Aggregation newAggregation = Aggregation.newAggregation(
                Aggregation.lookup("UDT_TestInstanceItem","name","elementName","instanceItem"),
                //从表名，主表联接字段，从表联接字段，别名

                Aggregation.unwind("$itemList"),
                Aggregation.match(criteria),
                Aggregation.group(groupIds)
                        .last("$name").as("名称")//取值，起别名
                        .first("$instanceItem.elementName").as("父项名称")
                        .first("$instanceItem.msg").as("子项msg")
                        .first("$instanceItem.name").as("子项名称"),
                Aggregation.sort(sort),
                Aggregation.skip(0),//Long类型的参数
                Aggregation.limit(100)
        );

        //查询
        AggregationResults<BasicDBObject> aggregate = mongoTemplate.aggregate(
                newAggregation ,"UDT_TestInstance",BasicDBObject.class //A表，是查询的主表
        );
        int count = mongoTemplate.aggregate(newAggregation ,"UDT_TestInstance",BasicDBObject.class).getMappedResults().size();

        Map<String, Object> result = new HashMap<>();
        result.put("result", aggregate.getMappedResults());
        result.put("count", count);
        return result;
    }

 上面的多表联查方法看起来有点繁琐，而且我在生产环境中运用的时候总是查不出数数据(demo可以查出数据)，所以用了下面的方法：

 

LookupOperation lookupOperation=LookupOperation.newLookup().
                    from("DYNC_EXT_TestInstanceItem").  //关联从表名
                    localField("partVersion").     //主表关联字段
                    foreignField("partVersion").//从表关联的字段
                    as("result");   //查询结果名
AggregationOperation match = Aggregation.match(criteria);
Aggregation aggregation=Aggregation.newAggregation(match, lookupOperation); //多条件
List<Map> results = mongoTemplate.aggregate(aggregation,"DYNC_EXT_TestInstance", Map.class).getMappedResults();
//上面的DYNC_EXT_TestInstance必须是查询的主表名
System.out.println(JSON.toJSONString(results));

这种写法阅读起来简单，有效适合小白

还有一种方法是利用Java中的集合，分别查出多张表的数据存在List集合中，在通过循环比较得出符合的数据。