MongoDB 组合多个条件查询（$and、$in、$gte、$lte）

2023-07-17 12:32:46

一，问题描述

数据格式： id, timeStamp,count

条件1：查询某个时间段内的数据： timeStamp BETWEEN startTime AND endTime。比如 timeStamp BETWEEN 1499875200 AND 1499875600

条件2：记录的 id 字段属于某集合： id IN (1001,1002,1003)

条件3：记录的count字段大于某个阈值，比如： count > 10000

二，代码实现

条件1实现如下， $gte （greater equal）表示“大于等于 ”。【$lte（lower equal）】表示“小于等于”

    private DBObject timeCondi(long startTimeStamp, long endTimeStamp){

        DBObject queryCondition = new BasicDBObject("$gte", startTimeStamp).append("$lte", endTimeStamp);

        BasicDBObject query = new BasicDBObject("timeStamp", queryCondition);

        return query;

    }

条件2的实现如下，使用 $in ArrayList<Integer> 来表示 id IN (1001,1002,1003)

    private DBObject inCondi() {

        List<Integer> idList = new ArrayList<Integer>();

        idList.add(1001);

        idList.add(1002);

        idList.add(1003);

        BasicDBObject inQuery = new BasicDBObject();

        inQuery.put("id", new BasicDBObject("$in", idList));

        return inQuery;

    }

组合条件1 和条件2 查询：（条件1 AND 条件2）.List 将所有 AND 组合的条件add进去，然后使用 $and 组合。

    private DBObject andQuery(long startTimeStamp, long endTimeStamp) {

        DBObject timeCondi = timeCondi(startTimeStamp, endTimeStamp);

        DBObject inCondi = inCondi();

        List<DBObject> andCondi = new ArrayList<DBObject>();

        andCondi.add(timeCondi);//组合 条件1

        andCondi.add(inCondi);//组合 条件2

        BasicDBObject andQuery = new BasicDBObject();

        andQuery.put("$and", andCondi);

        return andQuery;

    }

条件3：记录的count字段大于某个阈值，比如： count > 10000

    private DBObject countCondi(int size) {

        DBObject queryCondition = new BasicDBObject("$gte", size);

        BasicDBObject query = new BasicDBObject("count", queryCondition);

        return query;

    }

WINDOWS下安装好MongoDB后，MongoDB采用Clietn--Server 模式。WIN10下启动MongoDB服务端：-dbpath 指定数据存储目录(可自己指定一个)

D:\Program Files\MongoDB\Server\3.4\bin>.\mongod.exe -dbpath ..\data

另起一个 Cmd命令行，输入：

D:\Program Files\MongoDB\Server\3.4\bin>.\mongo.exe

就可以以Shell方式操作MongoDB了。

输入 exit 退出

D:\Program Files\MongoDB\Server\3.4\bin>netstat -ano | findstr 27017 查看端口是否开启。

另外，推荐一个MongoDB的可视化工具：RoboMongoDB。执行相应的查询语句如下：

三，新版本MongoDB(3.0版本以上)，使用Filters来组合多个条件

查询 timeStamp 在[startTimeStamp, endTimeStamp) 内的所有document

     private static Bson timeBetweenCondition(long startTimeStamp, long endTimeStamp) {

         Bson condition = Filters.and(Filters.gte("timeStamp", startTimeStamp), Filters.lt("timeStamp", endTimeStamp));

         return condition;

     }

上面的示例，使用Filters的and方法连接了两个条件，其实还可以连接多个条件：比如查询 timeStamp 在[startTimeStamp, endTimeStamp) 区间内，并且 uid 在List<Long>uids 内的document

     private static Bson multipleAndCondition(long startTimeStamp, long endTimeStamp, List<Long> uids) {

         Bson condition = Filters.and(Filters.gte("timeStamp", startTimeStamp), Filters.lt("timeStamp", endTimeStamp), Filters.in("uid", uids));

         return condition;

     }

Filters还组合了 IN 条件：比如，查询 uid 在[123, 456, 789]中，可用如下条件：

     private static Bson uidInCondition(List<Long> uids) {

         Bson condition = Filters.in("uid", uids);

         return condition;

     }

