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',
    `username` 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.Driver
    url: jdbc:mysql://127.0.0.1:3306/sql2dsl?useSSL=false&useUnicode=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
    username: root
    password: 123456
  elasticsearch:
    uris: http://127.0.0.1:9200 #多个用,分开
    # username: elastic
    # password: elastic
    connectionTimeout: 5s
    socketTimeout: 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)
    @TableField
    private String username;

    /**
     * 账号
     */
    @Field(type = FieldType.Keyword)
    @TableField
    private String account;

    /**
     * 年龄
     */
    @Field(type = FieldType.Integer)
    @TableField
    private Integer age;

    /**
     * 性别 0-女 1-男
     */
    @Field(type = FieldType.Integer)
    @TableField
    private Integer sex;

    /**
     * 地址
     */
    @Field(type = FieldType.Keyword)
    @TableField
    private String address;

    /**
     * 创建时间
     */
    @Field(name = "create_time", type = FieldType.Date)
    @TableField
    private Date createTime;

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        User user = (User) o;
        return Objects.equals(username, user.username)
                && Objects.equals(account, user.account)
                && Objects.equals(age, user.age)
                && Objects.equals(sex, user.sex)
                && Objects.equals(address, user.address);
    }

    @Override
    public int hashCode() {
        return Objects.hash(username, 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
 */
@Mapper
public interface UserMapper extends BaseMapper<User> {
}

4.6 创建Repository

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

/**
 * 用户信息 Repository
 *
 * @author CL
 */
@Repository
public 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 值
     * @return {@link String}
     */
    public String format(List<String> values) {
        StringJoiner format = new StringJoiner("\",\"", "\"", "\"");
        values.forEach(format::add);
        return format.toString();
    }

    /**
     * 等于
     *
     * @param fieldName 属性名称
     * @param value     值
     * @return {@link String}
     */
    public String eq(String fieldName, String value) {
        return String.format("{\"match_phrase\" : {\"%s\" : \"%s\"}}", fieldName, value);
    }

    /**
     * 不等
     *
     * @param fieldName 属性名称
     * @param value     值
     * @return {@link String}
     */
    public String neq(String fieldName, String value) {
        return String.format("{\"bool\" : {\"must_not\" : [{\"match_phrase\" : {\"%s\" : {\"query\" : \"%s\"}}}]}}", fieldName, value);
    }

    /**
     * 大于
     *
     * @param fieldName 属性名称
     * @param value     值
     * @return {@link String}
     */
    public String gt(String fieldName, String value) {
        return String.format("{\"range\" : {\"%s\" : {\"gt\" : \"%s\"}}}", fieldName, value);
    }

    /**
     * 大于等于
     *
     * @param fieldName 属性名称
     * @param value     值
     * @return {@link String}
     */
    public String gte(String fieldName, String value) {
        return String.format("{\"range\" : {\"%s\" : {\"from\" : \"%s\"}}}", fieldName, value);
    }

    /**
     * 小于
     *
     * @param fieldName 属性名称
     * @param value     值
     * @return {@link String}
     */
    public String lt(String fieldName, String value) {
        return String.format("{\"range\" : {\"%s\" : {\"lt\" : \"%s\"}}}", fieldName, value);
    }

    /**
     * 小于等于
     *
     * @param fieldName 属性名称
     * @param value     值
     * @return {@link String}
     */
    public String lte(String fieldName, String value) {
        return String.format("{\"range\" : {\"%s\" : {\"to\" : \"%s\"}}}", fieldName, value);
    }

    /**
     * 属于
     *
     * @param fieldName 属性名称
     * @param values    值
     * @return {@link String}
     */
    public String in(String fieldName, List<String> values) {
        return String.format("{\"terms\" : {\"%s\" : [%s]}}", fieldName, format(values));
    }

    /**
     * 不属于
     *
     * @param fieldName 属性名称
     * @param values    值
     * @return {@link String}
     */
    public String notIn(String fieldName, List<String> values) {
        return String.format("{\"bool\" : {\"must_not\" : {\"terms\" : {\"%s\" : [%s]}}}}", fieldName, format(values));
    }

    /**
     * 包含
     *
     * @param fieldName 属性名称
     * @param value     值
     * @return {@link String}
     */
    public String contain(String fieldName, String value) {
        return String.format("{\"query_string\":{\"default_field\": \"%s\",\"query\":\"%s\"}}", fieldName, value.replaceAll("%", "*"));
    }

    /**
     * 不包含
     *
     * @param fieldName 属性名称
     * @param value     值
     * @return {@link String}
     */
    public String notContain(String fieldName, String value) {
        return String.format("{\"bool\":{\"must_not\":{\"query_string\":{\"default_field\":\"%s\",\"query\":\"%s\"}}}}", fieldName, value.replaceAll("%", "*")
        );
    }

    /**
     * 为空
     *
     * @param fieldName 属性名称
     * @return {@link String}
     */
    public String empty(String fieldName) {
        return String.format("{\"bool\": { \"must_not\": { \"exists\": { \"field\": \"%s\" }}}}", fieldName);
    }

    /**
     * 不为空
     *
     * @param fieldName 属性名称
     * @return {@link String}
     */
    public String notEmpty(String fieldName) {
        return String.format("{\"bool\": { \"must\": { \"exists\": { \"field\": \"%s\" }}}}", fieldName);
    }

    /**
     * 在两者之间
     *
     * @param fieldName  属性名称
     * @param startValue 起始值
     * @param endValue   终止值
     * @return {@link String}
     */
    public String range(String fieldName, String startValue, String endValue) {
        return String.format("{\"range\" : {\"%s\" : {\"from\" : \"%s\", \"to\" : \"%s\"}}}", fieldName, startValue, endValue);
    }

    /**
     * 必须包含
     *
     * @param dsl DSL
     * @return {@link String}
     */
    public String must(String dsl) {
        return String.format("{\"bool\" : {\"must\" : [%s]}}", dsl);
    }

    /**
     * 可能包含
     *
     * @param dsl DSL
     * @return {@link String}
     */
    public String should(String dsl) {
        return String.format("{\"bool\" : {\"should\" : [%s]}}", dsl);
    }

    /**
     * 查询全部
     *
     * @return {@link String}
     */
    public String all() {
        return "{\"match_all\": {}}";
    }

    /**
     * DSL
     *
     * @param where   Where条件
     * @param groupBy 分组字段
     * @param orderBy 排序字段
     * @param from    偏移数
     * @param size    限制数
     * @return {@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("}");
        return 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.extern.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
@RequiredArgsConstructor
public class DSLConvert {

    private final DSLSelectSyntax dslSelectSyntax;

    /**
     * 转换
     *
     * @param sql    SQL
     * @param dbType 数据库类型
     * @return {@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());
        return optional.isPresent() ? handle(optional.get()) : null;
    }

    /**
     * 处理SQL
     *
     * @param sqlSelectQuery SQL Select查询
     * @return {@link ESMapperProvider}
     */
    private ESMapperProvider handle(SQLSelectQueryBlock sqlSelectQuery) {
        // 处理 Select
        List<SelectField> selectFieldList = handleSelect(sqlSelectQuery.getSelectList());
        // 处理 From
        String index = handleFrom(sqlSelectQuery.getFrom());
        // 处理 Where
        String where = handleWhere(sqlSelectQuery.getWhere(), true);
        // 处理 GroupBy
        String groupBy = handleGroupBy(selectFieldList, sqlSelectQuery.getGroupBy());
        // 处理 OrderBy
        String orderBy = handleOrderBy(sqlSelectQuery.getOrderBy());
        // 处理 Limit
        Page page = handleLimit(sqlSelectQuery.getLimit());
        // 生成DSL
        Integer 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);
        return new ESMapperProvider(index, dsl, 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 = "*";
                        } 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();
                    }
                    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表达式
     * @param isComplete 是否完整条件
     * @return {@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)
                return handleWhereAndOrOr(sqlBinaryOpExpr, operator);
            return handleWhereBinaryOp(sqlBinaryOpExpr, isComplete);
        } else if (sqlExpr instanceof SQLInListExpr) {
            return handleWhereInOrNotIn((SQLInListExpr) sqlExpr, isComplete);
        } else if (sqlExpr instanceof SQLBetweenExpr) {
            return handleWhereBetween((SQLBetweenExpr) sqlExpr, isComplete);
        }
        return dslSelectSyntax.all();
    }

    /**
     * 处理 AND 或 OR
     *
     * @param sqlBinaryOpExpr SQL两位元素操作
     * @return {@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) return dsl.toString();
        }
        return sqlBinaryOperator == BooleanAnd ? dslSelectSyntax.must(dsl.toString()) : dslSelectSyntax.should(dsl.toString());
    }

    /**
     * 处理二位元素操作
     *
     * @param sqlExpr    SQL表达式
     * @param isComplete 是否完整条件
     * @return {@link String}
     */
    private String handleWhereBinaryOp(SQLBinaryOpExpr sqlExpr, boolean isComplete) {
        StringBuilder dsl = new StringBuilder();
        SQLExpr leftExpr = sqlExpr.getLeft();
        SQLExpr rightExpr = sqlExpr.getRight();
        // 特殊处理 1 = 1 / 1 != 1
        if (leftExpr instanceof SQLIntegerExpr && rightExpr instanceof SQLIntegerExpr) {
            if (Objects.equals(getValue(leftExpr), getValue(rightExpr))) {
                if (sqlExpr.getOperator() == SQLBinaryOperator.Equality) {
                    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
            }
        }
        return isComplete ? dslSelectSyntax.must(dsl.toString()) : dsl.toString();
    }

    /**
     * 处理 in 或 notIn
     *
     * @param sqlInListExpr SQL In 表达式
     * @param isComplete    是否完整条件
     * @return {@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);
        return isComplete ? dslSelectSyntax.must(dsl) : dsl;
    }

    /**
     * 处理 between
     *
     * @param sqlBetweenExpr SQL Between 表达式
     * @param isComplete     是否完整条件
     * @return {@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);
        return isComplete ? dslSelectSyntax.must(dsl) : dsl;
    }

    /**
     * 处理 GroupBy
     *
     * @param selectFieldList        查询字段
     * @param sqlSelectGroupByClause SQL GroupBy 从句
     * @return {@link String}
     */
    private String handleGroupBy(List<SelectField> selectFieldList, SQLSelectGroupByClause sqlSelectGroupByClause) {
        if (selectFieldList.stream().allMatch(field -> Objects.isNull(field.getMethodName()))) return null;
        Queue<String> groupByList = CollUtil.newLinkedList();
        if (Objects.nonNull(sqlSelectGroupByClause)) {
            for (SQLExpr sqlExpr : sqlSelectGroupByClause.getItems()) {
                if (sqlExpr instanceof SQLIdentifierExpr) {
                    groupByList.add(((SQLIdentifierExpr) sqlExpr).getName());
                }
            }
        }
        return JSONUtil.toJsonStr(handleAggregate(selectFieldList, groupByList));
    }

    /**
     * 处理 OrderBy
     *
     * @param sqlOrderBy SQL OrderBy
     * @return {@link String}
     */
    private String handleOrderBy(SQLOrderBy sqlOrderBy) {
        if (Objects.isNull(sqlOrderBy)) return 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()));
        }
        return CollUtil.isNotEmpty(orderByList) ? JSONUtil.toJsonStr(orderByList) : null;
    }

    /**
     * 处理 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 selectFields 查询字段
     * @param groupByList  分组字段
     * @return {@link Map}
     */
    private Map<String, Object> handleAggregate(List<SelectField> selectFields, Queue<String> groupByList) {
        if (groupByList.isEmpty()) return 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));
        return MapUtil.of(groupBy, fieldMap);
    }

    /**
     * 处理聚合函数
     * {分组字段 : 配置}
     *
     * @param selectFieldList 查询字段
     * @return {@link Map}
     */
    private Map<String, Object> handleAggregate(List<SelectField> selectFieldList) {
        if (CollUtil.isEmpty(selectFieldList)) return 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)));
        }
        return result;
    }

    /**
     * 获取值
     *
     * @param sqlExpr 表达式
     * @return {@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")) {
                return getValue(CollUtil.getFirst(arguments));
            } else if (StrUtil.equalsAnyIgnoreCase(methodName, "to_timestamp", "from_unixtime")) {
                String tmp = getValue(CollUtil.getFirst(arguments));
                return CollUtil.getFirst(StrUtil.split(tmp, 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());
        } else if (sqlExpr instanceof SQLNullExpr) {
            value = null;
        }
        return value;
    }

    /**
     * 字符串转义
     *
     * @param str 字符串
     * @return {@link String}
     */
    private String escape(String str) {
        if (StringUtils.isBlank(str)) return str;
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < str.length(); i++) {
            char c = str.charAt(i);
            if (Character.isWhitespace(c) ||
                    c == '\\' ||
                    c == '\"' ||
                    c == '+' ||
                    c == '-' ||
                    c == '!' ||
                    c == '(' ||
                    c == ')' ||
                    c == '[' ||
                    c == ']' ||
                    c == '{' ||
                    c == '}' ||
                    c == ':' ||
                    c == '^' ||
                    c == '~' ||
                    c == '*' ||
                    c == '?' ||
                    c == '|' ||
                    c == '&' ||
                    c == ';' ||
                    c == '/' ||
                    c == '.' ||
                    c == '$') {
                sb.append('\\').append('\\');
            }
            sb.append(c);
        }
        return sb.toString();
    }

    /**
     * 查询字段
     */
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    private static class SelectField {

        /**
         * 字段名
         */
        private String name;

        /**
         * 别名
         */
        private String alias;

        /**
         * 方法名
         */
        private String methodName;

    }

    /**
     * 翻页
     */
    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    @Accessors(chain = true)
    private static class Page {

        /**
         * 开始位置
         */
        private Integer from;

        /**
         * 页大小
         */
        private Integer size;

    }

}

