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

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

先上代码：

  1 import java.math.BigDecimal;
  2 import java.text.ParseException;
  3 import java.text.SimpleDateFormat;
  4 import java.util.ArrayList;
  5 import java.util.Date;
  6 import java.util.List;
  7 
  8 import org.slf4j.Logger;
  9 import org.slf4j.LoggerFactory;
 10 import org.springframework.beans.factory.annotation.Autowired;
 11 import org.springframework.data.mongodb.core.MongoTemplate;
 12 import org.springframework.data.mongodb.core.aggregation.Aggregation;
 13 import org.springframework.data.mongodb.core.aggregation.AggregationResults;
 14 import org.springframework.data.mongodb.core.aggregation.Fields;
 15 import org.springframework.data.mongodb.core.aggregation.GroupOperation;
 16 import org.springframework.data.mongodb.core.aggregation.MatchOperation;
 17 import org.springframework.data.mongodb.core.aggregation.ProjectionOperation;
 18 import org.springframework.data.mongodb.core.mapreduce.GroupBy;
 19 import org.springframework.data.mongodb.core.mapreduce.GroupByResults;
 20 import org.springframework.data.mongodb.core.query.Criteria;
 21 import org.springframework.stereotype.Service;
 22 
 23 import com.mongodb.BasicDBList;
 24 import com.mongodb.BasicDBObject;
 25 import com.mongodb.CommandResult;
 26 
 27 @Service
 28 public class EquipmentRepository implements EquipmentRepository{
 29 
 30     private static final Logger logger = LoggerFactory.getLogger(EquipmentRepository.class);
 31     
 32     @Autowired
 33     MongoTemplate mongoTemplate;
 34 
 35     
 36     
 37     /**
 38      *<p>从登陆信息表中根据IP统计设备使用时间</p>
 39      * @param hostName 设备名称
 40      * @param startTime 统计开始时间
 41      * @param endTime 统计结束时间
 42      * @return 统计信息
 43      */    
 44     @Override
 45     public List<EquipStatistics> statisticTime(String hostName, Date startTime, Date endTime) {
 46         
 47         List<EquipStatistics> equipStatisticsList = new ArrayList<EquipStatistics>();
 48         
 49         try {
 50             
 51         String initial = "{hostName:'' ,equipmentTypeName:'', userDurateion : 0,count:0,"
 52                 + "startTime:"+startTime.getTime()+",endTime:"+endTime.getTime()+",nowTime:"+new Date().getTime()+"}";
 53         
 54         String reduceFunction =  "function(doc,result){"
 55                 + "if(doc.extraData.hostName)   { result.hostName = doc.extraData.hostName;}"
 56                 + "if(doc.extraData.deviceType) {result.equipmentTypeName = doc.extraData.deviceType;}"
 57                 + "var time = doc.logoffTime.valueOf() - doc.logonTime.valueOf();"
 58                 + "result.userDurateion +=time;"
 59                 +" result.count+=1;"
 60                 + "}";
 61         
 62         //时间的计算分四种情况
 63         List<EquipStatistics> equipStatisticsListTemp =null;
 64         for (int i = 0; i < 4; i++) {
 65             switch (i) {
 66             case 0:
 67                 //登出时间在开始和结束之间,登录在开始和结束之间的(登出-登录)
 68                 Criteria criteria = Criteria.where("logonIp").exists(true);
 69                 if(hostName !=null && !"".equals(hostName.trim())){
 70                      criteria.and("extraData.hostName").regex(hostName);
 71                 }
 72                 criteria.and("logoffTime").lt(endTime).gt(startTime).and("logonTime").lt(endTime).gt(startTime);
 73                 equipStatisticsListTemp =  searchDB(criteria, reduceFunction, initial);
 74                 
 75                 break;
 76             case 1:
 77                 //1、 登出时间为空或 登出时间在结束之后， 登录时间在开始与结束之间的(结束-登录)
 78                 reduceFunction =  "function(doc,result){"
 79                         + "if(doc.extraData.hostName)   { result.hostName = doc.extraData.hostName;}"
 80                         + "if(doc.extraData.deviceType) {result.equipmentTypeName = doc.extraData.deviceType;}"
 81                         + "var time = result.endTime - doc.logonTime.valueOf();"
 82                         + "result.userDurateion +=time;"
 83                         +" result.count+=1;"
 84                         + "}";
 85                 Criteria criteria1 = Criteria.where("logonIp").exists(true);
 86                 if(hostName !=null && !"".equals(hostName.trim())){
 87                      criteria1.and("extraData.hostName").regex(hostName);
 88                 }
 89                 
 90                 criteria1.andOperator(Criteria.where("logonTime").lt(endTime).gt(startTime)
 91                         .andOperator(Criteria.where("logoffTime").exists(false).orOperator(Criteria.where("logoffTime").gt(endTime))));
 92                 equipStatisticsListTemp =  searchDB(criteria1, reduceFunction, initial);
 93                 break;
 94             case 2:
 95                 //2、 登出时间为空， 登出时间在结束之后  ，登录时间在开始之前的 (结束-开始) 
 96                 reduceFunction =  "function(doc,result){"
 97                         + "if(doc.extraData.hostName)   { result.hostName = doc.extraData.hostName;}"
 98                         + "if(doc.extraData.deviceType) {result.equipmentTypeName = doc.extraData.deviceType;}"
 99                         + "var time = result.endTime - result.startTime;"
100                         + "result.userDurateion +=time;"
101                         +" result.count+=1;"
102                         + "}";
103                 Criteria criteria2 = Criteria.where("logonIp").exists(true);
104                 if(hostName !=null && !"".equals(hostName.trim())){
105                      criteria2.and("extraData.hostName").regex(hostName);
106                 }
107                 criteria2.andOperator(Criteria.where("logonTime").lt(startTime)
108                         .andOperator(Criteria.where("logoffTime").exists(false).orOperator(Criteria.where("logoffTime").gt(endTime))));
109                 equipStatisticsListTemp =  searchDB(criteria2, reduceFunction, initial);
110                 break;
111             case 3:
112                 //4、 登出时间在开始和结束之间，登录时间在开始之前的(登出-开始)
113                 reduceFunction =  "function(doc,result){"
114                         + "if(doc.extraData.hostName)   { result.hostName = doc.extraData.hostName;}"
115                         + "if(doc.extraData.deviceType) {result.equipmentTypeName = doc.extraData.deviceType;}"
116                         + "var time = doc.logoffTime.valueOf() - result.startTime;"
117                         + "result.userDurateion +=time;"
118                         +" result.count+=1;"
119                         + "}";
120                 Criteria criteria3 = Criteria.where("logonIp").exists(true);
121                 if(hostName !=null && !"".equals(hostName.trim())){
122                      criteria3.and("extraData.hostName").regex(hostName);
123                 }
124                 criteria3.and("logonTime").lt(startTime).and("logoffTime").lt(endTime).gt(startTime);
125                 equipStatisticsListTemp =  searchDB(criteria3, reduceFunction, initial);
126                 break;
127             default:
128                 break;
129             }
130             equipStatisticsList.addAll(equipStatisticsListTemp);
131             equipStatisticsListTemp = null;
132         }
133         
134         //去除重复数据 时长相加 赋值使用率
135         equipStatisticsList = addDuration(equipStatisticsList,daysBetween(startTime,endTime));
136         } catch (Throwable e) {
137             logger.error("统计设备使用信息失败："+e.getMessage(), e);
138             throw new AssetRuntimeException(e);
139         }
140         
141         
142         return equipStatisticsList;
143     }
144     
145     //获取相隔天数
146     private int daysBetween(Date startTime, Date endTime) {
147         return (int)((endTime.getTime()-startTime.getTime())/(1000 * 86400));
148     }
149 
150 
151     //查询数据库
152     private List<EquipStatistics> searchDB(Criteria criteria, String reduceFunction,
153             String initial) {
154         List<EquipStatistics> equipStatisticsList = new ArrayList<EquipStatistics>();
155         EquipStatistics equipStatistics = null;
156         GroupBy groupBy = GroupBy.key("logonIp")
157                 .initialDocument(initial)
158                 .reduceFunction(reduceFunction);
159         
160         GroupByResults<Session> results = mongoTemplate.group(criteria,
161                 "sessions", groupBy, Session.class);
162         BasicDBList list = (BasicDBList)results.getRawResults().get("retval");  
163         for (int i = 0; i < list.size(); i ++) {  
164             equipStatistics = new EquipStatistics();
165             BasicDBObject obj = (BasicDBObject)list.get(i);  
166             equipStatistics.setIp(obj.getString("logonIp"));
167             equipStatistics.setHostName(obj.getString("hostName"));
168             equipStatistics.setEquipmentTypeName(obj.getString("equipmentTypeName"));
169             equipStatistics.setUserDurateion(obj.getLong("userDurateion"));
170             equipStatisticsList.add(equipStatistics);
171         }
172         return equipStatisticsList;
173     }
174     
175 
176     //去重
177     private List<EquipStatistics> addDuration(List<EquipStatistics> equipStatisticsList,int days) {
178         
179         BigDecimal base = new BigDecimal(days*8*60*60*1000+"");
180         
181         if(equipStatisticsList!=null){
182             for (int i = 0; i < equipStatisticsList.size()-1; i++) {
183                 long userDurateion_i = equipStatisticsList.get(i).getUserDurateion();
184                 equipStatisticsList.get(i).setUserdDurationStr(formatTime(userDurateion_i));
185                 //
186                 BigDecimal userDur_i = new BigDecimal(userDurateion_i);
187                 double rate = userDur_i.divide(base, 4, BigDecimal.ROUND_HALF_UP).doubleValue();
188                 equipStatisticsList.get(i).setUserRate(rate);
189                 equipStatisticsList.get(i).setUserdRateStr(rate*100 + "%");
190                 for(int j = equipStatisticsList.size()-1; j>i;j--){
191                     long userDurateion_j = equipStatisticsList.get(j).getUserDurateion();
192                     BigDecimal userDur_j = new BigDecimal(userDurateion_j);
193                     rate = userDur_j.divide(base, 4, BigDecimal.ROUND_HALF_UP).doubleValue();
194                     if(equipStatisticsList.get(i).getIp().equals(equipStatisticsList.get(j).getIp())){
195                         equipStatisticsList.get(i).setUserDurateion(userDur_i.add(userDur_j).longValue());
196                         equipStatisticsList.get(i).setUserdDurationStr(formatTime(userDur_i.add(userDur_j).longValue()));
197                         rate = userDur_i.add(userDur_j).divide(base, 4, BigDecimal.ROUND_HALF_UP).doubleValue();
198                         equipStatisticsList.get(i).setUserRate(rate);
199                         equipStatisticsList.get(i).setUserdRateStr(rate*100 + "%");
200                         equipStatisticsList.remove(j);
201                     }else{
202                         equipStatisticsList.get(j).setUserdDurationStr(formatTime(userDurateion_j));
203                         equipStatisticsList.get(j).setUserRate(rate);
204                         equipStatisticsList.get(j).setUserdRateStr(rate*100 + "%");;
205                     }
206                 }
207             }
208         }
209         return equipStatisticsList;
210     }
211 
212      /*
213      * 毫秒转化时分秒毫秒
214      */
215     public String formatTime(Long ms) {
216         Integer ss = 1000;
217         Integer mi = ss * 60;
218         Integer hh = mi * 60;
219         Integer dd = hh * 24;
220 
221         Long day = ms / dd;
222         Long hour = (ms - day * dd) / hh;
223         Long minute = (ms - day * dd - hour * hh) / mi;
224         Long second = (ms - day * dd - hour * hh - minute * mi) / ss;
225         Long milliSecond = ms - day * dd - hour * hh - minute * mi - second * ss;
226         
227         StringBuffer sb = new StringBuffer();
228         if(day > 0) {
229             sb.append(day+"天");
230         }
231         if(hour > 0) {
232             sb.append(hour+"小时");
233         }
234         if(minute > 0) {
235             sb.append(minute+"分");
236         }
237         if(second > 0) {
238             sb.append(second+"秒");
239         }
240         if(milliSecond > 0) {
241             sb.append(milliSecond+"毫秒");
242         }
243         return sb.toString();
244     }
245 
246     
247 
248   //测试代码
249   
250       public List getSessionTime() {
251           try {
252           CommandResult result =     mongoTemplate.executeCommand("{aggregate : 'sessions', pipeline : "
253                   + "[{ $match : { logoffTime : {$exists:false} } },"
254               //    + " { $group : { _id :logonIp,logonTime:{$sum:{logonTime.valueOf()}},logoffTime:{$sum:{logffTime.va}} } },"
255                   + " {  $project : { _id : 0,logonHost : 1,logonIp : 1,logonTime : 1,extraData : 1,logoffTime : 1}}]}");
256           System.out.println(result);    
257               
258           GroupBy groupBy = GroupBy.key("logonIp")
259                   .initialDocument("{logonHost:'', sessionTime : 0, extraData : {}}")
260                   .reduceFunction("function(doc,result){"
261                           + "result.logonHost = doc.logonHost;"
262                           + "var time = doc.logoffTime.valueOf() - doc.logonTime.valueOf();"
263                           + "result.sessionTime +=time ;"
264                           + "result.extraData = doc.extraData}");
265           GroupByResults<Session> results = mongoTemplate.group(Criteria.where("logoffTime").exists(true),
266                   "sessions", groupBy, Session.class);
267           BasicDBList list = (BasicDBList)results.getRawResults().get("retval");  
268           for (int i = 0; i < list.size(); i ++) {  
269               BasicDBObject obj = (BasicDBObject)list.get(i);  
270               System.out.println(obj.get("count"));  
271           }  
272           System.out.println(results);  
273           }catch (Exception e) {
274               System.out.println(e);
275           }finally {
276           try{
277           MatchOperation matchOperation;
278           
279           matchOperation = new MatchOperation(Criteria.where("logonTime")
280                       .lte(new SimpleDateFormat("yyyy-MM-dd").parse("2016-09-14"))
281                       .gte(new SimpleDateFormat("yyyy-MM-dd").parse("2016-09-12"))
282                       .andOperator(Criteria.where("logoffTime")
283                       .lte(new SimpleDateFormat("yyyy-MM-dd").parse("2016-09-14"))
284                       .gte(new SimpleDateFormat("yyyy-MM-dd").parse("2016-09-12")))
285                       );
286           
287           GroupOperation groupOperation = new GroupOperation(Fields.fields("logonIp"));
288           
289           ProjectionOperation projectionOperation = new ProjectionOperation(Fields.fields("_id"));
290           
291           Aggregation aggregation = Aggregation.newAggregation(matchOperation,groupOperation,projectionOperation);
292           
293           AggregationResults<Object> groupResults 
294               = mongoTemplate.aggregate(aggregation, "sessions", Object.class);
295           
296           List<Object> groupList =  groupResults.getMappedResults();
297           for (Object object : groupList) {
298               System.out.println(object.toString());
299           }
300           } catch (ParseException e) {
301               e.printStackTrace();
302           }
303           }
304           return null;
305       }
306     
307 }

EquipmentRepository.java

//查询数据库
    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;

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

posted @ 2016-09-14 14:51 凉城阅读(5907) 评论(0) 编辑收藏举报

刷新页面返回顶部

凉城

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

公告