河南省汝州市建设门户网站网页美工设计的工作流程？

当前位置： 首页 > news >正文

河南省汝州市建设门户网站,网页美工设计的工作流程?,wordpress被墙,网站设计公司排名1. 简介 在开发中#xff0c;经常需要根据条件过滤大批量的JSON类型数据。如果仅需要过滤这一种类型#xff0c;将JSON转为List后过滤即可#xff1b;如果相同的条件既想过滤数据库表中的数据、也想过滤内存中JSON数据#xff0c;甚至想过滤Elasticsearch中的数据#xff… 1. 简介   在开发中经常需要根据条件过滤大批量的JSON类型数据。如果仅需要过滤这一种类型将JSON转为List后过滤即可如果相同的条件既想过滤数据库表中的数据、也想过滤内存中JSON数据甚至想过滤Elasticsearch中的数据那可使用本博客中的方案使用标准的SQL语法实现多种数据源中的查询或聚合操作。 2. JSONSQL能力总览 能力明细支持的数据库Mysql PostgreSQL等(采用Druid连接池中的SQL解析器理论上支持Druid支持的所有数据库)支持的语法查询全部、查询指定列、条件查询(、!、、、、、between、in、not in、like、not like、为空、不为空)、复杂条件查询(多个and/or组合)、order by、limit、group by(一维或多维)、count、min、max、avg、sum等其他聚合函数可自行增加支持的函数cast、concat、lower、upper、to_timestamp、from_unixtime、PostgreSQL(json_contain、json_not_contain、jsonb_array_length)、Mysql(json_contains、json_length)等其他函数可自行增加 3. 相关博客   DB SQL 转 ES DSL(支持多种数据库常用查询、统计、平均值、最大值、最小值、求和语法) 4. 示例代码# 4.1 创建项目 4.2 修改pom.xml ?xml version1.0 encodingUTF-8? project xmlnshttp://maven.apache.org/POM/4.0.0xmlns:xsihttp://www.w3.org/2001/XMLSchema-instancexsi:schemaLocationhttp://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsdmodelVersion4.0.0/modelVersiongroupIdcom.c3stones/groupIdartifactIdspring-boot-json-sql-demo/artifactIdversion1.0-SNAPSHOT/versionparentgroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-parent/artifactIdversion2.7.10/version/parentdependenciesdependencygroupIdcom.baomidou/groupIdartifactIdmybatis-plus-boot-starter/artifactIdversion3.5.3.1/version/dependencydependencygroupIdcom.alibaba.fastjson2/groupIdartifactIdfastjson2/artifactIdversion2.0.32/version/dependencydependencygroupIdcn.hutool/groupIdartifactIdhutool-all/artifactIdversion5.8.15/version/dependencydependencygroupIdcom.alibaba/groupIdartifactIddruid-spring-boot-starter/artifactIdversion1.2.18/version/dependencydependencygroupIdcom.mysql/groupIdartifactIdmysql-connector-j/artifactId/dependencydependencygroupIdorg.projectlombok/groupIdartifactIdlombok/artifactId/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-thymeleaf/artifactId/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-web/artifactId/dependencydependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-test/artifactIdscopetest/scope/dependency/dependenciesbuildpluginsplugingroupIdorg.springframework.boot/groupIdartifactIdspring-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 ListString 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;}Overridepublic 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 JQLBinaryOpExprT {/** 字段名称/private String fieldName;/** 操作符/private Operator operator;/** 值/private T[] values;SafeVarargspublic final JQLBinaryOpExpr setValues(T… values) {this.values values;return this;}Overridepublic 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 ListJQLBinaryOpExpr binaryOpExprList;/** 子查询模型/private ListJQLWhereModel childWhereList;Overridepublic 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;}Overridepublic 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 ListString groupNameList;Overridepublic 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;Overridepublic 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 ListJQLAggregationModel aggregations;/** 分组/private JQLGroupModel group;/** 排序/private ListJQLSortModel sorts;/** 起始位置/private Integer from;/** 大小/private Integer size;Overridepublic 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 {/** 数据来源/Deprecatedprivate String json;/** 数据模型/private ListDataModel dataList;/** JSONSQL/private JQLModel jql;/** 包含列/private String[] includes;/** 排除列/private String[] excludes;/** SQL/private String sql;public JSONMapperProvider(String sql) {this.sql sql;}Overridepublic 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, ListObject values);/** 不属于** param fieldName 属性名称* param values 值(不能包含英文逗号)* return {link JQLBinaryOpExpr}/JQLBinaryOpExpr notIn(String fieldName, ListObject 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, ListJQLAggregationModel aggregations, JQLGroupModel group, ListJQLSortModel 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(ListObject 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}/Overridepublic JQLBinaryOpExprString eq(String fieldName, Object value) {return new JQLBinaryOpExprString().setFieldName(fieldName).setOperator(Operator.EQ).setValues(format(value));}/** 不等** param fieldName 属性名称* param value 值* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprString neq(String fieldName, Object value) {return new JQLBinaryOpExprString().setFieldName(fieldName).setOperator(Operator.NEQ).setValues(format(value));}/** 大于** param fieldName 属性名称* param value 值* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprString gt(String fieldName, Object value) {return new JQLBinaryOpExprString().setFieldName(fieldName).setOperator(Operator.GT).setValues(format(value));}/** 大于等于** param fieldName 属性名称* param value 值* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprString gte(String fieldName, Object value) {return new JQLBinaryOpExprString().setFieldName(fieldName).setOperator(Operator.GTE).setValues(format(value));}/** 小于** param fieldName 属性名称* param value 值* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprString lt(String fieldName, Object value) {return new JQLBinaryOpExprString().setFieldName(fieldName).setOperator(Operator.LT).setValues(format(value));}/** 小于等于** param fieldName 属性名称* param value 值* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprString lte(String fieldName, Object value) {return new JQLBinaryOpExprString().setFieldName(fieldName).setOperator(Operator.LTE).setValues(format(value));}/** 属于** param fieldName 属性名称* param values 值(不能包含英文逗号)* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprString in(String fieldName, ListObject values) {return new JQLBinaryOpExprString().setFieldName(fieldName).setOperator(Operator.IN).setValues(format(values));}/** 不属于** param fieldName 属性名称* param values 值(不能包含英文逗号)* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprString notIn(String fieldName, ListObject values) {return new JQLBinaryOpExprString().setFieldName(fieldName).setOperator(Operator.NOT_IN).setValues(format(values));}/** 包含** param fieldName 属性名称* param value 值* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprString contain(String fieldName, Object value) {return new JQLBinaryOpExprString().setFieldName(fieldName).setOperator(Operator.CONTAIN).setValues(format(format(value)));}/** 不包含** param fieldName 属性名称* param value 值* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprString notContain(String fieldName, Object value) {return new JQLBinaryOpExprString().setFieldName(fieldName).setOperator(Operator.NOT_CONSTAIN).setValues(format(format(value)));}/** 为空** param fieldName 属性名称* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprString empty(String fieldName) {return new JQLBinaryOpExprString().setFieldName(fieldName).setOperator(Operator.EMPTY);}/** 不为空** param fieldName 属性名称* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprString notEmpty(String fieldName) {return new JQLBinaryOpExprString().setFieldName(fieldName).setOperator(Operator.NOT_EMPTY);}/** 在两者之间** param fieldName 属性名称* param startValue 起始值* param endValue 终止值* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprString range(String fieldName, Object startValue, Object endValue) {return new JQLBinaryOpExprString().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(ListObject 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}/Overridepublic JQLBinaryOpExprNumber eq(String fieldName, Object value) {return new JQLBinaryOpExprNumber().setFieldName(fieldName).setOperator(Operator.EQ).setValues(format(value));}/** 不等** param fieldName 属性名称* param value 值* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprNumber neq(String fieldName, Object value) {return new JQLBinaryOpExprNumber().setFieldName(fieldName).setOperator(Operator.NEQ).setValues(format(value));}/** 大于** param fieldName 属性名称* param value 值* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprNumber gt(String fieldName, Object value) {return new JQLBinaryOpExprNumber().setFieldName(fieldName).setOperator(Operator.GT).setValues(format(value));}/** 大于等于** param fieldName 属性名称* param value 值* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprNumber gte(String fieldName, Object value) {return new JQLBinaryOpExprNumber().setFieldName(fieldName).setOperator(Operator.GTE).setValues(format(value));}/** 小于** param fieldName 属性名称* param value 值* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprNumber lt(String fieldName, Object value) {return new JQLBinaryOpExprNumber().setFieldName(fieldName).setOperator(Operator.LT).setValues(format(value));}/** 小于等于** param fieldName 属性名称* param value 值* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprNumber lte(String fieldName, Object value) {return new JQLBinaryOpExprNumber().setFieldName(fieldName).setOperator(Operator.LTE).setValues(format(value));}/** 属于** param fieldName 属性名称* param values 值(不能包含英文逗号)* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprNumber in(String fieldName, ListObject values) {return new JQLBinaryOpExprNumber().setFieldName(fieldName).setOperator(Operator.IN).setValues(format(values));}/** 不属于** param fieldName 属性名称* param values 值(不能包含英文逗号)* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprNumber notIn(String fieldName, ListObject values) {return new JQLBinaryOpExprNumber().setFieldName(fieldName).setOperator(Operator.NOT_IN).setValues(format(values));}/** 包含** param fieldName 属性名称* param value 值* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprNumber contain(String fieldName, Object value) {return new JQLBinaryOpExprNumber().setFieldName(fieldName).setOperator(Operator.CONTAIN).setValues(format(value));}/** 不包含** param fieldName 属性名称* param value 值* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprNumber notContain(String fieldName, Object value) {return new JQLBinaryOpExprNumber().setFieldName(fieldName).setOperator(Operator.NOT_CONSTAIN).setValues(format(value));}/** 为空** param fieldName 属性名称* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprNumber empty(String fieldName) {return new JQLBinaryOpExprNumber().setFieldName(fieldName).setOperator(Operator.EMPTY);}/** 不为空** param fieldName 属性名称* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprNumber notEmpty(String fieldName) {return new JQLBinaryOpExprNumber().setFieldName(fieldName).setOperator(Operator.NOT_EMPTY);}/** 在两者之间** param fieldName 属性名称* param startValue 起始值* param endValue 终止值* return {link JQLBinaryOpExpr}/Overridepublic JQLBinaryOpExprNumber range(String fieldName, Object startValue, Object endValue) {return new JQLBinaryOpExprNumber().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(ListObject 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);OptSQLSelectQueryBlock 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) {// 处理 SelectListSelectField selectFieldList handleSelect(sqlSelectQuery.getSelectList());// 处理 FromString json handleFrom(sqlSelectQuery.getFrom());// 处理 WhereJQLWhereModel where handleWhere(sqlSelectQuery.getWhere());// 处理 AggregationListJQLAggregationModel aggregationList handleAggregate(selectFieldList);// 处理 GroupJQLGroupModel group handleGroup(sqlSelectQuery.getGroupBy());// 处理 OrderByListJQLSortModel sortList handleOrderBy(sqlSelectQuery.getOrderBy());// 处理 LimitPage page handleLimit(sqlSelectQuery.getLimit());// 生成JQLInteger 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 ListSelectField}/private ListSelectField handleSelect(ListSQLSelectItem 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);}ListJQLBinaryOpExpr leftList Opt.ofNullable(left.getBinaryOpExprList()).orElse(CollUtil.newArrayList());ListJQLBinaryOpExpr 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) {ListJQLBinaryOpExpr list CollUtil.newArrayList();SQLExpr leftExpr sqlExpr.getLeft();SQLExpr rightExpr sqlExpr.getRight();// 特殊处理 1 1 / 1 ! 1if (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();ListObject 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();ListSQLExpr 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)) {ListObject 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 ListJQLAggregationModel}/private ListJQLAggregationModel handleAggregate(ListSelectField selectFieldList) {ListJQLAggregationModel 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)) {ListString 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 ListJQLSortModel handleOrderBy(SQLOrderBy sqlOrderBy) {ListJQLSortModel 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();ListSQLExpr 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 \() {sb.append(\\).append(\\);}sb.append(c);}return sb.toString();}/*** 查询字段*/DataNoArgsConstructorAllArgsConstructorprivate static class SelectField {/*** 字段名*/private String name;/*** 别名*/private String alias;/*** 方法名*/private String methodName;}/*** 翻页*/DataNoArgsConstructorAllArgsConstructorAccessors(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;}Overridepublic 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(ListDataModel 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(ListSelectField 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(ListSelectField 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 {Resourceprivate MybatisPlusProperties mybatisPlusProperties;/*** 聚合** param provider JSON Mapper Provider* return {link List}*/public ListMapString, Object aggregation(JSONMapperProvider provider) {return execute(provider);}/*** 查询** param provider JSON Mapper Provider* return {link List}*/public ListMapString, Object query(JSONMapperProvider provider) {return execute(provider);}/*** 执行** param provider JSON Mapper Provider* return {link List}*/private ListMapString, Object execute(JSONMapperProvider provider) {// 如果开启Mybatis-Plus SQL打印则同时打印 JQLif (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();ListJSONObject 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();ListJQLAggregationModel aggregations jql.getAggregations();ListJQLSortModel sorts jql.getSorts();Integer from jql.getFrom();Integer size jql.getSize();// 过滤ListJSONObject filterList dataList.stream().filter(data - filter(data, where)).collect(Collectors.toList());// 过滤完的临时表存储ListMapString, 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) {ListBoolean 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[]) { // 可能是时间类型尝试将时间转成Longvalues 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 ListMapString, Object createTempTable(ListJSONObject dataList) {ListMapString, Object tempTable new ArrayList(dataList.size());dataList.forEach(data - {SetMap.EntryString, Object entries data.entrySet();MapString, Object map new LinkedHashMap(entries.size());for (Map.EntryString, Object entry : entries) {map.put(entry.getKey(), entry.getValue());}tempTable.add(map);});return tempTable;}/*** 分组并聚合** param tempTable 临时表* param group 分组* param aggregations 聚合* return {link List}*/private ListMapString, Object groupAndAggregation(ListMapString, Object tempTable,JQLGroupModel group,ListJQLAggregationModel aggregations) {ListString groupNameList Opt.ofNullable(group).map(JQLGroupModel::getGroupNameList).get();if (CollUtil.isEmpty(groupNameList)) return aggregation(tempTable, aggregations);ListMapString, Object resultList new ArrayList(tempTable.size());// 分组MapMapString, Object, ListMapString, Object collect tempTable.stream().collect(Collectors.groupingBy(data - groupNameList.stream().map(data::get).map(StrUtil::toString).collect(Collectors.joining()))).values().stream().map(maps - {MapString, Object key new LinkedHashMap(groupNameList.size());MapString, 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) - {MapString, Object map MapBuilder.String, Objectcreate(true).putAll(key).build();aggregation(value, aggregations).forEach(map::putAll);resultList.add(map);});return resultList;}/*** 聚合** param tempTable 临时表* param aggregations 聚合* return {link List}*/private ListMapString, Object aggregation(ListMapString, Object tempTable, ListJQLAggregationModel aggregations) {if (CollUtil.isEmpty(aggregations)) return tempTable;MapString, 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(ListMapString, Object tempTable, ListJQLSortModel 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 ListMapString, Object limit(ListMapString, 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 ListMapString, Object result(ListMapString, 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 MapString, Object includes(MapString, Object data, String[] includes) {if (ArrayUtil.isEmpty(includes) || MapUtil.isEmpty(data)) return data;MapString, 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 MapString, Object excludes(MapString, 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(MapString, Object data, String exclude) {ListString 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) {ListString split StrUtil.split(fieldName, StrUtil.DOT);int size CollUtil.size(split);if (size 1) {return data.get(fieldName);}String name CollUtil.getFirst(split);ListString 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 {TableIdprivate Long id;TableFieldprivate String name;TableFieldprivate Integer age;TableFieldprivate String sex;TableFieldprivate String address;TableFieldprivate Boolean status;TableField(value createTime)private LocalDate createTime;TableField(typeHandler JacksonTypeHandler.class)private ListString hobbies;TableField(value luckyNumbers, typeHandler JacksonTypeHandler.class)private ListInteger 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 BaseMapperUser {/*** 创建表*/void createTable();/*** 清除表*/void dropTable();} ?xml version1.0 encodingUTF-8 ? !DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtd mapper namespacecom.c3stones.user.mapper.UserMapperupdate idcreateTableCREATE 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));/updatedelete iddropTableDROP TABLE IF EXISTS test_user;/delete/mapper spring:datasource:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/jql?useSSLfalseuseUnicodetruecharacterEncodingutf-8zeroDateTimeBehaviorconvertToNullserverTimezoneAsia/Shanghaiusername: rootpassword: 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 {Resourceprivate UserMapper userMapper;Resourceprivate DBMapper dbMapper;private static ListDataModel dataList;/*** 构造查询SQL** return {link Stream Arguments }*/private static StreamArguments 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 StreamArguments}*/private static StreamArguments 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);}/*** 预置表及数据*/BeforeEachpublic void before() {ListUser userList new ArrayListUser() {{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));}/*** 清除表*/AfterEachpublic void after() {userMapper.dropTable();}/*** 测试查询** param sql SQL*/ParameterizedTestMethodSource(query)public void testQuery(String sql) {ListMapString, 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();ListMapString, Object jqlResult jsonMapper.query(provider);Assertions.assertEquals(dbResult.size(), jqlResult.size());for (int i 0; i dbResult.size(); i) {MapString, Object dbMap dbResult.get(i);MapString, Object jqlMap jqlResult.get(i);for (Map.EntryString, 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*/ParameterizedTestMethodSource(value {aggregation})public void testAggregation(String sql) {ListMapString, 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();ListMapString, Object jqlResult jsonMapper.aggregation(provider);Assertions.assertEquals(dbResult.size(), jqlResult.size());for (MapString, 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);}}}   控制台打印结果 6. JavaScript中使用类SQL过滤JSON类型数据 6.1 添加thymeleaf配置 spring:thymeleaf:prefix: classpath:/view/suffix: .htmlencoding: UTF-8servlet:content-type: text/html 6.2 创建页面 !DOCTYPE html html headtitleJSON-SQL/titlescript th:src{/jquery-2.2.4.min.js}/scriptscript th:src{/jsonsql-0.1.js} data-mainhome/script /head body H2 stylecolor: red注意where条件必须放到括号中。只支持等于。/H2 H3请输入json数据:/H3 textarea idjson placeholder请输入json数据 rows10 cols100/textareabr H3请输入SQL:/H3 textarea idsql placeholder请输入SQL rows5 cols100/textareabr H3button typebutton onclickhandleClick() stylewidth: 200px;height: 40px获取结果/button/H3 textarea idresult rows10 cols100/textareabr /body script typetext/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 7. 项目地址 spring-boot-json-sql-demo