正文
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;
} }
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;
这只是其中一种用法......
