Druid SQL解析
【摘要】
相关信息
本篇主要学习Druid 对Sql的语法解析。学习完之后,我们可以对任意sql进行解析,同时也可以基于AST语法树来生成sql语句。
(opens new window)
# 一、AST
AST是abstract syntax t...
相关信息
本篇主要学习Druid 对Sql的语法解析。学习完之后,我们可以对任意sql进行解析,同时也可以基于AST语法树来生成sql语句。
# 一、AST
AST是abstract syntax tree的缩写，也就是抽象语法树。和所有的Parser一样，Druid Parser会生成一个抽象语法树。
在Druid中，AST节点类型主要包括SQLObject、SQLExpr、SQLStatement三种抽象类型。
-
interface SQLObject {}
-
interface SQLExpr extends SQLObject {}
-
interface SQLStatement extends SQLObject {}
-
-
interface SQLTableSource extends SQLObject {}
-
class SQLSelect extends SQLObject {}
-
class SQLSelectQueryBlock extends SQLObject {}
1 2 3 4 5 6 7 8
# 二、语法树解析
# 2.1 核心类介绍
# 2.1.1 SQLStatemment DQL & DML顶级抽象
- DQL 数据查询语言 select
- DML 数据操纵语言 insert update delete
最常用的Statement当然是SELECT/UPDATE/DELETE/INSERT，他们分别是
| 核心类 | 说明 |
|---|---|
| SQLSelectStatement | 查询语句 |
| SQLUpdateStatement | 更新语句 |
| SQLDeleteStatement | 删除语句 |
| SQLInsertStatement | 新增语句 |
-
@Test
-
public void statement() {
-
// 以下全部 true
-
System.out.println(SQLUtils.parseSingleMysqlStatement("select * from users") instanceof SQLSelectStatement);
-
System.out.println(SQLUtils.parseSingleMysqlStatement("insert into users(id,name,age) values (1,'孙悟空',500)") instanceof SQLInsertStatement);
-
System.out.println(SQLUtils.parseSingleMysqlStatement("update users set name = '唐僧' where id = 1 ") instanceof SQLUpdateStatement);
-
System.out.println(SQLUtils.parseSingleMysqlStatement("delete from users where id = 1") instanceof SQLDeleteStatement);
-
}
1 2 3 4 5 6 7 8
# 2.1.2 SQLSelect SQL查询
SQLSelectStatement包含一个SQLSelect，SQLSelect包含一个SQLSelectQuery。SQLSelectQuery有主要的两个派生类， 分别是SQLSelectQueryBlock(单表sql查询)和SQLUnionQuery(union查询 (opens new window))。
-
/**
-
* SQLSelectStatement包含一个SQLSelect，SQLSelect包含一个SQLSelectQuery。SQLSelectQuery有主要的两个派生类，
-
* 分别是SQLSelectQueryBlock(单表sql查询)和SQLUnionQuery(联合查询)。
-
*/
-
@Test
-
public void SQLSelectQuery() {
-
// true
-
System.out.println(parseSQLSelectQuery("select * from users") instanceof SQLSelectQueryBlock);
-
// true
-
System.out.println(parseSQLSelectQuery("select name from users union select name from school") instanceof SQLUnionQuery);
-
}
-
-
public SQLSelectQuery parseSQLSelectQuery(String sql) {
-
SQLStatement sqlStatement = SQLUtils.parseSingleMysqlStatement(sql);
-
SQLSelectStatement sqlSelectStatement = Utils.cast(sqlStatement, SQLSelectStatement.class);
-
SQLSelect select = sqlSelectStatement.getSelect();
-
return select.getQuery();
-
}
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
# 2.1.3 SQLExpr
SQLExpr 是有几个实现类的。
-
select id,name,age from users where id = 1 and name = '孙悟空';
-
-
select u.id, u.name from users as u where id = 1 and name = ?;
1 2 3
| 核心类 | 举例 | 说明 | 适用范围 | 快速记忆 |
|---|---|---|---|---|
| SQLIdentifierExpr | id,name,age | SQLIdentifierExpr | 查询字段或者where条件 | 唯一标记 |
| SQLPropertyExpr | u.id,u.name | 区别于SQLIdentifierExpr,适用于有别名的场景; SQLPropertyExpr.name = id, SQLPropertyExpr.owner = SQLIdentifierExpr = u) | 查询字段或者where条件 | 有别名就是它 |
| SQLBinaryOpExpr | id = 1, id > 5 | SQLBinaryOpExpr(left = SQLIdentifierExpr = id ,right = SQLValuableExpr = 1) | where条件 | 有操作符就是它 |
| SQLVariantRefExpr | id = ? | 变量 | where条件 | 有变量符就是它 |
| SQLIntegerExpr | id = 1 | 数字类型 | 值类型 | - |
| SQLCharExpr | name = '孙悟空' | 字符类型 | 值类型 | - |
# 2.1.3.1 SQLBinaryOpExpr
-
/**
-
* 操作符相关: SQLBinaryOpExpr
-
*/
-
@Test
-
public void SQLBinaryOpExpr() {
-
String sql = "select * from users where id > 1 and age = 18";
-
SQLSelectQuery sqlSelectQuery = Utils.parseSQLSelectQuery(sql);
-
SQLSelectQueryBlock selectQueryBlock = Utils.cast(sqlSelectQuery, SQLSelectQueryBlock.class);
-
SQLExpr where = selectQueryBlock.getWhere();
-
List<SQLObject> conditions = where.getChildren();
-
// [id > 1 , age = 18] 出现了操作符所以是SQLBinaryOpExpr
-
for (SQLObject condition : conditions) {
-
SQLBinaryOpExpr conditionExpr = Utils.cast(condition, SQLBinaryOpExpr.class);
-
SQLBinaryOperator operator = conditionExpr.getOperator();
-
SQLIdentifierExpr conditionColumn = Utils.cast(conditionExpr.getLeft(), SQLIdentifierExpr.class);
-
SQLValuableExpr conditionColumnValue = Utils.cast(conditionExpr.getRight(), SQLValuableExpr.class);
-
Utils.print("条件字段:{},操作符号:{},条件值:{}", conditionColumn.getName(), operator.name, conditionColumnValue);
-
}
-
}
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
# 2.1.3.2 SQLVariantRefExpr
-
@Test
-
public void SQLVariantRefExpr() {
-
String sql = "select * from users where id = ? and name = ?";
-
SQLSelectQuery sqlSelectQuery = Utils.parseSQLSelectQuery(sql);
-
SQLSelectQueryBlock selectQueryBlock = Utils.cast(sqlSelectQuery, SQLSelectQueryBlock.class);
-
SQLExpr where = selectQueryBlock.getWhere();
-
List<SQLObject> conditions = where.getChildren();
-
// [id = ?] 出现了变量符,所以要用SQLVariantRefExpr
-
for (SQLObject condition : conditions) {
-
SQLBinaryOpExpr conditionExpr = Utils.cast(condition, SQLBinaryOpExpr.class);
-
SQLBinaryOperator operator = conditionExpr.getOperator();
-
SQLIdentifierExpr conditionColumn = Utils.cast(conditionExpr.getLeft(), SQLIdentifierExpr.class);
-
SQLVariantRefExpr conditionColumnValue = Utils.cast(conditionExpr.getRight(), SQLVariantRefExpr.class);
-
int index = conditionColumnValue.getIndex();
-
Utils.print("条件字段:{},操作符号:{},索引位:{}", conditionColumn.getName(), operator.name, index);
-
}
-
}
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
| 核心类 | 举例 | 说明 | 快速记忆 |
|---|---|---|---|
| SQLExprTableSource | select * from emp where i = 3 | name = SQLIdentifierExpr = emp | 单表查询 |
| SQLJoinTableSource | select * from emp e inner join org o on e.org_id = o.id | left = SQLExprTableSource(emp e),right = SQLExprTableSource(org o), condition = SQLBinaryOpExpr(e.org_id = o.id) | join 查询使用 |
| SQLSubqueryTableSource | select * from (select * from temp) a | from(...)是一个SQLSubqueryTableSource | 子查询语句 |
| SQLWithSubqueryClause | 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 | with |
# 2.2 SQL语句解析示例
# 2.2.1 解析 Where
注意如果条件语句中只有一个条件,那么where就是一个 SQLBinaryOpExpr。 当条件大于2个,使用 where.getChildren()
-
/**
-
* 判断where要
-
* 1. 注意是SQLBinaryOpExpr(id = 1) or (u.id = 1) 需要注意是否使用了别名<br>
-
* 2. 注意如果只有一个查询添加 where本身就是一个SQLBinaryOpExpr，如果是多个就要用 where.getChildren()<br></>
-
* 如果有别名: SQLPropertyExpr(name = id , ownerName = u)<br>
-
* 如果没别名: SQLIdentifierExpr(name = id) <br></>
-
* 值对象: SQLValuableExpr
-
*
-
* @param where 条件对象
-
*/
-
public static void parseWhere(SQLExpr where) {
-
if (where instanceof SQLBinaryOpExpr) {
-
parseSQLBinaryOpExpr(cast(where, SQLBinaryOpExpr.class));
-
} else {
-
List<SQLObject> childrenList = where.getChildren();
-
for (SQLObject sqlObject : childrenList) {
-
// 包含了 left 和 right
-
SQLBinaryOpExpr conditionBinary = cast(sqlObject, SQLBinaryOpExpr.class);
-
parseSQLBinaryOpExpr(conditionBinary);
-
}
-
}
-
-
}
-
-
public static void parseSQLBinaryOpExpr(SQLBinaryOpExpr conditionBinary) {
-
SQLExpr conditionExpr = conditionBinary.getLeft();
-
SQLExpr conditionValueExpr = conditionBinary.getRight();
-
// 左边有别名所以是SQLPropertyExpr
-
if (conditionExpr instanceof SQLPropertyExpr) {
-
SQLPropertyExpr conditionColumnExpr = cast(conditionExpr, SQLPropertyExpr.class);
-
// 右边根据类型进行转换 id是SQLIntegerExpr name是SQLCharExpr
-
SQLValuableExpr conditionColumnValue = cast(conditionValueExpr, SQLValuableExpr.class);
-
print("条件列名:{},条件别名:{},条件值:{}", conditionColumnExpr.getName(), conditionColumnExpr.getOwnernName(), conditionColumnValue);
-
}
-
// 如果没有别名
-
if (conditionExpr instanceof SQLIdentifierExpr) {
-
SQLIdentifierExpr conditionColumnExpr = cast(conditionExpr, SQLIdentifierExpr.class);
-
SQLValuableExpr conditionColumnValue = cast(conditionValueExpr, SQLValuableExpr.class);
-
print("条件列名:{},条件值:{}", conditionColumnExpr.getName(), conditionColumnValue);
-
}
-
}
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
解析查询的列信息
-
/**
-
* 解析查询字段,注意是否使用了别名.u.id as userId, u.name as userName, u.age as userAge<br>
-
* userId(sqlSelectItem.getAlias)<br>
-
* 如果有别名: u.id( id = SQLPropertyExpr.getName,u = SQLPropertyExpr.getOwnernName)<br>
-
* 如果没别名: id(id = SQLIdentifierExpr.name)
-
*
-
* @param selectColumnList 查询字段
-
*/
-
private void parseSQLSelectItem(List<SQLSelectItem> selectColumnList) {
-
for (SQLSelectItem sqlSelectItem : selectColumnList) {
-
// u.id as userId(selectColumnAlias)
-
String selectColumnAlias = sqlSelectItem.getAlias();
-
// u.id = SQLPropertyExpr
-
SQLExpr expr = sqlSelectItem.getExpr();
-
if (expr instanceof SQLPropertyExpr) {
-
SQLPropertyExpr selectColumnExpr = cast(expr, SQLPropertyExpr.class);
-
print("列名:{},别名:{},表别名:{}", selectColumnExpr.getName(), selectColumnAlias, selectColumnExpr.getOwnernName());
-
}
-
if (expr instanceof SQLIdentifierExpr) {
-
SQLIdentifierExpr selectColumnExpr = cast(expr, SQLIdentifierExpr.class);
-
print("列名:{},别名:{}", selectColumnExpr.getName(), selectColumnAlias);
-
}
-
}
-
}
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
-
@Test
-
public void SQLUpdateStatement() {
-
SQLStatement sqlStatement = SQLUtils.parseSingleMysqlStatement("update users u set u.name = '唐僧',age = 18 where u.id = 1 ");
-
SQLUpdateStatement sqlUpdateStatement = Utils.cast(sqlStatement, SQLUpdateStatement.class);
-
List<SQLUpdateSetItem> setItems = sqlUpdateStatement.getItems();
-
for (SQLUpdateSetItem setItem : setItems) {
-
SQLExpr column = setItem.getColumn();
-
if (column instanceof SQLPropertyExpr) {
-
SQLPropertyExpr sqlPropertyExpr = Utils.cast(column, SQLPropertyExpr.class);
-
SQLExpr value = setItem.getValue();
-
Utils.print("column:{},列owner:{},value:{}", sqlPropertyExpr.getName(), sqlPropertyExpr.getOwnernName(), value);
-
}
-
if (column instanceof SQLIdentifierExpr) {
-
SQLExpr value = setItem.getValue();
-
Utils.print("column:{},value:{}", column, value);
-
}
-
}
-
SQLExpr where = sqlUpdateStatement.getWhere();
-
Utils.startParse("解析where", Utils::parseWhere, where);
-
}
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个值
-
*
-
* @param limit 限制
-
*/
-
private void parseLimit(SQLLimit limit) {
-
// 偏移量
-
SQLExpr offset = limit.getOffset();
-
// 便宜数量
-
SQLExpr rowCount = limit.getRowCount();
-
print("偏移量:{},偏移数量:{}", offset, rowCount);
-
}
1 2 3 4 5 6 7 8 9 10 11 12
# 2.2.5 解析 SQLSelectGroupBy
-
@Test
-
public void groupBy() {
-
SQLStatement sqlStatement = SQLUtils.parseSingleMysqlStatement("select name,count(1) as count from users group by name,age having count > 2");
-
SQLSelectStatement selectStatement = Utils.cast(sqlStatement, SQLSelectStatement.class);
-
SQLSelect select = selectStatement.getSelect();
-
SQLSelectQueryBlock query = Utils.cast(select.getQuery(), SQLSelectQueryBlock.class);
-
SQLSelectGroupByClause groupBy = query.getGroupBy();
-
List<SQLExpr> items = groupBy.getItems();
-
for (SQLExpr item : items) {
-
// group by name
-
// group by age
-
SQLIdentifierExpr groupByColumn = Utils.cast(item, SQLIdentifierExpr.class);
-
Utils.print("group by {}", groupByColumn);
-
}
-
}
-
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
# 2.2.6 解析 Having
-
@Test
-
public void having() {
-
SQLStatement sqlStatement = SQLUtils.parseSingleMysqlStatement("select name,count(1) as count from users group by name,age having count > 2");
-
SQLSelectStatement selectStatement = Utils.cast(sqlStatement, SQLSelectStatement.class);
-
SQLSelect select = selectStatement.getSelect();
-
SQLSelectQueryBlock query = Utils.cast(select.getQuery(), SQLSelectQueryBlock.class);
-
SQLSelectGroupByClause groupBy = query.getGroupBy();
-
SQLExpr having = groupBy.getHaving();
-
// 因为只有一个条件,所以having就是SQLBinaryOpExpr
-
SQLBinaryOpExpr havingExpr = Utils.cast(having, SQLBinaryOpExpr.class);
-
// 没有使用别名,所以就是SQLIdentifierExpr
-
SQLExpr left = havingExpr.getLeft();
-
SQLIdentifierExpr leftExpr = Utils.cast(left, SQLIdentifierExpr.class);
-
// 数字类型就是
-
SQLExpr right = havingExpr.getRight();
-
SQLValuableExpr rightValue = Utils.cast(right, SQLValuableExpr.class);
-
SQLBinaryOperator operator = havingExpr.getOperator();
-
// left:count, operator:>,right:2
-
Utils.print("left:{}, operator:{},right:{}", leftExpr.getName(), operator.name, rightValue.getValue());
-
}
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 增加一个条件
-
@Test
-
public void SQLDeleteStatement(){
-
SQLStatement sqlStatement = SQLUtils.parseSingleMysqlStatement("delete from users where id = 1");
-
SQLDeleteStatement sqlDeleteStatement = Utils.cast(sqlStatement, SQLDeleteStatement.class);
-
sqlDeleteStatement.addCondition(SQLUtils.toSQLExpr("name = '孙悟空'"));
-
// DELETE FROM users
-
// WHERE id = 1
-
// AND name = '孙悟空'
-
System.out.println(SQLUtils.toSQLString(sqlDeleteStatement));
-
}
1 2 3 4 5 6 7 8 9 10
# 3.1.2 修改一个条件值
将条件id = 1 修改成 id = 2
-
@Test
-
public void SQLDeleteStatement2(){
-
SQLStatement sqlStatement = SQLUtils.parseSingleMysqlStatement("delete from users where id = 1");
-
SQLDeleteStatement sqlDeleteStatement = Utils.cast(sqlStatement, SQLDeleteStatement.class);
-
SQLExpr where = sqlDeleteStatement.getWhere();
-
SQLBinaryOpExpr sqlBinaryOpExpr = Utils.cast(where, SQLBinaryOpExpr.class);
-
// DELETE FROM users
-
// WHERE id = 2
-
sqlBinaryOpExpr.setRight(SQLUtils.toSQLExpr("2"));
-
System.out.println(SQLUtils.toSQLString(sqlDeleteStatement));
-
}
1 2 3 4 5 6 7 8 9 10 11
# 四、Visitor模式
访问者模式
所有的AST节点都支持Visitor模式，需要自定义遍历逻辑，可以实现相应的ASTVisitorAdapter派生类
-
public static class CustomerMySqlASTVisitorAdapter extends MySqlASTVisitorAdapter {
-
-
private final Map<String, SQLTableSource> ALIAS_MAP = new HashMap<String, SQLTableSource>();
-
-
private final Map<String, SQLExpr> ALIAS_COLUMN_MAP = new HashMap<String, SQLExpr>();
-
-
-
public boolean visit(SQLExprTableSource x) {
-
String alias = x.getAlias();
-
ALIAS_MAP.put(alias, x);
-
return true;
-
}
-
-
@Override
-
public boolean visit(MySqlSelectQueryBlock x) {
-
List<SQLSelectItem> selectList = x.getSelectList();
-
for (SQLSelectItem sqlSelectItem : selectList) {
-
String alias = sqlSelectItem.getAlias();
-
SQLExpr expr = sqlSelectItem.getExpr();
-
ALIAS_COLUMN_MAP.put(alias, expr);
-
}
-
return true;
-
}
-
-
public Map<String, SQLTableSource> getAliasMap() {
-
return ALIAS_MAP;
-
}
-
-
public Map<String, SQLExpr> getAliasColumnMap() {
-
return ALIAS_COLUMN_MAP;
-
}
-
}
-
-
@Test
-
public void AliasVisitor() {
-
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";
-
// 解析SQL
-
SQLStatement sqlStatement = SQLUtils.parseSingleMysqlStatement(sql);
-
CustomerMySqlASTVisitorAdapter customerMySqlASTVisitorAdapter = new CustomerMySqlASTVisitorAdapter();
-
sqlStatement.accept(customerMySqlASTVisitorAdapter);
-
// 表别名:{u=users}
-
System.out.println("表别名:" + customerMySqlASTVisitorAdapter.getAliasMap());
-
// 列别名{userName=u.name, userId=u.id, userAge=age}
-
System.out.println("列别名" + customerMySqlASTVisitorAdapter.getAliasColumnMap());
-
}
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
文章来源: springlearn.blog.csdn.net，作者：西魏陶渊明，版权归原作者所有，如需转载，请联系作者。
原文链接：springlearn.blog.csdn.net/article/details/125858132
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）