Druid SQL

# 一、AST

AST是abstract syntax tree的缩写，也就是抽象语法树。和所有的Parser一样，Druid Parser会生成一个抽象语法树。

在Druid中，AST节点类型主要包括SQLObject、SQLExpr、SQLStatement三种抽象类型。

# 二、语法树解析

# 2.1 核心类介绍

# 2.1.1 SQLStatemment DQL & DML顶级抽象

• DQL 数据查询语言 select
• DML 数据操纵语言 insert update delete

最常用的Statement当然是SELECT/UPDATE/DELETE/INSERT，他们分别是

# 2.1.2 SQLSelect SQL查询

SQLSelectStatement包含一个SQLSelect，SQLSelect包含一个SQLSelectQuery。SQLSelectQuery有主要的两个派生类， 分别是SQLSelectQueryBlock(单表sql查询)和SQLUnionQuery(union查询 (opens new window))。

1.  
   /**
2.  
   * SQLSelectStatement包含一个SQLSelect，SQLSelect包含一个SQLSelectQuery。SQLSelectQuery有主要的两个派生类，
3.  
   * 分别是SQLSelectQueryBlock(单表sql查询)和SQLUnionQuery(联合查询)。
4.  
   */
5.  
   @Test
6.  
   public void SQLSelectQuery() {
7.  
   // true
8.  
   System.out.println(parseSQLSelectQuery("select * from users") instanceof SQLSelectQueryBlock);
9.  
   // true
10.  
    System.out.println(parseSQLSelectQuery("select name from users union select name from school") instanceof SQLUnionQuery);
11.  
    }
12.  
     
13.  
    public SQLSelectQuery parseSQLSelectQuery(String sql) {
14.  
    SQLStatement sqlStatement = SQLUtils.parseSingleMysqlStatement(sql);
15.  
    SQLSelectStatement sqlSelectStatement = Utils.cast(sqlStatement, SQLSelectStatement.class);
16.  
    SQLSelect select = sqlSelectStatement.getSelect();
17.  
    return select.getQuery();
18.  
    }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

# 2.1.3 SQLExpr

SQLExpr 是有几个实现类的。

# 2.1.3.1 SQLBinaryOpExpr

1.  
   /**
2.  
   * 操作符相关: SQLBinaryOpExpr
3.  
   */
4.  
   @Test
5.  
   public void SQLBinaryOpExpr() {
6.  
   String sql = "select * from users where id > 1 and age = 18";
7.  
   SQLSelectQuery sqlSelectQuery = Utils.parseSQLSelectQuery(sql);
8.  
   SQLSelectQueryBlock selectQueryBlock = Utils.cast(sqlSelectQuery, SQLSelectQueryBlock.class);
9.  
   SQLExpr where = selectQueryBlock.getWhere();
10.  
    List<SQLObject> conditions = where.getChildren();
11.  
    // [id > 1 , age = 18] 出现了操作符所以是SQLBinaryOpExpr
12.  
    for (SQLObject condition : conditions) {
13.  
    SQLBinaryOpExpr conditionExpr = Utils.cast(condition, SQLBinaryOpExpr.class);
14.  
    SQLBinaryOperator operator = conditionExpr.getOperator();
15.  
    SQLIdentifierExpr conditionColumn = Utils.cast(conditionExpr.getLeft(), SQLIdentifierExpr.class);
16.  
    SQLValuableExpr conditionColumnValue = Utils.cast(conditionExpr.getRight(), SQLValuableExpr.class);
17.  
    Utils.print("条件字段:{},操作符号:{},条件值:{}", conditionColumn.getName(), operator.name, conditionColumnValue);
18.  
    }
19.  
    }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

# 2.1.3.2 SQLVariantRefExpr

1.  
   @Test
