DB SQL 转 ES DSL（支持多种数据库常用查询、统计、平均值、最大值、最小值、求和语法） |
您所在的位置：网站首页 › sql转es查询工具 › 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支持的所有数据库） 支持的语法 查询全部、查询指定列、条件查询（=、!=、>、=、 (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 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} */ private List handleSelect(List 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 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 selectFieldList, SQLSelectGroupByClause sqlSelectGroupByClause) { if (selectFieldList.stream().allMatch(field -> Objects.isNull(field.getMethodName()))) return null; Queue 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 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 handleAggregate(List selectFields, Queue groupByList) { if (groupByList.isEmpty()) return handleAggregate(selectFields); String groupBy = groupByList.poll(); HashMap 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 handleAggregate(List selectFieldList) { if (CollUtil.isEmpty(selectFieldList)) return null; Map 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 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 aggregation(String sql); /** * 查询 * * @param sql SQL * @return {@link List} */ @SelectProvider(method = "getSql", type = DBMapperProvider.class) List 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() { 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 ** ps: 函数必须指定别名 * * * @return {@link List} */ public static List 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 ** ps: 函数必须指定别名 * * * @return {@link List} */ public static List 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} */ private static Stream 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 result = userMapper.selectList(Wrappers.emptyWrapper()); result.forEach(user -> { Optional 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 执行结果截图
将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 userList = userMapper.selectList(Wrappers.emptyWrapper()); Iterable result = userRepository.saveAll(userList); Assertions.assertEquals(userList.size(), CollUtil.newArrayList(result).size()); } /** * 测试查询 */ @Order(2) @Test public void testQuery() { Iterable result = userRepository.findAll(); List userList = userMapper.selectList(Wrappers.emptyWrapper()); List resultIdList = CollUtil.newArrayList(result).stream().map(User::getId).collect(Collectors.toList()); List 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 执行结果截图
sql2dsl-demo |
【本文地址】