Java通过mongo-java-driver-3.0+分页查询mongodb详解

比眉伴天荒 2022-06-06 07:09 236阅读 0赞

本文以mongo-java-driver-3.5.0.jar为例

**1 mongodb查询语句查询：**

db.user.find().skip(1).limit(2) 与 db.user.find().limit(2).skip(1) 查询结果一样，都是先执行skip再limit，如果有sort排序，无论位置先后，最先执行顺序 sort再skip再limit。  
![这里写图片描述][SouthEast]

**2 两种查询方法：**

2.1find((Bson) filter).limit(1).skip(1) 与 find((Bson) filter).skip(1).limit(1)结果一样  
注：先执行skip再limit，所以结果有一条

public static MongoCollection<Document> connect() {  
        @SuppressWarnings("resource")  
        MongoClient client = new MongoClient("127.0.0.1", 27017);  
        MongoDatabase db = client.getDatabase("test");  
        MongoCollection<Document> collection = db.getCollection("user");  
        return collection;
    } 
    
    @Test  
    public void testLimitSkip1(){  
        int iid = 3, iid2 = 4;
        String name = "";
        String nickname = "";
        DBObject filter = new BasicDBObject();  
        if (iid >= 0) {
            filter.put("iid", new BasicDBObject("$gte", iid));          
        }
        // 得到iid<=4结果；
        // iid>=3条件被覆盖，Document.append不适合在同一个结果添加多个条件
        if (iid2 >= 0) {
            if (iid >= 0) {
                BasicDBObject[] queryObjectAnd =  new BasicDBObject[] { new BasicDBObject("iid", new BasicDBObject("$gte", iid)),  
                        new BasicDBObject("iid", new BasicDBObject("$lte", iid2)) };
                filter.put("$and", queryObjectAnd);
    } else {
    filter.put("iid",  new BasicDBObject("$lte", iid2)); 
    }
    
        }
        if (name != "") {
            filter.put("name",new BasicDBObject("$regex", name));
        }
        if (nickname != "") {
            filter.put("nickname",nickname);  
        }
    
        List<Document> results = new ArrayList<Document>();  
        // FindIterable<Document> iterables = connect().find((Bson) filter).skip(1).limit(1); 
        FindIterable<Document> iterables = connect().find((Bson) filter).limit(1).skip(1);  
        MongoCursor<Document> cursor = iterables.iterator();  
        while (cursor.hasNext()) {  
            results.add(cursor.next());  
        }   
        for(Document doc : results){  
            System.out.println(doc.toJson());  
        } 
    }

![这里写图片描述][SouthEast 1]

![这里写图片描述][SouthEast 2]

2.2collection.aggregate(List<\\Bson> filter)

2.2.1filter先加skip，后加limit  
注：先执行skip再limit，所以该结果有一条

public static MongoCollection<Document> connect() {  
        @SuppressWarnings("resource")  
        MongoClient client = new MongoClient("127.0.0.1", 27017);  
        MongoDatabase db = client.getDatabase("test");  
        MongoCollection<Document> collection = db.getCollection("user");  
        return collection;
    } 
    
    @Test  
    public void testSkipLimit(){  
        int iid = 3, iid2 = 4;
        String name = "";
        String nickname = "";
        DBObject filter = new BasicDBObject();  
        if (iid >= 0) {
            filter.put("iid", new BasicDBObject("$gte", iid));          
        }
        // 得到iid<=4结果；
        // iid>=3条件被覆盖，Document.append不适合在同一个结果添加多个条件
        if (iid2 >= 0) {
            if (iid >= 0) {
                BasicDBObject[] queryObjectAnd =  new BasicDBObject[] { new BasicDBObject("iid", new BasicDBObject("$gte", iid)),  
                        new BasicDBObject("iid", new BasicDBObject("$lte", iid2)) };
                filter.put("$and", queryObjectAnd);
    } else {
    filter.put("iid",  new BasicDBObject("$lte", iid2)); 
    }
    
        }
        if (name != "") {
            filter.put("name",new BasicDBObject("$regex", name));
        }
        if (nickname != "") {
            filter.put("nickname",nickname);  
        }
        DBObject matchBasicDBObjet = new BasicDBObject("$match", filter);
    // DBObject sortBasicDBObjet = new BasicDBObject("$sort", new BasicDBObject("CreateTime",-1)); 
        DBObject skipBasicDBObjet = new BasicDBObject("$skip", 1);
    DBObject limitBasicDBObjet = new BasicDBObject("$limit", 1); 
        List<Bson> listBsons = new ArrayList<Bson>();
        listBsons.add((Bson) matchBasicDBObjet);
        // listBsons.add((Bson) sortBasicDBObjet);
        // 先加skip，后加limit
        listBsons.add((Bson) skipBasicDBObjet);
        listBsons.add((Bson) limitBasicDBObjet);
    
        List<Document> results = new ArrayList<Document>();  
        AggregateIterable<Document> iterables = connect().aggregate(listBsons); 
        MongoCursor<Document> cursor = iterables.iterator();  
        while (cursor.hasNext()) {  
            results.add(cursor.next());  
        }  
        for(Document doc : results){  
            System.out.println(doc.toJson());  
        } 
    }

