MongoDB数据库GroupBy查询使用Spring-data-mongondb的实现

以前用MongoDB数据库都是简单的查询，直接用Query就可以，最近项目中用到了分组查询，完全不一样。第一次遇到，搞了好几天终于有点那意思了。

先上代码：

import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.AggregationResults;
import org.springframework.data.mongodb.core.aggregation.Fields;
import org.springframework.data.mongodb.core.aggregation.GroupOperation;
import org.springframework.data.mongodb.core.aggregation.MatchOperation;
import org.springframework.data.mongodb.core.aggregation.ProjectionOperation;
import org.springframework.data.mongodb.core.mapreduce.GroupBy;
import org.springframework.data.mongodb.core.mapreduce.GroupByResults;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.stereotype.Service;

import com.mongodb.BasicDBList;
import com.mongodb.BasicDBObject;
import com.mongodb.CommandResult;

@Service
public class EquipmentRepository implements EquipmentRepository{

    private static final Logger logger = LoggerFactory.getLogger(EquipmentRepository.class);
    
    @Autowired
    MongoTemplate mongoTemplate;

    
    
    /**
     *<p>从登陆信息表中根据IP统计设备使用时间</p>
     * @param hostName 设备名称
     * @param startTime 统计开始时间
     * @param endTime 统计结束时间
     * @return 统计信息
     */    
    @Override
    public List<EquipStatistics> statisticTime(String hostName, Date startTime, Date endTime) {
        
        List<EquipStatistics> equipStatisticsList = new ArrayList<EquipStatistics>();
        
        try {
            
        String initial = "{hostName:'' ,equipmentTypeName:'', userDurateion : 0,count:0,"
                + "startTime:"+startTime.getTime()+",endTime:"+endTime.getTime()+",nowTime:"+new Date().getTime()+"}";
        
        String reduceFunction =  "function(doc,result){"
                + "if(doc.extraData.hostName)   { result.hostName = doc.extraData.hostName;}"
                + "if(doc.extraData.deviceType) {result.equipmentTypeName = doc.extraData.deviceType;}"
                + "var time = doc.logoffTime.valueOf() - doc.logonTime.valueOf();"
                + "result.userDurateion +=time;"
                +" result.count+=1;"
                + "}";
        
        //时间的计算分四种情况
        List<EquipStatistics> equipStatisticsListTemp =null;
        for (int i = 0; i < 4; i++) {
            switch (i) {
            case 0:
                //登出时间在开始和结束之间,登录在开始和结束之间的(登出-登录)
                Criteria criteria = Criteria.where("logonIp").exists(true);
                if(hostName !=null && !"".equals(hostName.trim())){
                     criteria.and("extraData.hostName").regex(hostName);
                }
                criteria.and("logoffTime").lt(endTime).gt(startTime).and("logonTime").lt(endTime).gt(startTime);
                equipStatisticsListTemp =  searchDB(criteria, reduceFunction, initial);
                
                break;
            case 1:
                //1、 登出时间为空或 登出时间在结束之后， 登录时间在开始与结束之间的(结束-登录)
                reduceFunction =  "function(doc,result){"
                        + "if(doc.extraData.hostName)   { result.hostName = doc.extraData.hostName;}"
                        + "if(doc.extraData.deviceType) {result.equipmentTypeName = doc.extraData.deviceType;}"
                        + "var time = result.endTime - doc.logonTime.valueOf();"
                        + "result.userDurateion +=time;"
                        +" result.count+=1;"
                        + "}";
                Criteria criteria1 = Criteria.where("logonIp").exists(true);
                if(hostName !=null && !"".equals(hostName.trim())){
                     criteria1.and("extraData.hostName").regex(hostName);
                }
                
                criteria1.andOperator(Criteria.where("logonTime").lt(endTime).gt(startTime)
                        .andOperator(Criteria.where("logoffTime").exists(false).orOperator(Criteria.where("logoffTime").gt(endTime))));
                equipStatisticsListTemp =  searchDB(criteria1, reduceFunction, initial);
                break;
            case 2:
                //2、 登出时间为空， 登出时间在结束之后  ，登录时间在开始之前的 (结束-开始) 
                reduceFunction =  "function(doc,result){"
                        + "if(doc.extraData.hostName)   { result.hostName = doc.extraData.hostName;}"
                        + "if(doc.extraData.deviceType) {result.equipmentTypeName = doc.extraData.deviceType;}"
                        + "var time = result.endTime - result.startTime;"
                        + "result.userDurateion +=time;"
                        +" result.count+=1;"
                        + "}";
                Criteria criteria2 = Criteria.where("logonIp").exists(true);
                if(hostName !=null && !"".equals(hostName.trim())){
                     criteria2.and("extraData.hostName").regex(hostName);
                }
                criteria2.andOperator(Criteria.where("logonTime").lt(startTime)
                        .andOperator(Criteria.where("logoffTime").exists(false).orOperator(Criteria.where("logoffTime").gt(endTime))));
                equipStatisticsListTemp =  searchDB(criteria2, reduceFunction, initial);
                break;
            case 3:
                //4、 登出时间在开始和结束之间，登录时间在开始之前的(登出-开始)
                reduceFunction =  "function(doc,result){"
                        + "if(doc.extraData.hostName)   { result.hostName = doc.extraData.hostName;}"
                        + "if(doc.extraData.deviceType) {result.equipmentTypeName = doc.extraData.deviceType;}"
                        + "var time = doc.logoffTime.valueOf() - result.startTime;"
                        + "result.userDurateion +=time;"
                        +" result.count+=1;"
                        + "}";
                Criteria criteria3 = Criteria.where("logonIp").exists(true);
                if(hostName !=null && !"".equals(hostName.trim())){
                     criteria3.and("extraData.hostName").regex(hostName);
                }
                criteria3.and("logonTime").lt(startTime).and("logoffTime").lt(endTime).gt(startTime);
                equipStatisticsListTemp =  searchDB(criteria3, reduceFunction, initial);
                break;
            default:
                break;
            }
            equipStatisticsList.addAll(equipStatisticsListTemp);
            equipStatisticsListTemp = null;
        }
        
        //去除重复数据 时长相加 赋值使用率
        equipStatisticsList = addDuration(equipStatisticsList,daysBetween(startTime,endTime));
        } catch (Throwable e) {
            logger.error("统计设备使用信息失败："+e.getMessage(), e);
            throw new AssetRuntimeException(e);
        }
        
        
        return equipStatisticsList;
    }
    
