最近工作需要查询mongoDB,结果被搞得头大,后来发现是时间的问题,原来mongo中默认存储的是UTC时间,不是东八区时间,而且供应方插入的时间更是离奇,有时需要加8小时,有时需要减8小时,搞得很混乱,特此记录一下mongo的查询方式,其实还是蛮简单的。
整体的学习,可以参考我另一篇文章 https://zhuanlan.zhihu.com/p/599590563
首先使用Navicat完成2个条件的写法查询
db.getCollection("3000.4001").find({
"head.eidSrc_S": "1.2.4.4.12.01",
"head.ts": {
"$gte": ISODate("2021-11-17 02:44:08Z"),
"$lte": ISODate("2021-11-17 02:44:10Z")
}
});
接下来完成java查询
import com.inspur.collection.utils.HiveUtil;
import com.inspur.collection.utils.MongoUtil;
import com.mongodb.MongoClient;
import com.mongodb.client.MongoDatabase;
import org.apache.hadoop.conf.Configuration;
import org.apache.hadoop.fs.FileSystem;
import java.net.URI;
import java.sql.Connection;
/**
* 根据数据库类型分发各自连接
*
* @author yuchenghao
*/
public class UniversalInstance {
/**
* 获取 mongodb 连接
*
* @param ip
* @param port
* @param dbName
* @param username
* @param password
* @return
*/
public static MongoClient getMongodb(String ip, String port, String dbName, String username, String password) {
return MongoUtil.getClient(ip,Integer.parseInt(port),username,password,dbName);
}
public static MongoClient getMongodb(String ip, String port) {
return new MongoClient(ip, Integer.parseInt(port));
}
/**
* 获取hive连接
*
* @param ip
* @param port
* @param dbName
* @param username
* @param password
* @return
*/
public static Connection getHiveConnection(String ip, String port, String dbName, String username, String password) {
return HiveUtil.getConnection(ip, port, dbName, username, password);
}
/**
* 获取 hdfs 的连接
*
* @param ip
* @param port
* @return
*/
public static FileSystem getFileSystem(String ip, String port, String user) {
FileSystem fs = null;
try {
String uri = "hdfs://" + ip + ":" + port;
fs = FileSystem.newInstance(new URI(uri),new Configuration(),user);
}catch (Exception e){
e.printStackTrace();
}
return fs;
}
}
import cn.hutool.json.JSONUtil;
import com.inspur.collection.utils.DateUtil;
import com.inspur.collection.work.UniversalInstance;
import com.mongodb.BasicDBObject;
import com.mongodb.MongoClient;
import com.mongodb.client.FindIterable;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoCursor;
import com.mongodb.client.MongoDatabase;
import lombok.extern.slf4j.Slf4j;
import org.bson.Document;
import org.junit.Test;
import java.util.Date;
@Slf4j
public class MongoDbTest {
@Test
public void queryMongoDB(){
MongoClient mongoClient = null;
try {
//获取mongodb的客户端
mongoClient = UniversalInstance.getMongodb("172.20.0.XXX", "27017","admin","fly", "123456");
//获取dataBase
String database = "2021_11";
MongoDatabase mongodb = mongoClient.getDatabase(database);
String sourceName = "3000.4001";
MongoCollection<Document> collection = mongodb.getCollection(sourceName);
/**
* db.getCollection("3000.4001").find({
* "head.eidSrc_S": "1.2.4.4.12.01",
* "head.ts":{"$gte":ISODate("2021-11-17 02:44:08Z"),"$lte":ISODate("2021-11-17 02:44:10Z")}
* });
*/
//时区不同,需要置换
Date date1 = DateUtil.parse("2021-11-17 10:44:08", "yyyy-MM-dd HH:mm:ss");
System.out.println("query date1 " + date1.getTime());
Date date2 = DateUtil.parse("2021-11-17 10:44:10", "yyyy-MM-dd HH:mm:ss");
System.out.println("query date2 " + date2.getTime());
BasicDBObject query = new BasicDBObject();
query.put("head.ts", new BasicDBObject("$gte", date1).append("$lt", date2));
query.put("head.eidSrc_S","1.2.4.4.12.01");
FindIterable<Document> documents = collection.find(query);
MongoCursor<Document> iterator = documents.iterator();
while (iterator.hasNext()) {
Document next = iterator.next();
Document head = next.get("head", Document.class);
System.out.println(JSONUtil.toJsonStr(head));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (mongoClient != null) {
mongoClient.close();
}
}
}
}
以上最麻烦的就是时区的处理,因为你不知道会存储什么时区的数据。导致很多程序需要根据实际数据情况做出改动,这是最初架构设计上的失误,以此为鉴,后续数据库设计必须增加时区要求。