动态SQL解析-阿里云开发者社区

1.背景

动态sql的一大难点就是根据参数动态生成最终查询sql，我们熟知的orm框架mybatis为了实现动态sql能力，引入大量的标签，功能强大的同时也带来使用的复杂性，至于mybatis-plus是怎么实现的，大家自行查阅资料，我想复杂性并不低。当然我这里想说一下，全方面完美地实现动态sql本身就是一件复杂的事情。

接下来我想讲讲我的思路，借助于阿里开源的Druid在sql语法解析上的能力，根据传递参数动态判断实现sql语法where条件语句的动态剔除，从而达到动态sql能力。所以在讲述动态sql解析之前我们先来了解一下druid怎么解析sql语法的。

项目推荐：基于SpringBoot2.x、SpringCloud和SpringCloudAlibaba企业级系统架构底层框架封装，解决业务开发时常见的非功能性需求，防止重复造轮子，方便业务快速开发和企业技术栈框架统一管理。引入组件化的思想实现高内聚低耦合并且高度可配置化，做到可插拔。严格控制包依赖和统一版本管理，做到最少化依赖。注重代码规范和注释，非常适合个人学习和企业使用

Github地址：https://github.com/plasticene/plasticene-boot-starter-parent

Gitee地址：https://gitee.com/plasticene3/plasticene-boot-starter-parent

微信公众号：Shepherd进阶笔记

交流探讨群：Shepherd_126

2.Druid_SQL_AST

SQL Parser是Druid的一个重要组成部分，Druid内置使用SQL Parser来实现防御SQL注入（WallFilter）、合并统计没有参数化的SQL(StatFilter的mergeSql)、SQL格式化、分库分表。

和另一个知名解析器Antlr生成的SQL有很大不同的是，Druid SQL Parser性能非常好，可以用于生产环境直接对SQL进行分析处理

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

2.1 AST节点类型

在Druid中，AST节点类型主要包括SQLObject、SQLExpr、SQLStatement三种抽象类型，可以说这三个类型贯穿了整个sql语法树解析，提炼关键信息(eg:查询的字段、表名、where条件等等)过程

package com.alibaba.druid.sql.ast;

interface SQLObject {
   }
interface SQLExpr extends SQLObject {
   }
interface SQLStatement extends SQLObject {
   }

interface SQLTableSource extends SQLObject {
   }
class SQLSelect extends SQLObject {
   }
class SQLSelectQueryBlock extends SQLObject {
   }

2.2 常见SQLExpr类型

SQLExpr抽象类型对动态sql解析非常重要，动态sql的解析核心就是需要提炼出sql的所有where条件语句，然后对每一个条件语句结合参数进行动态处理，这里提炼出的每一个where条件就是下面常见类型中的一个

package com.alibaba.druid.sql.ast.expr;

// SQLName是一种的SQLExpr的Expr，包括SQLIdentifierExpr、SQLPropertyExpr等
public interface SQLName extends SQLExpr {
   }

// 例如 ID = 3 这里的ID是一个SQLIdentifierExpr
class SQLIdentifierExpr implements SQLExpr, SQLName {
   
    String name;
} 

// 例如 A.ID = 3 这里的A.ID是一个SQLPropertyExpr
class SQLPropertyExpr implements SQLExpr, SQLName {
   
    SQLExpr owner;
    String name;
} 

// 例如 ID = 3 这是一个SQLBinaryOpExpr
// left是ID (SQLIdentifierExpr)
// right是3 (SQLIntegerExpr)
class SQLBinaryOpExpr implements SQLExpr {
   
    SQLExpr left;
    SQLExpr right;
    SQLBinaryOperator operator;
}

// 例如 select * from where id = ?，这里的?是一个SQLVariantRefExpr，name是'?'
class SQLVariantRefExpr extends SQLExprImpl {
    
    String name;
}

// 例如 ID = 3 这里的3是一个SQLIntegerExpr
public class SQLIntegerExpr extends SQLNumericLiteralExpr implements SQLValuableExpr {
    
    Number number;

    // 所有实现了SQLValuableExpr接口的SQLExpr都可以直接调用这个方法求值
    @Override
    public Object getValue() {
   
        return this.number;
    }
}

// 例如 NAME = 'jobs' 这里的'jobs'是一个SQLCharExpr
public class SQLCharExpr extends SQLTextLiteralExpr implements SQLValuableExpr{
   
    String text;
}

2.3. 常用的SQLStatemment

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

package com.alibaba.druid.sql.ast.statement;

class SQLSelectStatement implements SQLStatement {
   
    SQLSelect select;
}
class SQLUpdateStatement implements SQLStatement {
   
    SQLExprTableSource tableSource;
     List<SQLUpdateSetItem> items;
     SQLExpr where;
}
class SQLDeleteStatement implements SQLStatement {
   
    SQLTableSource tableSource; 
    SQLExpr where;
}
class SQLInsertStatement implements SQLStatement {
   
    SQLExprTableSource tableSource;
    List<SQLExpr> columns;
    SQLSelect query;
}

