以前用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 }
//查询数据库 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;
这只是其中一种用法......