JSONSQL：使用SQL过滤JSON类型数据（支持多种数据库常用查询、统计、平均值、最大值、最小值、求和语法）...

左手的ㄟ右手 2023-10-12 08:56 18阅读 0赞

##### 1. 简介 #####

在开发中，经常需要根据条件过滤大批量的JSON类型数据。如果仅需要过滤这一种类型，将JSON转为List后过滤即可；如果相同的条件既想过滤数据库表中的数据、也想过滤内存中JSON数据，甚至想过滤Elasticsearch中的数据，那可使用本博客中的方案，使用标准的SQL语法实现多种数据源中的查询或聚合操作。

##### 2. JSONSQL能力总览 #####

<table> 
 <thead> 
  <tr> 
   <th>能力</th> 
   <th>明细</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>支持的数据库</td> 
   <td>Mysql PostgreSQL等(采用Druid连接池中的SQL解析器，理论上支持Druid支持的所有数据库)</td> 
  </tr> 
  <tr> 
   <td>支持的语法</td> 
   <td>查询全部、查询指定列、条件查询(=、!=、&gt;、&lt;、&gt;=、&lt;=、between、in、not in、like、not like、为空、不为空)、复杂条件查询(多个and/or组合)、order by、limit、group by(一维或多维)、count、min、max、avg、sum等，其他聚合函数可自行增加</td> 
  </tr> 
  <tr> 
   <td>支持的函数</td> 
   <td>cast、concat、lower、upper、to_timestamp、from_unixtime、PostgreSQL(json_contain、json_not_contain、jsonb_array_length)、Mysql(json_contains、json_length)等，其他函数可自行增加</td> 
  </tr> 
 </tbody> 
</table>

##### 3. 相关博客 #####

[DB SQL 转 ES DSL(支持多种数据库常用查询、统计、平均值、最大值、最小值、求和语法)][DB SQL _ ES DSL]

##### 4. 示例代码\# #####

###### 4.1 创建项目 ######

![cb4eec8594e3547fe900c5b54f80cd96.jpeg][]

###### 4.2 修改pom.xml ######

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
    
        <groupId>com.c3stones</groupId>
        <artifactId>spring-boot-json-sql-demo</artifactId>
        <version>1.0-SNAPSHOT</version>
    
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.7.10</version>
        </parent>
    
        <dependencies>
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>mybatis-plus-boot-starter</artifactId>
                <version>3.5.3.1</version>
            </dependency>
            <dependency>
                <groupId>com.alibaba.fastjson2</groupId>
                <artifactId>fastjson2</artifactId>
                <version>2.0.32</version>
            </dependency>
            <dependency>
                <groupId>cn.hutool</groupId>
                <artifactId>hutool-all</artifactId>
                <version>5.8.15</version>
            </dependency>
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>1.2.18</version>
            </dependency>
            <dependency>
                <groupId>com.mysql</groupId>
                <artifactId>mysql-connector-j</artifactId>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-thymeleaf</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    
    </project>

###### 4.3 创建枚举类 ######

import cn.hutool.core.util.StrUtil;
    import lombok.AllArgsConstructor;
    import lombok.Getter;
    
    /**
     * 聚合类型 枚举类
     *
     * @author CL
     */
    @Getter
    @AllArgsConstructor
    public enum AggregationType {
    
        COUNT("count", "统计"),
        MAX("max", "最大值"),
        MIN("min", "最小值"),
        AVG("avg", "平均值"),
        SUM("sum", "求和"),
        ;
    
        private final String value;
        private final String name;
    
        /**
         * 根据值获取枚举
         *
         * @param value 值
         * @return {@link AggregationType}
         */
        public static AggregationType findByValue(String value) {
            if (StrUtil.isNotEmpty(value)) {
                for (AggregationType aggregationType : values()) {
                    if (StrUtil.equalsIgnoreCase(aggregationType.getValue(), value)) {
                        return aggregationType;
                    }
                }
            }
            return null;
        }
    
    }

import cn.hutool.core.lang.Opt;
    import cn.hutool.core.util.ArrayUtil;
    import cn.hutool.core.util.NumberUtil;
    import cn.hutool.core.util.StrUtil;
    import lombok.AllArgsConstructor;
    import lombok.Getter;
    
    import java.util.Arrays;
    import java.util.Date;
    import java.util.List;
    import java.util.Objects;
    import java.util.stream.Stream;
    
    /**
     * 操作符 枚举类
     *
     * @author CL
     */
    @Getter
    @AllArgsConstructor
    public enum Operator {
    
        EQ(" = ", "=="),
        NEQ(" != ", "!="),
        GT(" > ", ">"),
        LT(" < ", "<"),
        GTE(" >= ", ">="),
        LTE(" <= ", "<="),
        IN(" in ", "in", "json_contain", "json_contains"),
        NOT_IN(" not in ", "notIn", "json_not_contain"),
        CONTAIN(" like ", "contain"),
        NOT_CONSTAIN(" not like ", "notContain"),
        EMPTY(" is null ", "isNull"),
        NOT_EMPTY(" is not null ", "notNull"),
        START_WITH(CONTAIN.character, "startWith"),
        END_WITH(CONTAIN.character, "endWith"),
        RANGE(" between ", "range"),
        ;
    
        private final String character;
        private final List<String> operators;
    
        Operator(String character, String... operators) {
            this.character = character;
            this.operators = Arrays.asList(operators);
        }
    
        /**
         * 根据值获取枚举
         *
         * @param value 值
         * @return {@link Operator}
         */
        public static Operator findByValue(String value) {
            if (StrUtil.isNotEmpty(value)) {
                for (Operator operator : values()) {
                    if (StrUtil.equalsAnyIgnoreCase(value, operator.getOperators().toArray(new String[0]))) {
                        return operator;
                    }
                }
            }
            return EQ;
        }
    
        /**
         * 判断字符串
         *
         * @param source   源
         * @param operator 操作符
         * @param target   目标
         * @return {@link Boolean}
         */
        public static boolean judgeStr(String source, Operator operator, String target) {
            boolean result = false;
            if (!(StrUtil.isEmpty(source) || StrUtil.isEmpty(target))) {
                switch (operator) {
                    case EQ:
                        result = StrUtil.equals(source, target);
                        break;
                    case NEQ:
                        result = !StrUtil.equals(source, target);
                        break;
                    case CONTAIN:
                        result = StrUtil.contains(source, target);
                        break;
                    case NOT_CONSTAIN:
                        result = !StrUtil.contains(source, target);
                        break;
                    case IN:
                        result = StrUtil.equalsAny(source, target.split(StrUtil.COMMA));
                        break;
                    case NOT_IN:
                        result = !StrUtil.equalsAny(source, target.split(StrUtil.COMMA));
                        break;
                    case START_WITH:
                        result = StrUtil.startWith(source, target);
                        break;
                    case END_WITH:
                        result = StrUtil.endWith(source, target);
                        break;
                    default:
                }
            }
            return result;
        }
    
        /**
         * 判断数字
         *
         * @param source   源
         * @param operator 操作符
         * @param targets  值
         * @return {@link Boolean}
         */
        public static boolean judgeNumber(Number source, Operator operator, Number... targets) {
            boolean result = false;
            if (!(Objects.isNull(source) || Objects.isNull(targets))) {
                double src = NumberUtil.toDouble(source);
                Double[] values = Stream.of(targets).map(NumberUtil::toDouble).toArray(Double[]::new);
                switch (operator) {
                    case EQ:
                        result = NumberUtil.equals(src, values[0]);
                        break;
                    case NEQ:
                        result = !NumberUtil.equals(src, values[0]);
                        break;
                    case GT:
                        result = src > Opt.ofNullable(values[0]).orElse(Double.MAX_VALUE);
                        break;
                    case LT:
                        result = src < Opt.ofNullable(values[0]).orElse(Double.MIN_VALUE);
                        break;
                    case GTE:
                        result = src >= Opt.ofNullable(values[0]).orElse(Double.MAX_VALUE);
                        break;
                    case LTE:
                        result = src <= Opt.ofNullable(values[0]).orElse(Double.MIN_VALUE);
                        break;
                    case IN:
                        result = StrUtil.equalsAny(source.toString(), Stream.of(targets)
                                .map(Number::toString).toArray(String[]::new));
                        break;
                    case NOT_IN:
                        result = !StrUtil.equalsAny(source.toString(), Stream.of(targets)
                                .map(Number::toString).toArray(String[]::new));
                        break;
                    case RANGE:
                        result = src >= Opt.ofNullable(values[0]).orElse(Double.MAX_VALUE)
                                && src <= Opt.ofNullable(values[1]).orElse(Double.MIN_VALUE);
                        break;
                    default:
                }
            }
            return result;
        }
    
        /**
         * 判断时间
         *
         * @param before   之前时间
         * @param operator 操作符
         * @param after    之后时间
         * @return {@link Boolean}
         */
        public static boolean judgeDate(Date before, Operator operator, Date after) {
            if (Objects.isNull(before) || Objects.isNull(after)) {
                return false;
            }
            return judgeNumber(before.getTime(), operator, after.getTime());
        }
    
        /**
         * 判断数组
         *
         * @param sources  源
         * @param operator 操作符
         * @param targets  值
         * @return {@link Boolean}
         */
        public static boolean judgeArray(Object[] sources, Operator operator, Object... targets) {
            boolean result = false;
            if (!(Objects.isNull(sources) || Objects.isNull(targets))) {
                String[] src = Stream.of(sources).filter(Objects::nonNull).map(Objects::toString).toArray(String[]::new);
                String[] values = Stream.of(targets).filter(Objects::nonNull).map(Objects::toString).toArray(String[]::new);
                switch (operator) {
                    case IN:
                        result = ArrayUtil.containsAny(src, values);
                        break;
                    case NOT_IN:
                        result = !ArrayUtil.containsAny(src, values);
                        break;
                    default:
                }
            }
            return result;
        }
    
        /**
         * 反转操作符
         *
         * @param operator 操作符
         * @return {@link Operator}
         */
        public static Operator reverse(Operator operator) {
            if (Objects.nonNull(operator)) {
                switch (operator) {
                    case EQ:
                        operator = NEQ;
                        break;
                    case NEQ:
                        operator = EQ;
                        break;
                    case GT:
                        operator = LT;
                        break;
                    case LT:
                        operator = GT;
                        break;
                    case GTE:
                        operator = LTE;
                        break;
                    case LTE:
                        operator = GTE;
                        break;
                    case IN:
                        operator = NOT_IN;
                        break;
                    case NOT_IN:
                        operator = IN;
                        break;
                    case CONTAIN:
                        operator = NOT_CONSTAIN;
                        break;
                    case NOT_CONSTAIN:
                        operator = CONTAIN;
                        break;
                    case EMPTY:
                        operator = NOT_EMPTY;
                        break;
                    case NOT_EMPTY:
                        operator = EMPTY;
                        break;
                    case START_WITH:
                        operator = END_WITH;
                        break;
                    case END_WITH:
                        operator = START_WITH;
                        break;
                    default:
                }
            }
            return operator;
        }
    
    }

import cn.hutool.core.util.StrUtil;
    import lombok.AllArgsConstructor;
    import lombok.Getter;
    
    /**
     * 排序类型 枚举类
     *
     * @author CL
     */
    @Getter
    @AllArgsConstructor
    public enum OrderType {
    
        ASC,
        DESC,
        ;
    
        /**
         * 根据值获取枚举
         *
         * @param value 值
         * @return {@link OrderType}
         */
        public static OrderType findByValue(String value) {
            if (StrUtil.isNotEmpty(value)) {
                for (OrderType orderType : values()) {
                    if (StrUtil.equalsIgnoreCase(orderType.toString(), value)) {
                        return orderType;
                    }
                }
            }
            return ASC;
        }
    
    }

import cn.hutool.core.util.StrUtil;
    import lombok.AllArgsConstructor;
    import lombok.Getter;
    
    import java.util.Objects;
    
    /**
     * 条件关系 枚举类
     *
     * @author CL
     */
    @Getter
    @AllArgsConstructor
    public enum Relation {
    
        AND(" and "),
        OR(" or "),
        NOT(" not "),
        ;
    
        private final String value;
    
        /**
         * 根据值获取枚举
         *
         * @param value 值
         * @return {@link Relation}
         */
        public static Relation findByValue(String value) {
            if (StrUtil.isNotEmpty(value)) {
                for (Relation relation : values()) {
                    if (StrUtil.equalsIgnoreCase(relation.getValue().trim(), value)) {
                        return relation;
                    }
                }
            }
            return AND;
        }
    
        /**
         * 反转关系
         *
         * @param relation 关系
         * @return {@link Relation}
         */
        public static Relation reverse(Relation relation) {
            if (Objects.equals(AND, relation)) {
                return OR;
            } else if (Objects.equals(OR, relation)) {
                return AND;
            }
            return relation;
        }
    
    }

###### 4.4 创建模型类(JSONSQL结构化实体) ######