2.4. SQLTableSource

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

class SQLTableSourceImpl extends SQLObjectImpl implements SQLTableSource {
    
    String alias;
}

// 例如 select * from emp where i = 3，这里的from emp是一个SQLExprTableSource
// 其中expr是一个name=emp的SQLIdentifierExpr
class SQLExprTableSource extends SQLTableSourceImpl {
   
    SQLExpr expr;
}

// 例如 select * from emp e inner join org o on e.org_id = o.id
// 其中left 'emp e' 是一个SQLExprTableSource，right 'org o'也是一个SQLExprTableSource
// condition 'e.org_id = o.id'是一个SQLBinaryOpExpr
class SQLJoinTableSource extends SQLTableSourceImpl {
   
    SQLTableSource left;
    SQLTableSource right;
    JoinType joinType; // INNER_JOIN/CROSS_JOIN/LEFT_OUTER_JOIN/RIGHT_OUTER_JOIN/...
    SQLExpr condition;
}

// 例如 select * from (select * from temp) a，这里第一层from(...)是一个SQLSubqueryTableSource
SQLSubqueryTableSource extends SQLTableSourceImpl {
   
    SQLSelect select;
}

/* 
例如
WITH RECURSIVE ancestors AS (
    SELECT *
    FROM org
    UNION
    SELECT f.*
    FROM org f, ancestors a
    WHERE f.id = a.parent_id
)
SELECT *
FROM ancestors;

这里的ancestors AS (...) 是一个SQLWithSubqueryClause.Entry
*/
class SQLWithSubqueryClause {
   
    static class Entry extends SQLTableSourceImpl {
    
         SQLSelect subQuery;
    }
}

2.5. SQLSelect & SQLSelectQuery

SQLSelectStatement包含一个SQLSelect，SQLSelect包含一个SQLSelectQuery，都是组成的关系。SQLSelectQuery有主要的两个派生类，分别是SQLSelectQueryBlock和SQLUnionQuery。

class SQLSelect extends SQLObjectImpl {
    
    SQLWithSubqueryClause withSubQuery;
    SQLSelectQuery query;
}

interface SQLSelectQuery extends SQLObject {
   }

class SQLSelectQueryBlock implements SQLSelectQuery {
   
    List<SQLSelectItem> selectList;
    SQLTableSource from;
    SQLExprTableSource into;
    SQLExpr where;
    SQLSelectGroupByClause groupBy;
    SQLOrderBy orderBy;
    SQLLimit limit;
}

class SQLUnionQuery implements SQLSelectQuery {
   
    SQLSelectQuery left;
    SQLSelectQuery right;
    SQLUnionOperator operator; // UNION/UNION_ALL/MINUS/INTERSECT
}

2.6 Visitor

Druid提供了多种默认实现的Visitor，可以满足基本需求，如果默认提供的不满足需求，可自行实现自定义Visitor。

比如我们要统计下一条SQL中涉及了哪些表，如果我们不用visitor，自行遍历AST，能实现，但是很繁琐，使用visitor就很简单：

public String parseSQL(String sql) {
   
    // 新建 MySQL Parser
    SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, JdbcUtils.MYSQL);

    // 使用Parser解析生成AST，这里SQLStatement就是AST
    SQLSelectStatement statement = (SQLSelectStatement) parser.parseStatement();

    // 使用visitor来访问AST
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    statement.accept(visitor);
    // 解析sql
    parseSelect(statement);

    // sql中的所有表
    Map<TableStat.Name, TableStat> tables = visitor.getTables();

    // druid格式化之后的sql
    String beautySQL = SQLUtils.toSQLString(statement);

    return beautySQL;
}

以上类型是动态sql解析都需要用到的，当然druid不止有这些类型，这里不在详情阐述，想要了解可自行到官网查看

3 使用Druid怎么实现动态sql解析

3.1 解析示例

在讲述实现原理之前直接上测试用例

SELECT
    * 
FROM
    (
    SELECT
        id,
        org_id,
        NAME,
        age,
        phone,
        email,
        ( SELECT dep_name FROM dept WHERE dep_id = @depId ) 
    FROM
        USER t 
    WHERE
        t.is_delete = 0 
        AND t.id IN ( SELECT id FROM USER WHERE org_id = @orgId ) 
        AND create_time >= @createTime 
        AND age = @age 
        AND type IN @type 
        AND state <> @state 
        AND ( name = @name OR user_name = @name ) 
    ) AS a 
WHERE
    a.org_id = @orgId 
    AND id IN @id 
    AND email LIKE '%@163.com' 
    AND phone = @phone

这里我们使用@var代表动态参数，同时sql语句@就是用来定义一个变量的，这点不谋而合。