具体更多的条件查询，可查看Filters的源代码。关于Filters的更多的用法可参考：Filters的若干使用示例

四，MongoDB查询时，过滤某些字段

使用Document对象来指定需要查询或者排除的字段。0 表示不返回该字段的值，1 表示返回该字段的值。

     //0 表示 不返回 该字段, 1 表示 返回该字段

     private static Document fetchFields() {

         Document fetchFields = new Document();

         fetchFields.put("_id", 0);//查询的结果 不返回 _id 字段

         fetchFields.put("uid", 1);//返回 uid 字段

         fetchFields.put("chat", 1);//返回 chat 字段

         return fetchFields;

     }

然后再将 fetchFields 对象传入 project()方法即可，如下：

collection.find(timeBetweenCondition(startTimeStamp, endTimeStamp)).projection(fetchFields())

整个完整示例参考：

import com.mongodb.MongoClient;

import com.mongodb.client.MongoCollection;

import com.mongodb.client.MongoCursor;

import com.mongodb.client.MongoDatabase;

import com.mongodb.client.model.Filters;

import org.bson.Document;

import org.bson.conversions.Bson;

import java.time.LocalDateTime;

import java.time.ZoneId;

import java.util.Arrays;

import java.util.List;

//import static com.mongodb.client.model.Filters.*;

/**

 * Created by Administrator on 2017/10/20.

 */

public class MongoUtil {

    private static final String HOST = "localhost";

    private static final int PORT = 27017;

    private static final String DB_NAME = "test";

    private static final MongoClient mongoClient = new MongoClient(HOST, PORT);

    private static MongoDatabase getDataBase() {

        return mongoClient.getDatabase(DB_NAME);

    }

    private static MongoCollection<Document> getCollection(String collName) {

        return getDataBase().getCollection(collName);

    }

    private static Bson timeBetweenCondition(long startTimeStamp, long endTimeStamp) {

        Bson condition = Filters.and(Filters.gte("timeStamp", startTimeStamp), Filters.lt("timeStamp", endTimeStamp));

        return condition;

    }

    private static Bson uidInCondition(List<Long> uids) {

        Bson condition = Filters.in("uid", uids);

        return condition;

    }

    private static Bson multipleAndCondition(long startTimeStamp, long endTimeStamp, List<Long> uids) {

        Bson condition = Filters.and(Filters.gte("timeStamp", startTimeStamp), Filters.lt("timeStamp", endTimeStamp), Filters.in("uid", uids));

        return condition;

    }

    //0 表示 不抽取该字段, 1 表示 抽取该字段

    private static Document fetchFields() {

        Document fetchFields = new Document();

        fetchFields.put("_id", 0);//查询的结果 不返回 _id 字段

        fetchFields.put("uid", 1);//返回 uid 字段

        fetchFields.put("chat", 1);//返回 chat 字段

        return fetchFields;

    }

    public static void main(String[] args) {

        MongoCollection collection = getCollection("mycollection_name");

        LocalDateTime today = LocalDateTime.of(2017, 8, 29, 0, 0, 0);

        LocalDateTime tomorrow = today.plusDays(1);

        long startTimeStamp = today.atZone(ZoneId.systemDefault()).toEpochSecond();

        long endTimeStamp = tomorrow.atZone(ZoneId.systemDefault()).toEpochSecond();

        Bson andCondition = multipleAndCondition(startTimeStamp, endTimeStamp, Arrays.asList(65496L));

//        Bson andCondition = timeBetweenCondition(startTimeStamp, endTimeStamp);

        Document fetchFields = fetchFields();
 MongoCursor<Document> cursor = collection.find(andCondition).projection(fetchFields).iterator();

        long count = collection.count(andCondition);

        System.out.println(count);

        while (cursor.hasNext()) {

            Document document = cursor.next();

            String chat = (String) document.get("chat");

            Long uid = (Long) document.get("uid");

            System.out.println("chat:" + chat + ", uid:" + uid);

        }

    }

}

五：参考文献

https://www.mkyong.com/mongodb/java-mongodb-query-document/

http://farenda.com/mongodb/mongodb-filtering-sorting-and-projections/

原文：http://www.cnblogs.com/hapjin/p/7450309.html

码农公寓

相关文章