Apache Calcite进行SQL解析(java代码实例)
作者:追求无悔 时间:2023-06-26 23:11:59
背景
当一个项目分了很多模块,很多个服务的时候,一些公共的配置就需要统一管理了,于是就有了元数据驱动!
简介
什么是Calcite?
是一款开源SQL解析工具, 可以将各种SQL语句解析成抽象语法树AST(Abstract Syntax Tree), 之后通过操作AST就可以把SQL中所要表达的算法与关系体现在具体代码之中。
Calcite能做啥?
SQL 解析
SQL 校验
查询优化
SQL 生成器
数据连接
实例
今天主要是贴出一个java代码实例,实现了:解析SQL语句中的表名
上代码:
SQL语句转化:
public static SqlNode parseStatement(String sql) {
SqlParser parser = SqlParser.create(sql, config.getParserConfig());
try {
return parser.parseQuery();
} catch (Exception e) {
e.printStackTrace();
throw new UnsupportedOperationException("operation not allowed");
}
}
解析Select中的表名:
private static Set<String> extractSourceTableInSelectSql(SqlNode sqlNode, boolean fromOrJoin) {
if (sqlNode == null) {
return new HashSet<>();
}
final SqlKind sqlKind = sqlNode.getKind();
if (SqlKind.SELECT.equals(sqlKind)) {
SqlSelect selectNode = (SqlSelect) sqlNode;
Set<String> selectList = new HashSet<>(extractSourceTableInSelectSql(selectNode.getFrom(), true));
selectNode.getSelectList().getList().stream().filter(node -> node instanceof SqlCall)
.forEach(node -> selectList.addAll(extractSourceTableInSelectSql(node, false)));
selectList.addAll(extractSourceTableInSelectSql(selectNode.getWhere(), false));
selectList.addAll(extractSourceTableInSelectSql(selectNode.getHaving(), false));
return selectList;
if (SqlKind.JOIN.equals(sqlKind)) {
SqlJoin sqlJoin = (SqlJoin) sqlNode;
Set<String> joinList = new HashSet<>();
joinList.addAll(extractSourceTableInSelectSql(sqlJoin.getLeft(), true));
joinList.addAll(extractSourceTableInSelectSql(sqlJoin.getRight(), true));
return joinList;
if (SqlKind.AS.equals(sqlKind)) {
SqlCall sqlCall = (SqlCall) sqlNode;
return extractSourceTableInSelectSql(sqlCall.getOperandList().get(0), fromOrJoin);
if (SqlKind.IDENTIFIER.equals(sqlKind)) {
Set<String> identifierList = new HashSet<>();
if (fromOrJoin) {
SqlIdentifier sqlIdentifier = (SqlIdentifier) sqlNode;
identifierList.add(sqlIdentifier.toString());
}
return identifierList;
Set<String> defaultList = new HashSet<>();
if (sqlNode instanceof SqlCall) {
SqlCall call = (SqlCall) sqlNode;
call.getOperandList()
.forEach(node -> defaultList.addAll(extractSourceTableInSelectSql(node, false)));
return defaultList;
}
解析Insert语句中的表名:
private static Set<String> extractSourceTableInInsertSql(SqlNode sqlNode, boolean fromOrJoin) {
SqlInsert sqlInsert = (SqlInsert) sqlNode;
Set<String> insertList = new HashSet<>(extractSourceTableInSelectSql(sqlInsert.getSource(), false));
final SqlNode targetTable = sqlInsert.getTargetTable();
if (targetTable instanceof SqlIdentifier) {
insertList.add(((SqlIdentifier) targetTable).toString());
}
return insertList;
}
执行效果
private static final String sql0 = "SELECT MIN(relation_id) FROM tableA JOIN TableB GROUP BY account_instance_id, follow_account_instance_id HAVING COUNT(*)>1";
private static final String sql1 = "SELECT * FROM blog_user_relation a WHERE (a.account_instance_id,a.follow_account_instance_id) IN (SELECT account_instance_id,follow_account_instance_id FROM Blogs_info GROUP BY account_instance_id, follow_account_instance_id HAVING COUNT(*) > 1)";
private static final String sql2 = "select name from (select * from student)";
private static final String sql3 = "SELECT * FROM Student LEFT JOIN Grade ON Student.sID = Grade.gID\n" +
"UNION\n" +
"SELECT * FROM Student RIGHT JOIN Grade ON Student.sID = Grade.gID";
private static final String sql4 = "SELECT *\n" +
"FROM teacher\n" +
"WHERE birth = (SELECT MIN(birth)\n" +
" FROM employee)";
private static final String sql5 = "SELECT sName\n" +
"FROM Student\n" +
"WHERE '450' NOT IN (SELECT courseID\n" +
" FROM Course\n" +
" WHERE sID = Student.sID)";
final SqlNode sqlNode0 = parseStatement(sql0);
System.out.println("sqlNode0: " + extractSourceTableInSelectSql(sqlNode0, false));
结果为:
来源:https://blog.51cto.com/noregrets/4960484
标签:Apache,Calcite,解析
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
WPF仿LiveCharts实现饼图的绘制
2022-02-08 02:17:05
![](https://img.aspxhome.com/file/2023/8/129758_0s.gif)
C#基于TimeSpan实现倒计时效果的方法
2021-10-19 00:06:04
![](https://img.aspxhome.com/file/2023/6/98296_0s.png)
基于android中读取assets目录下a.txt文件并进行解析的深入分析
2023-06-13 17:32:34
C#中调用命令行cmd开启wifi热点的实例代码
2023-11-28 03:15:04
![](https://img.aspxhome.com/file/2023/0/96350_0s.jpg)
Winform实现将网页生成图片的方法
2022-09-06 13:39:31
![](https://img.aspxhome.com/file/2023/8/76388_0s.jpg)
C#中WebBroeser控件用法实例教程
2021-09-14 18:11:05
C#使用NPOI实现Excel和DataTable的互转
2022-12-06 22:51:05
![](https://img.aspxhome.com/file/2023/6/87026_0s.jpg)
Springboot如何使用mybatis实现拦截SQL分页
2021-08-15 13:16:56
Java流处理stream使用详解
2021-09-17 18:27:58
SpringBoot配置Profile实现多环境支持
2023-07-29 21:53:20
![](https://img.aspxhome.com/file/2023/9/85419_0s.png)
Java编程探索之泛型擦除实例解析
2022-08-30 02:13:35
详解C语言内核字符串拷贝与比较
2023-11-02 15:22:30
![](https://img.aspxhome.com/file/2023/2/107392_0s.png)
WPF实现列表分页控件的示例代码
2023-12-25 06:46:02
![](https://img.aspxhome.com/file/2023/7/92977_0s.png)
c# 委托的常见用法
2021-08-11 13:44:05
GraalVM native-image编译后quarkus的超音速启动
2022-08-09 01:17:27
![](https://img.aspxhome.com/file/2023/1/122331_0s.png)
利用枚举法求直方图中最大矩形面积的方法实例
2022-06-22 21:16:31
Android自定义控制条效果
2023-01-23 23:59:12
![](https://img.aspxhome.com/file/2023/1/106051_0s.jpg)
SpringBoot使用Atomikos技术整合多数据源的实现
2021-11-08 23:46:23
SpringBoot通过自定义注解实现参数校验
2023-09-21 21:11:02
![](https://img.aspxhome.com/file/2023/3/58343_0s.png)
Android编程获取网络时间实例分析
2022-08-05 16:28:01