当前位置：首页 >数据库 > DB SQL 转 ES DSL（支持多种数据库常用查询、统计、平均值、最大值、最小值、求和语法）

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

1. 简介

日常开发中需要查询Elasticsearch中的数据时，一般会采用RestHighLevelClient高级客户端封装的API。项目中一般采用一种或多种关系型数据库（如：Mysql、PostgreSQL、Oracle等） + NoSQL（如：Elasticsearch）存储方案；不同关系数据库可以采用Mybatis-Plus方案屏蔽数据库的方言差异，我们期望可以像操作关系型数据库那样方便的使用SQL操作Elasticsearch，就需要一种方案可以解决此问题。
本博客使用SpringBoot+Mybatis-Plus+Mysql+Elasticsearch V7.6，除了提供对Table或Index的增删改查操作之外，还提供将SQL转DSL工具方法、Mybatis-Plus查询自定义SQL、RestHighLevelClient查询自定义DSL示例代码。

2. SQL转DSL能力总览

能力	明细
支持的数据库	Mysql PostgreSQL Oracle SQL Server DB2等（采用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等，其他函数可自行增加
支持的增删改语法	暂不支持insert、update、delete语句，可以参考ElasticsearchRepository示例代码，直接使用API即可

3. 初始化数据库

CREATE DATABASE `sql2dsl`;USE `sql2dsl`;DROP TABLE IF EXISTS `user`;CREATE TABLE `user` (`id` int NOT NULL AUTO_INCREMENT COMMENT 'ID',`useame` varchar(20) DEFAULT NULL COMMENT '姓名',`account` varchar(20) DEFAULT NULL COMMENT '账号',`age` int DEFAULT NULL COMMENT '年龄',`sex` int DEFAULT NULL COMMENT '性别',`address` varchar(50) DEFAULT NULL COMMENT '地址',`create_time` TIMESTAMP DEFAULT NULL COMMENT '创建时间',PRIMARY KEY (`id`)) COMMENT='用户信息表';

4. 示例代码

4.1 创建项目

4.2 修改pom.xml

<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>sql2dsl-demo</artifactId><version>0.0.1-SNAPSHOT</version><name>sql2dsl-demo</name><description>sql to dsl demo</description><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.6.14</version><relativePath/></parent><properties><java.version>1.8</java.version></properties><dependencies><dependency><groupId>cn.hutool</groupId><artifactId>hutool-all</artifactId><version>5.8.9</version></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.5.3.1</version></dependency><dependency><groupId>com.alibaba</groupId><artifactId>druid-spring-boot-starter</artifactId><version>1.2.15</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-data-elasticsearch</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><version>${parent.version}</version></plugin></plugins></build></project>

4.3 添加配置文件application.yml

spring:  datasource:driver-class-name: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://127.0.0.1:3306/sql2dsl?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghaiuseame: rootpassword: 123456  elasticsearch:uris: http://127.0.0.1:9200 #多个用,分开# useame: elastic# password: elasticconnectionTimeout: 5ssocketTimeout: 30s# Mybatis-plus配置mybatis-plus:  configuration:# 打印sql，生产建议关闭log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

4.4 创建实体

除了添加Mybatis-Plus注解操作Table外，还添加Elasticsearch注解，方便操作Index。

import com.baomidou.mybatisplus.annotation.IdType;import com.baomidou.mybatisplus.annotation.TableField;import com.baomidou.mybatisplus.annotation.TableId;import com.baomidou.mybatisplus.annotation.TableName;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import org.springframework.data.annotation.Id;import org.springframework.data.elasticsearch.annotations.Document;import org.springframework.data.elasticsearch.annotations.Field;import org.springframework.data.elasticsearch.annotations.FieldType;import java.util.Date;import java.util.Objects;/** * 用户信息 * * @author CL */@Data@NoArgsConstructor@AllArgsConstructor@TableName(value = "user")@Document(indexName = "user")public class User {/** * ID */@Id@TableId(type = IdType.AUTO)private Long id;/** * 姓名 */@Field(type = FieldType.Keyword)@TableFieldprivate String useame;/** * 账号 */@Field(type = FieldType.Keyword)@TableFieldprivate String account;/** * 年龄 */@Field(type = FieldType.Integer)@TableFieldprivate Integer age;/** * 性别 0-女 1-男 */@Field(type = FieldType.Integer)@TableFieldprivate Integer sex;/** * 地址 */@Field(type = FieldType.Keyword)@TableFieldprivate String address;/** * 创建时间 */@Field(name = "create_time", type = FieldType.Date)@TableFieldprivate Date createTime;@Overridepublic boolean equals(Object o) {if (this == o) retu true;if (o == null || getClass() != o.getClass()) retu false;User user = (User) o;retu Objects.equals(useame, user.useame)&& Objects.equals(account, user.account)&& Objects.equals(age, user.age)&& Objects.equals(sex, user.sex)&& Objects.equals(address, user.address);}@Overridepublic int hashCode() {retu Objects.hash(useame, account, age, sex, address);}}

4.5 创建Mapper

基于Mybatis-Plus的BaseMapper对Mysql中Table的增删改查操作。

import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.c3stones.entity.User;import org.apache.ibatis.annotations.Mapper;/** * 用户信息 Mapper * * @author CL */@Mapperpublic interface UserMapper extends BaseMapper<User> {}

4.6 创建Repository

基于spring-data-elasticsearch的ElasticsearchRepository对Elasticsearch中Index的增删改查操作。

/** * 用户信息 Repository * * @author CL */@Repositorypublic interface UserRepository extends ElasticsearchRepository<User, Long> {}

4.7 创建Elasticsearch DSL 统用查询语法类

