本文以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。
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());
}
}
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());
}
}
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条结果
}
}