    //获取相隔天数
    private int daysBetween(Date startTime, Date endTime) {
        return (int)((endTime.getTime()-startTime.getTime())/(1000 * 86400));
    }


    //查询数据库
    private List<EquipStatistics> searchDB(Criteria criteria, String reduceFunction,
            String initial) {
        List<EquipStatistics> equipStatisticsList = new ArrayList<EquipStatistics>();
        EquipStatistics equipStatistics = null;
        GroupBy groupBy = GroupBy.key("logonIp")
                .initialDocument(initial)
                .reduceFunction(reduceFunction);
        
        GroupByResults<Session> results = mongoTemplate.group(criteria,
                "sessions", groupBy, Session.class);
        BasicDBList list = (BasicDBList)results.getRawResults().get("retval");  
        for (int i = 0; i < list.size(); i ++) {  
            equipStatistics = new EquipStatistics();
            BasicDBObject obj = (BasicDBObject)list.get(i);  
            equipStatistics.setIp(obj.getString("logonIp"));
            equipStatistics.setHostName(obj.getString("hostName"));
            equipStatistics.setEquipmentTypeName(obj.getString("equipmentTypeName"));
            equipStatistics.setUserDurateion(obj.getLong("userDurateion"));
            equipStatisticsList.add(equipStatistics);
        }
        return equipStatisticsList;
    }
    

