JAVA基于MongoTemplate的复杂条件查询

这是一个Mongodb的通用查询方法,自己写的，将就用
功能点：
1.支持指定返回字段；
2.支持多字段排序；
3.支持分页查询；
4.支持多个过滤条件，包括 >,<,>=,<=,=,<>,in共7种比较方式，支持类似(name=‘aa’ and age > 10) or name =‘bb’ or age<5 的多and和or查询方式；不支持类似(name=‘aa’ and age > 10) or (name =‘bb’ and age<5)的查询方式

引入依赖

        <!-- JSON -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.58</version>
        </dependency>
        <!-- mongodb -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-mongodb</artifactId>
        </dependency>

查询字段实体定义

public class RequestModel implements Serializable {
    //表名,ep:User
    private String type;
    //过滤条件
    private List<FilterModel> filter;
    //返回字段，逗号分隔，*表示全返回,ep:name,age,sex
    private String sty;
    //排序字段
    private List<SortModel> sort;
    //页码
    private Integer p;
    //页大小
    private Integer ps;
    }

过滤条件实体定义

public class FilterModel  implements Serializable {
    //字段名,ep:name
    private String key;
    //值,双引号包裹表示字符串，单引号表示日期，其他的系统自行判断；ep:"xiaoming"，'2020-01-01'
    private String value;
    //操作符，包括 >,<,>=,<=,=,<>,in;ep:=
    private String oper;
    //and / or;ep:and
    private String logOper;
    }

排序条件实体定义

public class SortModel implements Serializable {
    //排序字段名;ep:age
    private String sortName;
    //排序方向，小于0倒序,ep:1
    private Integer sortWay = 0;
    }

主查询逻辑

/**
     * 从 Mongodb 查询数据
     */
    public String getByMongo(RequestModel model) {
        String currentType = model.getType() ;
        logger.info("查询的表名为 " + currentType);
        String result = "";
        try {
            logger.info("准备查询" + model.toString());
            //检查集合是否存在
            if (!mongoTemplate.collectionExists(currentType)) {
                logger.warn("不存在的集合名");
                return null;
            }
            long qstart = System.currentTimeMillis();
            //查询条件
            Query query = new Query();
            addStys(query, model);//添加返回字段
            if (query == null) {
                return "";
            }
            addFilters(query, model);//添加过滤条件
            addSorts(query, model);//添加排序条件

            long qend = System.currentTimeMillis();
            System.out.println("构建query耗时：" + (qend - qstart) + " ms");

            long countStart = System.currentTimeMillis();

            //不分页的总数量
            long totalCount = mongoTemplate.count(query, currentType);

            if (totalCount == 0) {
                logger.info("查询结果数据为空");
                return null;
            }
            addLimit(query, model);//添加分页条件
            long countEnd = System.currentTimeMillis();
            System.out.println("count耗时：" + (countEnd - countStart) + " ms");

            //计算总页数
            long totalPage = totalCount / model.getPs();
            if (totalCount % model.getPs() > 0) {
                totalPage += 1;
            }

            long start = System.currentTimeMillis();
            //查询
            List<Map> list = mongoTemplate.find(query, Map.class, currentType);
            long end = System.currentTimeMillis();
            System.out.println("查询数据耗时：" + (end - start) + " ms");
            long rstart = System.currentTimeMillis();
            List<Map> realResult = getRealResult(list);
            long rend = System.currentTimeMillis();
            System.out.println("Date格式化耗时：" + (rend - rstart) + " ms");

            result = JSON.toJSONString(realResult);
            long reTime = System.currentTimeMillis();
            System.out.println("结果转JSON耗时：" + (reTime - rend) + " ms");

        } catch (Exception ex) {

            logger.error("查询时出错", ex);
        }

        return result;
    }