测试用例代码如下：


 public static void main(String[] args) {
   
        String sql = "SELECT * FROM (SELECT id, org_id, NAME, age, phone, email, ( SELECT dep_name FROM dept   WHERE dep_id = @depId ) FROM USER t \n" +
                "WHERE t.is_delete = 0 AND t.id IN ( SELECT id FROM USER WHERE org_id = @orgId ) AND create_time>= @createTime AND age = @age AND type IN @type AND state <> @state \n" +
                "AND ( name = @name OR user_name = @name ) ) AS a \n" +
                "WHERE a.org_id = @orgId AND id IN @id AND email LIKE '%@163.com' AND phone = @phone";
        Map<String, String> params = new HashMap<>();
        params.put("@depId", "567");
        params.put("@orgId", "432");
        params.put("@createTime", "'2022-06-30 19:00:00'");
        params.put("@type", "6,7,8,9,10000");
        params.put("@name", "'张三'");
        params.put("@phone", "'1234567789'");
        DynamicSqlParser dynamicSqlParser = new DynamicSqlParser();
        String beautySQL = dynamicSqlParser.parseSQL(sql, params);
        System.out.println(beautySQL);
    }

从上面可以看到我们只传了部分参数，执行结果如下：

SELECT *
FROM (
    SELECT id, org_id, NAME, age, phone
        , email
        , (
            SELECT dep_name
            FROM dept
            WHERE dep_id = 567
        )
    FROM USER t
    WHERE t.is_delete = 0
        AND t.id IN (
            SELECT id
            FROM USER
            WHERE org_id = 432
        )
        AND create_TIME >= '2022-06-30 19:00:00'
        AND 1 = 1
        AND type IN (6,7,8,9,10000)
        AND 1 = 1
        AND (name = '张三'
            OR user_name = '张三')
) a
WHERE a.org_id = 432
    AND 1 = 1
    AND email LIKE '%@163.com'
    AND phone = '1234567789'

可以看到，我们传的参数都正常匹配上了对应的条件中，没传的参数对应的条件全部被替换成了1=1，这样就可以做到想查什么就查什么的动态sql能力了。

3.2 实现原理

实现思路相对来说还是挺简单的，借助Druid解析sql的能力，提炼出sql里面所有的带@var动态参数的where条件：

    /**
     * 存放解析出来的带@var动态参数的条件语句 动态参数 → 条件set集合
     */
    public static final ThreadLocal<Map<String, Set<String>>> varToWhere = new ThreadLocal<>();

    private void setVarToWhere(String var, String where) {
   
    Map<String, Set<String>> map = varToWhere.get();
    if (map == null) {
   
    map = new HashMap<>();
    }
    Set<String> wheres = map.get(var);
    if (wheres == null) {
   
    wheres = new HashSet<>();
    }
    wheres.add(where);
    map.put(var, wheres);
    varToWhere.set(map);
  }

为什么动态参数是对应一个条件set集合呢？

因为sql语句一个动态参数@var有可能用在了不同地方，如上面的@orgId参数用在了org_id = @orgId和a.org_id = @orgId这两个where条件中，所以我们要对应一个set

通过debug上面测试用例可以看到：varToWhere变量存的值：

{
  @createTime=[create_TIME >= @createTime], 
  @age=[age = @age], 
  @state=[state <> @state],
  @name=[user_name = @name, name = @name],
  @type=[type IN (@type)], 
  @orgId=[org_id = @orgId, a.org_id = @orgId], 
  @id=[id IN (@id)],
  @depId=[dep_id = @depId], 
  @phone=[phone = @phone]
}

最后经过sql文本字符串替换出来得到最终上面示例的sql语句

    public String parseSQL(String sql, Map<String, String> params) {
        String beautySQL = parseSQL(sql);
        Map<String, Set<String>> map = varToWhere.get();
        System.out.println(map);
        varToWhere.remove();
        for (Map.Entry<String, Set<String>> entry : map.entrySet()) {
            String var = entry.getKey();
            Set<String> wheres = entry.getValue();
            String replaceWhere = null;
            for(String where : wheres) {
                // 没有传这个参数，那么把这个条件变成1=1
                if (!params.containsKey(var)) {
                    replaceWhere = CONSTANT_CONDITION;
                } else {
                    replaceWhere = where.replaceAll(var, params.get(var));
                }
                beautySQL = beautySQL.replace(where, replaceWhere);
            }
        }
        return beautySQL;
    }

经个人测试，动态sql解析通过上面方法相对稳定，没出现问题，当然由于sql的千奇百怪，并一定保证所有sql场景都能正常解析，欢迎大家提供复杂sql测试。

动态SQL解析

1.背景

2.Druid_SQL_AST

2.1 AST节点类型

2.2 常见SQLExpr类型

2.3. 常用的SQLStatemment

2.4. SQLTableSource

2.5. SQLSelect & SQLSelectQuery

2.6 Visitor

3 使用Druid怎么实现动态sql解析

3.1 解析示例

3.2 实现原理

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

动态SQL解析

1.背景

2.Druid_SQL_AST

2.1 AST节点类型

2.2 常见SQLExpr类型

2.3. 常用的SQLStatemment

2.4. SQLTableSource

2.5. SQLSelect & SQLSelectQuery

2.6 Visitor

3 使用Druid怎么实现动态sql解析

3.1 解析示例

3.2 实现原理

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像