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

wordpress 方括号/seo搜索引擎优化岗位要求

news 文章来源：https://blog.csdn.net/qq_48008521/article/details/130999293 2025/3/14 0:34:04
wordpress 方括号,seo搜索引擎优化岗位要求,如何宣传网站,做货到付款的购物网站1. 简介 在开发中，经常需要根据条件过滤大批量的JSON类型数据。如果仅需要过滤这一种类型，将JSON转为List后过滤即可；如果相同的条件既想过滤数据库表中的数据、也想过滤内存中JSON数据，甚至想过滤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 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;}@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 JQLBinaryOpExpr<T> {/*** 字段名称*/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 List<JQLBinaryOpExpr> binaryOpExprList;/*** 子查询模型*/private List<JQLWhereModel> 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 List<String> 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 List<JQLAggregationModel> aggregations;/*** 分组*/private JQLGroupModel group;/*** 排序*/private List<JQLSortModel> 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 List<DataModel> 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, 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}*/@Overridepublic 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}*/@Overridepublic 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}*/@Overridepublic 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}*/@Overridepublic 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}*/@Overridepublic 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}*/@Overridepublic 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}*/@Overridepublic 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}*/@Overridepublic 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}*/@Overridepublic 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}*/@Overridepublic 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}*/@Overridepublic JQLBinaryOpExpr<String> empty(String fieldName) {return new JQLBinaryOpExpr<String>().setFieldName(fieldName).setOperator(Operator.EMPTY);}/*** 不为空** @param fieldName 属性名称* @return {@link JQLBinaryOpExpr}*/@Overridepublic JQLBinaryOpExpr<String> notEmpty(String fieldName) {return new JQLBinaryOpExpr<String>().setFieldName(fieldName).setOperator(Operator.NOT_EMPTY);}/*** 在两者之间** @param fieldName  属性名称* @param startValue 起始值* @param endValue   终止值* @return {@link JQLBinaryOpExpr}*/@Overridepublic 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}*/@Overridepublic 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}*/@Overridepublic 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}*/@Overridepublic 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}*/@Overridepublic 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}*/@Overridepublic 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}*/@Overridepublic 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}*/@Overridepublic 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}*/@Overridepublic 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}*/@Overridepublic 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}*/@Overridepublic 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}*/@Overridepublic JQLBinaryOpExpr<Number> empty(String fieldName) {return new JQLBinaryOpExpr<Number>().setFieldName(fieldName).setOperator(Operator.EMPTY);}/*** 不为空** @param fieldName 属性名称* @return {@link JQLBinaryOpExpr}*/@Overridepublic JQLBinaryOpExpr<Number> notEmpty(String fieldName) {return new JQLBinaryOpExpr<Number>().setFieldName(fieldName).setOperator(Operator.NOT_EMPTY);}/*** 在两者之间** @param fieldName  属性名称* @param startValue 起始值* @param endValue   终止值* @return {@link JQLBinaryOpExpr}*/@Overridepublic 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) {// 处理 SelectList<SelectField> selectFieldList = handleSelect(sqlSelectQuery.getSelectList());// 处理 FromString json = handleFrom(sqlSelectQuery.getFrom());// 处理 WhereJQLWhereModel where = handleWhere(sqlSelectQuery.getWhere());// 处理 AggregationList<JQLAggregationModel> aggregationList = handleAggregate(selectFieldList);// 处理 GroupJQLGroupModel group = handleGroup(sqlSelectQuery.getGroupBy());// 处理 OrderByList<JQLSortModel> 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 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 != 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();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@AllArgsConstructorprivate 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;}@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(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 {@Resourceprivate 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打印，则同时打印 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();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[]) { // 可能是时间类型，尝试将时间转成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 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 {@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 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.Driverurl: jdbc:mysql://127.0.0.1:3306/jql?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/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 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);}/*** 预置表及数据*/@BeforeEachpublic 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));}/*** 清除表*/@AfterEachpublic 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);}}}

  控制台打印结果：

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>
<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

7. 项目地址

spring-boot-json-sql-demo

相关文章：

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

1. 简介 在开发中&#xff0c;经常需要根据条件过滤大批量的JSON类型数据。如果仅需要过滤这一种类型&#xff0c;将JSON转为List后过滤即可&#xff1b;如果相同的条件既想过滤数据库表中的数据、也想过滤内存中JSON数据&#xff0c;甚至想过滤Elasticsearch中的数据&#xff…...

编程日记 2023/6/6 9:50:44

Linux输入输出重定向

目录 Linux输入输出重定向 Linux中的默认设备 输入输出重定向定义 输入输出重定向操作符 实用形式 标准输入、标准输出、标准错误 输出重定向案例 案例1 --- 输出重定向&#xff08;覆盖&#xff09; 案例2 --- 输出重定向&#xff08;追加&#xff09; 案例3 --- 错误…...

编程日记 2023/6/6 9:45:42

使用kettle进行数据统计

1.使用kettle设计一个能生成100个取值范围为0到100随机整数的转换。 为了完成该转换&#xff0c;需要使用生成记录控件、生成随机数控件、计算器控件及字段选择控件。控件布局如下图所示 生成记录控件可以在限制框内指定生成记录的个数&#xff0c;具体配置如图所示 生成随机数…...

编程日记 2023/6/6 9:40:42

线程的取消和清理

一、线程的取消 意义&#xff1a;随时杀掉一个线程 int pthread_cancel(pthread_t thread); 注意&#xff1a;线程的取消要有取消点才可以&#xff0c;不是说取消就取消&#xff0c;线程的取消点主要是阻塞的系统调用 二、运行段错误调试 可以使用gdb调试 使用gdb 运行代…...

编程日记 2023/6/6 9:35:41

day8 -- 全文本搜索

brief InnoDB存储引擎从MySQL 5.6开始支持全文本搜索。具体来说&#xff0c;MySQL使用InnoDB存储引擎的全文本搜索功能称为InnoDB全文本搜索&#xff08;InnoDB Full-Text Search&#xff09;。InnoDB全文本搜索支持标准的全文本搜索查询语法和多语言分词器&#xff0c;因此可…...

编程日记 2023/6/6 9:30:40

C语言：if-else语句

嗨&#xff0c;今天咱们讲讲C语言控制语句里的条件选择&#xff0c;主要总结下if else语句。 咱们生活里经常会有这样的场景&#xff0c;明天该怎么穿呢&#xff0c;得考虑下具体的天气。如果是晴天&#xff0c;温度还不错&#xff0c;可以穿T恤&#xff1b;如果是阴天&#xf…...

编程日记 2023/6/6 9:25:38

C语言---函数

1、函数是什么 学习库函数网站&#xff1a; https://cplusplus.com/reference/http://en.cppreference.comhttp://zh.cppreference.com 我们参考文档&#xff0c;学习几个库函数 2、库函数 3、自定义函数 自定义函数和库函数一样&#xff0c;有函数名&#xff0c;返回值类…...

编程日记 2023/6/6 9:20:37

【JVM】什么是双亲委派机制？

一、为什么会有这种机制&#xff1f; 类加载器将.class类加载到内存中时&#xff0c;为了避免重复加载&#xff08;确保Class对象的唯一性&#xff09;以及JVM的安全性&#xff0c;需要使用某一种方式来实现只加载一次&#xff0c;加载过就不能被修改或再次加载。 二、什么是双…...

编程日记 2023/6/6 9:15:36

Vulkan Tutorial 7 纹理贴图

目录 23 图像 图片库 暂存缓冲区 纹理图像 布局转换 将缓冲区复制到图像上 准备纹理图像 传输屏障掩码 清除 24 图像视图和采样器 纹理图像视图 采样器 Anisotropy 设备特征 25 组合图像采样器 更新描述符 纹理坐标系 着色器 23 图像 添加纹理将涉及以下步骤&am…...

编程日记 2023/6/6 9:10:35

LinkedBlockingQueue阻塞队列

➢ LinkedBlockingQueue阻塞队列 LinkedBlockingQueue类图 LinkedBlockingQueue 中也有两个 Node 分别用来存放首尾节点&#xff0c;并且里面有个初始值为 0 的原子变量 count 用来记录队列元素个数&#xff0c;另外里面有两个ReentrantLock的独占锁&#xff0c;分别用来控制…...

编程日记 2023/6/6 9:05:33

面试-Redis 常见问题，后续面试遇到新的在补充

面试-Redis 1.谈谈Redis 缓存穿透&#xff0c;击穿&#xff0c;雪崩及如何避免 缓存穿透&#xff1a;是指大量访问请求在访问一个不存在的key&#xff0c;由于key 不存在&#xff0c;就会去查询数据库&#xff0c;数据库中也不存在该数据&#xff0c;无法将数据存储到redis 中…...

编程日记 2023/6/6 9:00:32

2023年上半年数据库系统工程师上午真题及答案解析

1.计算机中, 系统总线用于( )连接。 A.接口和外设 B.运算器、控制器和寄存器 C.主存及外设部件 D.DMA控制器和中断控制器 2.在由高速缓存、主存和硬盘构成的三级存储体系中&#xff0c;CPU执行指令时需要读取数据&#xff0c;那么DMA控制器和中断CPU发出的数据地…...

编程日记 2023/6/6 8:55:31

设计模式概念

设计模式是软件工程领域中常用的解决问题的经验总结和最佳实践。它们提供了一套被广泛接受的解决方案&#xff0c;用于处理常见的设计问题&#xff0c;并促进可重用、可扩展和易于维护的代码。 设计模式的主要目标是提高软件的可重用性、可扩展性和灵活性&#xff0c;同时降低…...

编程日记 2023/6/6 8:50:30

arcpy批量对EXCE经纬度L进行投点，设置为wgs84坐标系，并利用该点计算每个区域内的核密度

以下是在 ArcPy 中批量对 Excel 经纬度 L 进行投点&#xff0c;设置为 WGS84 坐标系&#xff0c;并利用该点计算每个区域内的核密度的详细步骤&#xff1a; 1. 准备数据: 准备包含经纬度信息的 Excel 数据表格&#xff0c;我们假设文件路径为 "C:/Data/locations.xlsx&qu…...

编程日记 2023/6/6 8:45:29

Yolov5训练自己的数据集

先看下模型pt说明 YOLOv5s&#xff1a;这是 YOLOv5 系列中最小的模型。“s” 代表 “small”&#xff08;小&#xff09;。该模型在计算资源有限的设备上表现最佳&#xff0c;如移动设备或边缘设备。YOLOv5s 的检测速度最快&#xff0c;但准确度相对较低。 YOLOv5m&#xff1…...

编程日记 2023/6/6 8:40:28

Bert+FGSM中文文本分类

我上一篇博客已经分别用BertFGSM和BertPGD实现了中文文本分类&#xff0c;这篇文章与我上一篇文章BertFGSM/PGD实现中文文本分类&#xff08;Loss0.5L10.5L2)_Dr.sky_的博客-CSDN博客的不同之处在于主要在对抗训练函数和embedding添加扰动部分、模型定义部分、Loss函数传到部分…...

编程日记 2023/6/6 8:35:26

爬楼梯问题-从暴力递归到动态规划(java)

爬楼梯&#xff0c;每次只能爬一阶或者两阶&#xff0c;计算有多少种爬楼的情况 爬楼梯--题目描述暴力递归递归缓存动态规划暴力递归到动态规划专题 爬楼梯–题目描述 一个总共N 阶的楼梯&#xff08;N > 0&#xff09; 每次只能上一阶或者两阶。问总共有多少种爬楼方式。 示…...

编程日记 2023/6/6 8:30:24

浏览器如何验证SSL证书？

浏览器如何验证SSL证书&#xff1f;当前SSL证书应用越来越广泛&#xff0c;我们看见的HTTPS网站也越来越多。点击HTTPS链接签名的绿色小锁&#xff0c;我们可以看见SSL证书的详细信息。那么浏览器是如何验证SSL证书的呢? 浏览器如何验证SSL证书&#xff1f; 在浏览器的菜单中…...

编程日记 2023/6/6 8:25:23

Linux :: 【基础指令篇 :: 文件及目录操作：(10)】:: ll 指令 :: 查看指定目录下的文件详细信息

前言&#xff1a;本篇是 Linux 基本操作篇章的内容&#xff01; 笔者使用的环境是基于腾讯云服务器&#xff1a;CentOS 7.6 64bit。 学习集&#xff1a; C 入门到入土&#xff01;&#xff01;&#xff01;学习合集Linux 从命令到网络再到内核&#xff01;学习合集 目录索引&am…...

编程日记 2023/6/6 8:20:22

Java字符集/编码集

1 字符集/编码集 基础知识 计算机中储存的信息都是用二进制数表示的;我们在屏幕上看到的英文、汉字等字符是二进制数转换之后的结果 按照某种规则， 将字符存储到计算机中，称为编码。反之，将存储在计算机中的二进制数按照某种规则解析显示出来，称为解码。这里强调一下: 按照…...

编程日记 2023/6/6 8:15:21

Apache配置与应用

目录 虚拟web主机httpd服务支持的虚拟主机类型基于域名配置方法基于IP配置方法基于端口配置方法 apache连接保持构建Web虚拟目录与用户授权限制Apache日志分割 虚拟web主机 虚拟Web主机指的是在同一台服务器中运行多个Web站点&#xff0c;其中每一个站点实际上并不独立占用整个…...

编程日记 2023/6/6 8:10:19

API自动化测试【postman生成报告】

PostMan生成测试报告有两种&#xff1a; 1、控制台的模式 2、HTML的测试报告 使用到一个工具newman Node.js是前端的一个组件&#xff0c;主要可以使用它来开发异步的程序。 一、控制台的模式 1、安装node.js 双击node.js进行安装&#xff0c;安装成功后在控制台输入node …...

编程日记 2023/6/6 8:05:17

探索OpenAI插件：ChatWithGit，memecreator，boolio

引言 在当今的技术世界中&#xff0c;插件扮演着至关重要的角色&#xff0c;它们提供了一种简单有效的方式来扩展和增强现有的软件功能。在本文中&#xff0c;我们将探索三个OpenAI的插件&#xff1a;ChatWithGit&#xff0c;memecreator&#xff0c;和boolio&#xff0c;它们…...

编程日记 2023/6/6 8:00:16

linux irq

中断上下部 软中断、tasklet、工作对列 软中断优点&#xff1a;运行在软中断上下文&#xff0c;优先级比普通进程高&#xff0c;调度速度快。 缺点&#xff1a;由于处于中断上下文&#xff0c;所以不能睡眠。 相对于软中断/tasklet&#xff0c;工作对列运行在进程上下文 h…...

编程日记 2023/6/6 7:55:15

串口流控（CTS/RTS）使用详解

1.流控概念 在两个设备正常通信时&#xff0c;由于处理速度不同&#xff0c;就存在这样一个问题&#xff0c;有的快&#xff0c;有的慢&#xff0c;在某些情况下&#xff0c;就可能导致丢失数据的情况。 如台式机与单片机之间的通讯&#xff0c;接收端数据缓冲区已满&#xff0…...

编程日记 2023/6/6 7:50:14

kube-proxy模式详解

1 kube-proxy概述 kubernetes里kube-proxy支持三种模式&#xff0c;在v1.8之前我们使用的是iptables 以及 userspace两种模式&#xff0c;在kubernetes 1.8之后引入了ipvs模式&#xff0c;并且在v1.11中正式使用&#xff0c;其中iptables和ipvs都是内核态也就是基于netfilter&…...

编程日记 2023/6/6 7:45:13

汽车EDI：如何与Stellantis建立EDI连接？

Stellantis 是一家实力雄厚的汽车制造公司&#xff0c;由法国标致雪铁龙集团&#xff08;PSA集团&#xff09;和意大利菲亚特克莱斯勒汽车集团&#xff08;FCA集团&#xff09;合并而成&#xff0c;是世界上第四大汽车制造商&#xff0c;拥有包括标致、雪铁龙、菲亚特、克莱斯勒…...

编程日记 2023/6/6 7:40:12

【SCI征稿】1区计算机科学类SCI, 自引率低，对国人友好~

一、【期刊简介】 JCR1区计算机科学类SCI&EI 【期刊概况】IF: 7.0-8.0&#xff0c;JCR1区&#xff0c;中科院2区&#xff1b; 【终审周期】走期刊系统&#xff0c;3-5个月左右录用; 【检索情况】SCI&EI双检&#xff1b; 【自引率】1.30% 【征稿领域】发表人工智能…...

编程日记 2023/6/6 7:35:11

Vue.js优化策略与性能调优指南

导语&#xff1a;Vue.js是一款出色的前端框架&#xff0c;但在处理大规模应用或复杂场景时&#xff0c;性能问题可能会出现。本文将介绍一些Vue.js优化策略和性能调优指南&#xff0c;帮助您提升应用的性能和用户体验。 延迟加载&#xff1a;将应用的代码进行按需加载&#xff…...

编程日记 2023/6/6 7:30:10

HEVC环路后处理核心介绍

介绍 为什么需要环路后处理技术 hevc采用基于快的混合编码框架&#xff0c;方块效应、振铃效应、颜色偏差、图像模糊等失真效应依旧存在&#xff0c;为了降低此类失真影响&#xff0c;需要进行环路滤波技术&#xff1b; 采用的技术 去方块滤波DF&#xff0c;为了降低块效应…...

编程日记 2023/6/6 7:25:09