Druid是Java语言中最好的数据库连接池。Druid能够提供强大的监控和扩展功能。
先来看看我们要达到的效果:
这个项目的目的是让业务用户通过手动拖拽自己想要的字段即可展示、分析、下载数据，之前的流程是先添加数据源->在系统中导入表->配置分析模型-选择分析模型展示数据。这里的分析模型其实就是通过拖拽表类配置表与表之间是关联关系。然后拖拽字段后，前端就发送选择的字段表达式和过滤条件表达式到后端，通过配置是分析模型关系，解析出相应数据库的SQL语句发送到数据库执行。
现在有一种场景，就是用户能只需要查询单张表，如果这样也需要去配置分析模型，那么就增加了复杂度。所以，需要给用户提供一种直接通过数据源直接选择未建立关联关系的原表来查询、分析、下载数据。由于项目是没有源代码，而且之前的解析那一块都是基于分析模型，内部逻辑比较复杂。所以我选择了一种相对来说要方便一点的方式就是通过Driud来解析生成SQL语句。
我们都知道Druid是一个JDBC组件库，包括数据库连接池、SQL Parser等组件,我们一般都是拿来做JDBC连接池和SQL语句监控使用。这里，我只是把这次SQL Parser组件的使用过程记录一下。感兴趣的朋友可以去看看Druid 使用手册
Druid_SQL_AST学习
什么是AST
AST是abstract syntax tree的缩写，也就是抽象语法树。和所有的Parser一样，Druid Parser会生成一个抽象语法树。
在Druid SQL Parser中有哪些AST节点类型
在Druid中，AST节点类型主要包括SQLObject、SQLExpr、SQLStatement三种抽象类型。
1
2
3
4
5
6
7
8
9
| 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 {}
|
常用的SQLExpr有哪些
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
| 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;
}
|
常用的SQLStatemment
最常用的Statement当然是SELECT/UPDATE/DELETE/INSERT，他们分别是
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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
| 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.3. 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.4. 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
}
|
SQLCreateTableStatement
建表语句包含了一系列方法，用于方便各种操作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| public class SQLCreateTableStatement extends SQLStatementImpl implements SQLDDLStatement, SQLCreateStatement {
SQLExprTableSource tableSource;
List<SQLTableElement> tableElementList;
Select select;
// 忽略大小写的查找SQLCreateTableStatement中的SQLColumnDefinition
public SQLColumnDefinition findColumn(String columName) {}
// 忽略大小写的查找SQLCreateTableStatement中的column关联的索引
public SQLTableElement findIndex(String columnName) {}
// 是否外键依赖另外一个表
public boolean isReferenced(String tableName) {}
}
|
怎样产生AST
通过SQLUtils产生List
1
2
3
4
| import com.alibaba.druid.util.JdbcConstants;
String dbType = JdbcConstants.MYSQL;
List<SQLStatement> statementList = SQLUtils.parseStatements(sql, dbType);
|
通过SQLUtils产生SQLExpr
1
2
| String dbType = JdbcConstants.MYSQL;
SQLExpr expr = SQLUtils.toSQLExpr("id=3", dbType);
|
怎样打印AST节点
通过SQLUtils工具类打印节点
1
2
3
4
5
6
7
8
9
| package com.alibaba.druid.sql;
public class SQLUtils {
// 可以将SQLExpr/SQLStatement打印为String类型
static String toSQLString(SQLObject sqlObj, String dbType);
// 可以将一个<SQLStatement>打印为String类型
static String toSQLString(List<SQLStatement> statementList, String dbType);
}
|
在Select语句中添加Group by
这个先简单的做一个场景，依然拿本次这个项目来说，在前端发送SUM、COUNT等聚合表达式的SQL时，我们需要在语句后面添加Group by。
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
| public static String getSql(String sql, int offset, int count) {
List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, JdbcConstants.MYSQL);
SQLSelectStatement sqlSelectStatement = (SQLSelectStatement) stmtList.get(0);
SQLSelectQuery sqlSelectQuery = sqlSelectStatement.getSelect().getQuery();
MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
sqlSelectStatement.accept(visitor);
if (sqlSelectQuery instanceof SQLSelectQueryBlock) {
SQLSelectQueryBlock sqlSelectQueryBlock = (SQLSelectQueryBlock) sqlSelectQuery;
List<TableStat.Column> cols = new ArrayList<>(visitor.getColumns());
SQLExpr where = sqlSelectQueryBlock.getWhere();
// 获取字段列表
List<SQLSelectItem> selectItems = sqlSelectQueryBlock.getSelectList();
int idx = 0;
boolean hasAggregate = false;
SQLSelectGroupByClause sqlSelectGroupByClause = new SQLSelectGroupByClause();
//判断是否有聚合函数
for (int selectIdx = 0; selectIdx <= selectItems.size() - 1; selectIdx++) {
if (selectItems.get(selectIdx).getExpr() instanceof SQLAggregateExpr) {
hasAggregate = true;
break;
}
}
if (hasAggregate) {
//添加SQLSelectGroupByClause字段
for (int colIndex = 0; colIndex <= cols.size() - 1; colIndex++) {
if (cols.get(colIndex).isSelect()) {
sqlSelectGroupByClause.addItem(SQLUtils.toSQLExpr(cols.get(colIndex).toString(), JdbcConstants.MYSQL));
}
}
}
sqlSelectQueryBlock.setGroupBy(sqlSelectGroupByClause);
if (count >= 1) {
return PagerUtils.limit(sqlSelectStatement.toString(), JdbcConstants.MYSQL, offset, count);
}
return sqlSelectStatement.toString();
}
return "";
}
|
输入sql语句：
1
| SELECT SUM(`address`) `address`,SUM(`address1`) `address1` FROM `demo` WHERE 1 = 1
|
最终结果:
1
2
3
4
| SELECT SUM(`address`) AS `address`, SUM(`address1`) AS `address1`
FROM `demo`
WHERE 1 = 1
GROUP BY address, address1
|
生成分页
1
2
3
4
5
| SELECT SUM(`address`) AS `address`, SUM(`address1`) AS `address1`
FROM `demo`
WHERE 1 = 1
GROUP BY address, address1
LIMIT 1, 100
|
也可以创建一个SQLSelect对象来生成sql语句:
1
2
3
4
5
6
7
| SQLSelect sqlSelect = new SQLSelect();
MySqlSelectQueryBlock sqlSelectQueryBlock = new MySqlSelectQueryBlock();
sqlSelectQueryBlock.addSelectItem(new SQLSelectItem(new SQLIdentifierExpr("col1"))); //查询列名
sqlSelectQueryBlock.addSelectItem(new SQLSelectItem(new SQLIdentifierExpr("col2"))); //查询列名
sqlSelectQueryBlock.setFrom(new SQLExprTableSource(new SQLIdentifierExpr("demo"))); //表名
sqlSelect.setQuery(sqlSelectQueryBlock);
String sql = SQLUtils.toSQLString(sqlSelect);
|
Druid支持多种数据库的sql解析,在JdbcConstants中可以看到
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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
| public interface JdbcConstants {
String JTDS = "jtds";
String MOCK = "mock";
String HSQL = "hsql";
String DB2 = "db2";
String DB2_DRIVER = "com.ibm.db2.jcc.DB2Driver";
String POSTGRESQL = "postgresql";
String POSTGRESQL_DRIVER = "org.postgresql.Driver";
String SYBASE = "sybase";
String SQL_SERVER = "sqlserver";
String SQL_SERVER_DRIVER = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
String SQL_SERVER_DRIVER_SQLJDBC4 = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String SQL_SERVER_DRIVER_JTDS = "net.sourceforge.jtds.jdbc.Driver";
String ORACLE = "oracle";
String ORACLE_DRIVER = "oracle.jdbc.OracleDriver";
String ORACLE_DRIVER2 = "oracle.jdbc.driver.OracleDriver";
String ALI_ORACLE = "AliOracle";
String ALI_ORACLE_DRIVER = "com.alibaba.jdbc.AlibabaDriver";
String MYSQL = "mysql";
String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
String MYSQL_DRIVER_6 = "com.mysql.cj.jdbc.Driver";
String MYSQL_DRIVER_REPLICATE = "com.mysql.jdbc.";
String MARIADB = "mariadb";
String MARIADB_DRIVER = "org.mariadb.jdbc.Driver";
String DERBY = "derby";
String HBASE = "hbase";
String HIVE = "hive";
String HIVE_DRIVER = "org.apache.hive.jdbc.HiveDriver";
String H2 = "h2";
String H2_DRIVER = "org.h2.Driver";
String DM = "dm";
String DM_DRIVER = "dm.jdbc.driver.DmDriver";
String KINGBASE = "kingbase";
String KINGBASE_DRIVER = "com.kingbase.Driver";
String GBASE = "gbase";
String GBASE_DRIVER = "com.gbase.jdbc.Driver";
String XUGU = "xugu";
String XUGU_DRIVER = "com.xugu.cloudjdbc.Driver";
String OCEANBASE = "oceanbase";
String OCEANBASE_DRIVER = "com.mysql.jdbc.Driver";
String INFORMIX = "informix";
/**
* 阿里云odps
*/
String ODPS = "odps";
String ODPS_DRIVER = "com.aliyun.odps.jdbc.OdpsDriver";
String TERADATA = "teradata";
String TERADATA_DRIVER = "com.teradata.jdbc.TeraDriver";
/**
* Log4JDBC
*/
String LOG4JDBC = "log4jdbc";
String LOG4JDBC_DRIVER = "net.sf.log4jdbc.DriverSpy";
String PHOENIX = "phoenix";
String PHOENIX_DRIVER = "org.apache.phoenix.jdbc.PhoenixDriver";
String ENTERPRISEDB = "edb";
String ENTERPRISEDB_DRIVER = "com.edb.Driver";
String KYLIN = "kylin";
String KYLIN_DRIVER = "org.apache.kylin.jdbc.Driver";
String SQLITE = "sqlite";
String SQLITE_DRIVER = "org.sqlite.JDBC";
String ALIYUN_ADS = "aliyun_ads";
String ALIYUN_DRDS = "aliyun_drds";
String PRESTO = "presto";
String PRESTO_DRIVER = "com.facebook.presto.jdbc.PrestoDriver";
String ELASTIC_SEARCH = "elastic_search";
String ELASTIC_SEARCH_DRIVER = "com.alibaba.xdriver.elastic.jdbc.ElasticDriver";
String CLICKHOUSE = "clickhouse";
String CLICKHOUSE_DRIVER = "ru.yandex.clickhouse.ClickHouseDriver";
}
|
github
