最近做项目,完全基于mongodb,就连flowable的mysql也转成了mongodb,但是现在flowable的mongodb引擎是开发版,所以要填的坑也是很多。
头一次用mongo做项目
1.mongodbtemplate复杂查询
普通的查询就不说了,网上一堆一堆的,之前让我犯愁的好几个or 好几个and放一起该怎么查
解决办法:把or条件整理到数组中,用oroperater,
最后把所有条件整理到数组中,用andoperator把list拼起来
其中没新增一个条件都要用新的 new Criteria();
实例代码如下(包括模糊查询和分页查询以及各种and,or)
public static List<Criteria> condition(Map<String, Object> approvalMap){
List<Criteria> criteriaList = new ArrayList<>();
//时间区间
DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
Date startDate = null;
Date endDate = null;
//业务发起时间段:开始时间 yyyy-MM-dd
Object startTime1 = approvalMap.get("startTime");
String startTime = null;
if(startTime1!=null){
Criteria criteria = new Criteria();
startTime = (String) approvalMap.get("startTime");
try {
startDate = format.parse(startTime);
criteria.and("startTime").gte(startDate);
criteriaList.add(criteria);
} catch (ParseException e) {
e.printStackTrace();
}
}
//业务发起时间段:结束时间 yyyy-MM-dd
Object endTime1 = approvalMap.get("endTime");
String endTime = null;
if(endTime1!=null){
Criteria criteria = new Criteria();
endTime = (String) approvalMap.get("endTime");
try {
endDate = format.parse(endTime);
criteria.and("startTime").lt(endDate);
criteriaList.add(criteria);
} catch (ParseException e) {
e.printStackTrace();
}
}
//动态拼接查询条件
//业务类型(流程号)
String processDefinKey = (String) approvalMap.get("processDefinKey");
if (!StringUtils.isEmpty(processDefinKey)){
Criteria criteria = new Criteria();
criteria.and("processDefinKey").is(processDefinKey);
criteriaList.add(criteria);
}
//业务信息,关键词检索
String searchKey = (String) approvalMap.get("searchKey");
if(StringUtils.isNotEmpty(searchKey)){
Criteria criteria = new Criteria();
Pattern pattern=Pattern.compile("^.*"+searchKey+".*$", Pattern.CASE_INSENSITIVE);
criteria.and("businessList.value").regex(pattern);
criteriaList.add(criteria);
}
//来自(业务渠道)这个前端传来的是渠道编码 所以去查询sourceChannelType
String sourceChannel = (String) approvalMap.get("sourceChannel");
if (!StringUtils.isEmpty(sourceChannel)){
Criteria criteria = new Criteria();
criteria.and("sourceChannelType").is(sourceChannel);
criteriaList.add(criteria);
}
//发起人 发起人id
String startUser = (String) approvalMap.get("startUser");
if (!StringUtils.isEmpty(startUser)){
Criteria criteria = new Criteria();
criteria.and("startUser").is(startUser);
criteriaList.add(criteria);
}
//任务状态 1-已办结 2-办理中 3-已撤销 是一个数组
Object taskState = approvalMap.get("taskState");
if(taskState!=null){
List<String> taskStateArray = (ArrayList<String>) taskState;
Criteria[] criteriaArray = new Criteria[taskStateArray.size()];
Criteria criteriaTaskState = new Criteria();
for(int i=0;i<taskStateArray.size();i++){
criteriaArray[i] = Criteria.where("taskState").is(taskStateArray.get(i));
}
criteriaTaskState.orOperator(criteriaArray);
criteriaList.add(criteriaTaskState);
}
//业务状态(数组):3-驳回 2-撤回 ["3", "2"]
Object state = approvalMap.get("state");
if(state!=null){
List<String> stateArray = (ArrayList<String>) state;
Criteria[] criteriaArray = new Criteria[stateArray.size()];
Criteria criteriaState = new Criteria();
for(int i=0;i<stateArray.size();i++){
criteriaArray[i] = Criteria.where("state").is(stateArray.get(i));
}
criteriaState.orOperator(criteriaArray);
criteriaList.add(criteriaState);
}
//任务时限(数组):0-正常 1-逾期 2-预警
Object limitType = approvalMap.get("limitType");
if(limitType!=null){
List<String> limitTypeArray = (ArrayList<String>) limitType;
Criteria[] limitArray = new Criteria[limitTypeArray.size()];
Criteria criteriaLimit = new Criteria();
for(int i=0;i<limitTypeArray.size();i++){
limitArray[i] = Criteria.where("limitType").is(limitTypeArray.get(i));
}
criteriaLimit.orOperator(limitArray);
criteriaList.add(criteriaLimit);
}
/* Criteria[] criteriaEndArray = new Criteria[criteriaList.size()];
for(int i=0;i<criteriaList.size();i++){
criteriaEndArray[i] = criteriaList.get(i);
}
Criteria criteriaEnd = new Criteria();
criteriaEnd.andOperator(criteriaEndArray);*/
return criteriaList;
}
//拼写查询条件
List<Criteria> criteriaList = ChangeConditionUtil.condition(approvalMap);
//候选人中当前登陆用户 他才能办理
String operCode = (String) data.getCommon().get("operCode");
//我取到的授权人id应该是一个List<String> 因为可能有多个人把任务授权给我
List<String> list = new ArrayList<>();//此处为从登陆信息中取到的授权人idList
list.add(operCode);//1.当前登陆人和授权人都要查在不在候选人里边
List<String> delegationList = ChangeConditionUtil.findDelegation(data);
if(delegationList.size()>0){
list.addAll(delegationList);
}
//四种情况都是or的关系 所以长度是四种情况之和
Criteria[] dealUser = new Criteria[list.size()+1+delegationList.size()+1];
Criteria criteriaDeal = new Criteria();
for(int i=0;i<list.size();i++){
dealUser[i] = Criteria.where("candidateList").is(list.get(i));
}
//2.或者当前登陆用户是处理人assinge
dealUser[list.size()] = Criteria.where("assignee").is(operCode);
//3.转授权人是办理人---判断转授权人是否为空---不为空查assinge
for(int j=0;j<delegationList.size();j++){
dealUser[list.size()+1+j] = Criteria.where("assignee").is(delegationList.get(j));
}
// 4.当前登陆用户是转授权人---用登陆id查delegationUser
dealUser[list.size()+1+delegationList.size()] = Criteria.where("delegationUser").is(operCode);
criteriaDeal.orOperator(dealUser);
criteriaList.add(criteriaDeal);
Criteria[] criteriaEndArray = new Criteria[criteriaList.size()];
for(int i=0;i<criteriaList.size();i++){
criteriaEndArray[i] = criteriaList.get(i);
}
Criteria criteriaEnd = new Criteria();
criteriaEnd.andOperator(criteriaEndArray);
Query query = new Query(criteriaEnd);
log.info(query.toString());
//计算总数
long total = mongoTemplate.count(query, WF601Bean.class);
//查询结果集
List<WF601Bean> wf601List = mongoTemplate.find(query.with(pageable), WF601Bean.class);
//是否可办理
canHandled(wf601List,operCode);
Page<WF601Bean> wf601PageList = new PageImpl(wf601List, pageable, total);
return wf601PageList;
2.mongotemplate的分页
以上代码里也有分页查询,以下把分因为查询单独拿出来
//当前页码,默认为1 mongodb从0开始
int currentPageBig = (int)approvalMap.get("currentPage");
int currentPage = 0;
if(currentPageBig>=1){
currentPage = currentPageBig-1;//
}
//每页条数
int pageSize = (int) approvalMap.get("pageSize");
//排序
Sort sort = Sort.by(Sort.Direction.DESC, "startTime");
//分页
Pageable pageable = PageRequest.of(currentPage, pageSize, sort);
//计算总数
long total = mongoTemplate.count(query, WF601Bean.class);
//查询结果集
List<WF601Bean> wf601List = mongoTemplate.find(query.with(pageable), WF601Bean.class);
//是否可办理
canHandled(wf601List,operCode);
Page<WF601Bean> wf601PageList = new PageImpl(wf601List, pageable, total);
3.mongotemplate的分类统计
之前困扰我的是排序,后来发现,你分类统计的字段当中有你要排序的字段才行,没有的话据不能按照这个字段排序
Aggregation aggregation4 =
Aggregation.newAggregation(
Aggregation.match(criteriaEnd),
Aggregation.group("processDefinName","processDefinKey").count().as("resultCount"),//返回结果数量
Aggregation.sort(new Sort(Sort.Direction.DESC,"processDefinKey"))
// Aggregation.sort(Sort.Direction.DESC, "startTime")
);
AggregationResults<Map> aggregate =
mongoTemplate.aggregate(aggregation4, WF601Bean.class,Map.class);
4.分类统计之后想要得到 每种类型的详细信息,
需要把每条统计结果加到之前的查询条件里再查询一次,这个跟mysql一样
//接着上一块代码
List<Map> mappedResults = aggregate.getMappedResults();
for(Map map:mappedResults){
//取值
// String processDefinName = (String) map.get("processDefinName");
// String resultKey = (String) map.get("processDefinKey");
String resultKey = (String) map.get("_id");
int count = (Integer) map.get("resultCount");
//把业务流程号加到条件中再去查询
List<Criteria> newList = ChangeConditionUtil.condition(approvalMap);
Criteria criteria = new Criteria();
criteria.and("processDefinKey").is(resultKey);
newList.add(criteria);
Criteria[] criteriaArrayNew = new Criteria[newList.size()];
for(int i=0;i<newList.size();i++){
criteriaArrayNew[i] = newList.get(i);
}
Criteria criteriaEndNew = new Criteria();
criteriaEndNew.andOperator(criteriaArrayNew);
Query queryNew = new Query(criteriaEndNew);
List<WF601Bean> wf601NewList = mongoTemplate.find(queryNew, WF601Bean.class);
//检查 对比list长度和分类统计数量是否相等
System.err.println(count==wf601NewList.size()?true:false);
System.err.println("分类统计数量"+count+"----------------------------------list长度wf601NewList"+wf601NewList.size());
taskSummaryList.add( p70003List(wf601NewList));
}
5.mongodtemplat的update方法
public void update(String processInstanceId,String taskState){
Query query = new Query(Criteria.where("processInstanceId").is(processInstanceId));
mongoTemplate.updateFirst(query, Update.update("taskState",taskState),WF603Bean.class);
}
//其中"taskstate"是你要更新的字段
以上,实体中都有文档名,所以执行的时候才知道你要更新哪个表,但是我如果不通过实体,怎么找表?
6.mongotemplate在没有实体的情况下查询表
//用MongoDatabase mongoDatabase;
public List<Execution> findExecutionsByNativeQuery(Map<String, Object> parameterMap) {
throw new UnsupportedOperationException();
List<Bson> bsons = new ArrayList<>();
for(Map.Entry<String, Object> vo : parameterMap.entrySet()){
Bson eq = Filters.eq(vo.getKey(), vo.getValue());
bsons.add(eq);
}
Bson and = Filters.and(bsons);
return getMongoDbSession().find(COLLECTION_EXECUTIONS,and);
}
7.mongodbtemplate的save方法是主键存在时更新,主键不存在时添加
跟mysql一样
8.还有一个跟mongo没关系,就是发现的一个
//不为空就把待办追加进去 并且更新业务信息
wF603Bean.getWf603BeanList().add(wf601Bean);
因为引用地址一致,这样在你报错wf603Bean的时候就会把新增的wf601Bean也保存起来
9.主键设置
@Document是mongo注解 collection是表名 @Indexed是索引 @Id是主键 mongodb默认主键是_id字段 如果在实体上提娜佳@Id注解 那么这个表的主键就是当前字段,但是在mongo中的显示还是_id,但是我们在程序中查找以及其他操作还是按照processInstanceId操作
10.图形化工具
以上是mongotemplate我第一次使用发现的问题以及我觉得对我来说需要记录的点,然后再说mongo图形化连接工具
开始我用studio 3t ,后来破解失败,就直接用了robo 3T
建议每次在程序中把query条件打印出来放在robo中查询
11.mongo关联查询
11.1.先不带分页条件,查总数
//把oa221接口脱敏关联表主表 oa204接口信息表作为作为从表 两者是一一对应的关系
LookupOperation lookup = LookupOperation.newLookup().
from("oa204").
localField("interfaceId").//主表字段
foreignField("itfId").//次表字段 oa221的interfaceId和oa204的itfId对应
as("groups");
UnwindOperation unwind = unwind("groups");
//主表
Criteria criteria1 =new Criteria();
criteria1.and("groups.appId").is(appId);
AggregationOperation match1 = match(criteria1);
//不分页 查询总数
Aggregation aggregationNoPage = Aggregation.
newAggregation(lookup,match1,unwind);
int total = mongoTemplate.aggregate(aggregationNoPage, "oa221", Map.class).getMappedResults().size();
11.2.分页
new SkipOperation(skip),new LimitOperation(pageSize)
先不带分页条件查询,查出总数,如1
再带上分页条件查询,但是要自己写查询第几页到第几页
int skip = currentPage == 1?0: (currentPage-1)*pageSize;
String appId = (String)batchListDTO.getCondition().get("appId");
//把oa204接口信息表作为主表 oa221接口脱敏关联表作为从表 两者是一一对应的关系
LookupOperation lookup = LookupOperation.newLookup().
from("oa204").
localField("interfaceId").//主表字段
foreignField("itfId").//次表字段 oa221的interfaceId和oa204的itfId对应
as("groups");
UnwindOperation unwind = unwind("groups");
//主表
Criteria criteria1 =new Criteria();
criteria1.and("groups.appId").is(appId);
AggregationOperation match1 = match(criteria1);
//不分页 查询总数
Aggregation aggregationNoPage = Aggregation.
newAggregation(lookup,match1,unwind);
int total = mongoTemplate.aggregate(aggregationNoPage, "oa221", Map.class).getMappedResults().size();
// 构建 Aggregation 分页 查询结果集
Aggregation aggregationPage = Aggregation.
newAggregation(lookup,match1,unwind,new SkipOperation(skip),new LimitOperation(pageSize));
List<Map> paramRules = mongoTemplate.aggregate(aggregationPage, "oa221", Map.class).getMappedResults();