2.  
   public void SQLVariantRefExpr() {
3.  
   String sql = "select * from users where id = ? and name = ?";
4.  
   SQLSelectQuery sqlSelectQuery = Utils.parseSQLSelectQuery(sql);
5.  
   SQLSelectQueryBlock selectQueryBlock = Utils.cast(sqlSelectQuery, SQLSelectQueryBlock.class);
6.  
   SQLExpr where = selectQueryBlock.getWhere();
7.  
   List<SQLObject> conditions = where.getChildren();
8.  
   // [id = ?] 出现了变量符,所以要用SQLVariantRefExpr
9.  
   for (SQLObject condition : conditions) {
10.  
    SQLBinaryOpExpr conditionExpr = Utils.cast(condition, SQLBinaryOpExpr.class);
11.  
    SQLBinaryOperator operator = conditionExpr.getOperator();
12.  
    SQLIdentifierExpr conditionColumn = Utils.cast(conditionExpr.getLeft(), SQLIdentifierExpr.class);
13.  
    SQLVariantRefExpr conditionColumnValue = Utils.cast(conditionExpr.getRight(), SQLVariantRefExpr.class);
14.  
    int index = conditionColumnValue.getIndex();
15.  
    Utils.print("条件字段:{},操作符号:{},索引位:{}", conditionColumn.getName(), operator.name, index);
16.  
    }
17.  
    }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

# 2.1.4 SQLTableSource

常见的SQLTableSource包括SQLExprTableSource、SQLJoinTableSource、SQLSubqueryTableSource、SQLWithSubqueryClause.Entry

# 2.2 SQL语句解析示例

# 2.2.1 解析 Where

注意如果条件语句中只有一个条件,那么where就是一个 SQLBinaryOpExpr。 当条件大于2个,使用 where.getChildren()

1.  
   /**
2.  
   * 判断where要
3.  
   * 1. 注意是SQLBinaryOpExpr(id = 1) or (u.id = 1) 需要注意是否使用了别名<br>
4.  
   * 2. 注意如果只有一个查询添加 where本身就是一个SQLBinaryOpExpr，如果是多个就要用 where.getChildren()<br></>
5.  
   * 如果有别名: SQLPropertyExpr(name = id , ownerName = u)<br>
6.  
   * 如果没别名: SQLIdentifierExpr(name = id) <br></>
7.  
   * 值对象: SQLValuableExpr
8.  
   *
9.  
   * @param where 条件对象
10.  
    */
11.  
    public static void parseWhere(SQLExpr where) {
12.  
    if (where instanceof SQLBinaryOpExpr) {
13.  
    parseSQLBinaryOpExpr(cast(where, SQLBinaryOpExpr.class));
14.  
    } else {
15.  
    List<SQLObject> childrenList = where.getChildren();
16.  
    for (SQLObject sqlObject : childrenList) {
17.  
    // 包含了 left 和 right
18.  
    SQLBinaryOpExpr conditionBinary = cast(sqlObject, SQLBinaryOpExpr.class);
19.  
    parseSQLBinaryOpExpr(conditionBinary);
20.  
    }
21.  
    }
22.  
     
23.  
    }
24.  
     
25.  
    public static void parseSQLBinaryOpExpr(SQLBinaryOpExpr conditionBinary) {
26.  
    SQLExpr conditionExpr = conditionBinary.getLeft();
27.  
    SQLExpr conditionValueExpr = conditionBinary.getRight();
28.  
    // 左边有别名所以是SQLPropertyExpr
29.  
    if (conditionExpr instanceof SQLPropertyExpr) {
30.  
    SQLPropertyExpr conditionColumnExpr = cast(conditionExpr, SQLPropertyExpr.class);
31.  
    // 右边根据类型进行转换 id是SQLIntegerExpr name是SQLCharExpr
32.  
    SQLValuableExpr conditionColumnValue = cast(conditionValueExpr, SQLValuableExpr.class);
33.  
    print("条件列名:{},条件别名:{},条件值:{}", conditionColumnExpr.getName(), conditionColumnExpr.getOwnernName(), conditionColumnValue);
34.  
    }
35.  
    // 如果没有别名
36.  
    if (conditionExpr instanceof SQLIdentifierExpr) {
37.  
    SQLIdentifierExpr conditionColumnExpr = cast(conditionExpr, SQLIdentifierExpr.class);
38.  
    SQLValuableExpr conditionColumnValue = cast(conditionValueExpr, SQLValuableExpr.class);
39.  
    print("条件列名:{},条件值:{}", conditionColumnExpr.getName(), conditionColumnValue);
40.  
    }
41.  
    }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41

# 2.2.2 解析 SQLSelectItem

解析查询的列信息

