查询引擎: SQL反解析(json2sql)(附源码)

目录

1.前言

2.主要设计

1.请求体

2.构建Table

3.使用

4.自定义过滤条件

5.自定义聚合指标

1.前言

1.业务人员不懂SQL，通过UI自助查询关系型数据库；支持单表过滤、分组、排序、聚合指标、limit；

2.过滤条件包含：between、like、=、!=、>、>=、<、<=等

3.聚合指标包含：count、max、min、sum等

2.主要设计

1.请求体

1.QueryRequest

  

   

    

     

    
    

     
    
      private List<RequestElementMeasure> measures;
     
    
   

    

     

    
    

     
    
      private String table;
     
    
   

    

     

    
    

     
    
      private RequestElementFilter filter;
     
    
   

    

     

    
    

     
    
      private List<String> groupByList;
     
    
   

    

     

    
    

     
    
      private List<Integer> orderByList;
     
    
   

    

     

    
    

     
    
      private Long limit;
     
    
  

2.RequestElementMeasure

  

   

    

     

    
    

     
    
      private String field;
     
    
   

    

     

    
    

     
    
      private String aggregator;
     
    
   

    

     

    
    

     
    
      private List<Object> params;
     
    
  

3.RequestElementFilter

  

   

    

     

    
    

     

      private String relation;
     
    
   

    

     

    
    

     

      private List<RequestElementCondition> conditions;
     
    
   

    

     

    
    

     

      private List<RequestElementFilter> filters;
     
    
  

2.构建Table

  

   

    

     

    
    

     
    
      protected String tableName;
     
    
   

    

     

    
    

     
    
      protected List<AbstractColumn> columns = 
      new ArrayList<>();
     
    
   

    

     

    
    

     
    
      private AbstractFilter filter;
     
    
   

    

     

    
    

     
    
      private List<String> groupByList;
     
    
   

    

     

    
    

     
    
      private List<Integer> orderByList;
     
    
   

    

     

    
    

     
    
      protected Limit limit;
     
    
  

3.使用

  

   

    

     

    
    

     

      String requestJson = 
      "{\"limit\":10,\"orderByList\":[1],\"groupByList\":[\"name\"],\"filter\":{\"and\":true,\"conditions\":[{\"field\":\"age\",\"function\":\"equal\",\"params\":[20]}]},\"measures\":[{\"aggregator\":\"count\",\"field\":\"age\"}],\"table\":\"person\"}";
     
    
   

    

     

    
    

     

              QueryRequest request = JSONObject.parseObject(requestJson, QueryRequest.class);
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

              AliasGenerator aliasGenerator = 
      new AliasGenerator();
     
    
   

    

     

    
    

     

              Table table = 
      new Table(request.getTable());
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     
        
      for (String groupBy : request.getGroupByList()) {
     
    
   

    

     

    
    

     

                  table.addSelect(
      new AtomColumn(groupBy, aliasGenerator.nextAlias()));
     
    
   

    

     

    
    

     

              }
     
    
   

    

     

    
    

     

              request.getMeasures().forEach(measure -> {
     
    
   

    

     

    
    

     

                  ExpressionColumn expressionColumn = TableUtil.constructAggregation(measure, aliasGenerator.nextAlias());
     
    
   

    

     

    
    

     

                  table.addSelect(expressionColumn);
     
    
   

    

     

    
    

     

              });
     
    
   

    

     

    
    

     
 
     
    
   

    

     

    
    

     

              RequestElementFilter filter = request.getFilter();
     
    
   

    

     

    
    

     

              CompoundFilter compoundFilter = 
      new CompoundFilter();
     
    
   

    

     

    
    

     

              compoundFilter.setIsAnd(filter.isAnd());
     
    
   

    

     

    
    

     
        
      for (RequestElementCondition condition : filter.getConditions()) {
     
    
   

    

     

    
    

     

                  AbstractColumn column = 
      new AtomColumn(condition.getField(), aliasGenerator.nextAlias());
     
    
   

    

     

    
    

     

                  compoundFilter.addSubFilter(FilterUtil.getFunctionCondition(condition.getField(), condition.getFunction(), condition.getParams())
     
    
   

    

     

    
    

     

                          .createFilter(column));
     
    
   

    

     

    
    

     

              }
     
    
   

    

     

    
    

     

              table.setFilter(compoundFilter);
     
    
   

    

     

    
    

     

              table.setGroupByList(request.getGroupByList());
     
    
   

    

     

    
    

     

              table.setOrderByList(request.getOrderByList());
     
    
   

    

     

    
    

     

              table.setLimit(
      new Limit(request.getLimit()));
     
    
   

    

     

    
    

     

              System.out.println(
      ">>>" + table.constructSql());
     
    
  

4.自定义过滤条件

  

   

    

     

    
    

     

      1.定义FunctionCondition(参考GreaterCondition)
     
    
   

    

     

    
    

     

      2.定义SimpleFilter(参考Greater)
     
    
  

5.自定义聚合指标

  

   

    

     

    
    

     

      1.定义ExecutableExpression(参考Max)
     
    
   

    

     

    
    

     

      2.添加AggregatorType
     
    
   

    

     

    
    

     

      3.添加TableUtil
     
    
  

6.源码地址

https://github.com/ostarsier/queryengine