mongdb实用java分页查询与mongdb查询语句

mongdb语句

db.exception.aggregate([
    
   {$match : {
       pointId:9,
       enterpriseName:{'$regex': '瑞尔'},
       busId:{
           "$gt":"20200615000000000","$lt":"20200616000000000"
           } 
       }
   },
    {$project:{
        _id:1,
        pointId:1,
        enterpriseId:1,
        name:1,
        pointName:1,
        enterpriseName:1,
        mn:1,
        busId:1,
        dataType:1,
       "arrayofkeyvalue":{"$objectToArray":"$metrics"}
             }
     }
     ,

    {$unwind:{
        path:"$arrayofkeyvalue",
        preserveNullAndEmptyArrays:true
            }
    }
    ,
    {$facet: {
    paginatedResults: [
      { $skip: 0 }
    , { $limit: 10 }
    ]
    ,
    totalCount: [
      {
        $count: 'count'
      }
    ]
    }
  }

]);


  

java分页模糊查询、时间范围查询

package com.shbykj.springboot.monitor.controller;

import com.google.gson.Gson;
import com.mongodb.BasicDBList;
import com.mongodb.BasicDBObject;
import com.mongodb.DBObject;
import com.mongodb.client.AggregateIterable;
import com.mongodb.client.MongoCursor;
import com.shbykj.springboot.common.service.sys.CommonService;
import com.shbykj.springboot.query.ExceptionQuery;
import com.shbykj.springboot.query.PagerMongodb;
import com.shbykj.springboot.query.SpringDataPageAble;
import com.shbykj.springboot.result.Count;
import com.shbykj.springboot.util.StringUtils;
import lombok.extern.slf4j.Slf4j;
import org.bson.Document;
import org.bson.conversions.Bson;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 异常预警页面
 *
 * @author
 * @date 2020-11-03 10:21
 */
@Slf4j
@Controller
public class ExceptionJsonController {

    @Autowired
    MongoTemplate mongoTemplate;