import cn.hutool.core.util.StrUtil;import java.util.List;import java.util.Objects;import java.util.StringJoiner;import static com.c3stones.es.constants.Constant.*;/** * Elasticsearch DSL 统用查询语法 * * @author CL */public class DSLSelectSyntax {/** * 格式化 * * @param values 值 * @retu {@link String} */public String format(List<String> values) {StringJoiner format = new StringJoiner("\",\"", "\"", "\"");values.forEach(format::add);retu format.toString();}/** * 等于 * * @param fieldName 属性名称 * @param value 值 * @retu {@link String} */public String eq(String fieldName, String value) {retu String.format("{\"match_phrase\" : {\"%s\" : \"%s\"}}", fieldName, value);}/** * 不等 * * @param fieldName 属性名称 * @param value 值 * @retu {@link String} */public String neq(String fieldName, String value) {retu String.format("{\"bool\" : {\"must_not\" : [{\"match_phrase\" : {\"%s\" : {\"query\" : \"%s\"}}}]}}", fieldName, value);}/** * 大于 * * @param fieldName 属性名称 * @param value 值 * @retu {@link String} */public String gt(String fieldName, String value) {retu String.format("{\"range\" : {\"%s\" : {\"gt\" : \"%s\"}}}", fieldName, value);}/** * 大于等于 * * @param fieldName 属性名称 * @param value 值 * @retu {@link String} */public String gte(String fieldName, String value) {retu String.format("{\"range\" : {\"%s\" : {\"from\" : \"%s\"}}}", fieldName, value);}/** * 小于 * * @param fieldName 属性名称 * @param value 值 * @retu {@link String} */public String lt(String fieldName, String value) {retu String.format("{\"range\" : {\"%s\" : {\"lt\" : \"%s\"}}}", fieldName, value);}/** * 小于等于 * * @param fieldName 属性名称 * @param value 值 * @retu {@link String} */public String lte(String fieldName, String value) {retu String.format("{\"range\" : {\"%s\" : {\"to\" : \"%s\"}}}", fieldName, value);}/** * 属于 * * @param fieldName 属性名称 * @param values值 * @retu {@link String} */public String in(String fieldName, List<String> values) {retu String.format("{\"terms\" : {\"%s\" : [%s]}}", fieldName, format(values));}/** * 不属于 * * @param fieldName 属性名称 * @param values值 * @retu {@link String} */public String notIn(String fieldName, List<String> values) {retu String.format("{\"bool\" : {\"must_not\" : {\"terms\" : {\"%s\" : [%s]}}}}", fieldName, format(values));}/** * 包含 * * @param fieldName 属性名称 * @param value 值 * @retu {@link String} */public String contain(String fieldName, String value) {retu String.format("{\"query_string\":{\"default_field\": \"%s\",\"query\":\"%s\"}}", fieldName, value.replaceAll("%", "*"));}/** * 不包含 * * @param fieldName 属性名称 * @param value 值 * @retu {@link String} */public String notContain(String fieldName, String value) {retu String.format("{\"bool\":{\"must_not\":{\"query_string\":{\"default_field\":\"%s\",\"query\":\"%s\"}}}}", fieldName, value.replaceAll("%", "*"));}/** * 为空 * * @param fieldName 属性名称 * @retu {@link String} */public String empty(String fieldName) {retu String.format("{\"bool\": { \"must_not\": { \"exists\": { \"field\": \"%s\" }}}}", fieldName);}/** * 不为空 * * @param fieldName 属性名称 * @retu {@link String} */public String notEmpty(String fieldName) {retu String.format("{\"bool\": { \"must\": { \"exists\": { \"field\": \"%s\" }}}}", fieldName);}/** * 在两者之间 * * @param fieldName  属性名称 * @param startValue 起始值 * @param endValue终止值 * @retu {@link String} */public String range(String fieldName, String startValue, String endValue) {retu String.format("{\"range\" : {\"%s\" : {\"from\" : \"%s\", \"to\" : \"%s\"}}}", fieldName, startValue, endValue);}/** * 必须包含 * * @param dsl DSL * @retu {@link String} */public String must(String dsl) {retu String.format("{\"bool\" : {\"must\" : [%s]}}", dsl);}/** * 可能包含 * * @param dsl DSL * @retu {@link String} */public String should(String dsl) {retu String.format("{\"bool\" : {\"should\" : [%s]}}", dsl);}/** * 查询全部 * * @retu {@link String} */public String all() {retu "{\"match_all\": {}}";}/** * DSL * * @param whereWhere条件 * @param groupBy 分组字段 * @param orderBy 排序字段 * @param from偏移数 * @param size限制数 * @retu {@link String} */public String dsl(String where, String groupBy, String orderBy, Integer from, Integer size) {StringBuilder sb = new StringBuilder();sb.append(String.format("{\"" + DSL_QUERY + "\" : %s ", where));if (StrUtil.isNotBlank(groupBy)) {sb.append(String.format(" ,\"" + DSL_AGGREGATIONS + "\" : %s", groupBy));}if (StrUtil.isNotBlank(orderBy)) {sb.append(String.format(" ,\"" + DSL_SORT + "\" : %s", orderBy));}if (Objects.nonNull(from)) {sb.append(String.format(" ,\"" + DSL_FROM + "\" : %s ", from));}if (Objects.nonNull(size)) {sb.append(String.format(" ,\"" + DSL_SIZE + "\" : %s ", size));}sb.append("}");retu sb.toString();}}

4.8 创建SQL转DSL工具方法