import com.alibaba.fastjson2.JSONObject;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    /**
     * JSON 数据模型
     *
     * @author CL
     */
    @Data
    @NoArgsConstructor
    public class DataModel {
    
        /**
         * 数据
         */
        private JSONObject data;
    
        public DataModel(JSONObject data) {
            this.data = data;
        }
    
        @Override
        public String toString() {
            return JSONObject.toJSONString(this);
        }
    
    }

import com.alibaba.fastjson2.JSONObject;
    import com.c3stones.json.enums.Operator;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    import lombok.experimental.Accessors;
    
    /**
     * JSONSQL 二元运算表达式
     *
     * @author CL
     */
    @Data
    @NoArgsConstructor
    @Accessors(chain = true)
    public class JQLBinaryOpExpr<T> {
    
        /**
         * 字段名称
         */
        private String fieldName;
    
        /**
         * 操作符
         */
        private Operator operator;
    
        /**
         * 值
         */
        private T[] values;
    
        @SafeVarargs
        public final JQLBinaryOpExpr setValues(T... values) {
            this.values = values;
            return this;
        }
    
        @Override
        public String toString() {
            return JSONObject.toJSONString(this);
        }
    
    }

import com.alibaba.fastjson2.JSONObject;
    import com.alibaba.fastjson2.JSONWriter;
    import com.c3stones.json.enums.Relation;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    import lombok.experimental.Accessors;
    
    import java.util.List;
    
    /**
     * JSONSQL 查询模型
     *
     * @author CL
     */
    @Data
    @NoArgsConstructor
    @Accessors(chain = true)
    public class JQLWhereModel {
    
        /**
         * 关系
         */
        private Relation relation;
    
        /**
         * 二位运算表达式
         */
        private List<JQLBinaryOpExpr> binaryOpExprList;
    
        /**
         * 子查询模型
         */
        private List<JQLWhereModel> childWhereList;
    
        @Override
        public String toString() {
            return JSONObject.toJSONString(this, JSONWriter.Feature.ReferenceDetection);
        }
    
    }

import com.alibaba.fastjson2.JSONObject;
    import com.c3stones.json.enums.AggregationType;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    /**
     * JSON 聚合模型
     *
     * @author CL
     */
    @Data
    @NoArgsConstructor
    public class JQLAggregationModel {
    
        /**
         * 聚合类型
         */
        private AggregationType aggregationType;
    
        /**
         * 属性名称
         */
        private String fieldName;
    
        /**
         * 别名
         */
        private String alias;
    
        public JQLAggregationModel(AggregationType aggregationType, String fieldName, String alias) {
            this.aggregationType = aggregationType;
            this.fieldName = fieldName;
            this.alias = alias;
        }
    
        @Override
        public String toString() {
            return JSONObject.toJSONString(this);
        }
    
    }

import com.alibaba.fastjson2.JSONObject;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    import java.util.List;
    
    /**
     * JSONSQL 分组模型
     *
     * @author CL
     */
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    public class JQLGroupModel {
    
        /**
         * 分组属性名称
         */
        private List<String> groupNameList;
    
        @Override
        public String toString() {
            return JSONObject.toJSONString(this);
        }
    
    }

import com.alibaba.fastjson2.JSONObject;
    import com.c3stones.json.enums.OrderType;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    /**
     * JSON 排序模型
     *
     * @author CL
     */
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    public class JQLSortModel {
    
        /**
         * 属性名称
         */
        private String fieldName;
    
        /**
         * 排序类型
         */
        private OrderType orderType;
    
        @Override
        public String toString() {
            return JSONObject.toJSONString(this);
        }
    
    }

import com.alibaba.fastjson2.JSONObject;
    import com.alibaba.fastjson2.JSONWriter;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    import java.util.List;
    
    /**
     * JSONSQL 结构模型
     *
     * @author CL
     */
    @Data
    @NoArgsConstructor
    public class JQLModel {
    
        /**
         * 条件
         */
        private JQLWhereModel where;
    
        /**
         * 聚合
         */
        private List<JQLAggregationModel> aggregations;
    
        /**
         * 分组
         */
        private JQLGroupModel group;
    
        /**
         * 排序
         */
        private List<JQLSortModel> sorts;
    
        /**
         * 起始位置
         */
        private Integer from;
    
        /**
         * 大小
         */
        private Integer size;
    
        @Override
        public String toString() {
            return JSONObject.toJSONString(this, JSONWriter.Feature.ReferenceDetection);
        }
    
    }

import cn.hutool.core.collection.ListUtil;
    import cn.hutool.core.lang.Opt;
    import cn.hutool.core.util.ArrayUtil;
    import cn.hutool.core.util.StrUtil;
    import com.alibaba.fastjson2.JSONObject;
    import com.c3stones.json.mapper.model.DataModel;
    import com.c3stones.json.mapper.model.JQLModel;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    import lombok.experimental.Accessors;
    
    import java.util.List;
    import java.util.StringJoiner;
    import java.util.stream.Collectors;
    
    /**
     * JSON Mapper Provider
     *
     * @author CL
     */
    @Data
    @NoArgsConstructor
    @Accessors(chain = true)
    public class JSONMapperProvider {
    
        /**
         * 数据来源
         */
        @Deprecated
        private String json;
    
        /**
         * 数据模型
         */
        private List<DataModel> dataList;
    
        /**
         * JSONSQL
         */
        private JQLModel jql;
    
        /**
         * 包含列
         */
        private String[] includes;
    
        /**
         * 排除列
         */
        private String[] excludes;
    
        /**
         * SQL
         */
        private String sql;
    
        public JSONMapperProvider(String sql) {
            this.sql = sql;
        }
    
        @Override
        public String toString() {
            StringJoiner str = new StringJoiner(StrUtil.LF);
            str.add("data : " + Opt.ofNullable(dataList)
                    .orElse(ListUtil.empty()).stream().map(DataModel::toString)
                    .collect(Collectors.joining(StrUtil.LF)));
            str.add("jql : " + jql);
            if (ArrayUtil.isNotEmpty(includes)) {
                str.add("includes : " + JSONObject.toJSONString(includes));
            }
            if (ArrayUtil.isNotEmpty(excludes)) {
                str.add("excludes : " + JSONObject.toJSONString(excludes));
            }
            str.add("sql : " + sql);
    
            return str.toString();
        }
    
    }

###### 4.5 创建JSONSQL默认查询语法接口 ######

import cn.hutool.core.collection.CollUtil;
    import com.c3stones.json.enums.Relation;
    import com.c3stones.json.mapper.model.*;
    
    import java.util.List;
    import java.util.Objects;
    
    /**
     * JSONSQL 默认查询语法接口
     *
     * @author CL
     */
    @SuppressWarnings("all")
    public interface JSONDefaultSelect {
    
        /**
         * 等于
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        JQLBinaryOpExpr eq(String fieldName, Object value);
    
        /**
         * 不等
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        JQLBinaryOpExpr neq(String fieldName, Object value);
    
        /**
         * 大于
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        JQLBinaryOpExpr gt(String fieldName, Object value);
    
        /**
         * 大于等于
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        JQLBinaryOpExpr gte(String fieldName, Object value);
    
        /**
         * 小于
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        JQLBinaryOpExpr lt(String fieldName, Object value);
    
        /**
         * 小于等于
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        JQLBinaryOpExpr lte(String fieldName, Object value);
    
        /**
         * 属于
         *
         * @param fieldName 属性名称
         * @param values    值(不能包含英文逗号)
         * @return {@link JQLBinaryOpExpr}
         */
        JQLBinaryOpExpr in(String fieldName, List<Object> values);
    
        /**
         * 不属于
         *
         * @param fieldName 属性名称
         * @param values    值(不能包含英文逗号)
         * @return {@link JQLBinaryOpExpr}
         */
        JQLBinaryOpExpr notIn(String fieldName, List<Object> values);
    
        /**
         * 包含
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        JQLBinaryOpExpr contain(String fieldName, Object value);
    
        /**
         * 不包含
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        JQLBinaryOpExpr notContain(String fieldName, Object value);
    
        /**
         * 为空
         *
         * @param fieldName 属性名称
         * @return {@link JQLBinaryOpExpr}
         */
        JQLBinaryOpExpr empty(String fieldName);
    
        /**
         * 不为空
         *
         * @param fieldName 属性名称
         * @return {@link JQLBinaryOpExpr}
         */
        JQLBinaryOpExpr notEmpty(String fieldName);
    
        /**
         * 在两者之间
         *
         * @param fieldName  属性名称
         * @param startValue 起始值
         * @param endValue   终止值
         * @return {@link JQLBinaryOpExpr}
         */
        JQLBinaryOpExpr range(String fieldName, Object startValue, Object endValue);
    
        /**
         * 关系与
         *
         * @param binaryOpExprs 二元运算表达式
         * @return {@link JQLWhereModel}
         */
        default JQLWhereModel and(JQLBinaryOpExpr... binaryOpExprs) {
            return new JQLWhereModel().setRelation(Relation.AND).setBinaryOpExprList(CollUtil.newArrayList(binaryOpExprs));
        }
    
        /**
         * 关系
         *
         * @param binaryOpExprs 二元运算表达式
         * @return {@link JQLWhereModel}
         */
        default JQLWhereModel or(JQLBinaryOpExpr... binaryOpExprs) {
            return new JQLWhereModel().setRelation(Relation.OR).setBinaryOpExprList(CollUtil.newArrayList(binaryOpExprs));
        }
    
        /**
         * 子查询模型
         *
         * @param relation    子模型之间的关系
         * @param childWheres 子查询模型
         * @return {@link JQLWhereModel}
         */
        default JQLWhereModel child(Relation relation, JQLWhereModel... childWheres) {
            return new JQLWhereModel().setRelation(relation).setChildWhereList(CollUtil.newArrayList(childWheres));
        }
    
        /**
         * 查询全部
         *
         * @return {@link JQLWhereModel}
         */
        default JQLWhereModel all() {
            return new JQLWhereModel();
        }
    
        /**
         * JQL
         *
         * @param where        条件
         * @param aggregations 聚合
         * @param group        分组
         * @param sorts        排序
         * @param from         偏移数
         * @param size         限制数
         * @return {@link JQLModel}
         */
        default JQLModel jql(JQLWhereModel where, List<JQLAggregationModel> aggregations, JQLGroupModel group, List<JQLSortModel> sorts, Integer from, Integer size) {
            JQLModel jql = new JQLModel();
            jql.setWhere(where);
            if (CollUtil.isNotEmpty(aggregations)) {
                jql.setAggregations(aggregations);
            }
            if (Objects.nonNull(group)) {
                jql.setGroup(group);
            }
            if (CollUtil.isNotEmpty(sorts)) {
                jql.setSorts(sorts);
            }
            if (Objects.nonNull(from)) {
                jql.setFrom(from);
            }
            if (Objects.nonNull(size)) {
                jql.setSize(size);
            }
            return jql;
        }
    
    }

###### 4.6 创建JSONSQL字符串查询语法 ######