4.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.UsernamePasswordCredentials;
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
 */
@Configuration
public class ElasticsearchConfig extends AbstractElasticsearchConfiguration {

    @Autowired
    private ElasticsearchProperties elasticsearchProperties;

    /**
     * 重写 RestHighLevelClient
     *
     * @return {@link RestHighLevelClient}
     */
    @Override
    public RestHighLevelClient elasticsearchClient() {
        HttpHost[] httpHosts = elasticsearchProperties.getUris().stream().map(uri -> {
            URL url = URLUtil.url(uri);
            return 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 username = elasticsearchProperties.getUsername();
        String password = elasticsearchProperties.getPassword();
        return new RestHighLevelClient(RestClient
                .builder(httpHosts)
                .setRequestConfigCallback(
                        requestConfigBuilder -> requestConfigBuilder
                                .setConnectTimeout(connectTimeout)
                                .setSocketTimeout(socketTimeout)
                                .setConnectionRequestTimeout(connectTimeout))
                .setHttpClientConfigCallback(
                        httpClientBuilder -> {
                            if (StrUtil.isNotEmpty(username) && StrUtil.isNotEmpty(password)) {
                                CredentialsProvider credentialsProvider = new BasicCredentialsProvider();
                                credentialsProvider.setCredentials(AuthScope.ANY, new UsernamePasswordCredentials(username, password));
                                httpClientBuilder.setDefaultCredentialsProvider(credentialsProvider);
                            }
                            return 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
     * @return {@link String}
     */
    public String getSql(String sql) {
        return sql;
    }

}

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.SelectProvider;

import java.util.List;
import java.util.Map;

/**
 * 数据库 通用 Mapper
 *
 * @author CL
 */
@Mapper
public interface DBMapper {

    /**
     * 集合
     *
     * @param sql SQL
     * @return {@link List}
     */
    @SelectProvider(method = "getSql", type = DBMapperProvider.class)
    List<Map<String, Object>> aggregation(String sql);

    /**
     * 查询
     *
     * @param sql SQL
     * @return {@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
 */
@Data
public 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];
    }

    @Override
    public 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));
        }
        return str.toString();
    }