1.  
   /**
2.  
   * 解析查询字段,注意是否使用了别名.u.id as userId, u.name as userName, u.age as userAge<br>
3.  
   * userId(sqlSelectItem.getAlias)<br>
4.  
   * 如果有别名: u.id( id = SQLPropertyExpr.getName,u = SQLPropertyExpr.getOwnernName)<br>
5.  
   * 如果没别名: id(id = SQLIdentifierExpr.name)
6.  
   *
7.  
   * @param selectColumnList 查询字段
8.  
   */
9.  
   private void parseSQLSelectItem(List<SQLSelectItem> selectColumnList) {
10.  
    for (SQLSelectItem sqlSelectItem : selectColumnList) {
11.  
    // u.id as userId(selectColumnAlias)
12.  
    String selectColumnAlias = sqlSelectItem.getAlias();
13.  
    // u.id = SQLPropertyExpr
14.  
    SQLExpr expr = sqlSelectItem.getExpr();
15.  
    if (expr instanceof SQLPropertyExpr) {
16.  
    SQLPropertyExpr selectColumnExpr = cast(expr, SQLPropertyExpr.class);
17.  
    print("列名:{},别名:{},表别名:{}", selectColumnExpr.getName(), selectColumnAlias, selectColumnExpr.getOwnernName());
18.  
    }
19.  
    if (expr instanceof SQLIdentifierExpr) {
20.  
    SQLIdentifierExpr selectColumnExpr = cast(expr, SQLIdentifierExpr.class);
21.  
    print("列名:{},别名:{}", selectColumnExpr.getName(), selectColumnAlias);
22.  
    }
23.  
    }
24.  
    }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

# 2.2.3 解析 SQLUpdateSetItem

1.  
   @Test
2.  
   public void SQLUpdateStatement() {
3.  
   SQLStatement sqlStatement = SQLUtils.parseSingleMysqlStatement("update users u set u.name = '唐僧',age = 18 where u.id = 1 ");
4.  
   SQLUpdateStatement sqlUpdateStatement = Utils.cast(sqlStatement, SQLUpdateStatement.class);
5.  
   List<SQLUpdateSetItem> setItems = sqlUpdateStatement.getItems();
6.  
   for (SQLUpdateSetItem setItem : setItems) {
7.  
   SQLExpr column = setItem.getColumn();
8.  
   if (column instanceof SQLPropertyExpr) {
9.  
   SQLPropertyExpr sqlPropertyExpr = Utils.cast(column, SQLPropertyExpr.class);
10.  
    SQLExpr value = setItem.getValue();
11.  
    Utils.print("column:{},列owner:{},value:{}", sqlPropertyExpr.getName(), sqlPropertyExpr.getOwnernName(), value);
12.  
    }
13.  
    if (column instanceof SQLIdentifierExpr) {
14.  
    SQLExpr value = setItem.getValue();
15.  
    Utils.print("column:{},value:{}", column, value);
16.  
    }
17.  
    }
18.  
    SQLExpr where = sqlUpdateStatement.getWhere();
19.  
    Utils.startParse("解析where", Utils::parseWhere, where);
20.  
    }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

# 2.2.4 解析 SQLLimit

# 2.2.5 解析 SQLSelectGroupBy

1.  
   @Test
2.  
   public void groupBy() {
3.  
   SQLStatement sqlStatement = SQLUtils.parseSingleMysqlStatement("select name,count(1) as count from users group by name,age having count > 2");
4.  
   SQLSelectStatement selectStatement = Utils.cast(sqlStatement, SQLSelectStatement.class);
5.  
   SQLSelect select = selectStatement.getSelect();
6.  
   SQLSelectQueryBlock query = Utils.cast(select.getQuery(), SQLSelectQueryBlock.class);
7.  
   SQLSelectGroupByClause groupBy = query.getGroupBy();
8.  
   List<SQLExpr> items = groupBy.getItems();
9.  
   for (SQLExpr item : items) {
10.  
    // group by name
11.  
    // group by age
12.  
    SQLIdentifierExpr groupByColumn = Utils.cast(item, SQLIdentifierExpr.class);
13.  
    Utils.print("group by {}", groupByColumn);
14.  
    }
15.  
    }
16.  
     

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

# 2.2.6 解析 Having

1.  
   @Test
