/**
* 统一查询
*
* @param map 前端提交的查询参数
* @param stockKey 仓库的字段名
* @param fieldList 查询字段
* @param countFieldMap 统计字段
* @param sortMap 排序字段
* @param collectionName 表名
* @return
*/
public Map query(Map map, String stockKey, List<String> fieldList, Map<String, Integer> countFieldMap, Map<String, Integer> sortMap, String collectionName) {
//默认查询
QueryUtil queryUtil = defautQuery(map, stockKey);
//需要查询的字段
queryUtil.setGroupField(fieldList);
//默认返回统计字段
queryUtil.setCountField(countFieldMap);
//返回统计字段
queryUtil.setReturnField(fieldList);
//排序字段
queryUtil.setSortField(sortMap);
//查询
List queryList = queryUtil.queryAssemble();
List<Map<String, Object>> reList = publicService.aggregateByShell(collectionName, queryList);
HashMap<String, BigDecimal> countMap = new HashMap<>();
countFieldMap.forEach((key, Value) -> {
countMap.put(key + "Total", new BigDecimal(0));
});
countMap.remove("countTotal");
LinkedHashMap<String, Object> reMap = new LinkedHashMap<>();
if (reList.size() >= 1) {
Map<String, Object> maps = reList.get(0);
List<Map> data = (List<Map>) maps.get("data");
//修正_id
for (int i = 0; i < data.size(); i++) {
Map mp = data.get(i);
String id = String.valueOf(mp.get("id"));
if (!id.equals("null")) {
mp.put(CommonConstants.TABLE_ID, id);
data.set(i, mp);
}
}
maps.put("data", data);
reMap.put("page", maps);
for (int i = 0; i < data.size(); i++) {
int j = i;
countMap.forEach((key, Value) -> {
BigDecimal bigDecimal = countMap.get(key);
bigDecimal = bigDecimal.add(getBigDecimal(data.get(j).get(key.replaceAll("Total", ""))));
countMap.put(key, getBigDecimal(bigDecimal));
});
}
}
reMap.put("count", countMap);
return reMap;
}
查询语句
import com.alibaba.fastjson.JSON;
import com.sckj.basemongodbtool.commonconstants.CommonConstants;
import lombok.Setter;
import org.springframework.stereotype.Component;
import java.util.*;
@Component
public class QueryUtil {
private static final String GT = "$gt"; //大于
private static final String GTE = "$gte"; //大于等于
private static final String LT = "$lt"; //小于
private static final String LTE = "$lte"; //小于等于
private static final String NE = "$ne"; //不等于
private static final String MATCH = "$match"; //匹配关键字
private static final String GROUP = "$group"; //分组关键字
private static final String FACET = "$facet"; //部份关键字
private static final String COUNT = "$count"; //统计加关键字
private static final String SORT = "$sort"; //统计关键字
private String gtName = "gt";
private String gtValue = "gt";
private String gteName = "gte";
private String gteValue = "gte";
private String ltName = "lt";
private String ltValue = "lt";
private String lteName = "lte";
private String lteValue = "lt";
private String neName = "ne";
private String neValue = "lt";
@Setter
private Integer page = 1;//查询条数
@Setter
private Integer pageSize = 10;//跳过条数
@Setter
private Map<String, List<String>> matchIn;//查询条件in {查询字段:[字段值]}
@Setter
private List<Map<String, Object>> judgeNumber;//判断数字
@Setter
private List<String> groupField;//分组字段
@Setter
private Map<String, Integer> countField;//统计字段 key为查询字段,value 0 或1 0为字段本身 1为计数
@Setter
private List<String> returnField;//返回字段
@Setter
private Map<String, Integer> sortField;//排序字段
/**
* 组装多条件查询
*
* @param matchIn
* @return
*/
public LinkedHashMap<String, Object> mapIn(Map<String, List<String>> matchIn) {
LinkedHashMap<String, Object> reMap = new LinkedHashMap<>();
matchIn.forEach((key, value) -> {
reMap.put(key, inFieldToMap(value));
});
return reMap;
}
/**
* 组装语句
*
* @return
*/
public List queryAssemble() {
//查询条件
List<String> queryList = new ArrayList<>();
LinkedHashMap<String, Object> queryMap = new LinkedHashMap<>();
LinkedHashMap<String, Object> matchMap = new LinkedHashMap<>();
if (matchIn.size() != 0) {
matchMap = mapIn(matchIn);
}
if (judgeNumber != null && !judgeNumber.equals("null") && judgeNumber.size() != 0) {
for (int i = 0; i < judgeNumber.size(); i++) {
matchMap.putAll(judgeNumber.get(i));
}
}
if (matchMap.size() != 0) {
//拼接
queryMap.put(MATCH, matchMap);
String match = JSON.toJSONString(queryMap);
System.out.println(match);
queryList.add(match);
}
//查询字段
LinkedHashMap<String, Object> groupMap = new LinkedHashMap<>();
LinkedHashMap<String, Object> groupMapAll = new LinkedHashMap<>();
//返回查询字段
LinkedHashMap reMap = returnFieldToMap(returnField);
//需要查询的字段
if (groupField.size() != 0) {
groupMap.put(CommonConstants.TABLE_ID, queryFieldToMap(groupField));
}
//需要统计的字段
if (countField.size() != 0) {
countField.forEach((key, Value) -> {
groupMap.put(key, countFieldToMap(key, Value));
reMap.put(key, 1);
});
}
groupMapAll.put(GROUP, groupMap);
String group = JSON.toJSONString(groupMapAll);
System.out.println(group);
queryList.add(group);
//返回统计字段
reMap.put(CommonConstants.TABLE_ID, 0);
HashMap<Object, Object> projectMap = new HashMap<>();
projectMap.put("$project", reMap);
String project = JSON.toJSONString(projectMap);
System.out.println(project);
queryList.add(project);
//排序字段
LinkedHashMap<Object, Object> sortMap = new LinkedHashMap<>();
sortField.forEach((key, Value) -> {
sortMap.put(SORT, sortFieldToMap(key, Value));
});
String sort = JSON.toJSONString(sortMap);
System.out.println(sort);
queryList.add(sort);
//分页查询
LinkedHashMap<String, Object> facetMap = new LinkedHashMap<>();
LinkedHashMap<String, Object> pageMap = new LinkedHashMap<>();
ArrayList<Map> totalList = new ArrayList<>();
LinkedHashMap<String, Object> totalMap = new LinkedHashMap<>();
//统计字段
totalMap.put(COUNT, "total");
totalList.add(totalMap);
pageMap.put("total", totalList);
//分页字段
ArrayList<Map> dataList = new ArrayList<>();
LinkedHashMap<String, Object> skipMap = new LinkedHashMap<>();
skipMap.put("$skip", (page - 1) * pageSize);//跳过数量
dataList.add(skipMap);
LinkedHashMap<String, Object> limitMap = new LinkedHashMap<>();
limitMap.put("$limit", pageSize);//查询数量
dataList.add(limitMap);
pageMap.put("data", dataList);
facetMap.put(FACET, pageMap);
String page = JSON.toJSONString(facetMap);
System.out.println(page);
queryList.add(page);
return queryList;
}
/**
* 数组字段
*
* @param list
* @return
*/
public static LinkedHashMap inFieldToMap(List<String> list) {
LinkedHashMap<String, Object> reMap = new LinkedHashMap<>();
reMap.put("$in", list);
return reMap;
}
/**
* 查询字段
*
* @param list
* @return
*/
public static LinkedHashMap queryFieldToMap(List<String> list) {
LinkedHashMap<Object, Object> map = new LinkedHashMap<>();
list.forEach(str -> {
if (str.equals("_id")){
map.put("id", "$" + str);
}else {
map.put(str, "$" + str);
}
});
return map;
}
/**
* 统计字段
*
* @param str
* @return
*/
public static LinkedHashMap countFieldToMap(String str, int i) {
LinkedHashMap<Object, Object> map = new LinkedHashMap<>();
if (i == 0) {
map.put("$sum", "$" + str);
}
if (i == 1) {
map.put("$sum", 1);
}
return map;
}
/**
* 返回字段处理
*
* @param list
* @return
*/
public static LinkedHashMap returnFieldToMap(List<String> list) {
LinkedHashMap<Object, Object> reMap = new LinkedHashMap<>();
list.forEach(str -> {
if (str.equals("_id")){
reMap.put("id", "$_id.id" );
}else {
reMap.put(str, "$_id." + str);
}
});
return reMap;
}
/**
* 排序字段处理
*
* @param str
* @param i
* @return
*/
public static LinkedHashMap sortFieldToMap(String str, int i) {
LinkedHashMap<Object, Object> reMap = new LinkedHashMap<>();
reMap.put(str, i);
return reMap;
}
}
对时间数字的处理
import lombok. Setter ;
import org. springframework. stereotype. Component ;
import java. util. HashMap ;
import java. util. Map ;
@Setter
@Component
public class NumberUtil {
String column = null ;
String gtValue = null ;
String gteValue = null ;
String ltValue = null ;
String lteValue = null ;
String neValue = null ;
private static final String GT = "$gt" ;
private static final String GTE = "$gte" ;
private static final String LT = "$lt" ;
private static final String LTE = "$lte" ;
private static final String NE = "$ne" ;
public Map < String , Object > getNumber ( ) {
HashMap < String , Object > reMap = new HashMap < > ( ) ;
HashMap < String , Object > oMap = new HashMap < > ( ) ;
if ( column != null ) {
if ( gtValue != null ) {
oMap. put ( GT, gtValue) ;
}
if ( gteValue != null ) {
oMap. put ( GTE, gteValue) ;
}
if ( ltValue != null ) {
oMap. put ( LT, ltValue) ;
}
if ( lteValue != null ) {
oMap. put ( LTE, lteValue) ;
}
if ( neValue != null ) {
oMap. put ( NE, neValue) ;
}
reMap. put ( column, oMap) ;
}
return reMap;
}
}
查询条件组装
import dcocd. custom. slsyxt. Util . DateUtil ;
import lombok. Data ;
import org. springframework. stereotype. Component ;
import java. util. ArrayList ;
import java. util. HashMap ;
import java. util. List ;
import java. util. Map ;
@Component
@Data
public class SalesEntity {
public static final String STOCK = "stock" ;
public static String PLATFORM = "platform" ;
public static String PARAGRAPH = "area" ;
public static String WIRE = "wire" ;
public static String START_TIME = "startTime" ;
public static String END_TIME = "endTime" ;
public static String TYPE = "type" ;
public static String OTHER = "other" ;
public Integer page = 1 ;
public Integer pageSize = 20 ;
public List stock = new ArrayList ( ) ;
public List wire = new ArrayList ( ) ;
public String startTime;
public String endTime;
public String type;
public Map < String , List < String > > other;
public static SalesEntity getQuery ( Map maps) {
SalesEntity salesEntity = new SalesEntity ( ) ;
if ( maps == null || maps. get ( "paraList" ) == null ) {
return salesEntity;
}
HashMap < Object , Object > map = new HashMap < > ( ) ;
map. put ( "page" , maps. get ( "page" ) ) ;
map. put ( "pageSize" , maps. get ( "pageSize" ) ) ;
List < Map > paraList = ( List < Map > ) maps. get ( "paraList" ) ;
if ( paraList != null ) {
paraList. forEach ( ( tmap) -> {
tmap. forEach ( ( key, Value ) -> {
map. put ( tmap. get ( "column" ) , tmap. get ( "value" ) ) ;
} ) ;
} ) ;
}
if ( map == null || map. size ( ) == 0 ) {
return salesEntity;
}
if ( map. get ( TYPE) != null ) {
salesEntity. type = map. get ( TYPE) . toString ( ) ;
}
if ( map. get ( OTHER) != null ) {
salesEntity. other = ( Map ) map. get ( OTHER) ;
}
if ( ( List ) map. get ( STOCK) != null ) {
salesEntity. stock = ( List ) map. get ( STOCK) ;
}
if ( ( List ) map. get ( WIRE) != null ) {
salesEntity. wire = ( List ) map. get ( WIRE) ;
}
Object st = map. get ( START_TIME) ;
if ( st != null ) {
salesEntity. startTime = DateUtil . localToUtc ( String . valueOf ( st) ) ;
}
Object et = map. get ( END_TIME) ;
if ( et != null ) {
salesEntity. endTime = DateUtil . localToUtc ( String . valueOf ( et) ) ;
}
Object page = map. get ( "page" ) ;
Object pageSize = map. get ( "pageSize" ) ;
if ( page != null && pageSize != null ) {
salesEntity. page = Integer . parseInt ( page. toString ( ) ) ;
salesEntity. pageSize = Integer . parseInt ( pageSize. toString ( ) ) ;
}
return salesEntity;
}
}
public QueryUtil defautQuery ( Map map, String name) {
QueryUtil queryUtil = new QueryUtil ( ) ;
SalesEntity query = salesEntity. getQuery ( map) ;
List stock = query. getStock ( ) ;
List wire = query. getWire ( ) ;
String startTime = query. getStartTime ( ) ;
String endTime = query. getEndTime ( ) ;
String type = query. getType ( ) ;
Map < String , List < String > > other = query. getOther ( ) ;
String deptCode = userUtil. getUserInfo ( ) . getDeptCode ( ) ;
Map < String , Object > shops = getShops ( deptCode) ;
List < String > shopList = ( List < String > ) shops. get ( "data" ) ;
HashMap < String , List < String > > match = new HashMap < > ( ) ;
if ( ! StringUtils . isEmpty ( stock) && stock. size ( ) != 0 && ! stock. equals ( "null" ) ) {
match. put ( name, stock) ;
} else if ( ! StringUtils . isEmpty ( shopList) && shopList. size ( ) != 0 && ! shopList. equals ( "null" ) ) {
match. put ( name, shopList) ;
}
if ( ! StringUtils . isEmpty ( wire) && wire. size ( ) != 0 && ! wire. equals ( "null" ) ) {
match. put ( "wire" , wire) ;
}
if ( ! StringUtils . isEmpty ( type) && type. length ( ) != 0 && ! type. equals ( "null" ) ) {
ArrayList < String > strings = new ArrayList < > ( ) ;
strings. add ( type) ;
match. put ( "type" , strings) ;
}
if ( ! StringUtils . isEmpty ( other) && other. size ( ) != 0 && ! other. equals ( "null" ) ) {
other. forEach ( ( key, value) -> {
match. put ( "key" , value) ;
} ) ;
}
queryUtil. setMatchIn ( match) ;
NumberUtil numberUtil = new NumberUtil ( ) ;
if ( ! StringUtils . isEmpty ( startTime) && startTime != null && ! startTime. equals ( "null" ) ) {
numberUtil. setColumn ( "ctime" ) ;
numberUtil. setGteValue ( startTime) ;
}
if ( ! StringUtils . isEmpty ( endTime) && endTime != null && ! endTime. equals ( "null" ) ) {
numberUtil. setColumn ( "ctime" ) ;
numberUtil. setLteValue ( endTime) ;
}
if ( ! StringUtils . isEmpty ( numberUtil) && numberUtil != null && ! numberUtil. equals ( "null" ) ) {
Map < String , Object > number = numberUtil. getNumber ( ) ;
if ( number. size ( ) != 0 ) {
ArrayList < Map < String , Object > > list = new ArrayList < > ( ) ;
list. add ( number) ;
queryUtil. setJudgeNumber ( list) ;
}
}
queryUtil. setPage ( query. getPage ( ) ) ;
queryUtil. setPageSize ( query. getPageSize ( ) ) ;
return queryUtil;
}
数字转换
public static BigDecimal getBigDecimal ( Object value) {
BigDecimal ret = null ;
if ( value != null ) {
if ( value instanceof BigDecimal ) {
ret = ( BigDecimal ) value;
} else if ( value instanceof String ) {
ret = new BigDecimal ( ( String ) value) ;
} else if ( value instanceof BigInteger ) {
ret = new BigDecimal ( ( BigInteger ) value) ;
} else if ( value instanceof Number ) {
ret = new BigDecimal ( ( ( Number ) value) . doubleValue ( ) ) ;
} else {
throw new ClassCastException ( "Not possible to coerce [" + value + "] from class " + value. getClass ( ) + " into a BigDecimal." ) ;
}
}
return ret;
}
public BigDecimal getBigDecimal ( BigDecimal dou) {
BigDecimal bigDecimal = new BigDecimal ( dou. toString ( ) ) ;
BigDecimal moneys = bigDecimal. setScale ( 2 , BigDecimal . ROUND_HALF_UP) ;
return moneys;
}
语句查询aggregateByShell
public List < Map < String , Object > > aggregateByShell ( String key, List < String > queryList) {
List < BasicDBObject > bsonList = new ArrayList ( ) ;
for ( int r = 0 ; r < queryList. size ( ) ; ++ r) {
BasicDBObject bson = BasicDBObject . parse ( ( String ) queryList. get ( r) ) ;
bsonList. add ( bson) ;
}
MongoCollection < Document > collection = ( ( MongoTemplate ) MongoAop . threadLocal. get ( ) ) . getCollection ( key) ;
MongoCursor < Document > cursor = collection. aggregate ( bsonList) . iterator ( ) ;
ArrayList result;
Document list;
for ( result = new ArrayList ( ) ; cursor. hasNext ( ) ; result. add ( list) ) {
list = ( Document ) cursor. next ( ) ;
if ( list. get ( "_id" ) != null ) {
list. put ( "_id" , list. get ( "_id" ) . toString ( ) ) ;
}
}
return result;
}