    //去重
    private List<EquipStatistics> addDuration(List<EquipStatistics> equipStatisticsList,int days) {
        
        BigDecimal base = new BigDecimal(days*8*60*60*1000+"");
        
        if(equipStatisticsList!=null){
            for (int i = 0; i < equipStatisticsList.size()-1; i++) {
                long userDurateion_i = equipStatisticsList.get(i).getUserDurateion();
                equipStatisticsList.get(i).setUserdDurationStr(formatTime(userDurateion_i));
                //
                BigDecimal userDur_i = new BigDecimal(userDurateion_i);
                double rate = userDur_i.divide(base, 4, BigDecimal.ROUND_HALF_UP).doubleValue();
                equipStatisticsList.get(i).setUserRate(rate);
                equipStatisticsList.get(i).setUserdRateStr(rate*100 + "%");
                for(int j = equipStatisticsList.size()-1; j>i;j--){
                    long userDurateion_j = equipStatisticsList.get(j).getUserDurateion();
                    BigDecimal userDur_j = new BigDecimal(userDurateion_j);
                    rate = userDur_j.divide(base, 4, BigDecimal.ROUND_HALF_UP).doubleValue();
                    if(equipStatisticsList.get(i).getIp().equals(equipStatisticsList.get(j).getIp())){
                        equipStatisticsList.get(i).setUserDurateion(userDur_i.add(userDur_j).longValue());
                        equipStatisticsList.get(i).setUserdDurationStr(formatTime(userDur_i.add(userDur_j).longValue()));
                        rate = userDur_i.add(userDur_j).divide(base, 4, BigDecimal.ROUND_HALF_UP).doubleValue();
                        equipStatisticsList.get(i).setUserRate(rate);
                        equipStatisticsList.get(i).setUserdRateStr(rate*100 + "%");
                        equipStatisticsList.remove(j);
                    }else{
                        equipStatisticsList.get(j).setUserdDurationStr(formatTime(userDurateion_j));
                        equipStatisticsList.get(j).setUserRate(rate);
                        equipStatisticsList.get(j).setUserdRateStr(rate*100 + "%");;
                    }
                }
            }
        }
        return equipStatisticsList;
    }

     /*
     * 毫秒转化时分秒毫秒
     */
    public String formatTime(Long ms) {
        Integer ss = 1000;
        Integer mi = ss * 60;
        Integer hh = mi * 60;
        Integer dd = hh * 24;

        Long day = ms / dd;
        Long hour = (ms - day * dd) / hh;
        Long minute = (ms - day * dd - hour * hh) / mi;
        Long second = (ms - day * dd - hour * hh - minute * mi) / ss;
        Long milliSecond = ms - day * dd - hour * hh - minute * mi - second * ss;
        
        StringBuffer sb = new StringBuffer();
        if(day > 0) {
            sb.append(day+"天");
        }
        if(hour > 0) {
            sb.append(hour+"小时");
        }
        if(minute > 0) {
            sb.append(minute+"分");
        }
        if(second > 0) {
            sb.append(second+"秒");
        }
        if(milliSecond > 0) {
            sb.append(milliSecond+"毫秒");
        }
        return sb.toString();
    }

    

  //测试代码
  