import cn.hutool.core.collection.ListUtil;
    import cn.hutool.core.lang.Opt;
    import cn.hutool.core.util.StrUtil;
    import com.c3stones.json.enums.Operator;
    import com.c3stones.json.mapper.model.JQLBinaryOpExpr;
    import com.c3stones.json.parser.select.JSONDefaultSelect;
    
    import java.util.List;
    import java.util.Objects;
    
    /**
     * JSONSQL 字符串查询语法
     *
     * @author CL
     */
    @SuppressWarnings("unchecked")
    public class StringSelect implements JSONDefaultSelect {
    
        /**
         * 格式化值
         *
         * @param value 值
         * @return {@link String}
         */
        private String format(Object value) {
            return StrUtil.toStringOrNull(value);
        }
    
        /**
         * 格式化值
         *
         * @param value 值
         * @return {@link String[]}
         */
        private String[] format(List<Object> value) {
            return Opt.ofNullable(value).orElse(ListUtil.empty()).stream().map(StrUtil::toStringOrNull)
                    .filter(Objects::nonNull).toArray(String[]::new);
        }
    
        /**
         * 格式化
         *
         * @param value 值
         * @return {@link String}
         */
        private String format(String value) {
            return StrUtil.removeSuffix(StrUtil.removePrefix(value, "%"), "%");
        }
    
        /**
         * 等于
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<String> eq(String fieldName, Object value) {
            return new JQLBinaryOpExpr<String>().setFieldName(fieldName).setOperator(Operator.EQ).setValues(format(value));
        }
    
        /**
         * 不等
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<String> neq(String fieldName, Object value) {
            return new JQLBinaryOpExpr<String>().setFieldName(fieldName).setOperator(Operator.NEQ).setValues(format(value));
        }
    
        /**
         * 大于
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<String> gt(String fieldName, Object value) {
            return new JQLBinaryOpExpr<String>().setFieldName(fieldName).setOperator(Operator.GT).setValues(format(value));
        }
    
        /**
         * 大于等于
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<String> gte(String fieldName, Object value) {
            return new JQLBinaryOpExpr<String>().setFieldName(fieldName).setOperator(Operator.GTE).setValues(format(value));
        }
    
        /**
         * 小于
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<String> lt(String fieldName, Object value) {
            return new JQLBinaryOpExpr<String>().setFieldName(fieldName).setOperator(Operator.LT).setValues(format(value));
        }
    
        /**
         * 小于等于
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<String> lte(String fieldName, Object value) {
            return new JQLBinaryOpExpr<String>().setFieldName(fieldName).setOperator(Operator.LTE).setValues(format(value));
        }
    
        /**
         * 属于
         *
         * @param fieldName 属性名称
         * @param values    值(不能包含英文逗号)
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<String> in(String fieldName, List<Object> values) {
            return new JQLBinaryOpExpr<String>().setFieldName(fieldName).setOperator(Operator.IN).setValues(format(values));
        }
    
        /**
         * 不属于
         *
         * @param fieldName 属性名称
         * @param values    值(不能包含英文逗号)
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<String> notIn(String fieldName, List<Object> values) {
            return new JQLBinaryOpExpr<String>().setFieldName(fieldName).setOperator(Operator.NOT_IN).setValues(format(values));
        }
    
        /**
         * 包含
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<String> contain(String fieldName, Object value) {
            return new JQLBinaryOpExpr<String>().setFieldName(fieldName).setOperator(Operator.CONTAIN).setValues(format(format(value)));
        }
    
        /**
         * 不包含
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<String> notContain(String fieldName, Object value) {
            return new JQLBinaryOpExpr<String>().setFieldName(fieldName).setOperator(Operator.NOT_CONSTAIN).setValues(format(format(value)));
        }
    
        /**
         * 为空
         *
         * @param fieldName 属性名称
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<String> empty(String fieldName) {
            return new JQLBinaryOpExpr<String>().setFieldName(fieldName).setOperator(Operator.EMPTY);
        }
    
        /**
         * 不为空
         *
         * @param fieldName 属性名称
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<String> notEmpty(String fieldName) {
            return new JQLBinaryOpExpr<String>().setFieldName(fieldName).setOperator(Operator.NOT_EMPTY);
        }
    
        /**
         * 在两者之间
         *
         * @param fieldName  属性名称
         * @param startValue 起始值
         * @param endValue   终止值
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<String> range(String fieldName, Object startValue, Object endValue) {
            return new JQLBinaryOpExpr<String>().setFieldName(fieldName).setOperator(Operator.RANGE).setValues(format(startValue), format(endValue));
        }
    
    }

###### 4.7 创建JSONSQL数字查询语法 ######

import cn.hutool.core.collection.ListUtil;
    import cn.hutool.core.lang.Opt;
    import cn.hutool.core.util.NumberUtil;
    import cn.hutool.core.util.StrUtil;
    import com.c3stones.json.enums.Operator;
    import com.c3stones.json.mapper.model.JQLBinaryOpExpr;
    import com.c3stones.json.parser.select.JSONDefaultSelect;
    
    import java.util.List;
    import java.util.Objects;
    
    /**
     * JSONSQL 数字查询语法
     *
     * @author CL
     */
    @SuppressWarnings("unchecked")
    public class NumberSelect implements JSONDefaultSelect {
    
        /**
         * 格式化值
         *
         * @param value 值
         * @return {@link String}
         */
        private Number format(Object value) {
            return NumberUtil.parseNumber(StrUtil.toStringOrNull(value));
        }
    
        /**
         * 格式化值
         *
         * @param value 值
         * @return {@link Number[]}
         */
        private Number[] format(List<Object> value) {
            return Opt.ofNullable(value).orElse(ListUtil.empty()).stream().map(StrUtil::toStringOrNull)
                    .filter(Objects::nonNull).map(NumberUtil::parseNumber).toArray(Number[]::new);
        }
    
        /**
         * 等于
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<Number> eq(String fieldName, Object value) {
            return new JQLBinaryOpExpr<Number>().setFieldName(fieldName).setOperator(Operator.EQ).setValues(format(value));
        }
    
        /**
         * 不等
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<Number> neq(String fieldName, Object value) {
            return new JQLBinaryOpExpr<Number>().setFieldName(fieldName).setOperator(Operator.NEQ).setValues(format(value));
        }
    
        /**
         * 大于
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<Number> gt(String fieldName, Object value) {
            return new JQLBinaryOpExpr<Number>().setFieldName(fieldName).setOperator(Operator.GT).setValues(format(value));
        }
    
        /**
         * 大于等于
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<Number> gte(String fieldName, Object value) {
            return new JQLBinaryOpExpr<Number>().setFieldName(fieldName).setOperator(Operator.GTE).setValues(format(value));
        }
    
        /**
         * 小于
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<Number> lt(String fieldName, Object value) {
            return new JQLBinaryOpExpr<Number>().setFieldName(fieldName).setOperator(Operator.LT).setValues(format(value));
        }
    
        /**
         * 小于等于
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<Number> lte(String fieldName, Object value) {
            return new JQLBinaryOpExpr<Number>().setFieldName(fieldName).setOperator(Operator.LTE).setValues(format(value));
        }
    
        /**
         * 属于
         *
         * @param fieldName 属性名称
         * @param values    值(不能包含英文逗号)
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<Number> in(String fieldName, List<Object> values) {
            return new JQLBinaryOpExpr<Number>().setFieldName(fieldName).setOperator(Operator.IN).setValues(format(values));
        }
    
        /**
         * 不属于
         *
         * @param fieldName 属性名称
         * @param values    值(不能包含英文逗号)
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<Number> notIn(String fieldName, List<Object> values) {
            return new JQLBinaryOpExpr<Number>().setFieldName(fieldName).setOperator(Operator.NOT_IN).setValues(format(values));
        }
    
        /**
         * 包含
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<Number> contain(String fieldName, Object value) {
            return new JQLBinaryOpExpr<Number>().setFieldName(fieldName).setOperator(Operator.CONTAIN).setValues(format(value));
        }
    
        /**
         * 不包含
         *
         * @param fieldName 属性名称
         * @param value     值
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<Number> notContain(String fieldName, Object value) {
            return new JQLBinaryOpExpr<Number>().setFieldName(fieldName).setOperator(Operator.NOT_CONSTAIN).setValues(format(value));
        }
    
        /**
         * 为空
         *
         * @param fieldName 属性名称
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<Number> empty(String fieldName) {
            return new JQLBinaryOpExpr<Number>().setFieldName(fieldName).setOperator(Operator.EMPTY);
        }
    
        /**
         * 不为空
         *
         * @param fieldName 属性名称
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<Number> notEmpty(String fieldName) {
            return new JQLBinaryOpExpr<Number>().setFieldName(fieldName).setOperator(Operator.NOT_EMPTY);
        }
    
        /**
         * 在两者之间
         *
         * @param fieldName  属性名称
         * @param startValue 起始值
         * @param endValue   终止值
         * @return {@link JQLBinaryOpExpr}
         */
        @Override
        public JQLBinaryOpExpr<Number> range(String fieldName, Object startValue, Object endValue) {
            return new JQLBinaryOpExpr<Number>().setFieldName(fieldName).setOperator(Operator.RANGE).setValues(format(startValue), format(endValue));
        }
    
    }

###### 4.8 创建JSONSQL查询语法处理工厂 ######

import cn.hutool.core.collection.ListUtil;
    import cn.hutool.core.lang.Opt;
    import com.c3stones.json.parser.select.impl.StringSelect;
    import com.c3stones.json.parser.select.impl.NumberSelect;
    
    import java.util.List;
    import java.util.Objects;
    
    /**
     * JSONSQL 查询语法处理工厂
     *
     * @author CL
     */
    public class JSONSelectFactory {
    
        /**
         * 根据数据自动匹配查询语法实现类
         *
         * @param obj 数据
         * @return {@link JSONDefaultSelect}
         */
        public static JSONDefaultSelect match(Object obj) {
            if (Objects.nonNull(obj) && Number.class.isAssignableFrom(obj.getClass())) {
                return match(Number.class);
            }
            return any();
        }
    
        /**
         * 根据数据自动匹配查询语法实现类
         *
         * @param list 数据
         * @return {@link JSONDefaultSelect}
         */
        public static JSONDefaultSelect match(List<Object> list) {
            if (Opt.ofNullable(list).orElse(ListUtil.empty()).stream().allMatch(value ->
                    Number.class.isAssignableFrom(value.getClass()))) {
                return numberSelect;
            }
            return stringSelect;
        }
    
        /**
         * 根据数据类型自动匹配查询语法实现类
         *
         * @param clazz 数据类型
         * @return {@link JSONDefaultSelect}
         */
        public static JSONDefaultSelect match(Class<?> clazz) {
            if (Objects.nonNull(clazz) && Number.class.isAssignableFrom(clazz)) {
                return numberSelect;
            }
            return stringSelect;
        }
    
        /**
         * 随机获取查询语法实现类
         *
         * @return {@link JSONDefaultSelect}
         */
        public static JSONDefaultSelect any() {
            return stringSelect;
        }
    
        /**
         * JSONSQL 数字查询语法
         */
        private static final JSONDefaultSelect numberSelect = new NumberSelect();
    
        /**
         * JSONSQL 字符串查询语法
         */
        private static final JSONDefaultSelect stringSelect = new StringSelect();
    
    }

###### 4.9 创建SQL 转 JSONSQL转换类 ######

import cn.hutool.core.collection.CollUtil;
    import cn.hutool.core.collection.ListUtil;
    import cn.hutool.core.lang.Opt;
    import cn.hutool.core.util.BooleanUtil;
    import cn.hutool.core.util.IdUtil;
    import cn.hutool.core.util.StrUtil;
    import com.alibaba.druid.DbType;
    import com.alibaba.druid.sql.ast.*;
    import com.alibaba.druid.sql.ast.expr.*;
    import com.alibaba.druid.sql.ast.statement.*;
    import com.alibaba.druid.sql.parser.SQLStatementParser;
    import com.baomidou.mybatisplus.core.toolkit.StringUtils;
    import com.c3stones.json.enums.AggregationType;
    import com.c3stones.json.enums.Operator;
    import com.c3stones.json.enums.OrderType;
    import com.c3stones.json.enums.Relation;
    import com.c3stones.json.mapper.JSONMapperProvider;
    import com.c3stones.json.mapper.model.*;
    import com.c3stones.json.parser.select.JSONDefaultSelect;
    import com.c3stones.json.parser.select.JSONSelectFactory;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    import lombok.RequiredArgsConstructor;
    import lombok.experimental.Accessors;
    import lombok.extern.slf4j.Slf4j;
    
    import java.util.Arrays;
    import java.util.Collections;
    import java.util.List;
    import java.util.Objects;
    import java.util.stream.Collectors;
    
    import static cn.hutool.core.text.CharPool.DOUBLE_QUOTES;
    import static cn.hutool.core.text.StrPool.*;
    import static com.alibaba.druid.sql.ast.expr.SQLBinaryOperator.*;
    
    /**
     * SQL 转 JSONSQL
     *
     * @author CL
     */
    @Slf4j
    @RequiredArgsConstructor
    @SuppressWarnings("all")
    public class JQLConvert {
        /**
         * SQL 常量 - *
         */
        public static final String SQL_ASTERISK = "*";
    
        private final JSONDefaultSelect jsonSelectSyntax;
    
        /**
         * 转换
         *
         * @param sql SQL
         * @return {@link JSONMapperProvider}
         */
        /**
         * 转换
         *
         * @param sql    SQL
         * @param dbType 数据库类型
         * @return {@link JSONMapperProvider}
         */
        public JSONMapperProvider convert(String sql, DbType dbType) {
            SQLStatementParser sqlStatementParser = new SQLStatementParser(sql, dbType);
            Opt<SQLSelectQueryBlock> optional = Opt.ofNullable(sqlStatementParser)
                    .map(parser -> (SQLSelectStatement) sqlStatementParser.parseStatement())
                    .map(SQLSelectStatement::getSelect)
                    .map(sqlSelect -> (SQLSelectQueryBlock) sqlSelect.getQuery());
            return optional.isPresent() ? handle(optional.get()).setSql(sql) : new JSONMapperProvider(sql);
        }
    
        /**
         * 处理SQL
         *
         * @param sqlSelectQuery SQL Select查询
         * @return {@link JSONMapperProvider}
         */
        private JSONMapperProvider handle(SQLSelectQueryBlock sqlSelectQuery) {
            // 处理 Select
            List<SelectField> selectFieldList = handleSelect(sqlSelectQuery.getSelectList());
            // 处理 From
            String json = handleFrom(sqlSelectQuery.getFrom());
            // 处理 Where
            JQLWhereModel where = handleWhere(sqlSelectQuery.getWhere());
            // 处理 Aggregation
            List<JQLAggregationModel> aggregationList = handleAggregate(selectFieldList);
            // 处理 Group
            JQLGroupModel group = handleGroup(sqlSelectQuery.getGroupBy());
            // 处理 OrderBy
            List<JQLSortModel> sortList = handleOrderBy(sqlSelectQuery.getOrderBy());
            // 处理 Limit
            Page page = handleLimit(sqlSelectQuery.getLimit());
            // 生成JQL
            Integer from = Opt.ofNullable(page).map(Page::getFrom).get();
            Integer size = Opt.ofNullable(page).map(Page::getSize).get();
            JQLModel jql = jsonSelectSyntax.jql(where, aggregationList, group, sortList, from, size);
            String[] includes = selectFieldList.stream().map(field -> Opt.ofNullable(field.getAlias()).orElse(field.getName()))
                    .filter(fieldName -> !StrUtil.equals(SQL_ASTERISK, fieldName) && Objects.nonNull(fieldName)).toArray(String[]::new);
            return new JSONMapperProvider().setJson(json).setJql(jql).setIncludes(includes);
        }
    
        /**
         * 处理查询字段
         *
         * @param sqlSelectItemList 查询元素
         * @return {@link List<SelectField>}
         */
        private List<SelectField> handleSelect(List<SQLSelectItem> sqlSelectItemList) {
            return Opt.ofNullable(sqlSelectItemList).orElse(Collections.emptyList())
                    .stream().map(sqlSelectItem -> {
                        String name = null, alias, methodName = null;
                        alias = sqlSelectItem.getAlias();
                        // SQL 表达式
                        SQLExpr sqlExpr = sqlSelectItem.getExpr();
                        if (sqlExpr instanceof SQLAggregateExpr) { // 聚合查询
                            SQLAggregateExpr sqlAggregateExpr = (SQLAggregateExpr) sqlExpr;
                            SQLExpr firstSqlExpr = CollUtil.getFirst(sqlAggregateExpr.getArguments());
                            methodName = sqlAggregateExpr.getMethodName();
                            if (firstSqlExpr instanceof SQLAllColumnExpr) {
                                name = SQL_ASTERISK;
                            } else if (firstSqlExpr instanceof SQLIdentifierExpr) {
                                name = ((SQLIdentifierExpr) firstSqlExpr).getName();
                            }
                            alias = Opt.ofNullable(alias).orElse(methodName);
                        } else if (sqlExpr instanceof SQLAllColumnExpr) {  // 查询全部
                            name = SQL_ASTERISK;
                        } else if (sqlExpr instanceof SQLIdentifierExpr) { // 查询指定列
                            name = ((SQLIdentifierExpr) sqlExpr).getName();
                        } else if (sqlExpr instanceof SQLPropertyExpr) { // 查询对象列
                            SQLPropertyExpr sqlPropertyExpr = (SQLPropertyExpr) sqlExpr;
                            name = sqlPropertyExpr.getOwnerName() + StrUtil.DOT + sqlPropertyExpr.getName();
                        }
                        return new SelectField(name, alias, methodName);
                    }).collect(Collectors.toList());
        }
    
        /**
         * 处理 From
         *
         * @param sqlTableSource SQL表资源
         * @return {@link String}
         */
        private String handleFrom(SQLTableSource sqlTableSource) {
            String index = null;
            if (sqlTableSource instanceof SQLExprTableSource) {
                SQLExpr tableSqlExpr = ((SQLExprTableSource) sqlTableSource).getExpr();
                if (tableSqlExpr instanceof SQLIdentifierExpr) {
                    index = ((SQLIdentifierExpr) tableSqlExpr).getName();
                }
            }
            return index;
        }
    
        /**
         * 处理 Where条件
         *
         * @param sqlExpr SQL表达式
         * @return {@link JQLWhereModel}
         */
        private JQLWhereModel handleWhere(SQLExpr sqlExpr) {
            if (sqlExpr instanceof SQLBinaryOpExpr) {
                SQLBinaryOpExpr sqlBinaryOpExpr = (SQLBinaryOpExpr) sqlExpr;
                SQLBinaryOperator operator = sqlBinaryOpExpr.getOperator();
                if (BooleanAnd == operator || BooleanOr == operator) {
                    return handleWhereAndOrOr(sqlBinaryOpExpr, operator);
                }
                return handleWhereBinaryOp(sqlBinaryOpExpr);
            } else if (sqlExpr instanceof SQLInListExpr) {
                return handleWhereInOrNotIn((SQLInListExpr) sqlExpr);
            } else if (sqlExpr instanceof SQLBetweenExpr) {
                return handleWhereBetween((SQLBetweenExpr) sqlExpr);
            } else if (sqlExpr instanceof SQLMethodInvokeExpr) {
                return handleMethodInvoke((SQLMethodInvokeExpr) sqlExpr);
            }
            return jsonSelectSyntax.all();
        }
    
        /**
         * 处理 AND 或 OR
         *
         * @param sqlBinaryOpExpr SQL两位元素操作
         * @return {@link JQLWhereModel}
         */
        private JQLWhereModel handleWhereAndOrOr(SQLBinaryOpExpr sqlBinaryOpExpr,
                                                 SQLBinaryOperator sqlBinaryOperator) {
            SQLExpr leftExpr = sqlBinaryOpExpr.getLeft();
            SQLExpr rightExpr = sqlBinaryOpExpr.getRight();
            JQLWhereModel left = handleWhere(leftExpr);
            JQLWhereModel right = handleWhere(rightExpr);
            Integer leftSize = Opt.ofNullable(left).map(JQLWhereModel::getBinaryOpExprList).map(List::size).orElse(0);
            Integer rightSize = Opt.ofNullable(right).map(JQLWhereModel::getBinaryOpExprList).map(List::size).orElse(0);
            Relation relation = (sqlBinaryOperator == BooleanAnd) ? Relation.AND : Relation.OR;
            if (leftSize > 1 || rightSize > 1 || CollUtil.isNotEmpty(left.getChildWhereList())
                    || CollUtil.isNotEmpty(right.getChildWhereList())) {
                return jsonSelectSyntax.child(relation, left, right);
            }
            List<JQLBinaryOpExpr> leftList = Opt.ofNullable(left.getBinaryOpExprList()).orElse(CollUtil.newArrayList());
            List<JQLBinaryOpExpr> rightList = Opt.ofNullable(right.getBinaryOpExprList()).orElse(CollUtil.newArrayList());
            return Objects.equals(Relation.AND, relation) ?
                    jsonSelectSyntax.and(CollUtil.addAll(leftList, rightList).toArray(new JQLBinaryOpExpr[0]))
                    : jsonSelectSyntax.or(CollUtil.addAll(leftList, rightList).toArray(new JQLBinaryOpExpr[0]));
        }
    
        /**
         * 处理二位元素操作
         *
         * @param sqlExpr SQL表达式
         * @return {@link JQLWhereModel}
         */
        private JQLWhereModel handleWhereBinaryOp(SQLBinaryOpExpr sqlExpr) {
            List<JQLBinaryOpExpr> list = CollUtil.newArrayList();
            SQLExpr leftExpr = sqlExpr.getLeft();
            SQLExpr rightExpr = sqlExpr.getRight();
            // 特殊处理 1 = 1 / 1 != 1
            if (leftExpr instanceof SQLIntegerExpr && rightExpr instanceof SQLIntegerExpr) {
                if (Objects.equals(getValue(leftExpr), getValue(rightExpr))) {
                    if (sqlExpr.getOperator() == SQLBinaryOperator.Equality) {
                        list.add(jsonSelectSyntax.empty(IdUtil.fastUUID()));
                    } else {
                        list.add(jsonSelectSyntax.notEmpty(IdUtil.fastUUID()));
                    }
                }
            } else {
                String fieldName = null;
                if (leftExpr instanceof SQLIdentifierExpr) {
                    SQLIdentifierExpr sqlIdentifierExpr = (SQLIdentifierExpr) leftExpr;
                    fieldName = sqlIdentifierExpr.getName();
                } else if (leftExpr instanceof SQLPropertyExpr) {
                    SQLPropertyExpr sqlPropertyExpr = (SQLPropertyExpr) leftExpr;
                    fieldName = sqlPropertyExpr.getOwnerName() + StrUtil.DOT + sqlPropertyExpr.getName();
                } else if (leftExpr instanceof SQLMethodInvokeExpr) {
                    return handleMethodInvoke((SQLMethodInvokeExpr) leftExpr);
                }
                Object value = getValue(rightExpr);
                JSONDefaultSelect matchSelect = JSONSelectFactory.match(value);
                switch (sqlExpr.getOperator()) {
                    case Equality:
                        list.add(matchSelect.eq(fieldName, value));
                        break;
                    case NotEqual:
                    case LessThanOrGreater:
                        list.add(matchSelect.neq(fieldName, value));
                        break;
                    case GreaterThan:
                        list.add(matchSelect.gt(fieldName, value));
                        break;
                    case GreaterThanOrEqual:
                        list.add(matchSelect.gte(fieldName, value));
                        break;
                    case LessThan:
                        list.add(matchSelect.lt(fieldName, value));
                        break;
                    case LessThanOrEqual:
                        list.add(matchSelect.lte(fieldName, value));
                        break;
                    case Like:
                        list.add(matchSelect.contain(fieldName, value));
                        break;
                    case NotLike:
                        list.add(matchSelect.notContain(fieldName, value));
                        break;
                    case Is:
                        list.add(matchSelect.empty(fieldName));
                        break;
                    case IsNot:
                        list.add(matchSelect.notEmpty(fieldName));
                        break;
                    default:
                        // no operate
                }
            }
            return jsonSelectSyntax.and(list.toArray(new JQLBinaryOpExpr[0]));
        }
    
        /**
         * 处理 in 或 notIn
         *
         * @param sqlInListExpr SQL In 表达式
         * @return {@link JQLWhereModel}
         */
        private JQLWhereModel handleWhereInOrNotIn(SQLInListExpr sqlInListExpr) {
            SQLIdentifierExpr sqlIdentifierExpr = (SQLIdentifierExpr) sqlInListExpr.getExpr();
            String fieldName = sqlIdentifierExpr.getName();
            List<Object> values = sqlInListExpr.getTargetList().stream().map(this::getValue).collect(Collectors.toList());
            JSONDefaultSelect matchSelect = JSONSelectFactory.match(values);
            return jsonSelectSyntax.and(sqlInListExpr.isNot() ? matchSelect.notIn(fieldName, values) : matchSelect.in(fieldName, values));
        }
    
        /**
         * 处理 between
         *
         * @param sqlBetweenExpr SQL Between 表达式
         * @return {@link JQLWhereModel}
         */
        private JQLWhereModel handleWhereBetween(SQLBetweenExpr sqlBetweenExpr) {
            SQLIdentifierExpr sqlIdentifierExpr = (SQLIdentifierExpr) sqlBetweenExpr.getTestExpr();
            String field = sqlIdentifierExpr.getName();
            Object startValue = getValue(sqlBetweenExpr.getBeginExpr());
            Object endValue = getValue(sqlBetweenExpr.getEndExpr());
            JSONDefaultSelect matchSelect = JSONSelectFactory.match(Arrays.asList(startValue, endValue));
            return jsonSelectSyntax.and(matchSelect.range(field, startValue, endValue));
        }
    
        /**
         * 处理函数调用
         *
         * @param sqlMethodInvokeExpr SQL函数调用表达式
         * @return {@link JQLWhereModel}
         */
        private JQLWhereModel handleMethodInvoke(SQLMethodInvokeExpr sqlMethodInvokeExpr) {
            String methodName = sqlMethodInvokeExpr.getMethodName();
            List<SQLExpr> arguments = sqlMethodInvokeExpr.getArguments();
            SQLExpr leftExpr = CollUtil.get(arguments, 0);
            SQLExpr rightExpr = CollUtil.get(arguments, 1);
    
            String fieldName = null;
            if (leftExpr instanceof SQLIdentifierExpr) {
                SQLIdentifierExpr sqlIdentifierExpr = (SQLIdentifierExpr) leftExpr;
                fieldName = sqlIdentifierExpr.getName();
            } else if (leftExpr instanceof SQLPropertyExpr) {
                SQLPropertyExpr sqlPropertyExpr = (SQLPropertyExpr) leftExpr;
                fieldName = sqlPropertyExpr.getOwnerName() + StrUtil.DOT + sqlPropertyExpr.getName();
            }
    
            if (StrUtil.equalsAnyIgnoreCase(methodName, "json_contain", "json_not_contain", "json_contains")) {
                List<Object> values = null;
                if (rightExpr instanceof SQLArrayExpr) {
                    values = ((SQLArrayExpr) rightExpr).getValues().stream().map(this::getValue).collect(Collectors.toList());
                } else if (rightExpr instanceof SQLCharExpr) {
                    values = Opt.ofNullable(((SQLCharExpr) rightExpr)).map(SQLCharExpr::getValue).map(str -> StrUtil.strip(
                                    StrUtil.toStringOrNull(str), StrUtil.toString(C_BRACKET_START), StrUtil.toString(C_BRACKET_END)))
                            .map(str -> StrUtil.strip(str, COMMA)).map(CollUtil::newArrayList).orElse(CollUtil.newArrayList())
                            .stream().map(str -> StrUtil.strip(StrUtil.toStringOrNull(str), StrUtil.toString(DOUBLE_QUOTES),
                                    StrUtil.toString(DOUBLE_QUOTES))).map(StrUtil::trim).collect(Collectors.toList());
                }
                JSONDefaultSelect matchSelect = JSONSelectFactory.match(values);
                boolean isContain = Objects.equals(Operator.IN, Operator.findByValue(methodName));
                SQLObject parent = sqlMethodInvokeExpr.getParent();
                if (parent instanceof SQLBinaryOpExpr) {
                    SQLBinaryOperator operator = ((SQLBinaryOpExpr) parent).getOperator();
                    Boolean value = BooleanUtil.toBoolean(StrUtil.toStringOrNull(getValue(((SQLBinaryOpExpr) parent).getRight())));
                    if (BooleanUtil.isTrue(value)) {
                        isContain = Objects.equals(Equality, operator) ? true : false;
                    } else {
                        isContain = Objects.equals(Equality, operator) ? false : true;
                    }
                }
                return jsonSelectSyntax.and(isContain ? matchSelect.in(fieldName, values) : matchSelect.notIn(fieldName, values));
            } else if (StrUtil.equalsAnyIgnoreCase(methodName, "jsonb_array_length", "json_length")) {
                SQLObject parent = sqlMethodInvokeExpr.getParent();
                if (parent instanceof SQLBinaryOpExpr) {
                    SQLBinaryOpExpr sqlBinaryOpExpr = (SQLBinaryOpExpr) parent;
                    JSONDefaultSelect matchSelect = JSONSelectFactory.any();
                    return jsonSelectSyntax.and(Objects.equals(Equality, sqlBinaryOpExpr.getOperator()) ?
                            matchSelect.empty(fieldName) : matchSelect.notEmpty(fieldName));
                }
            }
            return jsonSelectSyntax.all();
        }
    
        /**
         * 处理聚合函数
         *
         * @param selectFieldList 查询字段
         * @return {@link List<JQLAggregationModel>}
         */
        private List<JQLAggregationModel> handleAggregate(List<SelectField> selectFieldList) {
            List<JQLAggregationModel> list = CollUtil.newArrayList();
            for (SelectField selectField : Opt.ofNullable(selectFieldList).orElse(ListUtil.empty())) {
                AggregationType aggregationType = AggregationType.findByValue(selectField.getMethodName());
                if (Objects.isNull(aggregationType)) continue;
                list.add(new JQLAggregationModel(aggregationType, selectField.getName(),
                        Opt.ofNullable(selectField.getAlias()).orElse(selectField.getMethodName())));
            }
            return list;
        }
    
        /**
         * 处理分组表
         *
         * @param sqlSelectGroupByClause SQL GroupBy 从句
         * @return {@link JQLGroupModel}
         */
        private JQLGroupModel handleGroup(SQLSelectGroupByClause sqlSelectGroupByClause) {
            if (Objects.nonNull(sqlSelectGroupByClause)) {
                List<String> groupNameList = CollUtil.newArrayList();
                for (SQLExpr sqlExpr : sqlSelectGroupByClause.getItems()) {
                    if (sqlExpr instanceof SQLIdentifierExpr) {
                        groupNameList.add(((SQLIdentifierExpr) sqlExpr).getName());
                    }
                }
                return new JQLGroupModel(groupNameList);
            }
            return null;
        }
    
        /**
         * 处理 OrderBy
         *
         * @param sqlOrderBy SQL OrderBy
         * @return {@link String}
         */
        private List<JQLSortModel> handleOrderBy(SQLOrderBy sqlOrderBy) {
            List<JQLSortModel> list = CollUtil.newArrayList();
            for (SQLSelectOrderByItem sqlSelectOrderByItem : Opt.ofNullable(sqlOrderBy).map(SQLOrderBy::getItems).orElse(ListUtil.empty())) {
                SQLIdentifierExpr orderBySqlIdentifierExpr = (SQLIdentifierExpr) sqlSelectOrderByItem.getExpr();
                SQLOrderingSpecification sqlOrderingSpecification = sqlSelectOrderByItem.getType();
                list.add(new JQLSortModel(orderBySqlIdentifierExpr.getName(), OrderType.findByValue(StrUtil.toStringOrNull(sqlOrderingSpecification))));
            }
            return list;
        }
    
        /**
         * 处理 Limit
         *
         * @param sqlLimit SQL Limit
         * @return {@link Page}
         */
        private Page handleLimit(SQLLimit sqlLimit) {
            if (Objects.isNull(sqlLimit)) return null;
            SQLIntegerExpr sqlLimitOffset = (SQLIntegerExpr) sqlLimit.getOffset();
            SQLIntegerExpr sqlLimitRowCount = (SQLIntegerExpr) sqlLimit.getRowCount();
            Integer from = Objects.isNull(sqlLimitOffset) ? 0 : sqlLimitOffset.getNumber().intValue();
            Integer size = sqlLimitRowCount.getNumber().intValue();
            return new Page().setFrom(from).setSize(size);
        }
    
        /**
         * 获取值
         *
         * @param sqlExpr 表达式
         * @return {@link Object}
         */
        private Object getValue(SQLExpr sqlExpr) {
            Object value = null;
            if (sqlExpr instanceof SQLIntegerExpr) {
                value = ((SQLIntegerExpr) sqlExpr).getNumber();
            } else if (sqlExpr instanceof SQLCharExpr) {
                value = ((SQLCharExpr) sqlExpr).getText();
            } else if (sqlExpr instanceof SQLNumberExpr) {
                value = ((SQLNumberExpr) sqlExpr).getNumber();
            } else if (sqlExpr instanceof SQLMethodInvokeExpr) {
                SQLMethodInvokeExpr methodInvokeExpr = (SQLMethodInvokeExpr) sqlExpr;
                String methodName = methodInvokeExpr.getMethodName();
                List<SQLExpr> arguments = methodInvokeExpr.getArguments();
                if (StrUtil.containsIgnoreCase("concat", methodName)) {
                    value = arguments.stream().map(this::getValue).map(StrUtil::toStringOrNull)
                            .filter(Objects::nonNull).collect(Collectors.joining());
                } else if (StrUtil.equalsAnyIgnoreCase(methodName, "lower", "upper")) {
                    return getValue(CollUtil.getFirst(arguments));
                } else if (StrUtil.equalsAnyIgnoreCase(methodName, "to_timestamp", "from_unixtime")) {
                    Object tmp = getValue(CollUtil.getFirst(arguments));
                    return CollUtil.getFirst(StrUtil.split(tmp.toString(), StrUtil.DOT));
                }
            } else if (sqlExpr instanceof SQLCastExpr) {
                SQLCastExpr sqlCastExpr = (SQLCastExpr) sqlExpr;
                return getValue(sqlCastExpr.getExpr());
            } else if (sqlExpr instanceof SQLBinaryOpExpr) {
                SQLBinaryOpExpr sqlBinaryOpExpr = (SQLBinaryOpExpr) sqlExpr;
                return getValue(sqlBinaryOpExpr.getLeft()) + StrUtil.DOT
                        + getValue(sqlBinaryOpExpr.getRight());
            }
            return value;
        }
    
        /**
         * 字符串转义
         *
         * @param str 字符串
         * @return {@link String}
         */
        private String escape(String str) {
            if (StringUtils.isBlank(str)) return str;
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < str.length(); i++) {
                char c = str.charAt(i);
                if (Character.isWhitespace(c) ||
                        c == '\\' ||
                        c == '\"' ||
                        c == '+' ||
                        c == '-' ||
                        c == '!' ||
                        c == '(' ||
                        c == ')' ||
                        c == '[' ||
                        c == ']' ||
                        c == '{' ||
                        c == '}' ||
                        c == ':' ||
                        c == '^' ||
                        c == '~' ||
                        c == '*' ||
                        c == '?' ||
                        c == '|' ||
                        c == '&' ||
                        c == ';' ||
                        c == '/' ||
                        c == '.' ||
                        c == '$') {
                    sb.append('\\').append('\\');
                }
                sb.append(c);
            }
            return sb.toString();
        }
    
        /**
         * 查询字段
         */
        @Data
        @NoArgsConstructor
        @AllArgsConstructor
        private static class SelectField {
    
            /**
             * 字段名
             */
            private String name;
    
            /**
             * 别名
             */
            private String alias;
    
            /**
             * 方法名
             */
            private String methodName;
    
        }
    
        /**
         * 翻页
         */
        @Data
        @NoArgsConstructor
        @AllArgsConstructor
        @Accessors(chain = true)
        private static class Page {
    
            /**
             * 开始位置
             */
            private Integer from;
    
            /**
             * 页大小
             */
            private Integer size;
    
        }
    
    }

