背景

最近项目经理让我着手做一些报表，前台基于React+echarts+antd ，后台spring boot，报表差不多有七八个，而且前后台我一个人写，心累。
要制图的数据都是存储在MongoDB，而MongoDB查询又不支持传统的sql方式查询。

例如 一个简单的查询语句

select d , sum(f) from a where b = 'xx' and c = 'xx' group by d order by e

写成MongoDB的文档查询方式的话就是下面这样

db.getCollection("a").aggregate(
    [
        { 
            "$match" : {
                "b" : "xx", 
                "c" : "xx"
            }
        }, 
        { 
            "$group" : {
                "_id" : {
                    "d" : "$d"
                }, 
                "SUM(f)" : {
                    "$sum" : "$f"
                }
            }
        }, 
        { 
            "$project" : {
                "d" : "$_id.d", 
                "SUM(f)" : "$SUM(f)", 
                "e" : "$_id.e", 
                "_id" : 0
            }
        }, 
        { 
            "$sort" : {
                "e" : 1
            }
        }, 
        { 
            "$project" : {
                "_id" : 0, 
                "d" : "$d", 
                "SUM(f)" : "$SUM(f)"
            }
        }
    ]
);

而项目中的查询只可能比这个复杂（条件更多），因此每个报表的查询单纯写查询语句都要琢磨半天，因此简单封装了下。满足我当前需要的一些查询操作。

思路

采用类Builder模式的方式构建整个查询语句如 Builder<Type>().select(field).from(table).where(condition).groupBy(field).orderBy(another).build();

要注意MongoDB聚合操作中pipeline执行的顺序，它是依据你构建的查询文档的顺序执行的，也就是说如果你的match在group by 操作之后的话，他是会先取执行group by操作再执行match操作，因此会出现意想不到的效果。

因此采用一个TreeMap存储查询语句，排序各个操作的优先级，最终转换成pipeline，保证与想要执行的操作顺序一致。

代码

package com.xxx.risk.report.util.mongo;

import com.google.common.base.Strings;
import com.mongodb.client.MongoCollection;
import com.mongodb.client.MongoDatabase;
import org.bson.Document;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.Arrays;
import java.util.TreeMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import java.util.stream.StreamSupport;

import static com.google.common.base.Preconditions.checkArgument;
import static com.google.common.base.Preconditions.checkNotNull;
import static com.xxx.risk.report.common.Dict.MongoSupp.AGG;
import static com.xxx.risk.report.common.Dict.MongoSupp._ID;
import static com.xxx.risk.report.common.Dict.Symbol.USD;
import static com.xxx.risk.report.util.mongo.Action.*;

@Component
public class MongoUtil {

    private final Pattern pat = Pattern.compile("^(sum|avg)\\((\\w+)\\)");

    @Autowired
    private MongoDatabase database;

    private final Logger log = LoggerFactory.getLogger(this.getClass());

    public MongoCollection<Document> getCollection(String collectionName) {
        return database.getCollection(collectionName);
    }

    public <T> MongoCollection<T> getCollection(String collectionName, Class<T> type) {
        return database.getCollection(collectionName, type);
    }

    public static String sum(String field){
        checkNotNull(field,"to sum field cannot be null");
        return "sum("+field+")";
    }

    public static String avg(String field){
        checkNotNull(field,"to avg field cannot be null");
        return "avg("+field+")";
    }


    public <T> Querys<T> Querys(){
        return new Querys<>();
    }

    public class Querys<T>{
        private TreeMap<Action,Object> sql = new TreeMap<>();//use default compare
        private String table ;
        private Class<T> type;
        boolean sort = false;


        public Querys<T> select(String...filed){
            if (filed.length==0)
                return this;
            Document select =  (Document) sql.getOrDefault(Select,new Document());
            Arrays.stream(filed).forEach(x->{
                Matcher mat = pat.matcher(x);
                if (mat.find()){
                    Document group = (Document) sql.getOrDefault(GroupBy,new Document(_ID,new Document()));
                    group.append(x,new Document(USD+mat.group(1),USD+mat.group(2)));
                    sql.put(GroupBy,group);
                    select.append(x,USD+x);
                }
                else select.append(x,AGG+x);
            });
            sql.put(Select,select);
            return this;
        }

        public Querys<T> from(String table,Class<T> type){
            this.table = table;
            this.type = type;
            return this;
        }

        public Querys<T> where(){
            return this;
        }

        public Querys<T> where(Document where){
            if (where!=null&&!where.isEmpty())
                sql.put(Where,where);
            return this;
        }