      public List getSessionTime() {
          try {
          CommandResult result =     mongoTemplate.executeCommand("{aggregate : 'sessions', pipeline : "
                  + "[{ $match : { logoffTime : {$exists:false} } },"
              //    + " { $group : { _id :logonIp,logonTime:{$sum:{logonTime.valueOf()}},logoffTime:{$sum:{logffTime.va}} } },"
                  + " {  $project : { _id : 0,logonHost : 1,logonIp : 1,logonTime : 1,extraData : 1,logoffTime : 1}}]}");
          System.out.println(result);    
              
          GroupBy groupBy = GroupBy.key("logonIp")
                  .initialDocument("{logonHost:'', sessionTime : 0, extraData : {}}")
                  .reduceFunction("function(doc,result){"
                          + "result.logonHost = doc.logonHost;"
                          + "var time = doc.logoffTime.valueOf() - doc.logonTime.valueOf();"
                          + "result.sessionTime +=time ;"
                          + "result.extraData = doc.extraData}");
          GroupByResults<Session> results = mongoTemplate.group(Criteria.where("logoffTime").exists(true),
                  "sessions", groupBy, Session.class);
          BasicDBList list = (BasicDBList)results.getRawResults().get("retval");  
          for (int i = 0; i < list.size(); i ++) {  
              BasicDBObject obj = (BasicDBObject)list.get(i);  
              System.out.println(obj.get("count"));  
          }  
          System.out.println(results);  
          }catch (Exception e) {
              System.out.println(e);
          }finally {
          try{
          MatchOperation matchOperation;
          
          matchOperation = new MatchOperation(Criteria.where("logonTime")
                      .lte(new SimpleDateFormat("yyyy-MM-dd").parse("2016-09-14"))
                      .gte(new SimpleDateFormat("yyyy-MM-dd").parse("2016-09-12"))
                      .andOperator(Criteria.where("logoffTime")
                      .lte(new SimpleDateFormat("yyyy-MM-dd").parse("2016-09-14"))
                      .gte(new SimpleDateFormat("yyyy-MM-dd").parse("2016-09-12")))
                      );
          
          GroupOperation groupOperation = new GroupOperation(Fields.fields("logonIp"));
          
          ProjectionOperation projectionOperation = new ProjectionOperation(Fields.fields("_id"));
          
          Aggregation aggregation = Aggregation.newAggregation(matchOperation,groupOperation,projectionOperation);
          
          AggregationResults<Object> groupResults 
              = mongoTemplate.aggregate(aggregation, "sessions", Object.class);
          
          List<Object> groupList =  groupResults.getMappedResults();
          for (Object object : groupList) {
              System.out.println(object.toString());
          }
          } catch (ParseException e) {
              e.printStackTrace();
          }
          }
          return null;
      }
    
}

//查询数据库
    private List<EquipStatistics> searchDB(Criteria criteria, String reduceFunction,
            String initial) {
        List<EquipStatistics> equipStatisticsList = new ArrayList<EquipStatistics>();
        EquipStatistics equipStatistics = null;
        GroupBy groupBy = GroupBy.key("logonIp")
                .initialDocument(initial)
                .reduceFunction(reduceFunction);
        
        GroupByResults<Session> results = mongoTemplate.group(criteria,
                "sessions", groupBy, Session.class);
        BasicDBList list = (BasicDBList)results.getRawResults().get("retval");  
        for (int i = 0; i < list.size(); i ++) {  
            equipStatistics = new EquipStatistics();
            BasicDBObject obj = (BasicDBObject)list.get(i);  
            equipStatistics.setIp(obj.getString("logonIp"));
            equipStatistics.setHostName(obj.getString("hostName"));
            equipStatistics.setEquipmentTypeName(obj.getString("equipmentTypeName"));
            equipStatistics.setUserDurateion(obj.getLong("userDurateion"));
            equipStatisticsList.add(equipStatistics);
        }
        return equipStatisticsList;
    }

分组查询主要使用org.springframework.data.mongodb.core.mapreduce.GroupBy这个spring中的类：

例：

GroupBy groupBy = GroupBy.key("logonIp")
.initialDocument(initial)
.reduceFunction(reduceFunction);
GroupByResults<T> results = mongoTemplate.group(criteria,
"sessions", groupBy, T.class);

GroupBy.key('key'): key是所进行分组字段的字段名；

initial : 初始化对象，可理解为最后查询返回的数据初始化；

reduceFunction： js函数，用于对返回的结果进行处理操作；

function(doc,result){}:

doc是根据查询条件(相当于where条件)获取的每一条数据，result是最后的查询结果，初始值就是initial对象；

查询操作：

mongoTemplate.group(criteria,"session", groupBy, T.class);

criteria：相当于SQL中的where条件；

session: 数据库中的表名；

groupBy: -以上；

T.class: 这里是数据库表对应的domain

 BasicDBList list = (BasicDBList)results.getRawResults().get("retval")

获取结果转为BasicDBList，"retval"是固定值，必须是它;

BasicDBObject obj = (BasicDBObject)list.get(i);  obj.getString("key");

key为initial中的key值，通过以上代码获取key值对应的value;

这只是其中一种用法......