java mongodb 查询表_Java实现对mongoDB的两表关联查询

Java实现对mongoDB的两表关联查询

记录一次学习java实现mongodb的两表关联查询的过程，方便日后需要用到的时候进行回顾。

场景：mongodb中有两张表，需要根据id进行关联查询。

表1数据如下：

表二数据如下：

实现两张表的关联查询，需要用到mongodb的lookup，在查询结果返回的时候，需要将没有结果集为空的数据过滤掉，此时要用到mongodb的match。

java实现需要用到mongo-java-driver包，这里使用mongo-java-driver-3.9.0.jar。阿里的maven镜像依赖如下

org.mongodb

mongo-java-driver

3.9.0

Java代码实现如下：

MongoClient mongoClient = new MongoClient("localhost", 27017);

//“test”为连接的数据库

MongoDatabase mongoDatabase = mongoClient.getDatabase("test");

//test1是表1

MongoCollection mongoCollection = mongoDatabase.getCollection("test1");

List aggregateList = new ArrayList<>(1);

//利用lookup进行关联查询

//test2是表2，两个表根据id进行关联，关联的结果定义一个新的字段

/**Aggregates.lookup函数的说明：

* lookup(final String from, final String localField, final String foreignField, final String as)

* Creates a $lookup pipeline stage, joining the current collection with the one specified in from

* using equality match between the local field and the foreign field

* @param from the name of the collection in the same database to perform the join with.

* @param localField the field from the local collection to match values against.

* @param foreignField the field in the from collection to match values against.

* @param as the name of the new array field to add to the input documents.

*/

aggregateList.add(Aggregates.lookup("test2", "id", "id", "result"));

//利用match将没有关联到的结果过滤掉

aggregateList.add(Aggregates.match(Filters.ne("result", new ArrayList(0))));

AggregateIterable aggregateIterable = mongoCollection.aggregate(aggregateList);

for (Document document : aggregateIterable) {

System.out.println(document.toJson());

}

返回结果如下：

{ "_id" : { "oid" : "5e92c58671f9147a73e03662" }, "id" : "aaa", "keys" : ["name", "mobile"], "values" : ["姓名", "手机"], "result" : [{ "_id" : { "oid" : "5e92c48571f9147a73e0365f" }, "id" : "aaa", "name" : "张三", "mobile" : "12345678987" }, { "_id" : { "oid" : "5e92d5e871f9147a73e03667" }, "id" : "aaa", "name" : "李四", "mobile" : "12345678987" }] }

{ "_id" : { "oid" : "5e92c64e71f9147a73e03664" }, "id" : "ccc", "keys" : ["imsi", "mobile"], "values" : ["IMSI", "手机"], "result" : [{ "_id" : { "oid" : "5e92c73f71f9147a73e03665" }, "id" : "ccc", "imsi" : "28834765932", "mobile" : "12345678987" }] }

整个实现过程需要对mongodb中“管道”的概念有一定的理解。比如在这个场景中，首先通过lookup将关联上的结果查询出来，再通过“管道”将结果集交给match进行过滤。