import cn.hutool.core.collection.CollUtil;import cn.hutool.core.lang.Opt;import cn.hutool.core.map.MapUtil;import cn.hutool.core.util.IdUtil;import cn.hutool.core.util.StrUtil;import cn.hutool.json.JSONUtil;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.es.mapper.ESMapperProvider;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import lombok.RequiredArgsConstructor;import lombok.experimental.Accessors;import lombok.exte.slf4j.Slf4j;import java.util.*;import java.util.stream.Collectors;import static com.alibaba.druid.sql.ast.expr.SQLBinaryOperator.BooleanAnd;import static com.alibaba.druid.sql.ast.expr.SQLBinaryOperator.BooleanOr;import static com.c3stones.es.constants.Constant.*;import static java.util.Collections.singletonMap;/** * SQL 转 DSL * * @author CL */@Slf4j@RequiredArgsConstructorpublic class DSLConvert {private final DSLSelectSyntax dslSelectSyntax;/** * 转换 * * @param sqlSQL * @param dbType 数据库类型 * @retu {@link ESMapperProvider} */public ESMapperProvider 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());retu optional.isPresent() ? handle(optional.get()) : null;}/** * 处理SQL * * @param sqlSelectQuery SQL Select查询 * @retu {@link ESMapperProvider} */private ESMapperProvider handle(SQLSelectQueryBlock sqlSelectQuery) {// 处理 SelectList<SelectField> selectFieldList = handleSelect(sqlSelectQuery.getSelectList());// 处理 FromString index = handleFrom(sqlSelectQuery.getFrom());// 处理 WhereString where = handleWhere(sqlSelectQuery.getWhere(), true);// 处理 GroupByString groupBy = handleGroupBy(selectFieldList, sqlSelectQuery.getGroupBy());// 处理 OrderByString orderBy = handleOrderBy(sqlSelectQuery.getOrderBy());// 处理 LimitPage page = handleLimit(sqlSelectQuery.getLimit());// 生成DSLInteger from = Opt.ofNullable(page).map(Page::getFrom).get();Integer size = Opt.ofNullable(page).map(Page::getSize).get();String dsl = dslSelectSyntax.dsl(where, groupBy, orderBy, from, size);String[] includes = selectFieldList.stream().map(field -> Opt.ofNullable(field.getAlias()).orElse(field.getName())).filter(field -> !StrUtil.equals("*", field)).toArray(String[]::new);retu new ESMapperProvider(index, dsl, includes);}/** * 处理查询字段 * * @param sqlSelectItemList 查询元素 * @retu {@link List<SelectField>} */private List<SelectField> handleSelect(List<SQLSelectItem> sqlSelectItemList) {retu 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 = "*";} else if (firstSqlExpr instanceof SQLIdentifierExpr) {name = ((SQLIdentifierExpr) firstSqlExpr).getName();}} else if (sqlExpr instanceof SQLAllColumnExpr) {  // 查询全部name = "*";} else if (sqlExpr instanceof SQLMethodInvokeExpr) { // 函数调用SQLMethodInvokeExpr methodInvokeExpr = (SQLMethodInvokeExpr) sqlExpr;SQLExpr firstSqlExpr = CollUtil.getFirst(methodInvokeExpr.getArguments());methodName = methodInvokeExpr.getMethodName();if (firstSqlExpr instanceof SQLIdentifierExpr) {name = ((SQLIdentifierExpr) firstSqlExpr).getName();} else if (firstSqlExpr instanceof SQLBinaryOpExpr) {name = handleWhere(firstSqlExpr, true);} else {name = firstSqlExpr.toString();}} else if (sqlExpr instanceof SQLIdentifierExpr) { // 查询指定列name = ((SQLIdentifierExpr) sqlExpr).getName();}retu new SelectField(name, alias, methodName);}).collect(Collectors.toList());}/** * 处理 From * * @param sqlTableSource SQL表资源 * @retu {@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();}}retu index;}/** * 处理 Where条件 * * @param sqlExprSQL表达式 * @param isComplete 是否完整条件 * @retu {@link String} */private String handleWhere(SQLExpr sqlExpr, boolean isComplete) {if (sqlExpr instanceof SQLBinaryOpExpr) {SQLBinaryOpExpr sqlBinaryOpExpr = (SQLBinaryOpExpr) sqlExpr;SQLBinaryOperator operator = sqlBinaryOpExpr.getOperator();if (BooleanAnd == operator || BooleanOr == operator)retu handleWhereAndOrOr(sqlBinaryOpExpr, operator);retu handleWhereBinaryOp(sqlBinaryOpExpr, isComplete);} else if (sqlExpr instanceof SQLInListExpr) {retu handleWhereInOrNotIn((SQLInListExpr) sqlExpr, isComplete);} else if (sqlExpr instanceof SQLBetweenExpr) {retu handleWhereBetween((SQLBetweenExpr) sqlExpr, isComplete);}retu dslSelectSyntax.all();}/** * 处理 AND 或 OR * * @param sqlBinaryOpExpr SQL两位元素操作 * @retu {@link String} */private String handleWhereAndOrOr(SQLBinaryOpExpr sqlBinaryOpExpr, SQLBinaryOperator sqlBinaryOperator) {SQLExpr leftExpr = sqlBinaryOpExpr.getLeft();SQLExpr rightExpr = sqlBinaryOpExpr.getRight();String left = handleWhere(leftExpr, false);String right = handleWhere(rightExpr, false);StringJoiner dsl = new StringJoiner(StrUtil.equalsAny(StrUtil.EMPTY, left, right) ? StrUtil.EMPTY : StrUtil.COMMA);dsl.add(left).add(right);SQLObject parent = sqlBinaryOpExpr.getParent();if (parent instanceof SQLBinaryOpExpr) {if (((SQLBinaryOpExpr) parent).getOperator() == sqlBinaryOperator) retu dsl.toString();}retu sqlBinaryOperator == BooleanAnd ? dslSelectSyntax.must(dsl.toString()) : dslSelectSyntax.should(dsl.toString());}/** * 处理二位元素操作 * * @param sqlExprSQL表达式 * @param isComplete 是否完整条件 * @retu {@link String} */private String handleWhereBinaryOp(SQLBinaryOpExpr sqlExpr, boolean isComplete) {StringBuilder dsl = new StringBuilder();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) {dsl.append(dslSelectSyntax.empty(IdUtil.fastUUID()));} else {dsl.append(dslSelectSyntax.notEmpty(IdUtil.fastUUID()));}}} else {SQLIdentifierExpr sqlIdentifierExpr = (SQLIdentifierExpr) sqlExpr.getLeft();String fieldName = sqlIdentifierExpr.getName();String value = getValue(rightExpr);switch (sqlExpr.getOperator()) {case Equality:dsl.append(dslSelectSyntax.eq(fieldName, value));break;case NotEqual:dsl.append(dslSelectSyntax.neq(fieldName, value));break;case GreaterThan:dsl.append(dslSelectSyntax.gt(fieldName, value));break;case GreaterThanOrEqual:dsl.append(dslSelectSyntax.gte(fieldName, value));break;case LessThan:dsl.append(dslSelectSyntax.lt(fieldName, value));break;case LessThanOrEqual:dsl.append(dslSelectSyntax.lte(fieldName, value));break;case Like:dsl.append(dslSelectSyntax.contain(fieldName, escape(value)));break;case NotLike:dsl.append(dslSelectSyntax.notContain(fieldName, value));break;case Is:dsl.append(dslSelectSyntax.empty(fieldName));break;case IsNot:dsl.append(dslSelectSyntax.notEmpty(fieldName));break;default:// no operate}}retu isComplete ? dslSelectSyntax.must(dsl.toString()) : dsl.toString();}/** * 处理 in 或 notIn * * @param sqlInListExpr SQL In 表达式 * @param isComplete是否完整条件 * @retu {@link String} */private String handleWhereInOrNotIn(SQLInListExpr sqlInListExpr, boolean isComplete) {SQLIdentifierExpr sqlIdentifierExpr = (SQLIdentifierExpr) sqlInListExpr.getExpr();String fieldName = sqlIdentifierExpr.getName();List<String> values = sqlInListExpr.getTargetList().stream().map(this::getValue).collect(Collectors.toList());String dsl = sqlInListExpr.isNot() ? dslSelectSyntax.notIn(fieldName, values) : dslSelectSyntax.in(fieldName, values);retu isComplete ? dslSelectSyntax.must(dsl) : dsl;}/** * 处理 between * * @param sqlBetweenExpr SQL Between 表达式 * @param isComplete 是否完整条件 * @retu {@link String} */private String handleWhereBetween(SQLBetweenExpr sqlBetweenExpr, boolean isComplete) {SQLIdentifierExpr sqlIdentifierExpr = (SQLIdentifierExpr) sqlBetweenExpr.getTestExpr();String field = sqlIdentifierExpr.getName();String startValue = getValue(sqlBetweenExpr.getBeginExpr());String endValue = getValue(sqlBetweenExpr.getEndExpr());String dsl = dslSelectSyntax.range(field, startValue, endValue);retu isComplete ? dslSelectSyntax.must(dsl) : dsl;}/** * 处理 GroupBy * * @param selectFieldList查询字段 * @param sqlSelectGroupByClause SQL GroupBy 从句 * @retu {@link String} */private String handleGroupBy(List<SelectField> selectFieldList, SQLSelectGroupByClause sqlSelectGroupByClause) {if (selectFieldList.stream().allMatch(field -> Objects.isNull(field.getMethodName()))) retu null;Queue<String> groupByList = CollUtil.newLinkedList();if (Objects.nonNull(sqlSelectGroupByClause)) {for (SQLExpr sqlExpr : sqlSelectGroupByClause.getItems()) {if (sqlExpr instanceof SQLIdentifierExpr) {groupByList.add(((SQLIdentifierExpr) sqlExpr).getName());}}}retu JSONUtil.toJsonStr(handleAggregate(selectFieldList, groupByList));}/** * 处理 OrderBy * * @param sqlOrderBy SQL OrderBy * @retu {@link String} */private String handleOrderBy(SQLOrderBy sqlOrderBy) {if (Objects.isNull(sqlOrderBy)) retu null;List<Map<String, String>> orderByList = CollUtil.newArrayList();for (SQLSelectOrderByItem sqlSelectOrderByItem : sqlOrderBy.getItems()) {SQLIdentifierExpr orderBySqlIdentifierExpr = (SQLIdentifierExpr) sqlSelectOrderByItem.getExpr();SQLOrderingSpecification sqlOrderingSpecification = sqlSelectOrderByItem.getType();orderByList.add(singletonMap(orderBySqlIdentifierExpr.getName(), sqlOrderingSpecification.name()));}retu CollUtil.isNotEmpty(orderByList) ? JSONUtil.toJsonStr(orderByList) : null;}/** * 处理 Limit * * @param sqlLimit SQL Limit * @retu {@link Page} */private Page handleLimit(SQLLimit sqlLimit) {if (Objects.isNull(sqlLimit)) retu 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();retu new Page().setFrom(from).setSize(size);}/** * 处理聚合函数 * {分组字段 : 配置} * * @param selectFields 查询字段 * @param groupByList  分组字段 * @retu {@link Map} */private Map<String, Object> handleAggregate(List<SelectField> selectFields, Queue<String> groupByList) {if (groupByList.isEmpty()) retu handleAggregate(selectFields);String groupBy = groupByList.poll();HashMap<String, Object> fieldMap = MapUtil.of(DSL_TERMS, MapUtil.of(DSL_AGGREGATIONS_FIELD, groupBy));fieldMap.put(DSL_AGGREGATIONS, handleAggregate(selectFields, groupByList));retu MapUtil.of(groupBy, fieldMap);}/** * 处理聚合函数 * {分组字段 : 配置} * * @param selectFieldList 查询字段 * @retu {@link Map} */private Map<String, Object> handleAggregate(List<SelectField> selectFieldList) {if (CollUtil.isEmpty(selectFieldList)) retu null;Map<String, Object> result = MapUtil.newHashMap(2);for (SelectField field : selectFieldList) {String method = field.getMethodName();if (StrUtil.isEmpty(method)) continue;String fieldName = field.getName();String alias = field.getAlias();if (StrUtil.equals(method, DSL_COUNT)) {method = DSL_VALUE_COUNT;if (StrUtil.equals(fieldName, "*")) fieldName = "_index";}result.put(alias, MapUtil.of(method, MapUtil.of(DSL_AGGREGATIONS_FIELD, fieldName)));}retu result;}/** * 获取值 * * @param sqlExpr 表达式 * @retu {@link String} */private String getValue(SQLExpr sqlExpr) {String value = StrUtil.EMPTY;if (sqlExpr instanceof SQLIntegerExpr) {value = ((SQLIntegerExpr) sqlExpr).getNumber().toString();} else if (sqlExpr instanceof SQLCharExpr) {value = ((SQLCharExpr) sqlExpr).getText();} else if (sqlExpr instanceof SQLNumberExpr) {value = ((SQLNumberExpr) sqlExpr).getNumber().toString();} 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).collect(Collectors.joining());} else if (StrUtil.equalsAnyIgnoreCase(methodName, "lower", "upper")) {retu getValue(CollUtil.getFirst(arguments));} else if (StrUtil.equalsAnyIgnoreCase(methodName, "to_timestamp", "from_unixtime")) {String tmp = getValue(CollUtil.getFirst(arguments));retu CollUtil.getFirst(StrUtil.split(tmp, StrUtil.DOT));}} else if (sqlExpr instanceof SQLCastExpr) {SQLCastExpr sqlCastExpr = (SQLCastExpr) sqlExpr;retu getValue(sqlCastExpr.getExpr());} else if (sqlExpr instanceof SQLBinaryOpExpr) {SQLBinaryOpExpr sqlBinaryOpExpr = (SQLBinaryOpExpr) sqlExpr;retu getValue(sqlBinaryOpExpr.getLeft()) + StrUtil.DOT+ getValue(sqlBinaryOpExpr.getRight());} else if (sqlExpr instanceof SQLNullExpr) {value = null;}retu value;}/** * 字符串转义 * * @param str 字符串 * @retu {@link String} */private String escape(String str) {if (StringUtils.isBlank(str)) retu 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);}retu 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.9 创建Elasticsearch 配置类

import cn.hutool.core.util.StrUtil;import cn.hutool.core.util.URLUtil;import org.apache.http.HttpHost;import org.apache.http.auth.AuthScope;import org.apache.http.auth.UseamePasswordCredentials;import org.apache.http.client.CredentialsProvider;import org.apache.http.impl.client.BasicCredentialsProvider;import org.elasticsearch.client.RestClient;import org.elasticsearch.client.RestHighLevelClient;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.autoconfigure.elasticsearch.ElasticsearchProperties;import org.springframework.context.annotation.Configuration;import org.springframework.data.elasticsearch.config.AbstractElasticsearchConfiguration;import java.net.URL;/** * Elasticsearch 配置类 * * @author CL */@Configurationpublic class ElasticsearchConfig extends AbstractElasticsearchConfiguration {@Autowiredprivate ElasticsearchProperties elasticsearchProperties;/** * 重写 RestHighLevelClient * * @retu {@link RestHighLevelClient} */@Overridepublic RestHighLevelClient elasticsearchClient() {HttpHost[] httpHosts = elasticsearchProperties.getUris().stream().map(uri -> {URL url = URLUtil.url(uri);retu new HttpHost(url.getHost(), url.getPort(), url.getProtocol());}).toArray(HttpHost[]::new);int connectTimeout = (int) elasticsearchProperties.getConnectionTimeout().getSeconds() * 1000;int socketTimeout = (int) elasticsearchProperties.getSocketTimeout().getSeconds() * 1000;String useame = elasticsearchProperties.getUseame();String password = elasticsearchProperties.getPassword();retu new RestHighLevelClient(RestClient.builder(httpHosts).setRequestConfigCallback(requestConfigBuilder -> requestConfigBuilder.setConnectTimeout(connectTimeout).setSocketTimeout(socketTimeout).setConnectionRequestTimeout(connectTimeout)).setHttpClientConfigCallback(httpClientBuilder -> {if (StrUtil.isNotEmpty(useame) && StrUtil.isNotEmpty(password)) {CredentialsProvider credentialsProvider = new BasicCredentialsProvider();credentialsProvider.setCredentials(AuthScope.ANY, new UseamePasswordCredentials(useame, password));httpClientBuilder.setDefaultCredentialsProvider(credentialsProvider);}retu httpClientBuilder;}));}}

4.10 创建常量类

/** * 常量 * * @author CL */public class Constant {/** * DSL 常量 - 查询 */public static final String DSL_QUERY = "query";/** * DSL 常量 - 统计 */public static final String DSL_COUNT = "count";/** * DSL 常量 - 统计 */public static final String DSL_VALUE_COUNT = "value_count";/** * DSL 常量 - 最大值 */public static final String DSL_MAX = "max";/** * DSL 常量 - 最小值 */public static final String DSL_MIN = "min";/** * DSL 常量 - 平均值 */public static final String DSL_AVG = "avg";/** * DSL 常量 - 求和 */public static final String DSL_SUM = "sum";/** * DSL 常量 - 分组 */public static final String DSL_TERMS = "terms";/** * DSL 常量 - 分组大小 */public static final String DSL_TERMS_SIZE = "size";/** * DSL 常量 - 聚合 */public static final String DSL_AGGREGATIONS = "aggregations";/** * DSL 常量 - 聚合属性 */public static final String DSL_AGGREGATIONS_FIELD = "field";/** * DSL 常量 - 排序 */public static final String DSL_SORT = "sort";/** * DSL 常量 - 偏移量 */public static final String DSL_FROM = "from";/** * DSL 常量 - 限制数 */public static final String DSL_SIZE = "size";}

4.11 创建数据库通用查询Mapper（自定义SQL查询）

/** * 数据库 通用 Mapper Provider * * @author CL */public class DBMapperProvider {/** * 获取SQL * * @param sql SQL * @retu {@link String} */public String getSql(String sql) {retu sql;}}

import org.apache.ibatis.annotations.Mapper;import org.apache.ibatis.annotations.SelectProvider;import java.util.List;import java.util.Map;/** * 数据库 通用 Mapper * * @author CL */@Mapperpublic interface DBMapper {/** * 集合 * * @param sql SQL * @retu {@link List} */@SelectProvider(method = "getSql", type = DBMapperProvider.class)List<Map<String, Object>> aggregation(String sql);/** * 查询 * * @param sql SQL * @retu {@link List} */@SelectProvider(method = "getSql", type = DBMapperProvider.class)List<Map<String, Object>> query(String sql);}

4.12 创建Elasticsearch通用查询Mapper（SQL 转 DSL 查询）

/** * Elasticsearch 通用 Mapper Provider * * @author CL */@Datapublic class ESMapperProvider {/** * 索引名称 */private String index;/** * DSL */private DslModel dsl;/** * 包含列 */private String[] includes;/** * 排除列 */private String[] excludes;public ESMapperProvider(String index, String dsl) {this(index, dsl, null);}public ESMapperProvider(String index, String dsl, String[] includes) {this.index = index;this.dsl = new DslModel(dsl);this.includes = ArrayUtil.isNotEmpty(includes) ? includes : new String[0];this.excludes = new String[0];}@Overridepublic String toString() {StringJoiner str = new StringJoiner(StrUtil.LF);str.add("index : " + index);str.add("dsl : " + dsl);if (ArrayUtil.isNotEmpty(includes)) {str.add("includes : " + JSONUtil.toJsonStr(includes));}if (ArrayUtil.isNotEmpty(excludes)) {str.add("excludes : " + JSONUtil.toJsonStr(excludes));}retu str.toString();}/** * DSL 结构模型 */@Data@NoArgsConstructorpublic static class DslModel {/** * 查询 */private JSONObject query;/** * 聚合 */private JSONObject aggregations;/** * 排序 */private JSONArray sort;/** * 起始位置 */private Integer from;/** * 大小 */private Integer size;public DslModel(String dsl) {JSONObject parseObj = JSONUtil.parseObj(dsl);this.query = Opt.ofNullable(parseObj).map(obj -> obj.getJSONObject(DSL_QUERY)).get();this.aggregations = Opt.ofNullable(parseObj).map(obj -> obj.getJSONObject(DSL_AGGREGATIONS)).get();this.sort = Opt.ofNullable(parseObj).map(obj -> obj.getJSONArray(DSL_SORT)).get();this.from = Opt.ofNullable(parseObj).map(obj -> obj.getInt(DSL_FROM)).get();this.size = Opt.ofNullable(parseObj).map(obj -> obj.getInt(DSL_SIZE)).get();}@Overridepublic String toString() {retu JSONUtil.toJsonStr(this);}}}

import cn.hutool.core.lang.Opt;import cn.hutool.core.util.ArrayUtil;import cn.hutool.core.util.StrUtil;import cn.hutool.json.JSONArray;import cn.hutool.json.JSONObject;import cn.hutool.json.JSONUtil;import lombok.Data;import lombok.NoArgsConstructor;import java.util.StringJoiner;import static com.c3stones.es.constants.Constant.*;/** * Elasticsearch 通用 Mapper Provider * * @author CL */@Datapublic class ESMapperProvider {/** * 索引名称 */private String index;/** * DSL */private DslModel dsl;/** * 包含列 */private String[] includes;/** * 排除列 */private String[] excludes;public ESMapperProvider(String index, String dsl, String[] includes) {this.index = index;this.dsl = new DslModel(dsl);this.includes = ArrayUtil.isNotEmpty(includes) ? includes : new String[0];this.excludes = new String[0];}@Overridepublic String toString() {StringJoiner str = new StringJoiner(StrUtil.LF);str.add("index : " + index);str.add("dsl : " + dsl);if (ArrayUtil.isNotEmpty(includes)) {str.add("includes : " + JSONUtil.toJsonStr(includes));}if (ArrayUtil.isNotEmpty(excludes)) {str.add("excludes : " + JSONUtil.toJsonStr(excludes));}retu str.toString();}/** * DSL 结构模型 */@Data@NoArgsConstructorpublic static class DslModel {/** * 查询 */private JSONObject query;/** * 聚合 */private JSONObject aggregations;/** * 排序 */private JSONArray sort;/** * 起始位置 */private Integer from;/** * 大小 */private Integer size;public DslModel(String dsl) {JSONObject parseObj = JSONUtil.parseObj(dsl);this.query = Opt.ofNullable(parseObj).map(obj -> obj.getJSONObject(DSL_QUERY)).get();this.aggregations = Opt.ofNullable(parseObj).map(obj -> obj.getJSONObject(DSL_AGGREGATIONS)).get();this.sort = Opt.ofNullable(parseObj).map(obj -> obj.getJSONArray(DSL_SORT)).get();this.from = Opt.ofNullable(parseObj).map(obj -> obj.getInt(DSL_FROM)).get();this.size = Opt.ofNullable(parseObj).map(obj -> obj.getInt(DSL_SIZE)).get();}@Overridepublic String toString() {retu JSONUtil.toJsonStr(this);}}}

4.13 创建启动类

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

5. 单元测试

5.1 提供数据工厂，提供基础数据、查询SQL、聚合SQL

import com.c3stones.entity.User;import java.util.Arrays;import java.util.Date;import java.util.List;/** * 数据工厂 * * @author CL */public class DataFactory {/** * 构造用户信息 * * @retu {@link List} */public static List<User> user() {retu Arrays.asList(new User(1L, "张三", "zhangsan", 20, 1, "西安", new Date()),new User(2L, "李四", "lisi", 25, 0, "北京", new Date()),new User(3L, "王五", "wangwu", 30, 1, "上海", new Date()),new User(4L, "赵六", "zhaoliu", 30, 0, "北京", new Date()));}/** * 构造查询SQL * <p style="color:yellow"> * ps: 函数必须指定别名 * <p/> * * @retu {@link List} */public static List<String> mysqlQuery() {retu Arrays.asList("select * from user","select * from user order by age desc","select id, useame from user limit 0,2","select * from user where age between 25 and 30","select id, age, sex from user where create_time between '2023-01-01' and '2023-01-31'","select * from user where create_time between from_unixtime(1672502400000/1000) and from_unixtime(1675180799999/1000)","select * from user where id < 10 and useame like concat('%' ,'张', '%')","select * from user where id < 10 and useame not like '%李%'","select id, account, address from user where age > 18 and (useame like concat('张', '%') or account = lower('zhangsan') or address in ('北京', '西安'))");}/** * 构造聚合SQL * <p style="color:yellow"> * ps: 函数必须指定别名 * <p/> * * @retu {@link List} */public static List<String> mysqlAggregation() {retu Arrays.asList("select count(*) as count from user","select count(id) as count from user where sex = 0 or sex = -1","select age, count(id) as count from user group by age","select address, sex, count(*) as count from user group by address, sex","select age, count(id) as count from user where age > 25 or useame like concat('%' ,'张', '%')  group by age","select min(age) as min from user","select sex, max(age) as max from user group by sex","select avg(age) as avg from user","select sex, sum(age) as sum from user group by sex");}}

5.2 测试Table新增、查询、统计

import java.util.List;import java.util.Optional;import java.util.stream.Stream;/** * 用户信息 Mapper 单元测试 * * @author CL */@SpringBootTest(classes = Application.class)@TestMethodOrder(MethodOrderer.OrderAnnotation.class)public class UserMapperTest {@Autowiredprivate UserMapper userMapper;/** * 构造用户信息 * * @retu {@link Stream<Arguments>} */private static Stream<Arguments> user() {retu DataFactory.user().stream().map(Arguments::of);}/** * 测试新增 * * @param user 用户信息 */@Order(1)@ParameterizedTest@MethodSource(value = {"user"})public void testSave(User user) {// 自增主键user.setId(null);int result = userMapper.insert(user);Assertions.assertEquals(1, result);}/** * 测试查询 */@Order(2)@Testpublic void testQuery() {List<User> result = userMapper.selectList(Wrappers.emptyWrapper());result.forEach(user -> {Optional<User> optional = DataFactory.user().stream().filter(u ->StrUtil.equals(user.getAccount(), u.getAccount())).findFirst();Assertions.assertTrue(optional.isPresent());Assertions.assertEquals(user, optional.get());});}/** * 测试统计 */@Order(3)@Testpublic void testCount() {Long count = userMapper.selectCount(Wrappers.emptyWrapper());Assertions.assertEquals(DataFactory.user().size(), count.intValue());}}

5.2.1 执行结果截图

5.2.2 数据截图

5.3 测试Index新增、查询、统计

将Table表同步到Index，方便后续测试SQL和DSL查询结果。

import cn.hutool.core.collection.CollUtil;import com.baomidou.mybatisplus.core.toolkit.Wrappers;import com.c3stones.Application;import com.c3stones.db.mapper.UserMapper;import com.c3stones.entity.User;import org.junit.jupiter.api.*;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;import java.util.List;import java.util.stream.Collectors;/** * 用户信息 Repository 单元测试 * * @author CL */@SpringBootTest(classes = Application.class)@TestMethodOrder(MethodOrderer.OrderAnnotation.class)public class UserRepositoryTest {@Autowiredprivate UserMapper userMapper;@Autowiredprivate UserRepository userRepository;/** * 测试新增 */@Order(1)@Testpublic void testSave() {List<User> userList = userMapper.selectList(Wrappers.emptyWrapper());Iterable<User> result = userRepository.saveAll(userList);Assertions.assertEquals(userList.size(), CollUtil.newArrayList(result).size());}/** * 测试查询 */@Order(2)@Testpublic void testQuery() {Iterable<User> result = userRepository.findAll();List<User> userList = userMapper.selectList(Wrappers.emptyWrapper());List<Long> resultIdList = CollUtil.newArrayList(result).stream().map(User::getId).collect(Collectors.toList());List<Long> userIdList = userList.stream().map(User::getId).collect(Collectors.toList());Assertions.assertTrue(CollUtil.containsAll(userIdList, resultIdList));}/** * 测试统计 */@Order(3)@Testpublic void testCount() {long result = userRepository.count();Long count = userMapper.selectCount(Wrappers.emptyWrapper());Assertions.assertEquals(count, result);}}

5.3.1 执行结果截图

5.3.2 访问http://127.0.0.1:9200/user/_search查看数据

5.4 测试SQL转SQL

import cn.hutool.core.util.StrUtil;import com.alibaba.druid.DbType;import com.c3stones.common.DataFactory;import com.c3stones.es.mapper.ESMapperProvider;import lombok.exte.slf4j.Slf4j;import org.junit.jupiter.api.Assertions;import org.junit.jupiter.params.ParameterizedTest;import org.junit.jupiter.params.provider.Arguments;import org.junit.jupiter.params.provider.MethodSource;import java.util.Objects;import java.util.stream.Stream;/** * SQL 转 DSL 单元测试 * * @author CL */@Slf4jpublic class DSLConvertTest {/** * 构造查询SQL * * @retu {@link Stream<Arguments>} */private static Stream<Arguments> mysqlQuery() {retu DataFactory.mysqlQuery().stream().map(Arguments::of);}/** * 构造聚合SQL * * @retu {@link Stream<Arguments>} */private static Stream<Arguments> mysqlAggregation() {retu DataFactory.mysqlAggregation().stream().map(Arguments::of);}/** * 测试 SQL转 DSL * * @param sql SQL */@ParameterizedTest@MethodSource(value = {"mysqlQuery", "mysqlAggregation"})public void testConvert(String sql) {DSLConvert dslConvert = new DSLConvert(new DSLSelectSyntax());ESMapperProvider provider = dslConvert.convert(sql, DbType.mysql);log.debug(StrUtil.LF + "sql : " + sql + StrUtil.LF + provider);Assertions.assertTrue(Objects.nonNull(provider));Assertions.assertTrue(Objects.nonNull(provider.getIndex()));Assertions.assertTrue(Objects.nonNull(provider.getDsl()));}}

5.4.1 执行结果截图

5.4.2 转换结果

sql : select * from userindex : userdsl : {"query":{"match_all":{}}}

sql : select * from user order by age descindex : userdsl : {"query":{"match_all":{}},"sort":[{"age":"DESC"}]}

sql : select id, useame from user limit 0,2index : userdsl : {"query":{"match_all":{}},"from":0,"size":2}includes : ["id","useame"]

sql : select * from user where age between 25 and 30index : userdsl : {"query":{"bool":{"must":[{"range":{"age":{"from":"25","to":"30"}}}]}}}

sql : select id, age, sex from user where create_time between '2023-01-01' and '2023-01-31'index : userdsl : {"query":{"bool":{"must":[{"range":{"create_time":{"from":"2023-01-01","to":"2023-01-31"}}}]}}}includes : ["id","age","sex"]

sql : select * from user where create_time between from_unixtime(1672502400000/1000) and from_unixtime(1675180799999/1000)index : userdsl : {"query":{"bool":{"must":[{"range":{"create_time":{"from":"1672502400000","to":"1675180799999"}}}]}}}

sql : select * from user where id < 10 and useame like concat('%' ,'张', '%')index : userdsl : {"query":{"bool":{"must":[{"range":{"id":{"lt":"10"}}},{"query_string":{"default_field":"useame","query":"*张*"}}]}}}

sql : select * from user where id < 10 and useame not like '%李%'index : userdsl : {"query":{"bool":{"must":[{"range":{"id":{"lt":"10"}}},{"bool":{"must_not":{"query_string":{"default_field":"useame","query":"*李*"}}}}]}}}

sql : select id, account, address from user where age > 18 and (useame like concat('张', '%') or account = lower('zhangsan') or address in ('北京', '西安'))index : userdsl : {"query":{"bool":{"must":[{"range":{"age":{"gt":"18"}}},{"bool":{"should":[{"query_string":{"default_field":"useame","query":"张*"}},{"match_phrase":{"account":"zhangsan"}},{"terms":{"address":["北京","西安"]}}]}}]}}}includes : ["id","account","address"]

sql : select count(*) as count from userindex : userdsl : {"query":{"match_all":{}},"aggregations":{"count":{"value_count":{"field":"_index"}}}}includes : ["count"]

sql : select count(id) as count from user where sex = 0 or sex = -1index : userdsl : {"query":{"bool":{"should":[{"match_phrase":{"sex":"0"}},{"match_phrase":{"sex":"-1"}}]}},"aggregations":{"count":{"value_count":{"field":"id"}}}}includes : ["count"]

sql : select age, count(id) as count from user group by ageindex : userdsl : {"query":{"match_all":{}},"aggregations":{"age":{"terms":{"field":"age"},"aggregations":{"count":{"value_count":{"field":"id"}}}}}}includes : ["age","count"]

sql : select address, sex, count(*) as count from user group by address, sexindex : userdsl : {"query":{"match_all":{}},"aggregations":{"address":{"terms":{"field":"address"},"aggregations":{"sex":{"terms":{"field":"sex"},"aggregations":{"count":{"value_count":{"field":"_index"}}}}}}}}includes : ["address","sex","count"]

sql : select age, count(id) as count from user where age > 25 or useame like concat('%' ,'张', '%')  group by ageindex : userdsl : {"query":{"bool":{"should":[{"range":{"age":{"gt":"25"}}},{"query_string":{"default_field":"useame","query":"*张*"}}]}},"aggregations":{"age":{"terms":{"field":"age"},"aggregations":{"count":{"value_count":{"field":"id"}}}}}}includes : ["age","count"]

sql : select min(age) as min from userindex : userdsl : {"query":{"match_all":{}},"aggregations":{"min":{"min":{"field":"age"}}}}includes : ["min"]

sql : select sex, max(age) as max from user group by sexindex : userdsl : {"query":{"match_all":{}},"aggregations":{"sex":{"terms":{"field":"sex"},"aggregations":{"max":{"max":{"field":"age"}}}}}}includes : ["sex","max"]

sql : select avg(age) as avg from userindex : userdsl : {"query":{"match_all":{}},"aggregations":{"avg":{"avg":{"field":"age"}}}}includes : ["avg"]

sql : select sex, sum(age) as sum from user group by sexindex : userdsl : {"query":{"match_all":{}},"aggregations":{"sex":{"terms":{"field":"sex"},"aggregations":{"sum":{"sum":{"field":"age"}}}}}}includes : ["sex","sum"]

5.5 测试SQL 转 DSL 的查询结果

import cn.hutool.core.date.DateField;import cn.hutool.core.date.DateUtil;import cn.hutool.core.lang.Opt;import cn.hutool.core.map.MapUtil;import cn.hutool.core.util.NumberUtil;import cn.hutool.core.util.StrUtil;import cn.hutool.json.JSONArray;import com.alibaba.druid.DbType;import com.c3stones.common.DataFactory;import com.c3stones.db.mapper.DBMapper;import com.c3stones.es.convert.DSLConvert;import com.c3stones.es.convert.DSLSelectSyntax;import com.c3stones.es.mapper.ESMapper;import com.c3stones.es.mapper.ESMapperProvider;import org.junit.jupiter.api.Assertions;import org.junit.jupiter.params.ParameterizedTest;import org.junit.jupiter.params.provider.Arguments;import org.junit.jupiter.params.provider.MethodSource;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.boot.test.context.SpringBootTest;import java.io.IOException;import java.util.Date;import java.util.List;import java.util.Map;import java.util.Objects;import java.util.stream.Stream;/** * SQL 转 DSL 单元测试 * * @author CL */@SpringBootTest(classes = Application.class)public class SQL2DSLTest {@Autowiredprivate DBMapper dbMapper;@Autowiredprivate ESMapper esMapper;/** * 构造查询SQL * * @retu {@link Stream < Arguments >} */private static Stream<Arguments> mysqlQuery() {retu DataFactory.mysqlQuery().stream().map(Arguments::of);}/** * 构造聚合SQL * * @retu {@link Stream<Arguments>} */private static Stream<Arguments> mysqlAggregation() {retu DataFactory.mysqlAggregation().stream().map(Arguments::of);}/** * 测试查询 * * @param sql SQL */@ParameterizedTest@MethodSource(value = {"mysqlQuery"})public void testQuery(String sql) throws IOException {List<Map<String, Object>> dbResult = dbMapper.query(sql);DSLConvert dslConvert = new DSLConvert(new DSLSelectSyntax());ESMapperProvider provider = dslConvert.convert(sql, DbType.mysql);List<Map<String, Object>> esResult = esMapper.query(provider);Assertions.assertEquals(dbResult.size(), esResult.size());for (int i = 0; i < dbResult.size(); i++) {Map<String, Object> dbMap = dbResult.get(i);Map<String, Object> esMap;if (!Opt.ofNullable(provider).map(ESMapperProvider::getDsl).map(ESMapperProvider.DslModel::getSort).map(JSONArray::isEmpty).orElse(true)) {esMap = esResult.get(i);} else {esMap = esResult.stream().filter(map -> Objects.equals(dbMap.get("id"), map.get("id"))).findFirst().orElse(MapUtil.empty());}for (Map.Entry<String, Object> entry : dbMap.entrySet()) {Object expected = entry.getValue();Object actual = esMap.get(entry.getKey());if (expected instanceof Date || actual instanceof Date) {expected = DateUtil.parse(expected.toString()).second();actual = DateUtil.parse(actual.toString()).offset(DateField.HOUR, 8).second();}Assertions.assertEquals(expected, actual);}}}/** * 测试聚合 * * @param sql SQL */@ParameterizedTest@MethodSource(value = {"mysqlAggregation"})public void testAggregation(String sql) throws IOException {List<Map<String, Object>> dbResult = dbMapper.aggregation(sql);DSLConvert dslConvert = new DSLConvert(new DSLSelectSyntax());ESMapperProvider provider = dslConvert.convert(sql, DbType.mysql);List<Map<String, Object>> esResult = esMapper.aggregation(provider);Assertions.assertEquals(dbResult.size(), esResult.size());for (Map<String, Object> dbMap : dbResult) {boolean match = esResult.stream().anyMatch(result -> dbMap.entrySet().stream().allMatch(dbEntry -> {String v1 = StrUtil.toStringOrNull(dbEntry.getValue());String v2 = StrUtil.toStringOrNull(result.get(dbEntry.getKey()));if (NumberUtil.isNumber(v1) && NumberUtil.isNumber(v2)) {v1 = String.format("%.5f", NumberUtil.parseDouble(v1));v2 = String.format("%.5f", NumberUtil.parseDouble(v2));}retu StrUtil.equals(v1, v2);}));Assertions.assertTrue(match);}}}