###### 4.10 创建JSONSQL异常类 ######

/**
     * JSONSQL 异常
     *
     * @author CL
     */
    public class JQLException extends RuntimeException {
    
        private final ErrorCode errorCode;
    
        public JQLException(ErrorCode errorCode) {
            super(errorCode.getReason());
            this.errorCode = errorCode;
        }
        public JQLException(ErrorCode errorCode, String message, Throwable cause) {
            super(message, cause);
            this.errorCode = errorCode;
        }
    
        @Override
        public String getMessage() {
            return this.errorCode.getReason() + "， 建议：" + this.errorCode.getSuggest();
        }
    
    }

import lombok.AllArgsConstructor;
    import lombok.Getter;
    
    /**
     * 错误码
     *
     * @author CL
     */
    @Getter
    @AllArgsConstructor
    public enum ErrorCode {
    
        PROVIDER_MISS_ERROR(
                -1,
                "参数为空",
                "请调用#{com.c3stones.json.parser.convert.JQLConvert.convert(String sql)}方法得到#{com.c3stones.json.mapper.JSONMapperProvider}"
        ),
        DATA_MISS_ERROR(
                -2,
                "数据为空",
                "请调用#{com.*.*.biz.mapper.json.JSONMapperProvider.setDataModel(List<DataModel> dataList)}设置数据。"
        ),
        JQL_MISS_ERROR(
                -3,
                "JQL为空",
                "请检查#{com.*.*.biz.mapper.json.JSONMapperProvider}中的#{jql}属性，它由#{com.c3stones.json.parser.convert.JQLConvert.convert(String sql)}方法设置。"
        ),
        JQL_WHERE_RELATION_MISS_ERROR(
                -4,
                "JQL Where中Relation为空",
                "请检查#{com.c3stones.json.mapper.model.JQLModel}中的#{where}属性中的#{relation}属性，它由#{com.c3stones.json.parser.convert.JQLConvert.handleWhere(SQLExpr sqlExpr)}方法设置，可能是使用了除[and、or]之外的连接符。"
        ),
        JQL_WHERE_BINARYOPEXPR_FIELDNAME_MISS_ERROR(
                -5,
                "JQL Where条件中二位操作表达式的属性名称为空",
                "请检查#{com.c3stones.json.mapper.model.JQLModel}中的#{where}属性中的#{binaryOpExprList}属性，它由#{com.c3stones.json.parser.convert.JQLConvert.handleWhere(SQLExpr sqlExpr)}方法设置，可能是使用了除[json_contain、json_not_contain、jsonb_array_length]之外的函数包装属性名称。"
        ),
        JQL_WHERE_BINARYOPEXPR_OPERATOR_MISS_ERROR(
                -6,
                "JQL Where条件中二位操作表达式的操作符为空",
                "请检查#{com.c3stones.json.mapper.model.JQLModel}中的#{where}属性中的#{binaryOpExprList}属性，它由#{com.c3stones.json.parser.convert.JQLConvert.handleWhere(SQLExpr sqlExpr)}方法设置，可能是使用了除[=、!=、>、>=、<、<=、<>、like、not like、is、is not]之外的操作符，或者使用了代码中未适配的函数调用。"
        ),
        JQL_AGGREGATION_TYPE_MISS_ERROR(
                -7,
                "JQL 聚合操作中聚合类型为空",
                "请检查#{com.c3stones.json.mapper.model.JQLModel}中的#{binaryOpExprList}属性中的#{aggregationType}属性，它由#{com.c3stones.json.parser.convert.JQLConvert.handleAggregate(List<SelectField> selectFieldList)}方法设置，可能是使用了除[count、max、min、avg、sum]之外的聚合函数。"
        ),
        JQL_AGGREGATION_FIELDNAME_MISS_ERROR(
                -8,
                "JQL 聚合操作中属性名称为空",
                "请检查#{com.c3stones.json.mapper.model.JQLModel}中的#{binaryOpExprList}属性中的#{fieldName}属性，它由#{com.c3stones.json.parser.convert.JQLConvert.handleAggregate(List<SelectField> selectFieldList)}方法设置，可能是没有设置属性。"
        ),
        JQL_GROUP_FIELNAME_MISS_ERROR(
                -9,
                "JQL 分组属性为空",
                "请检查#{com.c3stones.json.mapper.model.JQLModel}中的#{group}属性中的#{groupNameList}属性，它由#{com.c3stones.json.parser.convert.JQLConvert.handleGroup(SQLSelectGroupByClause sqlSelectGroupByClause)}方法设置，如果设置了属性别名请使用别名，否则请检查是否和属性名称一致，很小可能是没有设置分组属性或使用了函数进行了包装。"
        ),
        JQL_SORT_FIELDNAME_MISS_ERROR(
                -10,
                "JQL 排序中属性名称为空",
                "请检查#{com.c3stones.json.mapper.model.JQLModel}中的#{sorts}属性中的#{fieldName}属性，它由#{com.c3stones.json.parser.convert.JQLConvert.handleOrderBy(SQLOrderBy sqlOrderBy)}方法设置，可能是没有设置参数，如果设置了属性别名请使用别名，否则请检查是否和属性名称一致，很小可能是没有设置排序属性。"
        ),
        JQL_FROM_MISS_ERROR(
                -11,
                "JQL 偏移数为空",
                "请检查#{com.c3stones.json.mapper.model.JQLModel}中的#{from}属性，它由#{com.c3stones.json.parser.convert.JQLConvert.handleLimit(SQLLimit sqlLimit)}方法设置，它可能小于0，或者没有设置值。"
        ),
        JQL_SIZE_MISS_ERROR(
                -12,
                "JQL 限制数为空",
                "请检查#{com.c3stones.json.mapper.model.JQLModel}中的#{size}属性，它由#{com.c3stones.json.parser.convert.JQLConvert.handleLimit(SQLLimit sqlLimit)}方法设置，它可能小于0，或者没有设置值。"
        ),
        FILTER_EXECUTE_ERROR(
                -13,
                "过滤二元表达式异常",
                "请检查SQL中的Where条件，可能在#{com.c3stones.json.parser.convert.JQLConvert.convert(String sql)}方法中处理#{com.c3stones.json.mapper.model.JQLWhereModel}时存在不支持的语法，也可能在将不同的数据类型转换成通用的Number或String类型处理时发生异常。如果您熟悉#{com.c3stones.json.mapper.model.JQLWhereModel}的数据定义，直接检查它将会提高解决的效率。"
        ),
        ;
    
        /**
         * 错误码
         */
        private final Integer code;
    
        /**
         * 原因
         */
        private final String reason;
    
        /**
         * 建议
         */
        private final String suggest;
    
    }