    /**
     * DSL 结构模型
     */
    @Data
    @NoArgsConstructor
    public 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();
        }

        @Override
        public String toString() {
            return 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
 */
@Data
public 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];
    }

    @Override
    public 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));
        }
        return str.toString();
    }

    /**
     * DSL 结构模型
     */
    @Data
    @NoArgsConstructor
    public 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();
        }

        @Override
        public String toString() {
            return JSONUtil.toJsonStr(this);
        }

    }

}

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. 单元测试

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 {

    /**
     * 构造用户信息
     *
     * @return {@link List}
     */
    public static List<User> user() {
        return 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/>
     *
     * @return {@link List}
     */
    public static List<String> mysqlQuery() {
        return Arrays.asList(
                "select * from user",
                "select * from user order by age desc",
                "select id, username 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 username like concat('%' ,'张', '%')",
                "select * from user where id < 10 and username not like '%李%'",
                "select id, account, address from user where age > 18 and (username like concat('张', '%') or account = lower('zhangsan') or address in ('北京', '西安'))"
        );
    }

    /**
     * 构造聚合SQL
     * <p style="color:yellow">
     * ps: 函数必须指定别名
     * <p/>
     *
     * @return {@link List}
     */
    public static List<String> mysqlAggregation() {
        return 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 username 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 {

    @Autowired
    private UserMapper userMapper;

    /**
     * 构造用户信息
     *
     * @return {@link Stream<Arguments>}
     */
    private static Stream<Arguments> user() {
        return 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)
    @Test
    public 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)
    @Test
    public 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 {

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private UserRepository userRepository;

    /**
     * 测试新增
     */
    @Order(1)
    @Test
    public 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)
    @Test
    public 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)
    @Test
    public 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.extern.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
 */
@Slf4j
public class DSLConvertTest {

    /**
     * 构造查询SQL
     *
     * @return {@link Stream<Arguments>}
     */
    private static Stream<Arguments> mysqlQuery() {
        return DataFactory.mysqlQuery().stream().map(Arguments::of);
    }

    /**
     * 构造聚合SQL
     *
     * @return {@link Stream<Arguments>}
     */
    private static Stream<Arguments> mysqlAggregation() {
        return 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 user
index : user
dsl : {"query":{"match_all":{}}}

sql : select * from user order by age desc
index : user
dsl : {"query":{"match_all":{}},"sort":[{"age":"DESC"}]}

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

sql : select * from user where age between 25 and 30
index : user
dsl : {"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 : user
dsl : {"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 : user
dsl : {"query":{"bool":{"must":[{"range":{"create_time":{"from":"1672502400000","to":"1675180799999"}}}]}}}

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

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

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

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

sql : select count(id) as count from user where sex = 0 or sex = -1
index : user
dsl : {"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 age
index : user
dsl : {"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, sex
index : user
dsl : {"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 username like concat('%' ,'张', '%')  group by age
index : user
dsl : {"query":{"bool":{"should":[{"range":{"age":{"gt":"25"}}},{"query_string":{"default_field":"username","query":"*张*"}}]}},"aggregations":{"age":{"terms":{"field":"age"},"aggregations":{"count":{"value_count":{"field":"id"}}}}}}
includes : ["age","count"]

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

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

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

sql : select sex, sum(age) as sum from user group by sex
index : user
dsl : {"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 {

    @Autowired
    private DBMapper dbMapper;

    @Autowired
    private ESMapper esMapper;

    /**
     * 构造查询SQL
     *
     * @return {@link Stream < Arguments >}
     */
    private static Stream<Arguments> mysqlQuery() {
        return DataFactory.mysqlQuery().stream().map(Arguments::of);
    }

    /**
     * 构造聚合SQL
     *
     * @return {@link Stream<Arguments>}
     */
    private static Stream<Arguments> mysqlAggregation() {
        return 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));
                        }
                        return StrUtil.equals(v1, v2);
                    }));
            Assertions.assertTrue(match);
        }
    }

}

5.5.1 执行结果截图

6 项目地址

sql2dsl-demo