        public Querys<T> groupBy(String...filed){
            if (filed.length==0)
                return this;
            Document group =  (Document) sql.getOrDefault(GroupBy,new Document(_ID,new Document()));
            Document id = (Document) group.get(_ID);
            Arrays.stream(filed).forEach(x->id.append(x,USD+x));
            group.put(_ID,id);
            sql.put(GroupBy,group);
            return this;
        }

        public Querys<T> orderBy(Sort...sorts){
            if (sorts.length==0)
                return this;
            Document sort = new Document();
            Arrays.stream(sorts).forEach(x->sort.append(x.getField(),x.order.toMongoOrder()));
            sql.put(OrderBy,sort);
            this.sort = true;
            return this;
        }

        public Querys<T> range(int skip,int limit){
            if (skip<=0||limit<=0)
                return this;
            sql.put(Skip,skip);
            sql.put(Limit,limit);
            return this;
        }

        public Querys<T> skip(int skip){
            if (skip<=0)
                return this;
            sql.put(Skip,skip);
            return this;
        }

        public Querys<T> limit(int limit){
            if (limit<=0)
                return this;
            sql.put(Limit,limit);
            return this;
        }

        public Stream<T> exec(){
            checkArgument(!Strings.isNullOrEmpty(table),"the table name cannot be null or empty .");
            log.debug("sql : {}",sql);
            MongoCollection<T> collection = getCollection(table,type);
            return StreamSupport.stream(collection.aggregate(
                    sql.entrySet()
                    .stream()
                            .map(i->new Document(i.getKey().toMongoField(),i.getValue()))
                            .collect(Collectors.toList()))
                    .spliterator(),!sort);
        }
    }
}

能够支持的mongo聚合操作

public enum Action{
    Where,GroupBy,Select,OrderBy,Skip,Limit;
    public String toMongoField(){
        switch (this){
            case Where:return MATCH;
            case GroupBy:return GROUP;
            case Select:return PROJECT;
            case OrderBy:return SORT;
            case Skip:return SKIP;
            default: return LIMIT;
        }
    }
}

排序字段转换

public enum Order{
    Asc,Desc;
    public int toMongoOrder(){
        return this==Asc?1:-1;
    }
}

public class Sort {
    private String field;

    Order order;

    private Sort(){}

    public Sort(String field, Order order){
        this.field = field;
        this.order = order;
    }

    public String getField() {
        return field;
    }

    public void setField(String field) {
        this.field = field;
    }

    public Order getOrder() {
        return order;
    }

    public void setOrder(Order order) {
        this.order = order;
    }
}

相关字典

public class Dict {

    public static class Symbol{
        public static final char COMMA = ',';
        public static final char MINUS = '-';
        public static final char USD = '$';
        public static final char POINT = '.';
    }

    public static class MongoSupp{
        public static final String GROUP = "$group";
        public static final String MATCH = "$match";
        public static final String PROJECT = "$project";
        public static final String SORT = "$sort";
        public static final String SKIP = "$skip";
        public static final String LIMIT = "$limit";
        public static final String SUM = "$sum";
        public static final String AVG = "$avg";
        public static final String _ID = "_id";
        public static final String AGG = USD+_ID+POINT;
    }

}

使用方式

    @Test
    public void test14(){
        util.<Document>Querys()
                .select("sum(position)")
                .from(NameDefine.TB_InvestorPosition, Document.class)
                .groupBy("investorid")
                .orderBy(new Sort("sum(position)",Order.Desc))
                .exec()
                .collect(Collectors.toList())
                .forEach(System.out::println);
    }



"Inside of a method": {
        //拿到展示字段
        String field = req.getVolumeType().toRiskField();
        //拿到表名称
        String table = req.getVolumeType().deriveTableName();
        //拿到group by字段
        String group = req.getSelectType().deriveGroupByField();

        return util.<Document>Querys()
                .select(group,sum(field))
                .from(table,Document.class)
                .where(ands)
                .groupBy(group)
                .exec()
                .map(x->new PieResp(x.getString(group),x.get(sum(field))))//将查询出来两列的Document转换成想要的类型
                .collect(Collectors.toList());
}
...

最后

公司项目闭源，因此只能贴出一小部分供大家参考。

功能可能并不完美，github上还有一些直接标准sql转Mongo查询语句的库，比我这个强大很多，但是对于我这个简单的服务来说并不需要如此强大的功能。当然还有很复杂操作是不支持的，例如lookup等关联操作，count等操作。可以根据个人需求拓展。代码也有很多不严谨的地方，还望多多指正。