###### 4.11 创建JSONSQL断言类 ######

import cn.hutool.core.lang.Opt;
    import com.c3stones.json.enums.Operator;
    import com.c3stones.json.mapper.JSONMapperProvider;
    import com.c3stones.json.mapper.exception.ErrorCode;
    import com.c3stones.json.mapper.exception.JQLException;
    import com.c3stones.json.mapper.model.*;
    import org.springframework.util.Assert;
    
    import java.math.BigInteger;
    import java.util.Collection;
    import java.util.Objects;
    
    import static cn.hutool.core.collection.CollUtil.newArrayList;
    import static cn.hutool.core.collection.ListUtil.empty;
    
    /**
     * JSONSQL 断言
     *
     * @author CL
     */
    public class JQLAssert {
    
        /**
         * 校验JSONSQL语法和
         *
         * @param provider JSON Mapper Provider
         */
        public static void validated(JSONMapperProvider provider) {
            validated((Object) provider);
            validated(provider.getDataList());
            validated(provider.getJql());
            JQLModel jql = provider.getJql();
            validated(jql.getWhere());
            validated(jql.getAggregations());
            validated(jql.getGroup());
            validated(jql.getSorts());
            validated(jql.getFrom());
            validated(jql.getSize());
        }
    
        /**
         * 对象断言
         *
         * @param obj 对象
         */
        private static void validated(Object obj) {
            if (obj instanceof JSONMapperProvider) {
                notNull(obj, ErrorCode.PROVIDER_MISS_ERROR);
            } else if (obj instanceof JQLModel) {
                notNull(obj, ErrorCode.JQL_MISS_ERROR);
            } else if (obj instanceof JQLWhereModel) {
                JQLWhereModel where = (JQLWhereModel) obj;
                if (Objects.nonNull(where.getBinaryOpExprList()) || Objects.nonNull(where.getChildWhereList()))
                    notNull(obj, ErrorCode.JQL_WHERE_RELATION_MISS_ERROR);
                Opt.ofNullable(where.getBinaryOpExprList()).orElse(empty()).forEach(expr -> validated(expr));
                Opt.ofNullable(where.getChildWhereList()).orElse(empty()).forEach(childWhere -> validated(childWhere));
            } else if (obj instanceof JQLBinaryOpExpr) {
                JQLBinaryOpExpr binaryOpExpr = (JQLBinaryOpExpr) obj;
                notNull(binaryOpExpr.getFieldName(), ErrorCode.JQL_WHERE_BINARYOPEXPR_FIELDNAME_MISS_ERROR);
                notNull(binaryOpExpr.getOperator(), ErrorCode.JQL_WHERE_BINARYOPEXPR_OPERATOR_MISS_ERROR);
            } else if (obj instanceof JQLAggregationModel) {
                JQLAggregationModel aggregation = (JQLAggregationModel) obj;
                notNull(aggregation.getAggregationType(), ErrorCode.JQL_AGGREGATION_TYPE_MISS_ERROR);
                notNull(aggregation.getFieldName(), ErrorCode.JQL_AGGREGATION_FIELDNAME_MISS_ERROR);
            } else if (obj instanceof JQLGroupModel) {
                JQLGroupModel group = (JQLGroupModel) obj;
                noNullElements(group.getGroupNameList(), ErrorCode.JQL_GROUP_FIELNAME_MISS_ERROR);
            } else if (obj instanceof JQLSortModel) {
                JQLSortModel sort = (JQLSortModel) obj;
                notNull(sort.getFieldName(), ErrorCode.JQL_SORT_FIELDNAME_MISS_ERROR);
            } else if (obj instanceof Integer) {
                Integer integer = (Integer) obj;
                gte(integer, BigInteger.ZERO.intValue(), ErrorCode.JQL_FROM_MISS_ERROR);
            }
        }
    
        /**
         * 校验集合参数
         *
         * @param collection 集合
         */
        private static void validated(Collection collection) {
            Opt.ofNullable(collection).orElse(newArrayList()).forEach(obj -> validated(obj));
        }
    
        /**
         * 对象非空断言
         *
         * @param obj       对象
         * @param errorCode 错误码
         */
        private static void notNull(Object obj, ErrorCode errorCode) {
            Assert.notNull(obj, () -> {
                throw new JQLException(errorCode);
            });
        }
    
        /**
         * 集合非空断言
         *
         * @param collection 对象
         * @param errorCode  错误码
         */
        private static void noNullElements(Collection collection, ErrorCode errorCode) {
            Assert.notEmpty(collection, () -> {
                throw new JQLException(errorCode);
            });
        }
    
        /**
         * a大于等于b断言
         *
         * @param a         数字a
         * @param b         数字b
         * @param errorCode 财错误码
         */
        private static void gte(Number a, Number b, ErrorCode errorCode) {
            Assert.isTrue(Operator.judgeNumber(a, Operator.GTE, b), () -> {
                throw new JQLException(errorCode);
            });
        }
    
    }