    /**
     * 异常预警分页查询
     *
     * @param request
     * @return
     */
    @RequestMapping("/monitor/exceptionWarn")
    @ResponseBody
    public Map<String, Object> exceptionWarn(HttpServletRequest request, ExceptionQuery exceptionQuery) {
        Map<String, Object> mapList = new HashMap<String, Object>();

        try {
            //queryList集合里的顺序不能乱，否则会报错。
            List queryList = new ArrayList<>();
 match(相当于 WHERE 或者 HAVING )=========时间范围查询、站点where查询
            String pointId="9";
            String startTime = "20200615000000000";
            String endTime = "20200616000000000";
            String enterpriseName=request.getParameter("enterpriseName");
            String pointName=request.getParameter("pointName");
            String mn=request.getParameter("mn");
            BasicDBObject matchQuery = new BasicDBObject();
            //站点id
            if(StringUtils.isNotBlank(pointId)){
                matchQuery.put("pointId", Integer.valueOf(pointId));
            }
            //时间范围
            if(StringUtils.isNotBlank(startTime)&&StringUtils.isNotBlank(endTime)){
                BasicDBObject times = new BasicDBObject("$gt", startTime);
                times.put("$lt", endTime);
                matchQuery.put("busId", times);
            }
//            enterpriseName:{'$regex': '瑞尔'},
            //企业名称
            if(StringUtils.isNotBlank(enterpriseName)){
                matchQuery.put("enterpriseName", new BasicDBObject("$regex",enterpriseName));
            }
            //站点名称
            if(StringUtils.isNotBlank(pointName)){
                matchQuery.put("pointName", new BasicDBObject("$regex",pointName));
            }
            //MN
            if(StringUtils.isNotBlank(mn)){
                matchQuery.put("mn", new BasicDBObject("$regex",mn));
            }
           if( matchQuery.isEmpty()==false){
               BasicDBObject match = new BasicDBObject("$match", matchQuery);
               queryList.add(match);
           }


project：修改输入文档的结构。可以用来重命名、增加或删除域，也可以用于创建计算结果以及嵌套文档。

            //设置显示的字段集合
            BasicDBObject fields = new BasicDBObject("pointId", 1);
            //处理嵌套对象====metrics对象转数组
            fields.put("arrayofkeyvalue", new BasicDBObject("$objectToArray", "$metrics"));
            fields.put("enterpriseId", 1);
            fields.put("name", 1);
            fields.put("pointName", 1);
            fields.put("enterpriseName", 1);
            fields.put("busId", 1);
            fields.put("mn", 1);
            fields.put("busId", 1);
            fields.put("dataType", 1);
            BasicDBObject project = new BasicDBObject("$project", fields);
unwind(拆分内嵌对象，相当开窗函数作用)
            BasicDBObject unwindQuery = new BasicDBObject("path", "$arrayofkeyvalue");
            unwindQuery.put("preserveNullAndEmptyArrays", true);
            BasicDBObject unwind = new BasicDBObject("$unwind", unwindQuery);


group（相当于 GROUP BY）
//            BasicDBObject group = new BasicDBObject("$group", new BasicDBObject("_id", "$subject")
//                    .append("count", new BasicDBObject("$sum", 1)));
//
sort（排序）
//            BasicDBObject sort = new BasicDBObject("$sort", new BasicDBObject("count", -1));//1:正序，-1倒序
//
skip(跳过前面多少条数据，分页时使用)
limt(只要前多少条数据，分页时使用)
            String Cpage=request.getParameter("Cpage");


            Integer pageSize = exceptionQuery.getLimit();//大小

            BasicDBObject limit = new BasicDBObject("$limit", pageSize);
            //默认第一页
            BasicDBObject skip=new BasicDBObject("$skip",0);

            if(StringUtils.isNotBlank(Cpage)){
                 skip = new BasicDBObject("$skip", (Integer.valueOf(Cpage )- 1) * pageSize);
            }

            BasicDBObject[] array1 = {skip, limit};

            BasicDBObject facetquery = new BasicDBObject("paginatedResults", array1);
            BasicDBObject count = new BasicDBObject("$count", "count");
            BasicDBObject[] array2 = {count};
            facetquery.put("totalCount", array2);
            //{$facet: {
            //    paginatedResults: [
            //      { $skip: 0 }
            //    , { $limit: 10 }
            //    ]
            //    ,
            //    totalCount: [
            //      {
            //        $count: 'count'
            //      }
            //    ]
            //    }
            BasicDBObject facet = new BasicDBObject("$facet", facetquery);


            queryList.add(project);
//            queryList .add(group);
//            queryList .add(sort);


            queryList.add(unwind);


            queryList.add(facet);//分页，count
            AggregateIterable<Document> exception = mongoTemplate.getCollection("exception").aggregate(queryList);
            //利用gson工具把document对象转化为实体类对象
            Gson gson = new Gson();
            List<ExceptionQuery> exceptionQueryList = new ArrayList<ExceptionQuery>();

            boolean b = exception.iterator().hasNext();
            Count count1 = new Count();
            if (b) {
                Document next = exception.iterator().next();
                //list
                List<Document> paginatedResults = (List<Document>) next.get("paginatedResults");
               if(paginatedResults.size()>0&&paginatedResults!=null){
                   for (int i = 0; i < paginatedResults.size(); i++) {
                       Document document= paginatedResults.get(i);
                       exceptionQuery.set_id(document.get("_id").toString());//
                       exceptionQuery.setBusId(document.get("busId").toString());
                       exceptionQuery.setEnterpriseName(document.get("enterpriseName").toString());
                       exceptionQuery.setDataType(document.get("dataType").toString());
                       exceptionQuery.setEnterpriseId(document.get("enterpriseId").toString());
                       exceptionQuery.setPointName(document.get("pointName").toString());
                       exceptionQuery.setPointId(document.get("pointId").toString());
                       exceptionQuery.setMn(document.get("mn").toString());
                       exceptionQuery.setArrayofkeyvalue(document.get("arrayofkeyvalue"));

                       if(StringUtils.isNotBlank(Cpage)){
                           exceptionQuery.setPage(Integer.valueOf(Cpage ));
                       }

                       exceptionQueryList.add(exceptionQuery);
                   }
                   //count总条数
                   List<Count> totalCount = (List<Count>) next.get("totalCount");
                   if(totalCount.size()>0&&totalCount!=null) {

                       String str = gson.toJson(totalCount.get(0));
                       count1 = gson.fromJson(str, Count.class);
                   }

                   mapList.put("data", exceptionQueryList);
                   mapList.put("msg", "异常报警数据查询成功！");
                   mapList.put("count", count1.getCount());
                   mapList.put("jsonFlag", 1);
               }

            }
            mapList.put("jsonFlag", 1);
            mapList.put("msg", "无数据！");
        } catch (java.lang.Exception e) {
            System.out.println(e);
            mapList.put("msg", "异常报警数据查询失败！");
            mapList.put("jsonFlag", -1);
        }

        return mapList;
    }

}