添加过滤条件

 //添加Filter条件
    private Query addFilters(Query query, RequestModel model) {
        List<FilterModel> filterList = model.getFilter();
        /***********字段条件过滤***********/
        if (filterList != null && filterList.size() > 0) {
            List<Criteria> andList = new ArrayList<>();
            List<Criteria> orList = new ArrayList<>();
            for (FilterModel filter : filterList) {
                String value = filter.getValue();
                Criteria criteria;
                Object v = value;
                //字符串类型
                if (value.startsWith("\"") && value.endsWith("\"")) {
                    value = value.substring(1, value.length() - 1);
                    v = value;
                }
                //日期类型
                else if (value.startsWith("'") && value.endsWith("'") && !"in".equals(filter.getOper())) {
                    value = value.substring(1, value.length() - 1);
                    Date date = DateUtil.dateToISODate(value);
                    if (date == null) {
                        date = DateUtil.dateToISODate(value, "yyyy-MM-dd");
                    }
                    v = date;
                }
                //数字类型
                else {
                    try {
                        Double dv = Double.parseDouble(value);
                        v = dv;
                    } catch (Exception ex) {

                    }
                }
                switch (filter.getOper()) {
                    case "=":
                        criteria = Criteria.where(filter.getKey()).is(v);
                        break;
                    case ">":
                        criteria = Criteria.where(filter.getKey()).gt(v);
                        break;
                    case "<":
                        criteria = Criteria.where(filter.getKey()).lt(v);
                        break;
                    case ">=":
                        criteria = Criteria.where(filter.getKey()).gte(v);
                        break;
                    case "<=":
                        criteria = Criteria.where(filter.getKey()).lte(v);
                        break;
                    case "<>":
                        criteria = Criteria.where(filter.getKey()).ne(v);
                        break;
                    case "in":
                        //源数据
                        if (filter.getValue() == null) {
                            continue;
                        }
                        String[] values = filter.getValue().split(",");
                        Object[] para = values;
                        //日期in查询数组
                        Date[] dates = null;
                        //字符串类型
                        if (values != null && values[0].startsWith("\"")) {
                            String[] strs = new String[values.length];
                            for (int i = 0; i < values.length; i++) {
                                value = value.substring(1, value.length() - 1);
                                strs[i] = value;
                            }
                            para = strs;
                        }
                        //日期类型
                        else if (values != null && values[0].startsWith("'")) {
                            logger.info("判断是否为日期类型");
                            dates = new Date[values.length];
                            for (int i = 0; i < values.length; i++) {
                                Date date = DateUtil.dateToISODate(values[i].substring(1, values[i].length() - 1), "yyyy-MM-dd");
                                dates[i] = date;
                            }
                            para = dates;
                            logger.info("in 里的为日期类型");
                        }
                        //数字类型
                        else {
                            logger.info("判断是否为数字类型");
                            Double[] doubles = new Double[values.length];
                            try {
                                for (int i = 0; i < values.length; i++) {
                                    Double dv = Double.parseDouble(values[i]);
                                    doubles[i] = dv;
                                }
                                para = doubles;
                                logger.info("in 里的为数字类型");
                            } catch (Exception ex) {
                            }
                        }
                        if (para != null && para.length > 0) {
                            logger.info(para[0].getClass() + "");
                        }
                        //添加in过滤条件
                        criteria = Criteria.where(filter.getKey()).in(para);
                        break;
                    default:
                        continue;
                }
                //或条件
                if (filter.getLogOper().equals("or")) {
                    orList.add(criteria);
                } else {
                    andList.add(criteria);
                }
            }
            //或条件数组,数组上线+1是为了把且条件集合加进去
            Criteria[] orC = new Criteria[1];
            if (orList.size() > 0) {
                orC = new Criteria[orList.size() + 1];
                for (int i = 0; i < orList.size(); i++) {
                    Criteria or = orList.get(i);
                    orC[i] = or;
                }
            }
            //且条件数组
            Criteria[] andC = new Criteria[0];
            if (andList.size() > 0) {
                andC = new Criteria[andList.size()];
                for (int i = 0; i < andList.size(); i++) {
                    Criteria and = andList.get(i);
                    andC[i] = and;
                }
            }
            //把全部且条件放在一起合成一个，再与其他的或条件一起组装
            orC[orC.length - 1] = new Criteria().andOperator(andC);
            query.addCriteria(new Criteria().orOperator(orC));
        }
        return query;
    }

添加返回字段

/**
     * 添加返回字段
     */
    private Query addStys(Query query, RequestModel model) {
        /***********返回字段过滤***********/
        String[] stys = model.getSty().split(",");
        if (stys == null || stys.length == 0) {
            query = null;
            return query;
        }
        query.fields().exclude("_id");
        if (!(stys.length == 1 && stys[0].equals("*"))) {
            for (String sty : stys) {
                query.fields().include(sty.trim());
            }
        }
        return query;
    }

添加排序条件

    /**
     * 添加排序条件
     */
    private Query addSorts(Query query, RequestModel model) {
        /***************排序***************/
        List<SortModel> sortList = model.getSort();
        if (sortList != null && sortList.size() > 0) {
            List<Sort.Order> orders = new ArrayList<>();
            //添加多个排序条件
            for (int i = 0; i < sortList.size(); i++) {
                SortModel sortModel = sortList.get(i);
                Sort.Direction direction = Sort.Direction.ASC;
                if (sortModel.getSortWay() < 0) {
                    direction = Sort.Direction.DESC;
                }
                Sort.Order order = new Sort.Order(direction, sortModel.getSortName());
                orders.add(order);
            }
            query.with(Sort.by(orders));
        }
        return query;
    }

添加排序条件

    /**
     * 添加排序条件
     */
    private Query addLimit(Query query, RequestModel model) {
        /***************分页*************/
        //设置页大小
        if (model.getPs() == null || model.getPs() <= 0) {
            model.setPs(50);
        }
        //分页
        if (model.getP() != null && model.getP() > 1) {
            query.skip((model.getP() - 1) * model.getPs());
        }
        query.limit(model.getPs());
        return query;
    }

将返回结果中的日期格式化

 /**
     * 将结果中的Date格式化输出
     */
    private List<Map> getRealResult(List<Map> list) {
        if (list == null || list.size() == 0) {
            return null;
        }
        //日期类型的字段
        List<Object> dateKeys = new ArrayList<>();
        for (Object key : list.get(0).keySet()) {
            Object value = list.get(0).get(key);
            if (value instanceof Date) {
                dateKeys.add(key);
            }
        }
        list.stream().parallel().forEach(map -> {
            for (Object key : dateKeys) {
                map.put(key, date2String((Date) map.get(key)));
            }
        });

        return list;
    }
    
     /**
     * 日期格式化
     */
    public static String date2String(Date date) {
        try {
            if (date == null)
                return null;
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            String str = sdf.format(date);
            return str;
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return null;
    }