###### 4.12 创建JSONSQL查询Mapper ######

import cn.hutool.core.collection.CollUtil;
    import cn.hutool.core.collection.ListUtil;
    import cn.hutool.core.date.DateTime;
    import cn.hutool.core.date.DateUtil;
    import cn.hutool.core.lang.Opt;
    import cn.hutool.core.map.MapBuilder;
    import cn.hutool.core.map.MapUtil;
    import cn.hutool.core.util.ArrayUtil;
    import cn.hutool.core.util.NumberUtil;
    import cn.hutool.core.util.StrUtil;
    import com.alibaba.fastjson2.JSON;
    import com.alibaba.fastjson2.JSONObject;
    import com.alibaba.fastjson2.util.TypeUtils;
    import com.baomidou.mybatisplus.autoconfigure.MybatisPlusProperties;
    import com.c3stones.json.enums.Operator;
    import com.c3stones.json.enums.OrderType;
    import com.c3stones.json.enums.Relation;
    import com.c3stones.json.mapper.exception.ErrorCode;
    import com.c3stones.json.mapper.exception.JQLException;
    import com.c3stones.json.mapper.model.*;
    import com.c3stones.json.mapper.valid.JQLAssert;
    import lombok.extern.slf4j.Slf4j;
    import org.apache.ibatis.session.Configuration;
    import org.springframework.stereotype.Component;
    
    import javax.annotation.Resource;
    import java.math.BigDecimal;
    import java.math.BigInteger;
    import java.math.RoundingMode;
    import java.util.*;
    import java.util.stream.Collectors;
    import java.util.stream.Stream;
    
    /**
     * JSON Mapper
     *
     * @author CL
     */
    @Slf4j
    @Component
    public class JSONMapper {
    
        @Resource
        private MybatisPlusProperties mybatisPlusProperties;
    
        /**
         * 聚合
         *
         * @param provider JSON Mapper Provider
         * @return {@link List}
         */
        public List<Map<String, Object>> aggregation(JSONMapperProvider provider) {
            return execute(provider);
        }
    
        /**
         * 查询
         *
         * @param provider JSON Mapper Provider
         * @return {@link List}
         */
        public List<Map<String, Object>> query(JSONMapperProvider provider) {
            return execute(provider);
        }
    
        /**
         * 执行
         *
         * @param provider JSON Mapper Provider
         * @return {@link List}
         */
        private List<Map<String, Object>> execute(JSONMapperProvider provider) {
            // 如果开启Mybatis-Plus SQL打印，则同时打印 JQL
            if (Opt.ofNullable(mybatisPlusProperties).map(MybatisPlusProperties::getConfiguration).map(Configuration::getLogImpl).isPresent()
                    && log.isInfoEnabled()) {
                log.info(JSONObject.toJSONString(provider));
            }
    
            JQLAssert.validated(provider);
            if (CollUtil.isEmpty(provider.getDataList())) return ListUtil.empty();
            List<JSONObject> dataList = provider.getDataList().stream().map(DataModel::getData).collect(Collectors.toList());
            if (CollUtil.isEmpty(dataList)) return ListUtil.empty();
            String[] includes = provider.getIncludes();
            String[] excludes = provider.getExcludes();
            JQLModel jql = provider.getJql();
            JQLWhereModel where = jql.getWhere();
            JQLGroupModel group = jql.getGroup();
            List<JQLAggregationModel> aggregations = jql.getAggregations();
            List<JQLSortModel> sorts = jql.getSorts();
            Integer from = jql.getFrom();
            Integer size = jql.getSize();
    
            // 过滤
            List<JSONObject> filterList = dataList.stream().filter(data -> filter(data, where)).collect(Collectors.toList());
            // 过滤完的临时表存储
            List<Map<String, Object>> tempTable = createTempTable(filterList);
            // 分组聚合
            tempTable = groupAndAggregation(tempTable, group, aggregations);
            // 排序
            sort(tempTable, sorts);
            // 限制
            tempTable = limit(tempTable, from, size);
    
            // 包含列/排除列
            return result(tempTable, includes, excludes);
        }
    
        /**
         * 过滤
         *
         * @param data  数据
         * @param where 条件
         * @return {@link Boolean}
         */
        private boolean filter(JSONObject data, JQLWhereModel where) {
            List<Boolean> resultList = CollUtil.newArrayList();
            Opt.ofNullable(where.getBinaryOpExprList()).orElse(ListUtil.empty()).forEach(binaryOpExpr -> {
                String fieldName = binaryOpExpr.getFieldName();
                Object source = getValue(data, fieldName);
                Operator operator = binaryOpExpr.getOperator();
                if (Objects.equals(Operator.EMPTY, operator)) {
                    resultList.add(source instanceof Iterable ? CollUtil.isEmpty((Iterable<?>) source) : Objects.isNull(source));
                } else if (Objects.equals(Operator.NOT_EMPTY, operator)) {
                    resultList.add(source instanceof Iterable ? CollUtil.isNotEmpty((Iterable<?>) source) : Objects.nonNull(source));
                } else {
                    resultList.add(filter(source, operator, binaryOpExpr.getValues()));
                }
            });
            Opt.ofNullable(where.getChildWhereList()).orElse(ListUtil.empty()).forEach(childWhere -> resultList.add(filter(data, childWhere)));
            return Objects.equals(Relation.OR, where.getRelation()) ? resultList.stream().anyMatch(result -> result)
                    : resultList.stream().allMatch(result -> result);
        }
    
        /**
         * 过滤
         *
         * @param source   源数据
         * @param operator 操作符
         * @param values   目标数据
         * @return {@link Boolean}
         */
        private boolean filter(Object source, Operator operator, Object[] values) {
            boolean temp = false;
            try {
                if (Objects.nonNull(source) && Number.class.isAssignableFrom(source.getClass())) {
                    if (values instanceof String[]) { // 可能是时间类型，尝试将时间转成Long
                        values = Stream.of(values).map(value -> DateUtil.parse(TypeUtils.cast(value, String.class)))
                                .map(DateTime::getTime).toArray(Number[]::new);
                    }
                    temp = Operator.judgeNumber(TypeUtils.cast(source, Number.class), operator, TypeUtils.cast(values, Number[].class));
                } else if (Objects.nonNull(source) && Date.class.isAssignableFrom(source.getClass())) {
                    temp = Operator.judgeNumber(TypeUtils.toDate(source).getTime(), operator, Stream.of(values).map(value ->
                            DateUtil.parse(TypeUtils.cast(value, String.class))).map(DateTime::getTime).toArray(Number[]::new));
                } else if (Objects.nonNull(source) && source instanceof Collection) {
                    temp = Operator.judgeArray(TypeUtils.cast(source, Object[].class), operator, values);
                } else {
                    temp = Operator.judgeStr(StrUtil.toStringOrNull(source), operator, Stream.of(values)
                            .map(StrUtil::toStringOrNull).filter(Objects::nonNull).collect(Collectors.joining(StrUtil.COMMA)));
                }
            } catch (Exception e) {
                log.warn(StrUtil.EMPTY, new JQLException(ErrorCode.FILTER_EXECUTE_ERROR, e.getMessage(), e));
            }
            return temp;
        }
    
        /**
         * 创建临时表
         *
         * @param dataList 过滤后的数据
         * @return {@link List}
         */
        private List<Map<String, Object>> createTempTable(List<JSONObject> dataList) {
            List<Map<String, Object>> tempTable = new ArrayList<>(dataList.size());
            dataList.forEach(data -> {
                Set<Map.Entry<String, Object>> entries = data.entrySet();
                Map<String, Object> map = new LinkedHashMap<>(entries.size());
                for (Map.Entry<String, Object> entry : entries) {
                    map.put(entry.getKey(), entry.getValue());
                }
                tempTable.add(map);
            });
            return tempTable;
        }
    
        /**
         * 分组并聚合
         *
         * @param tempTable    临时表
         * @param group        分组
         * @param aggregations 聚合
         * @return {@link List}
         */
        private List<Map<String, Object>> groupAndAggregation(List<Map<String, Object>> tempTable,
                                                              JQLGroupModel group,
                                                              List<JQLAggregationModel> aggregations) {
            List<String> groupNameList = Opt.ofNullable(group).map(JQLGroupModel::getGroupNameList).get();
            if (CollUtil.isEmpty(groupNameList)) return aggregation(tempTable, aggregations);
    
            List<Map<String, Object>> resultList = new ArrayList<>(tempTable.size());
    
            // 分组
            Map<Map<String, Object>, List<Map<String, Object>>> collect = tempTable.stream()
                    .collect(Collectors.groupingBy(data -> groupNameList.stream().map(data::get)
                            .map(StrUtil::toString).collect(Collectors.joining()))).values()
                    .stream().map(maps -> {
                        Map<String, Object> key = new LinkedHashMap<>(groupNameList.size());
                        Map<String, Object> first = CollUtil.getFirst(maps);
                        groupNameList.forEach(groupName -> key.put(groupName, first.get(groupName)));
                        return MapUtil.entry(key, maps);
                    }).collect(Collectors.toMap(Map.Entry::getKey, Map.Entry::getValue));
    
            // 聚合
            collect.forEach((key, value) -> {
                Map<String, Object> map = MapBuilder.<String, Object>create(true).putAll(key).build();
                aggregation(value, aggregations).forEach(map::putAll);
                resultList.add(map);
            });
    
            return resultList;
        }
    
        /**
         * 聚合
         *
         * @param tempTable    临时表
         * @param aggregations 聚合
         * @return {@link List}
         */
        private List<Map<String, Object>> aggregation(List<Map<String, Object>> tempTable, List<JQLAggregationModel> aggregations) {
            if (CollUtil.isEmpty(aggregations)) return tempTable;
    
            Map<String, Object> map = MapUtil.newHashMap(aggregations.size(), true);
            int newScale = 4;
            aggregations.stream().collect(Collectors.groupingBy(JQLAggregationModel::getFieldName)).forEach((fileName, aggregationList) -> {
                DoubleSummaryStatistics statistics = tempTable.stream().map(data -> StrUtil.toStringOrNull(data.get(fileName))).mapToDouble(data ->
                        Opt.ofTry(() -> NumberUtil.parseDouble(data)).orElse(BigInteger.ZERO.doubleValue())).summaryStatistics();
                aggregationList.forEach(aggregation -> {
                    Object value = null;
                    switch (aggregation.getAggregationType()) {
                        case SUM:
                            value = BigDecimal.valueOf(statistics.getSum()).longValue();
                            break;
                        case MAX:
                            value = BigDecimal.valueOf(statistics.getMax()).setScale(newScale, RoundingMode.HALF_UP).doubleValue();
                            break;
                        case MIN:
                            value = BigDecimal.valueOf(statistics.getMin()).setScale(newScale, RoundingMode.HALF_UP).doubleValue();
                            break;
                        case AVG:
                            value = BigDecimal.valueOf(statistics.getAverage()).setScale(newScale, RoundingMode.HALF_UP).doubleValue();
                            break;
                        case COUNT:
                            value = BigDecimal.valueOf(statistics.getCount()).longValue();
                            break;
                    }
                    map.put(aggregation.getAlias(), value);
                });
            });
            return CollUtil.newArrayList(map);
        }
    
        /**
         * 排序
         *
         * @param tempTable 临时表
         * @param sorts     排序
         */
        private void sort(List<Map<String, Object>> tempTable, List<JQLSortModel> sorts) {
            Opt.ofNullable(sorts).orElse(ListUtil.empty()).forEach(sort -> {
                String fieldName = sort.getFieldName();
                OrderType orderType = sort.getOrderType();
                tempTable.sort((d1, d2) -> {
                    Integer a = Integer.valueOf(StrUtil.toString((d1.get(fieldName))));
                    Integer b = Integer.valueOf(StrUtil.toString((d2.get(fieldName))));
                    return Objects.equals(OrderType.ASC, orderType) ? a - b : b - a;
                });
            });
        }
    
        /**
         * 限制
         *
         * @param tempTable 临时表
         * @param from      偏移数
         * @param size      限制数
         * @return {@link List}
         */
        private List<Map<String, Object>> limit(List<Map<String, Object>> tempTable, Integer from, Integer size) {
            return tempTable.stream().skip(Opt.ofNullable(from).orElse(0)).limit(Opt.ofNullable(size).orElse(tempTable.size())).collect(Collectors.toList());
        }
    
        /**
         * 结果
         *
         * @param tempTable 临时表
         * @param includes  包含列
         * @param excludes  排除列
         * @return {@link List}
         */
        private List<Map<String, Object>> result(List<Map<String, Object>> tempTable, String[] includes, String[] excludes) {
            return tempTable.stream().map(data -> includes(data, includes)).map(data -> excludes(data, includes, excludes))
                    .collect(Collectors.toList());
        }
    
        /**
         * 包含
         *
         * @param data     数据
         * @param includes 包含列
         * @return {@link Map}
         */
        private Map<String, Object> includes(Map<String, Object> data, String[] includes) {
            if (ArrayUtil.isEmpty(includes) || MapUtil.isEmpty(data)) return data;
            Map<String, Object> map = new HashMap<>(data.size());
            for (String include : includes) {
                String json = JSONObject.toJSONString(data);
                JSONObject jsonObject = JSONObject.parseObject(json);
                map.put(include, getValue(jsonObject, include));
            }
            return MapUtil.isEmpty(map) ? data : map;
        }
    
        /**
         * 排除
         *
         * @param data     数据
         * @param includes 包含列
         * @param excludes 排除列
         * @return {@link Map}
         */
        private Map<String, Object> excludes(Map<String, Object> data, String[] includes, String[] excludes) {
            if (ArrayUtil.isEmpty(excludes) || MapUtil.isEmpty(data)) return data;
            for (String exclude : excludes) {
                if (!StrUtil.containsAny(exclude, includes)) {
                    excludes(data, exclude);
                } else {
                    data.remove(exclude);
                }
            }
            return data;
        }
    
        /**
         * 排除
         *
         * @param data    数据
         * @param exclude 排除列
         */
        private void excludes(Map<String, Object> data, String exclude) {
            List<String> split = StrUtil.split(exclude, StrUtil.DOT);
            int size = CollUtil.size(split);
            if (size <= 1) {
                data.remove(exclude);
                return;
            }
            String name = CollUtil.getFirst(split);
            String subName = StrUtil.join(StrUtil.DOT, CollUtil.sub(split, 1, size));
            String json = JSONObject.toJSONString(data.get(name));
            if (!JSON.isValidObject(json)) return;
            JSONObject jsonObject = JSONObject.parseObject(json);
            excludes(jsonObject, subName);
            data.put(name, jsonObject);
        }
    
        /**
         * 获取数据
         *
         * @param data      数据
         * @param fieldName 属性名称
         * @return {@link Object}
         */
        private Object getValue(JSONObject data, String fieldName) {
            List<String> split = StrUtil.split(fieldName, StrUtil.DOT);
            int size = CollUtil.size(split);
            if (size <= 1) {
                return data.get(fieldName);
            }
            String name = CollUtil.getFirst(split);
            List<String> subList = CollUtil.sub(split, 1, size);
            String subName = StrUtil.join(StrUtil.DOT, subList);
            String json = JSONObject.toJSONString(data.get(name));
            if (!JSON.isValidObject(json)) return null;
            JSONObject jsonObject = JSONObject.parseObject(json);
            return getValue(jsonObject, subName);
        }
    
    }

