MongoDB 组合多个条件查询($and、$in、$gte、$lte)
一,问题描述
数据格式: 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
1 private static Bson timeBetweenCondition(long startTimeStamp, long endTimeStamp) { 2 Bson condition = Filters.and(Filters.gte("timeStamp", startTimeStamp), Filters.lt("timeStamp", endTimeStamp)); 3 return condition; 4 }
上面的示例,使用Filters的and方法连接了两个条件,其实还可以连接多个条件:比如查询 timeStamp 在[startTimeStamp, endTimeStamp) 区间内,并且 uid 在List<Long>uids 内的document
1 private static Bson multipleAndCondition(long startTimeStamp, long endTimeStamp, List<Long> uids) { 2 Bson condition = Filters.and(Filters.gte("timeStamp", startTimeStamp), Filters.lt("timeStamp", endTimeStamp), Filters.in("uid", uids)); 3 return condition; 4 }
Filters还组合了 IN 条件:比如,查询 uid 在[123, 456, 789]中,可用如下条件:
1 private static Bson uidInCondition(List<Long> uids) { 2 Bson condition = Filters.in("uid", uids); 3 return condition; 4 }
具体更多的条件查询,可查看Filters的源代码。关于Filters的更多的用法可参考:Filters的若干使用示例
四,MongoDB查询时,过滤某些字段
使用Document对象来指定需要查询或者排除的字段。0 表示不返回该字段的值,1 表示返回该字段的值。
1 //0 表示 不返回 该字段, 1 表示 返回该字段 2 private static Document fetchFields() { 3 Document fetchFields = new Document(); 4 fetchFields.put("_id", 0);//查询的结果 不返回 _id 字段 5 fetchFields.put("uid", 1);//返回 uid 字段 6 fetchFields.put("chat", 1);//返回 chat 字段 7 return fetchFields; 8 }
然后再将 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