Docker里安装MongoDB/创建用户/登录
mkdir -p /docker_volume/mongodb/data
docker run --name mongo -d -p 27017:27017 --net host -v /docker_volume/mongodb/data:/data/db mongo:5.0 --auth
docker exec -it mongo mongo admin
db.createUser({ user:'test',pwd:'test',roles:[ { role:'userAdminAnyDatabase', db: 'admin'},'readWriteAnyDatabase']})
exit
docker exec -it mongo mongo -u test -p test命令行操作
show dbs 显示所有数据库
db 查看当前数据库
use mydb 切换到数据库,如果不存在就会创建,并没有真正生成对应的数据文件，如果此时退出，此数据库将被删除，只有在此数据库中创建集合后，才会真正生成数据文件
show collections 或使用show tables 查看当前数据库中所有的集合
db.createCollection(name) 显示创建集合
db.getCollectionNames() 集合列表
db.getCollectionInfos(filter,nameOnly,authorizedCollections) 集合详细信息
filter 过滤器，比如{name:"clients"},可选
nameOnly 只显示名字,默认false,可选
authorizedCollections 与 nameOnly: true 一起使用时，authorizedCollections 参数允许用户在没有所需权限的情况下应用访问控制来运行命令
db.dropDatabase() 删除当前数据库
db.example.insertOne({}) 向集合example插入一条数据，如果集合不存在，自动创建集合
db.example.insertMany( [{},...,{}] ) 向集合example插入多条数据，如果集合不存在，自动创建集合
db.example.find(conditions) 查询数据，并格式化显示
db.example.findOne(conditions) 查询数据，返回一条
db.example.remove(conditions) 删除数据
conditions为查询条件，可选
{ "Item": "Pen" }
字段Item=Pen
{"Inventory": {"OnHand": 47,"MinOnHand": 50 }
字段Inventory={"OnHand": 47,"MinOnHand": 50 },如果Inventory有其他字段则不匹配
{"Inventory.OnHand": 47, "Inventory.MinOnHand": 50 }
字段Inventory={"OnHand": 47,"MinOnHand": 50 },如果Inventory有其他字段也会匹配
{"Colors": ["Red","Green","Blue","Black"]}
数组需要完全匹配，少值或者顺序不一致都不匹配
{ "Colors": "Red" }
如果Colors的值是数组并且数组内包含"Red"，也会匹配
{ "Inventory.OnHand": { $lt: 50 } }
匹配值<50，其他比较方法：$eq,$gt,$gte,$lt,$lte,$ne,$in,$nin
{$or:[{name:"user2"},{age:3}]}
满足任意条件，其他逻辑：$and,$not,$nor
{"name":{$exists:1}}
存在字段name。$exists:0表示不存在
{$text:{$search:"runoob"}}
使用全文索引查询
db.example.update(conditions,modify,option) 更新数据
db.example.updateOne(conditions,modify,option) 更新一条数据
db.example.updateMany(conditions,modify,option) 更新多条数据
db.example.replaceOne(conditions,new) 替换数据
modify
{ "name": "abcd"}
如果不包含操作符，则整个对象更新为新对象的值，_id保持不变
{ $set: { "Date.Year": 1987, "Date.DoW": "Saturday","Words": 2482 } }
更新字段值或者新增字段
{ $unset: { Words:1 }
删除字段
{ $inc:{age:2} }
字段做加法
{ $min:{lowScore : 10} }
如果lowScore>10,则更新成10，否则不更新
{ $max:{lowScore : 10} }
如果lowScore<10,则更新成10，否则不更新
{ $currentDate: {birthday: {$type: "date"}} }
设置birthday的类型为date，值为当前时间，类型可以为date或者timestamp
{ $push : {scores: 91}},{ $push: { scores: { $each: [ 70, 78], $slice: 0 } } }
{ $push: { quizzes: { $each: [ { id: 3, score: 8 }, { id: 4, score: 7 }, { id: 5, score: 6 } ], $sort: { score: 1 } } }
向数组字段添加元素
{ $addToSet: {letters: "c" ] }},{ $addToSet: { scores: { $each: [ 100, 20, 50 ] } } }
向数组字段添加元素，重复值不会插入
{ $pop: {scores: -1}},{$pop: {scores: 1}}
从数组删除元素,-1表示删除第一个，1表示删除最后一个
{ $pull: {fruits: {$in : ["apples", "oranges"]}, vegetables: "carrots" }}
从数组fruits中删除"apples" 和 "oranges"，从vegetables数组中删除"carrots"
{ $pull: {results: { score : 8, item : "B" }} }
删除数组元素中包含值为8的score字段和值为"B"的item字段
{ $pull: { results: { answers: { $elemMatch: { q: 2, a: { $gte: 8 } } } } } }
删除results数组元素，其满足ansmers字段至少包含一个q值为2以及a大于等于8
{ $pullAll: {scores: [67, 54]}}
从scores数组中删除分值为"67"和"54"的成绩
option
{upsert:true}
如果不存在则插入数据
{ multi: true }
update方法默认只更新一条，可以使用multi指定更新多条
db.collection.createIndex(keys, options) 创建索引
keys
{"title":1,"description":-1}
title升序，description降序
{"post_text":"text"}
post_text字段创建全文索引
options
background:Boolean
建索引过程会阻塞其它数据库操作，background可指定以后台方式创建索引，即增加 "background" 可选参数。
DocumentDB和MongoDB 4.0默认true；MongoDB 4.2之后忽略此参数。
unique:Boolean
建立的索引是否唯一。指定为true创建唯一索引。默认值为false.
name:string
索引的名称。如果未指定，MongoDB的通过连接索引的字段名和排序顺序生成一个索引名称。
sparse:Boolean
对文档中不存在的字段数据不启用索引；这个参数需要特别注意，如果设置为true的话，在索引字段中不会查询出不包含对应字段的文档.。默认值为 false.
expireAfterSeconds:Integer
指定一个以秒为单位的数值，完成 TTL设定，设定集合的生存时间。
db.log_events.createIndex( { "createdAt": 1 }, { expireAfterSeconds: 3600 } )
当前时间>=createAt字段的值+3600秒时，数据过期删除
v
索引的版本号。默认的索引版本取决于mongod创建索引时运行的版本。
weights
索引权重值，数值在 1 到 99,999 之间，表示该索引相对于其他索引字段的得分权重。
default_language
对于文本索引，该参数决定了停用词及词干和词器的规则的列表。 默认为英语
language_override
对于文本索引，该参数指定了包含在文档中的字段名，语言覆盖默认的language，默认值为 language.
db.col.getIndexes() 查看集合索引
db.col.totalIndexSize() 查看集合索引大小
db.col.dropIndexes() 删除集合所有索引
db.col.dropIndex("索引名称") 删除集合指定索引
find().pretty() 对结果格式化，可选
find().count() 取得件数
find().sort({age:1}) 排序,列出排序字段，1表示升序,-1表示降序
find().skip(n) 跳过前n条记录
find().limit(n) 只显示n条记录
db.collection.aggregate(pipeline, options)
聚合处理,pipeline为管道处理，可以有多个管道，顺次执行
pipeline
{ $match: { $or: [ { score: { $gt: 70, $lt: 90 } }, { views: { $gte: 1000 } } ] } }
条件过滤, (score>70 and score<90) or views >=1000
{ $group : { _id : { $dateToString: { format: "%Y-%m-%d", date: "$date" }}, totalSaleAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } }, averageQuantity: { $avg: "$quantity" }, count: { $sum: 1 } } }
使用date字段按日分组，聚合字段:totalSaleAmount,averageQuantity,count
可用聚合
$avg,$max,$min,$sum 平均值,最大,最小,合计
$first,$last 第一个,最后一个
$addToSet,$push,$mergeObjects 合并成Set,合并成数组,合并对象
$stdDevPop,$stdDevSamp 标准偏差,样本标准偏差
{ $group : { _id : null, totalSaleAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } }, averageQuantity: { $avg: "$quantity" }, count: { $sum: 1 } } }
分组字段为null表示全体聚合
{ $group : { _id : "$author", books: { $push: "$title" } } }
分组字段为author，把title字段聚合为数组
{ $group : { _id : { month: { $month: "$date" }, day: { $dayOfMonth: "$date" }, year: { $year: "$date" } }, books: { $push: "$title" } } }
分组字段可为多个
{ $sort : { age : -1, posts: 1 } }
排序：age降序,posts升序
{ $limit : 5 }
限制输出条数
JAVA使用MongoClient
package com.example.demo.mongo;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.time.temporal.ChronoUnit;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.List;
import java.util.TimeZone;
import java.util.concurrent.TimeUnit;
import org.bson.Document;
import org.bson.conversions.Bson;
import com.mongodb.ConnectionString;
import com.mongodb.MongoClientSettings;
import com.mongodb.client.AggregateIterable;
import com.mongodb.client.FindIterable;
import com.mongodb.client.MongoClient;
import com.mongodb.client.MongoClients;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoCursor;
import com.mongodb.client.MongoDatabase;
import com.mongodb.client.model.Accumulators;
import com.mongodb.client.model.Aggregates;
import com.mongodb.client.model.BsonField;
import com.mongodb.client.model.CreateCollectionOptions;
import com.mongodb.client.model.Filters;
import com.mongodb.client.model.IndexOptions;
import com.mongodb.client.model.Indexes;
import com.mongodb.client.model.Projections;
import com.mongodb.client.model.Sorts;
import com.mongodb.client.model.TimeSeriesGranularity;
import com.mongodb.client.model.TimeSeriesOptions;
public class StudyMongoClient {
private static MongoClient mongoClient;
static {
try {
// 连接到MongoDB服务 如果是远程连接可以替换“localhost”为服务器所在IP地址
String url = "mongodb://testRoot:123456@localhost:27018";
MongoClientSettings settings = MongoClientSettings.builder()
.applyConnectionString(new ConnectionString(url))
.applyToSocketSettings(builder-> {
// 客户端向MongoDB实例发出连接请求的最长时间
builder.connectTimeout(5, TimeUnit.SECONDS);
// 客户端在接收响应之前可以逗留在服务器上的时间
builder.readTimeout(10, TimeUnit.SECONDS);
})
.applyToClusterSettings(builder-> {
// 如果连接地址写错,这个超时起作用
builder.serverSelectionTimeout(5, TimeUnit.SECONDS);
})
.build();
mongoClient = MongoClients.create(settings);
System.out.println("Connect to database successfully");
} catch (Exception e) {
System.err.println(e.getClass().getName() + ": " + e.getMessage());
}
}
/**
* 开始测试
*
* @param args
*/
public static void main(String[] args) {
TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
String dbName = "0751ac0e-53b2-4c69-8828-01441e3df593";
String collectionName = "cat1/cat2";
// 删除数据
// delete(dbName, collectionName);
// 删除集合
dropCollection(dbName, collectionName);
// 删除数据库
dropDatabase(dbName);
// // 创建集合
// createCollection(dbName, collectionName);
// // 创建集合数据过期索引
// createIndexForTTL(dbName, collectionName, "createdAt", 1L, TimeUnit.HOURS);
// 创建时间序列集合
createTimeSeriesCollection(dbName, collectionName, 1L, TimeUnit.HOURS);
System.out.println("集合列表:" + showCollection(dbName));
insert(dbName, collectionName);
// update(dbName, collectionName);
query(dbName, collectionName);
}
/**
* 删除数据库
*/
public static void dropDatabase(String dbName) {
// 连接到数据库
MongoDatabase mongoDatabase = mongoClient.getDatabase(dbName);
System.out.println("连接到数据库");
mongoDatabase.drop();
System.out.println("删除数据库完了");
}
/**
* 集合列表
*/
public static List<String> showCollection(String dbName) {
// 连接到数据库
MongoDatabase mongoDatabase = mongoClient.getDatabase(dbName);
System.out.println("连接到数据库");
List<String> nameList = new ArrayList<>();
mongoDatabase.listCollectionNames().forEach((name)-> nameList.add(name));
return nameList;
}
/**
* 创建集合
*/
public static void createCollection(String dbName, String collectionName) {
// 连接到数据库
MongoDatabase mongoDatabase = mongoClient.getDatabase(dbName);
System.out.println("连接到数据库");
mongoDatabase.createCollection(collectionName);
System.out.println("集合创建成功");
}
/**
* 创建时间序列集合
* MongoDB 5.0之前不支持时间序列集合
* 更新命令的限制：
* 查询条件和更新字段只能是metaField里的字段
* 必须使用仅包含更新运算符表达式的更新文档执行更新
* 必须使用带有 multi: true 或 updateMany() 方法的更新命令
* 更新命令不能设置 upsert: true的更新插入操作
* 删除命令的限制：
* 使用带有 justOne: false 或 deleteMany() 方法的删除命令
*/
public static void createTimeSeriesCollection(String dbName, String collectionName, Long expireAfter, TimeUnit timeUnit) {
// 连接到数据库
MongoDatabase mongoDatabase = mongoClient.getDatabase(dbName);
System.out.println("连接到数据库");
// 时间字段
TimeSeriesOptions tsOptions = new TimeSeriesOptions("createdAt")
.metaField("") // 元数据字段,可以不设置
.granularity(TimeSeriesGranularity.valueOf("HOURS")); // 单个桶允许覆盖的最大时间跨度 SECONDS:1h/MINUTES:24h/HOURS:30d
CreateCollectionOptions collOptions = new CreateCollectionOptions().timeSeriesOptions(tsOptions).expireAfter(expireAfter, timeUnit);
mongoDatabase.createCollection(collectionName, collOptions);
System.out.println("时间序列集合创建成功");
}
/**
* 创建集合数据过期索引
*/
public static void createIndexForTTL(String dbName, String collectionName, String filed, Long expireAfter, TimeUnit timeUnit) {
// 连接到数据库
MongoDatabase mongoDatabase = mongoClient.getDatabase(dbName);
System.out.println("连接到数据库");
MongoCollection<Document> collection = mongoDatabase.getCollection(collectionName);
collection.createIndex(Indexes.ascending(filed), new IndexOptions().expireAfter(expireAfter, timeUnit));
System.out.println("集合数据过期索引创建成功");
}
/**
* 删除集合
*/
public static void dropCollection(String dbName, String collectionName) {
// 连接到数据库
MongoDatabase mongoDatabase = mongoClient.getDatabase(dbName);
System.out.println("连接到数据库");
mongoDatabase.getCollection(collectionName).drop();
System.out.println("集合删除成功");
}
/**
* 插入文档
*/
public static void insert(String dbName, String collectionName) {
// 连接到数据库
MongoDatabase mongoDatabase = mongoClient.getDatabase(dbName);
System.out.println("连接到数据库");
// 获取集合
MongoCollection<Document> collection = mongoDatabase.getCollection(collectionName);
// 向集合中插入文档
LocalDateTime dateTime = LocalDateTime.now();
long milliSecond=dateTime.toInstant(ZoneOffset.UTC).toEpochMilli();
Document document = new Document("name1/name2", "管理员").append("code1/code2", "manager").append("sort", 100).append("score1/score2", 60).append("createdAt", dateTime).append("createdAtNum", milliSecond);
List<Document> documents = new ArrayList<>();
documents.add(document);
dateTime = LocalDateTime.now();
milliSecond=dateTime.toInstant(ZoneOffset.UTC).toEpochMilli();
documents.add(new Document("name1/name2", "员工1").append("code1/code2", "member").append("sort", 101).append("score1/score2", 90).append("createdAt", dateTime).append("createdAtNum", milliSecond).append("class1/class2", ""));
dateTime = LocalDateTime.now();
milliSecond=dateTime.toInstant(ZoneOffset.UTC).toEpochMilli();
documents.add(new Document("name1/name2", "员工2").append("code1/code2", "member").append("sort", 101).append("score1/score2", 80).append("createdAt", dateTime).append("createdAtNum", milliSecond));
dateTime = LocalDateTime.now().plus(-5, ChronoUnit.WEEKS);
milliSecond=dateTime.toInstant(ZoneOffset.UTC).toEpochMilli();
documents.add(new Document("name1/name2", "员工3").append("code1/code2", "member").append("sort", 101).append("score1/score2", 91).append("createdAt", dateTime).append("createdAtNum", milliSecond).append("class1/class2", 0));
dateTime = LocalDateTime.now();
milliSecond=dateTime.toInstant(ZoneOffset.UTC).toEpochMilli();
documents.add(new Document("name1/name2", "员工4").append("code1/code2", "member").append("sort", 101).append("score1/score2", 95).append("createdAt", dateTime).append("createdAtNum", milliSecond).append("class1/class2", null));
collection.insertMany(documents);
System.out.println("文档插入成功");
}
/**
* 更新文档
*/
public static void update(String dbName, String collectionName) {
// 连接到数据库
MongoDatabase mongoDatabase = mongoClient.getDatabase(dbName);
System.out.println("连接到数据库");
// 获取集合
MongoCollection<Document> collection = mongoDatabase.getCollection(collectionName);
// 更新文档
collection.updateMany(Filters.eq("code1/code2", "manager"), new Document("$set", new Document("name1/name2", "经理")));
// 遍历所有文档
FindIterable<Document> findIterable = collection.find();
MongoCursor<Document> mongoCursor = findIterable.iterator();
while (mongoCursor.hasNext()) {
System.out.println(mongoCursor.next());
}
}
/**
* 删除文档
*/
public static void delete(String dbName, String collectionName) {
// 连接到数据库
MongoDatabase mongoDatabase = mongoClient.getDatabase(dbName);
System.out.println("连接到数据库");
// 获取集合
MongoCollection<Document> collection = mongoDatabase.getCollection(collectionName);
// 删除符合条件的第一个文档
//collection.deleteOne(Filters.eq("code1/code2", "manager"));
// 删除所有符合条件的文档
collection.deleteMany(Filters.eq("code1/code2", "manager"));
// 遍历所有文档
FindIterable<Document> findIterable = collection.find();
MongoCursor<Document> mongoCursor = findIterable.iterator();
while (mongoCursor.hasNext()) {
System.out.println(mongoCursor.next());
}
}
/**
* 查询文档
*/
public static void query(String dbName, String collectionName) {
// 连接到数据库
MongoDatabase mongoDatabase = mongoClient.getDatabase(dbName);
System.out.println("连接到数据库");
// 获取集合
MongoCollection<Document> collection = mongoDatabase.getCollection(collectionName);
// 遍历所有文档
FindIterable<Document> findIterable = collection.find();
MongoCursor<Document> mongoCursor = findIterable.iterator();
System.out.println("遍历所有结果:");
while (mongoCursor.hasNext()) {
System.out.println(mongoCursor.next());
}
// 查询当前集合所有文档数量
long count = collection.countDocuments();
System.out.println("当前文档数量:" + count);
// 带条件查询
FindIterable<Document> documentFindIterable = collection
.find(Filters.eq("code1/code2", "member"))
.projection(Projections.include("name1/name2", "code1/code2", "createdAt", "createdAt", "score1/score2"))
.sort(Sorts.ascending("createdAt"))
.limit(1000);
MongoCursor<Document> documentMongoCursor = documentFindIterable.iterator();
System.out.println("条件查询结果:");
int i = 0;
while (documentMongoCursor.hasNext()) {
Document next = documentMongoCursor.next();
if (i == 0) {
i++;
next.forEach((name, obj)-> {
System.out.println(name + ": " + obj.getClass());
});
}
System.out.println(next.toJson());
}
// 聚合查询
AggregateIterable<Document> aggregate = collection.aggregate(makeAggregaList());
MongoCursor<Document> documentMongoCursor2 = aggregate.iterator();
System.out.println("聚合查询结果:");
i = 0;
while (documentMongoCursor2.hasNext()) {
Document next = documentMongoCursor2.next();
if (i == 0) {
i++;
next.forEach((name, obj)-> {
System.out.println(name + ": " + obj.getClass());
});
}
System.out.println(next.toJson());
}
}
/**
* 生成聚合查询
*/
private static List<Bson> makeAggregaList() {
// 生成查询条件
Bson filters = Filters.and(Filters.or(Filters.gte("score1/score2", 90), Filters.eq("code1/code2", "manager")));
// 生成GroupBy和聚合字段
// Document groupByTime = groupByTime1("$createdAt", QuantizeUnit.Week, TimeZone.getDefault());
// Document groupByTime = groupByTime2("$createdAt", DateTruncUnit.week, 2, TimeZone.getDefault(), "thursday");
// Document groupByTime = groupByTime3("$createdAtNum", ChronoUnit.WEEKS, 2);
Document groupByTime = groupByTime4("$createdAt", ChronoUnit.WEEKS, 2);
Document groupBy = new Document("time", groupByTime);
groupBy.append("code1/code2", "$code1/code2");
List<BsonField> fields = new ArrayList<BsonField>();
fields.add(Accumulators.avg("avg_score1/score2", "$score1/score2"));
Document countIfNotNull = Document.parse("{'$cond': [{'$gt':['$class1/class2', null]}, 1, 0]}");
fields.add(Accumulators.sum("count_score1/score2", countIfNotNull));
fields.add(Accumulators.max("max_score1/score2", "$score1/score2"));
fields.add(Accumulators.min("min_score1/score2", "$score1/score2"));
fields.add(Accumulators.sum("sum_score1/score2", "$score1/score2"));
return Arrays.asList(
Aggregates.match(filters),
Aggregates.group(groupBy, fields),
Aggregates.sort(Sorts.ascending("_id.time")),
Aggregates.limit(1000)
);
}
/**
* 按照时间分组,实现类似$dateTrunc的效果,但是不支持quarter并且binSize只能是1
*/
private static Document groupByTime1(String srcField, QuantizeUnit quantizeUnit, TimeZone timeZone) {
String timeExpression = quantizeUnit.getExpression();
// 方式1:使用对象拼装
Document groupByTimeStr = new Document("$dateToString", new Document("format", timeExpression).append("date", "$"+ srcField));
Document groupByTimeParam = new Document("dateString", groupByTimeStr).append("format", timeExpression);
Document groupByTimeOld = new Document("$dateFromString", groupByTimeParam);
// 方式2:使用Json字符串
String bsonString = "{'$dateFromString': {"
+ "'dateString': {"
+ "'$dateToString': {"
+ "'date': '%1$s',"
+ "'format': '%2$s',"
+ "'timezone': '%3$s'"
+ "}"
+ "},"
+ "'format': '%2$s',"
+ "'timezone': '%3$s'"
+ "}"
+ "}";
Document groupByTime = Document.parse(String.format(bsonString, srcField, timeExpression, timeZone.getID()));
System.out.println(groupByTimeOld.toJson());
System.out.println(groupByTime.toJson());
return groupByTime;
}
static enum QuantizeUnit {
Millisecond ("%Y-%m-%d %H:%M:%S.%L"),
Second ("%Y-%m-%d %H:%M:%S"),
Minute ("%Y-%m-%d %H:%M"),
Hour ("%Y-%m-%d %H"),
Day ("%Y-%m-%d"),
Week ("%G/%V"),
Month ("%Y-%m"),
Year ("%Y");
private final String expression;
private QuantizeUnit(String expression) {
this.expression = expression;
}
public String getExpression() {
return expression;
}
}
/**
* 按照时间分组,调用$dateTrunc
* MongoDB 5.0新功能,DocumentDB不支持
*/
private static Document groupByTime2(String srcField, DateTruncUnit unit, int binSize, TimeZone timeZone, String startOfWeek) {
String bsonString = "{'$dateTrunc': {"
+ "'date': '%1$s',"
+ "'unit': '%2$s',"
+ "'binSize': %3$s,"
+ "'timezone': '%4$s',"
+ "'startOfWeek': '%5$s'"
+ "}"
+ "}";
Document groupByTime = Document.parse(String.format(bsonString, srcField, unit, binSize, timeZone.getID(), startOfWeek));
System.out.println(groupByTime.toJson());
return groupByTime;
}
static enum DateTruncUnit {
second,
minute,
hour,
day,
month,
week,
quarter,
year;
}
/**
* 按照时间分组,实现类似$dateTrunc的效果,但是字段需要为长整型
*/
private static Document groupByTime3(String srcField, ChronoUnit unit, int binSize) {
// 使用毫秒表示的分组时间间隔
long millisecondRange = getDivisorValueForNumericFloor(unit) * binSize;
// 基准时间(1970-01-01 00:00:00)
long baseDate = 0L;
// 划分时间 = 数据时间 - ((数据时间 - 基准时间(1970-01-01 00:00:00)) % 使用毫秒表示的分组时间间隔 )
String bsonString = "{'$subtract': ["
+ "'%1$s',"
+ "{'$mod': [{"
+ "'$subtract': ["
+ "'%1$s',"
+ "%2$s"
+ "]},"
+ "%3$s"
+ "]"
+ "}]"
+ "}";
System.out.println(String.format(bsonString, srcField, baseDate, millisecondRange));
Document groupByTime = Document.parse(String.format(bsonString, srcField, baseDate, millisecondRange));
System.out.println(groupByTime.toJson());
return groupByTime;
}
/**
* 按照时间分组,实现类似$dateTrunc的效果
*/
private static Document groupByTime4(String srcField, ChronoUnit unit, int binSize) {
// 使用毫秒表示的分组时间间隔
long millisecondRange = getDivisorValueForNumericFloor(unit) * binSize;
// 基准时间(1970-01-01 00:00:00)
long baseDate = 0L;
// 划分时间 = 数据时间 - ((数据时间 - 基准时间(1970-01-01 00:00:00)) % 使用毫秒表示的分组时间间隔 )
// 基准时间转date
String longToDateTemp = "{'$date': {'$numberLong': '%d'}}";
String baseDateStr = String.format(longToDateTemp, baseDate);
// 计算
String bsonString = "{'$subtract': ["
+ "'%1$s',"
+ "{'$mod': [{"
+ "'$subtract': ["
+ "'%1$s',"
+ "%2$s"
+ "]},"
+ "%3$s"
+ "]"
+ "}]"
+ "}";
bsonString = String.format(bsonString, srcField, baseDateStr, millisecondRange);
System.out.println(bsonString);
Document groupByTime = Document.parse(bsonString);
System.out.println(groupByTime.toJson());
return groupByTime;
}
private static long getDivisorValueForNumericFloor(final ChronoUnit timeUnitRange) {
final long divisorLong;
switch (timeUnitRange) {
case WEEKS:
divisorLong = ChronoUnit.WEEKS.getDuration().toMillis();
break;
case DAYS:
divisorLong = ChronoUnit.DAYS.getDuration().toMillis();
break;
case HOURS:
divisorLong = ChronoUnit.HOURS.getDuration().toMillis();
break;
case MINUTES:
divisorLong = ChronoUnit.MINUTES.getDuration().toMillis();
break;
case SECONDS:
divisorLong = ChronoUnit.SECONDS.getDuration().toMillis();
break;
case MILLIS:
divisorLong = 1;
break;
default:
divisorLong = ChronoUnit.DAYS.getDuration().toMillis();
break;
}
return divisorLong;
}
}
JAVA使用DocumentDB JDBC
package com.example.demo.mongo;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import software.amazon.documentdb.jdbc.DocumentDbDataSource;
public class StudyDocumentDBJDBC {
private static DocumentDbDataSource docDataSource;
private static String dbName = "0751ac0e-53b2-4c69-8828-01441e3df593";
static {
docDataSource = new DocumentDbDataSource();
docDataSource.setHostname("127.0.0.1");
docDataSource.setUser("testRoot");
docDataSource.setPassword("123456");
docDataSource.setDatabase(dbName);
docDataSource.setTlsEnabled(false);
}
public static void main(String[] args) throws SQLException {
String collectionName = "cat1/cat2";
//printMetaData(dbName, collectionName);
query(collectionName);
}
private static void printMetaData(String dbName, String collectionName) throws SQLException {
try(Connection connection = docDataSource.getConnection();
Statement statement = connection.createStatement();) {
System.out.println("开始查询表结构");
ResultSet resultSet = statement.executeQuery("select * from \"" + collectionName + "\" limit 1");
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
System.out.println("表结构");
for (int i=1; i<= columnCount; i++) {
System.out.print(metaData.getColumnName(i));
System.out.print(": ");
System.out.print(metaData.getColumnClassName(i));
System.out.print(": ");
System.out.print(metaData.getColumnTypeName(i));
System.out.println();
}
System.out.println();
printResultSet(resultSet);
resultSet.close();
}
}
private static void query(String collectionName) throws SQLException {
System.out.println("进入查询方法");
try(Connection connection = docDataSource.getConnection();
Statement statement = connection.createStatement();) {
String colScore = "\"score1/score2\"";
String colCode = "\"code1/code2\"";
String sql = "select floor(createdAt to WEEK) as createdAt,"+colCode+",avg("+colScore+") as avg1, count(1) as count1, max("+colScore+"), min("+colScore+"), sum("+colScore+") ";
sql += "from \"" + collectionName + "\" ";
sql += "where "+colScore+" >=90 or "+colCode+" = 'manager' ";
sql += "group by floor(createdAt to WEEK), "+colCode;
System.out.println("查询开始");
ResultSet resultSet = statement.executeQuery(sql);
System.out.println("查询结果");
printResultSet(resultSet);
resultSet.close();
}
}
private static void printResultSet(ResultSet resultSet) throws SQLException {
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
int row = 0;
System.out.println("打印结果开始");
while(resultSet.next()) {
System.out.print("第" + (++row) + "行 ");
for (int i=1; i<= columnCount; i++) {
System.out.print(metaData.getColumnName(i));
System.out.print(":");
System.out.print(resultSet.getObject(i));
System.out.print(" ");
}
System.out.println();
}
System.out.println("打印结果结束");
}
}
9014
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