###### 4.13 创建启动类 ######

import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    /**
     * 启动类
     *
     * @author CL
     */
    @SpringBootApplication
    public class Application {
    
        public static void main(String[] args) {
            SpringApplication.run(Application.class, args);
        }
    
    }

##### 5. 单元测试 #####

测试方案：创建一张用户表并预置一些数据，使用相同的SQL分别通过数据库查询和通过JSONSQL查询，断言两种方式查询的结果。

###### 5.1 前期准备 ######

import com.baomidou.mybatisplus.annotation.TableField;
    import com.baomidou.mybatisplus.annotation.TableId;
    import com.baomidou.mybatisplus.annotation.TableName;
    import com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler;
    import lombok.AllArgsConstructor;
    import lombok.Data;
    import lombok.NoArgsConstructor;
    
    import java.time.LocalDate;
    import java.util.List;
    
    /**
     * 用户信息
     *
     * @author CL
     */
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    @TableName(value = "test_user", autoResultMap = true)
    public class User {
    
        @TableId
        private Long id;
    
        @TableField
        private String name;
    
        @TableField
        private Integer age;
    
        @TableField
        private String sex;
    
        @TableField
        private String address;
    
        @TableField
        private Boolean status;
    
        @TableField(value = "createTime")
        private LocalDate createTime;
    
        @TableField(typeHandler = JacksonTypeHandler.class)
        private List<String> hobbies;
    
        @TableField(value = "luckyNumbers", typeHandler = JacksonTypeHandler.class)
        private List<Integer> luckyNumbers;
    
    }

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
    import com.c3stones.user.entity.User;
    import org.apache.ibatis.annotations.Mapper;
    
    /**
     * 用户Mapper
     *
     * @author CL
     */
    @Mapper
    public interface UserMapper extends BaseMapper<User> {
    
        /**
         * 创建表
         */
        void createTable();
    
        /**
         * 清除表
         */
        void dropTable();
    
    }

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
    <mapper namespace="com.c3stones.user.mapper.UserMapper">
    
        <update id="createTable">
            CREATE TABLE IF NOT EXISTS `test_user`  (
                `id` bigint NOT NULL,
                `name` varchar(20),
                `age` tinyint,
                `sex` varchar(1),
                `address` varchar(255),
                `status` tinyint,
                `createTime` datetime,
                `hobbies` json,
                `luckyNumbers` json,
                PRIMARY KEY (`id`)
            );
        </update>
    
        <delete id="dropTable">
            DROP TABLE IF EXISTS `test_user`;
        </delete>
    
    </mapper>

spring:
      datasource:
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://127.0.0.1:3306/jql?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
        username: root
        password: 123456

###### 5.2 创建单元测试 ######

