以前用MongoDB数据库都是简单的查询，直接用Query就可以，最近项目中用到了分组查询，完全不一样。第一次遇到，搞了好几天终于有点那意思了。
先上代码：
代码语言：javascript复制 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;
这只是其中一种用法......
