SpringBoot Mongo
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-mongodb</artifactId>
</dependency>
<!--sql转mongo语法工具-->
<dependency>
<groupId>com.github.vincentrussell</groupId>
<artifactId>sql-to-mongo-db-query-converter</artifactId>
<version>1.11</version>
</dependency>
package cn.stylefeng.guns.utils; import cn.stylefeng.guns.GunsApplication; import cn.stylefeng.guns.core.util.RedisUtil; import cn.stylefeng.guns.modular.system.model.ApplicationData; import com.alibaba.fastjson.JSONObject; import com.github.vincentrussell.query.mongodb.sql.converter.MongoDBQueryHolder; import com.github.vincentrussell.query.mongodb.sql.converter.ParseException; import com.github.vincentrussell.query.mongodb.sql.converter.QueryConverter; import com.mongodb.*; import com.mongodb.client.*; import org.beetl.ext.format.DateFormat; import org.bson.Document; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.data.mongodb.core.MongoTemplate; import org.springframework.data.mongodb.core.aggregation.*; import org.springframework.data.mongodb.core.query.BasicQuery; import org.springframework.data.mongodb.core.query.Criteria; import org.springframework.data.mongodb.core.query.Query; import org.springframework.data.mongodb.core.query.Update; import org.springframework.test.context.junit4.SpringRunner; import javax.annotation.Resource; import java.text.SimpleDateFormat; import java.util.*; import static org.springframework.data.mongodb.core.aggregation.Aggregation.*; import static org.springframework.data.mongodb.core.aggregation.ArithmeticOperators.*; import static org.springframework.data.mongodb.core.aggregation.ConditionalOperators.when; import static org.springframework.data.mongodb.core.query.Criteria.where; @RunWith(SpringRunner.class) @SpringBootTest(classes = GunsApplication.class) public class MongodbTest { @Autowired private MongoTemplate mongoTemplate; @Test public void mongoTest() { // 直接执行原生查询语句 https://blog.csdn.net/dark_horse_lk/article/details/100563960 // ArrayList<BasicDBObject> list = new ArrayList<>(); // String condition = "{ " + // " "$group":{ " + // " "_id":"$SEX", " + // " "count":{$sum:1}, " + // " "d1":{$sum:{$cond:{if:{"$and":[{"$gte":["$AGE_DX","0"]},{"$lte":["$AGE_DX","15"]}]},then:1,else:0}}}, " + // " "d2":{$sum:{$cond:{if:{"$and":[{"$gte":["$AGE_DX","16"]},{"$lte":["$AGE_DX","30"]}]},then:1,else:0}}}, " + // " "d3":{$sum:{$cond:{if:{"$and":[{"$gte":["$AGE_DX","31"]},{"$lte":["$AGE_DX","45"]}]},then:1,else:0}}}, " + // " "d4":{$sum:{$cond:{if:{"$and":[{"$gte":["$AGE_DX","46"]},{"$lte":["$AGE_DX","60"]}]},then:1,else:0}}}, " + // " "d5":{$sum:{$cond:{if:{"$and":[{"$gte":["$AGE_DX","61"]},{"$lte":["$AGE_DX","75"]}]},then:1,else:0}}}, " + // " "d6":{$sum:{$cond:{if:{"$and":[{"$gte":["$AGE_DX","76"]},{"$lte":["$AGE_DX","90"]}]},then:1,else:0}}}, " + // " "d7":{$sum:{$cond:{if:{"$gt":["$AGE_DX","90"]},then:1,else:0}}} " + // " } " + // "}"; // BasicDBObject group = BasicDBObject.parse(condition); // list.add(group); // BasicDBObject limit = BasicDBObject.parse("{$limit:3}"); // list.add(limit); // AggregateIterable<Document> documents = mongoTemplate.getCollection("xinhua").aggregate(list); // for (Map document : documents) { // System.out.println(document); // } // for (Map document : documents) { // System.out.println(document); // } // 基本查询 https://www.cnblogs.com/herosoft/p/8405451.html https://blog.csdn.net/qq_33206732/article/details/80017746 // Query query = new Query(Criteria.where("_id").is("5ee820d49f8a1b1d1479c42b")); // Map record = mongoTemplate.findOne(query, Map.class, "xinhua"); // System.out.println(record); // Query query = new Query(Criteria.where("time").gt(new Date("2020/06/12"))); // Map record = mongoTemplate.findOne(query, Map.class, "xinhua"); // System.out.println(record); // Query query = new Query(); // List<Map> documents = mongoTemplate.find(query, Map.class, "xinhua"); Query query = new Query(); query.addCriteria(Criteria.where("GUNS_XINHUA_VERSION").is(17)); long count = mongoTemplate.count(query, "xinhua"); System.out.println(count); // List<Map> documents = mongoTemplate.findAll(Map.class, "xinhua"); // for (Map document : documents) { // System.out.println(document); // } // documents.forEach((key, value) -> { // System.out.println(key + "==" + value); // }); // Update update = new Update().set("likes", 110); // mongoTemplate.updateFirst(query, update, "xinhua"); // Map<String, Object> map = new HashMap<String, Object>() { // { // put("time1", new Date("2012/04/15")); //// put("time2", new Date("2012-04-15")); // put("time3", new Date("2012/4/15")); // put("sex", 1); // put("many", 34.555); // } // }; // mongoTemplate.insert(map, "xinhua"); // List<Map> list = new ArrayList<>(); // list.add(map); // mongoTemplate.insert(list, "xinhua"); } @Test public void getTest() { ServerAddress serverAddress = new ServerAddress("10.1.192.130", 27017); List<ServerAddress> addrs = new ArrayList<>(); addrs.add(serverAddress); MongoCredential credential = MongoCredential.createScramSha1Credential("xinhua", "admin", "xinhua111".toCharArray()); List<MongoCredential> credentials = new ArrayList<>(); credentials.add(credential); MongoClient mongoClient = new MongoClient(addrs, credentials); MongoDatabase mongoDatabase = mongoClient.getDatabase("xinhua"); MongoCollection<Document> xinhua = mongoDatabase.getCollection("xinhua"); // 插入单个文档 Document document = new Document("wonder-group-test", "万达信息"); xinhua.insertOne(document); // 插入多个文档 // List<Document> documents = new ArrayList<>(); // documents.add(document); // xinhua.insertMany(documents); // 检索所有文档 // FindIterable<Document> findIterable = xinhua.find(); // MongoCursor<Document> mongoCursor = findIterable.iterator(); // while (mongoCursor.hasNext()) { // System.out.println(mongoCursor.next()); // } } @Test public void getAggregation() { //封装查询条件 List<AggregationOperation> operations = new ArrayList<>(); // operations.add(Aggregation.match(Criteria.where("SEX").is("1"))); // operations.add(Aggregation.count().as("total")); // operations.add(Aggregation.group().count().as("count")); operations.add(Aggregation.group("SEX").count().as("count")); Aggregation aggregation = newAggregation(operations); AggregationResults<Map> results = mongoTemplate.aggregate(aggregation, "xinhua", Map.class); List<Map> mappedResults = results.getMappedResults(); System.out.println(mappedResults); // GroupOperation as = Aggregation.group("SEX").sum("payment").as("totalPayment").sum("num").as("itemNum").count().as("orderNum"); // Criteria criteria = Criteria.where("SEX").is(1); // Aggregation customerAgg = Aggregation.newAggregation( // Aggregation.project("buyerNick", "payment", "num", "tid", "userId", "address", "mobile", "orders"), // Aggregation.match(criteria), // Aggregation.unwind("orders"), // Aggregation.group("SEX").sum("payment").as("totalPayment").sum("num").as("itemNum").count().as("orderNum"), // Aggregation.sort(new Sort(new Sort.Order(Sort.Direction.DESC, "totalPayment"))), // Aggregation.skip(startRows), // Aggregation.limit(pageSize) // ); } @Test public void getAggregationIF() { // https://www.it1352.com/1637289.html mongodb if用法 // Aggregation aggregation = // newAggregation( // project("id"). // and(when(where("status").is("A")).then(1).otherwise(0)).as("status"). // and(Subtract.valueOf("end_time").subtract("start_time")).as("diffTime"), // group("$id").count().as("total").sum("status").as("live").avg("diffTime").as("chat_hrs")); // https://blog.csdn.net/w2015ei/article/details/80664220 List<AggregationOperation> operations = new ArrayList<>(); Criteria matchCriteria = new Criteria(); List<Map<String, Object>> condition = new ArrayList<>(); for (Map cond : condition) { switch ((String) cond.get("type")) { case "-1": matchCriteria.andOperator(); break; case "0": break; case "1": case "2": break; case "3": break; } } matchCriteria.where("SEX").is("1"); operations.add(Aggregation.match(matchCriteria)); operations.add(Aggregation.group("SEX") .count().as("cnum"). sum(when(new Criteria().andOperator(where("AGE_DX").gte("0"), where("AGE_DX").gte("0").lte("15"))).then(1).otherwise(0)).as("d1"). sum(when(new Criteria().andOperator(where("AGE_DX").gte("16"), where("AGE_DX").lte("30"))).then(1).otherwise(0)).as("d2"). sum(when(new Criteria().andOperator(where("AGE_DX").gte("31"), where("AGE_DX").lte("45"))).then(1).otherwise(0)).as("d3"). sum(when(new Criteria().andOperator(where("AGE_DX").gte("46"), where("AGE_DX").lte("60"))).then(1).otherwise(0)).as("d4"). sum(when(new Criteria().andOperator(where("AGE_DX").gte("61"), where("AGE_DX").lte("75"))).then(1).otherwise(0)).as("d5"). sum(when(new Criteria().andOperator(where("AGE_DX").gte("76"), where("AGE_DX").lte("90"))).then(1).otherwise(0)).as("d6"). sum(when(where("AGE_DX").gte("90")).then(1).otherwise(0)).as("d7")); Aggregation aggregation = newAggregation(operations); AggregationResults<Map> results = mongoTemplate.aggregate(aggregation, "xinhua", Map.class); List<Map> mappedResults = results.getMappedResults(); System.out.println(mappedResults); } /** * sql转mongodb，只能处理一些简单的查询，复杂的自己手写，要求mongodb版本4.0以上 * * @throws ParseException */ @Test public void SqlTransferMongo() throws ParseException { String sql1 = "select object.key1, object2.key3, object1.key4 from my_collection where object.key2 = 34 AND object2.key4 > 5"; String sql2 = "SELECT COUNT(*) AS cnum,SEX,SUM(IF((0 <= AGE_DX AND AGE_DX >= 15),1,0)) AS d1,SUM(IF((16 <= AGE_DX AND AGE_DX >= 30),1,0)) AS d2,SUM(IF((AGE_DX > 90),1,0)) AS d7 FROM xin_databases"; String sql3 = "select x from db WHERE SEX >= 1 and SEX <= 3"; String sql4 = "select x from db WHERE INHOS_DATE >= "2020/06/25" and INHOS_DATE <= "2020/07/02" OR AGE_DX >= "23" and AGE_DX <= "56" AND SEX in ("1") AND EXPDT >= "2" and EXPDT <= "6""; QueryConverter queryConverter = new QueryConverter.Builder().sqlString(sql4).build(); MongoDBQueryHolder mongoDBQueryHolder = queryConverter.getMongoQuery(); String collection = mongoDBQueryHolder.getCollection(); Document query = mongoDBQueryHolder.getQuery(); Document projection = mongoDBQueryHolder.getProjection(); Document sort = mongoDBQueryHolder.getSort(); System.out.println(collection); System.out.println(query); System.out.println(query.toJson()); System.out.println(projection); System.out.println(projection.toJson()); System.out.println(sort); System.out.println(sort.toJson()); ArrayList<BasicDBObject> list = new ArrayList<>(); String condition = "{ " + " "$group":{ " + " "_id":"$SEX", " + " "count":{$sum:1}, " + " "d1":{$sum:{$cond:{if:{"$and":[{"$gte":["$AGE_DX","0"]},{"$lte":["$AGE_DX","15"]}]},then:1,else:0}}}, " + " "d2":{$sum:{$cond:{if:{"$and":[{"$gte":["$AGE_DX","16"]},{"$lte":["$AGE_DX","30"]}]},then:1,else:0}}}, " + " "d3":{$sum:{$cond:{if:{"$and":[{"$gte":["$AGE_DX","31"]},{"$lte":["$AGE_DX","45"]}]},then:1,else:0}}}, " + " "d4":{$sum:{$cond:{if:{"$and":[{"$gte":["$AGE_DX","46"]},{"$lte":["$AGE_DX","60"]}]},then:1,else:0}}}, " + " "d5":{$sum:{$cond:{if:{"$and":[{"$gte":["$AGE_DX","61"]},{"$lte":["$AGE_DX","75"]}]},then:1,else:0}}}, " + " "d6":{$sum:{$cond:{if:{"$and":[{"$gte":["$AGE_DX","76"]},{"$lte":["$AGE_DX","90"]}]},then:1,else:0}}}, " + " "d7":{$sum:{$cond:{if:{"$gt":["$AGE_DX","90"]},then:1,else:0}}} " + " } " + "}"; BasicDBObject group = BasicDBObject.parse(condition); String querySQL = "{"$match":" + mongoDBQueryHolder.getQuery().toJson() + "}"; BasicDBObject match = BasicDBObject.parse(querySQL); list.add(match); list.add(group); AggregateIterable<Document> documents = mongoTemplate.getCollection("xinhua").aggregate(list); for (Map document : documents) { System.out.println(document); } } }