![这里写图片描述][SouthEast 3]

2.2.1filter先加limit，后加skip  
注：先执行limit再skip，所以该结果有0条

public static MongoCollection<Document> connect() {  
        @SuppressWarnings("resource")  
        MongoClient client = new MongoClient("127.0.0.1", 27017);  
        MongoDatabase db = client.getDatabase("test");  
        MongoCollection<Document> collection = db.getCollection("user");  
        return collection;
    } 
    
    @Test  
    public void testLimitSkip(){  
        int iid = 3, iid2 = 4;
        String name = "";
        String nickname = "";
        DBObject filter = new BasicDBObject();  
        if (iid >= 0) {
            filter.put("iid", new BasicDBObject("$gte", iid));          
        }
        // 得到iid<=4结果；
        // iid>=3条件被覆盖，Document.append不适合在同一个结果添加多个条件
        if (iid2 >= 0) {
            if (iid >= 0) {
                BasicDBObject[] queryObjectAnd =  new BasicDBObject[] { new BasicDBObject("iid", new BasicDBObject("$gte", iid)),  
                        new BasicDBObject("iid", new BasicDBObject("$lte", iid2)) };
                filter.put("$and", queryObjectAnd);
    } else {
    filter.put("iid",  new BasicDBObject("$lte", iid2)); 
    }
    
        }
        if (name != "") {
            filter.put("name",new BasicDBObject("$regex", name));
        }
        if (nickname != "") {
            filter.put("nickname",nickname);  
        }
        DBObject matchBasicDBObjet = new BasicDBObject("$match", filter);
    // DBObject sortBasicDBObjet = new BasicDBObject("$sort", new BasicDBObject("CreateTime",-1)); 
        DBObject skipBasicDBObjet = new BasicDBObject("$skip", 1);
    DBObject limitBasicDBObjet = new BasicDBObject("$limit", 2); 
        List<Bson> listBsons = new ArrayList<Bson>();
        listBsons.add((Bson) matchBasicDBObjet);
        // listBsons.add((Bson) sortBasicDBObjet);
        // 先加limit，后加skip
        listBsons.add((Bson) limitBasicDBObjet);
        listBsons.add((Bson) skipBasicDBObjet);
    
        List<Document> results = new ArrayList<Document>();  
        AggregateIterable<Document> iterables = connect().aggregate(listBsons); 
        MongoCursor<Document> cursor = iterables.iterator();  
        while (cursor.hasNext()) {  
            results.add(cursor.next());  
        }  
        for(Document doc : results){  
            System.out.println(doc.toJson());  // 0条结果
        } 
    }

[SouthEast]: /images/20220606/49a5f1f012c142929c093189f4c45dee.png
[SouthEast 1]: /images/20220606/6172894a6ebd4dc99ad5db7982b5d7d2.png
[SouthEast 2]: /images/20220606/01742fd167cc4dfb8220558f5985e8dd.png
[SouthEast 3]: /images/20220606/a6afc8e1ee1942e68f67156efea6696b.png