import cn.hutool.core.date.DateUtil;
    import cn.hutool.core.date.LocalDateTimeUtil;
    import cn.hutool.core.util.BooleanUtil;
    import cn.hutool.core.util.NumberUtil;
    import cn.hutool.core.util.StrUtil;
    import cn.hutool.json.JSONUtil;
    import com.alibaba.druid.DbType;
    import com.alibaba.fastjson2.JSONObject;
    import com.baomidou.mybatisplus.core.toolkit.Wrappers;
    import com.c3stones.Application;
    import com.c3stones.db.mapper.DBMapper;
    import com.c3stones.user.entity.User;
    import com.c3stones.json.mapper.model.DataModel;
    import com.c3stones.json.parser.convert.JQLConvert;
    import com.c3stones.json.parser.select.JSONSelectFactory;
    import com.c3stones.user.mapper.UserMapper;
    import lombok.extern.slf4j.Slf4j;
    import org.junit.jupiter.api.AfterEach;
    import org.junit.jupiter.api.Assertions;
    import org.junit.jupiter.api.BeforeEach;
    import org.junit.jupiter.params.ParameterizedTest;
    import org.junit.jupiter.params.provider.Arguments;
    import org.junit.jupiter.params.provider.MethodSource;
    import org.springframework.boot.test.context.SpringBootTest;
    
    import javax.annotation.Resource;
    import java.time.LocalDate;
    import java.time.LocalDateTime;
    import java.time.ZoneId;
    import java.util.*;
    import java.util.stream.Collectors;
    import java.util.stream.Stream;
    
    /**
     * 测试SQL转JSONSQL并查询
     *
     * @author CL
     */
    @Slf4j
    @SpringBootTest(classes = Application.class)
    public class JQLMapperTest {
    
        @Resource
        private UserMapper userMapper;
    
        @Resource
        private DBMapper dbMapper;
    
        private static List<DataModel> dataList;
    
        /**
         * 构造查询SQL
         *
         * @return {@link Stream < Arguments >}
         */
        private static Stream<Arguments> query() {
            return Stream.of(
                    "select * from test_user",
                    "select * from test_user where id = 1 ",
                    "select * from test_user where id = 1 and name = '张三'",
                    "select * from test_user where id = 2 or name = '张三'",
                    "select * from test_user where name = '张三' and age > 28",
                    "select * from test_user where id = 1 or name = '张三' and (age > 28 or sex = '男')",
                    "select * from test_user where id = 1 and name = '张三' and (age > 28 or sex = '男') and (address like '%陕西%' and status != '0')",
                    "select * from test_user where name in ('张三', '李四')",
                    "select * from test_user where id = 1 and ( name in ('张三', '李四') and createTime between '2023-01-01' and '2023-04-30' )",
                    "select * from test_user where ( name in ('张三', '李四') and age between 25 and 30 )",
                    "select id, name from test_user where ( name in ('张三', '李四') and age between 26 and 30 )",
    
                    "select id from test_user where 1 = 1",
                    "select id from test_user where 1 != 1",
                    "select id from test_user where name = 'XXX'",
                    "select id from test_user where name is null",
                    "select id from test_user where name is not null",
    
                    "select hobbies from test_user where json_contains(hobbies, '[\"看书\"]')",
                    "select * from test_user where json_contains(hobbies, '[\"看书\"]')",
                    "select * from test_user where json_contains(hobbies, '[\"看书\"]') = 0",
                    "select hobbies from test_user where json_length(hobbies) = 0 or hobbies is null",
                    "select * from test_user where json_length(hobbies) > 0"
            ).map(Arguments::of);
        }
    
        /**
         * 构造聚合SQL
         *
         * @return {@link Stream<Arguments>}
         */
        private static Stream<Arguments> aggregation() {
            return Stream.of(
                    "select count(*) from test_user",
                    "select count(*) as count from test_user",
                    "select count(id) from test_user where id = 1",
                    "select count(id) as count from test_user where id > 1",
                    "select count(id) from test_user group by name",
                    "select sex, count(id) as count from test_user group by sex",
                    "select sex, count(*) as count from test_user group by sex order by count",
                    "select sex, count(*) as count from test_user group by sex order by count desc",
                    "select sex, count(*) as count from test_user group by sex order by count desc limit 1",
                    "select sex, count(*) as count from test_user group by sex order by count desc limit 1, 1",
    
                    "select sum(age), max(id), min(age), avg(id), count(age) from test_user",
                    "select sex, sum(age), max(id), min(age), avg(age), count(*) from test_user where name != 'xxx' group by sex"
            ).map(Arguments::of);
        }
    
        /**
         * 预置表及数据
         */
        @BeforeEach
        public void before() {
            List<User> userList = new ArrayList<User>() {
        {
                add(new User(1L, "张三", 25, "男", "陕西省西安市", true, LocalDate.of(2023, 1, 1),
                        Stream.of("看书", "听歌").map(Objects::toString).collect(Collectors.toList()), Stream.of(7, 21).collect(Collectors.toList())));
                add(new User(2L, "李四", 28, "女", "陕西省渭南市", true, LocalDate.of(2023, 4, 1),
                        Stream.of("逛街", "购物").map(Objects::toString).collect(Collectors.toList()), Collections.emptyList()));
                add(new User(3L, "王五", 30, "男", "北京市", false, LocalDate.of(2023, 5, 1),
                        Stream.of("看书").map(Objects::toString).collect(Collectors.toList()), Stream.of(8, 24).collect(Collectors.toList())));
            }};
            dataList = userList.stream().map(user -> new DataModel(JSONObject.parseObject(JSONUtil.toJsonStr(user)))).collect(Collectors.toList());
    
            userMapper.createTable();
            userMapper.delete(Wrappers.emptyWrapper());
            userList.forEach(user -> userMapper.insert(user));
        }
    
        /**
         * 清除表
         */
        @AfterEach
        public void after() {
            userMapper.dropTable();
        }
    
        /**
         * 测试查询
         *
         * @param sql SQL
         */
        @ParameterizedTest
        @MethodSource("query")
        public void testQuery(String sql) {
            List<Map<String, Object>> dbResult = dbMapper.aggregation(sql);
    
            JQLConvert jqlConvert = new JQLConvert(JSONSelectFactory.any());
            JSONMapperProvider provider = jqlConvert.convert(sql, DbType.mysql);
            provider.setDataList(dataList);
            JSONMapper jsonMapper = new JSONMapper();
            List<Map<String, Object>> jqlResult = jsonMapper.query(provider);
    
            Assertions.assertEquals(dbResult.size(), jqlResult.size());
    
            for (int i = 0; i < dbResult.size(); i++) {
                Map<String, Object> dbMap = dbResult.get(i);
                Map<String, Object> jqlMap = jqlResult.get(i);
    
                for (Map.Entry<String, Object> entry : dbMap.entrySet()) {
                    Object expected = entry.getValue();
                    Object actual = jqlMap.get(entry.getKey());
                    if (expected instanceof LocalDateTime || actual instanceof Long) {
                        expected = Date.from(LocalDateTimeUtil.parse(expected.toString())
                                .atZone(ZoneId.systemDefault()).toInstant());
                        actual = DateUtil.date(NumberUtil.parseLong(actual.toString()));
                    } else if (expected instanceof String && actual instanceof Collection) {
                        expected = ((String) expected).replaceAll(StrUtil.SPACE, StrUtil.EMPTY);
                        actual = JSONObject.toJSONString(actual);
                    } else if (expected instanceof Integer[] && actual instanceof Collection) {
                        expected = JSONObject.toJSONString(expected);
                        actual = JSONObject.toJSONString(actual);
                    } else if (expected instanceof Number && actual instanceof Number) {
                        expected = NumberUtil.parseDouble(expected.toString());
                        actual = NumberUtil.parseDouble(actual.toString());
                    } else if (expected instanceof Number && actual instanceof Boolean) {
                        expected = BooleanUtil.toBoolean(expected.toString());
                        actual = BooleanUtil.toBoolean(actual.toString());
                    }
                    Assertions.assertEquals(expected, actual);
                }
            }
        }
    
        /**
         * 测试聚合
         *
         * @param sql SQL
         */
        @ParameterizedTest
        @MethodSource(value = {"aggregation"})
        public void testAggregation(String sql) {
            List<Map<String, Object>> dbResult = dbMapper.aggregation(sql);
    
            JQLConvert jqlConvert = new JQLConvert(JSONSelectFactory.any());
            JSONMapperProvider provider = jqlConvert.convert(sql, DbType.mysql);
            provider.setDataList(dataList);
            JSONMapper jsonMapper = new JSONMapper();
            List<Map<String, Object>> jqlResult = jsonMapper.aggregation(provider);
    
            Assertions.assertEquals(dbResult.size(), jqlResult.size());
    
            for (Map<String, Object> dbMap : dbResult) {
                boolean match = jqlResult.stream().anyMatch(result -> dbMap.entrySet().stream().allMatch(
                        entry -> {
                            String key = entry.getKey();
                            if (StrUtil.contains(key, "(")) {
                                key = StrUtil.subBefore(key, "(", false);
                            }
                            String v1 = StrUtil.toStringOrNull(entry.getValue());
                            String v2 = StrUtil.toStringOrNull(result.get(key));
                            if (NumberUtil.isNumber(v1) && NumberUtil.isNumber(v2)) {
                                v1 = String.format("%.5f", NumberUtil.parseDouble(v1));
                                v2 = String.format("%.5f", NumberUtil.parseDouble(v2));
                            }
                            return StrUtil.equals(v1, v2);
                        }));
                Assertions.assertTrue(match);
            }
        }
    
    }

控制台打印结果：![8bdaec3abc8d735fc0df84f888590276.jpeg][]

##### 6. JavaScript中使用类SQL过滤JSON类型数据 #####

###### 6.1 添加thymeleaf配置 ######

spring:
      thymeleaf:
        prefix: classpath:/view/
        suffix: .html
        encoding: UTF-8
        servlet:
          content-type: text/html

###### 6.2 创建页面 ######

<!DOCTYPE html>
    <html>
    <head>
        <title>JSON-SQL</title>
        <script th:src="@{/jquery-2.2.4.min.js}"></script>
        <script th:src="@{/jsonsql-0.1.js}" data-main="home"></script>
    </head>
    <body>
    <H2 style="color: red">注意：where条件必须放到括号中。只支持等于。</H2>
    <H3>请输入json数据:</H3>
    <textarea id="json" placeholder="请输入json数据" rows="10" cols="100"></textarea><br>
    <H3>请输入SQL:</H3>
    <textarea id="sql" placeholder="请输入SQL" rows="5" cols="100"></textarea><br>
    <H3><button type="button" onclick="handleClick()" style="width: 200px;height: 40px">获取结果</button></H3>
    <textarea id="result" rows="10" cols="100"></textarea><br>
    </body>
    <script type="text/javascript">
        $(function() {
            $('#json').val("{\"data\":[" +
                "{\"id\":1,\"name\":\"张三\",\"age\":25,\"sex\":\"男\",\"address\":\"陕西省西安市\",\"status\":true,\"createTime\":1672502400000,\"hobbies\":[\"看书\",\"听歌\"],\"luckyNumbers\":[7,21]}," +
                "{\"id\":2,\"name\":\"李四\",\"age\":28,\"sex\":\"女\",\"address\":\"陕西省渭南市\",\"status\":true,\"createTime\":1680278400000,\"hobbies\":[\"逛街\",\"购物\"],\"luckyNumbers\":[]}," +
                "{\"id\":3,\"name\":\"王五\",\"age\":30,\"sex\":\"男\",\"address\":\"北京市\",\"status\":false,\"createTime\":1682870400000,\"hobbies\":[\"看书\"],\"luckyNumbers\":[8,24]}" +
                "]}");
            $('#sql').val("select * from json.data where (name=='张三' || name=='李四') order by age DESC limit 2");
        });
        function handleClick() {
            var json = eval("(" + $('#json').val() + ")");
            var sql = $('#sql').val();
            var result = jsonsql.query(sql, json);
            $('#result').val(JSON.stringify(result));
        }
    </script>
    </html>

###### 6.3 创建Controller ######

import org.springframework.stereotype.Controller;
    import org.springframework.web.bind.annotation.GetMapping;
    
    /**
     * 首页Controller
     *
     * @author CL
     */
    @Controller
    public class IndexController {
    
        /**
         * 首页
         *
         * @return
         */
        @GetMapping(value = "index")
        public String index() {
            return "index";
        }
    
    }

###### 6.4 启动项目 ######

浏览器访问：[http://127.0.0.1:8080/index][http_127.0.0.1_8080_index]![3a81bfa54f07709aa36ac431ac65b25c.jpeg][]

##### 7. 项目地址 #####

[spring-boot-json-sql-demo][]

[DB SQL _ ES DSL]: https://www.cnblogs.com/cao-lei/p/17062889.html
[cb4eec8594e3547fe900c5b54f80cd96.jpeg]: https://img-blog.csdnimg.cn/img_convert/cb4eec8594e3547fe900c5b54f80cd96.jpeg
[8bdaec3abc8d735fc0df84f888590276.jpeg]: https://img-blog.csdnimg.cn/img_convert/8bdaec3abc8d735fc0df84f888590276.jpeg
[http_127.0.0.1_8080_index]: http://127.0.0.1:8080/index
[3a81bfa54f07709aa36ac431ac65b25c.jpeg]: https://img-blog.csdnimg.cn/img_convert/3a81bfa54f07709aa36ac431ac65b25c.jpeg
[spring-boot-json-sql-demo]: https://github.com/C3Stones/blog