查詢引擎: 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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/236112.html

標籤：其他

上一篇：sql末考必過！

下一篇：資料庫欄位內容丟失