2.  
   public void having() {
3.  
   SQLStatement sqlStatement = SQLUtils.parseSingleMysqlStatement("select name,count(1) as count from users group by name,age having count > 2");
4.  
   SQLSelectStatement selectStatement = Utils.cast(sqlStatement, SQLSelectStatement.class);
5.  
   SQLSelect select = selectStatement.getSelect();
6.  
   SQLSelectQueryBlock query = Utils.cast(select.getQuery(), SQLSelectQueryBlock.class);
7.  
   SQLSelectGroupByClause groupBy = query.getGroupBy();
8.  
   SQLExpr having = groupBy.getHaving();
9.  
   // 因为只有一个条件,所以having就是SQLBinaryOpExpr
10.  
    SQLBinaryOpExpr havingExpr = Utils.cast(having, SQLBinaryOpExpr.class);
11.  
    // 没有使用别名,所以就是SQLIdentifierExpr
12.  
    SQLExpr left = havingExpr.getLeft();
13.  
    SQLIdentifierExpr leftExpr = Utils.cast(left, SQLIdentifierExpr.class);
14.  
    // 数字类型就是
15.  
    SQLExpr right = havingExpr.getRight();
16.  
    SQLValuableExpr rightValue = Utils.cast(right, SQLValuableExpr.class);
17.  
    SQLBinaryOperator operator = havingExpr.getOperator();
18.  
    // left:count, operator:>,right:2
19.  
    Utils.print("left:{}, operator:{},right:{}", leftExpr.getName(), operator.name, rightValue.getValue());
20.  
    }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

# 三、语法树生成

前面的内容如果都搞清楚了,那么我们就能对sql进行解析,通知可以修改sql解析后的语法树,同时再将修改后的语法树,重新转换成sql

# 3.1 修改语法树

# 3.1.1 增加一个条件

# 3.1.2 修改一个条件值

将条件id = 1 修改成 id = 2

# 四、Visitor模式

访问者模式

所有的AST节点都支持Visitor模式，需要自定义遍历逻辑，可以实现相应的ASTVisitorAdapter派生类

1.  
   public static class CustomerMySqlASTVisitorAdapter extends MySqlASTVisitorAdapter {
2.  
    
3.  
   private final Map<String, SQLTableSource> ALIAS_MAP = new HashMap<String, SQLTableSource>();
4.  
    
5.  
   private final Map<String, SQLExpr> ALIAS_COLUMN_MAP = new HashMap<String, SQLExpr>();
6.  
    
7.  
    
8.  
   public boolean visit(SQLExprTableSource x) {
9.  
   String alias = x.getAlias();
10.  
    ALIAS_MAP.put(alias, x);
11.  
    return true;
12.  
    }
13.  
     
14.  
    @Override
15.  
    public boolean visit(MySqlSelectQueryBlock x) {
16.  
    List<SQLSelectItem> selectList = x.getSelectList();
17.  
    for (SQLSelectItem sqlSelectItem : selectList) {
18.  
    String alias = sqlSelectItem.getAlias();
19.  
    SQLExpr expr = sqlSelectItem.getExpr();
20.  
    ALIAS_COLUMN_MAP.put(alias, expr);
21.  
    }
22.  
    return true;
23.  
    }
24.  
     
25.  
    public Map<String, SQLTableSource> getAliasMap() {
26.  
    return ALIAS_MAP;
27.  
    }
28.  
     
29.  
    public Map<String, SQLExpr> getAliasColumnMap() {
30.  
    return ALIAS_COLUMN_MAP;
31.  
    }
32.  
    }
33.  
     
34.  
    @Test
35.  
    public void AliasVisitor() {
36.  
    String sql = "select u.id as userId, u.name as userName, age as userAge from users as u where u.id = 1 and u.name = '孙悟空' limit 2,10";
37.  
    // 解析SQL
38.  
    SQLStatement sqlStatement = SQLUtils.parseSingleMysqlStatement(sql);
39.  
    CustomerMySqlASTVisitorAdapter customerMySqlASTVisitorAdapter = new CustomerMySqlASTVisitorAdapter();
40.  
    sqlStatement.accept(customerMySqlASTVisitorAdapter);
41.  
    // 表别名:{u=users}
42.  
    System.out.println("表别名:" customerMySqlASTVisitorAdapter.getAliasMap());
43.  
    // 列别名{userName=u.name, userId=u.id, userAge=age}
44.  
    System.out.println("列别名" customerMySqlASTVisitorAdapter.getAliasColumnMap());
45.  
    }

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45