【Mysql系列】jsqlparser学习-SQL转换为java类
jsqlparser学习一.主要开源API1.guava下的graph包graph包下的类,解决DAG矢量图问题(算子之间的顺序关系),不是本文重点,主要讲jsqlparser<dependency><groupId>com.google.guava</groupId><artifactId>guava</artifactId><v
💝💝💝欢迎来到我的博客,很高兴能够在这里和您见面!希望您在这里可以感受到一份轻松愉快的氛围,不仅可以获得有趣的内容和知识,也可以畅所欲言、分享您的想法和见解。
- 推荐:kuan 的首页,持续学习,不断总结,共同进步,活到老学到老
- 导航
非常期待和您一起在这个小小的网络世界里共同探索、学习和成长。💝💝💝 ✨✨ 欢迎订阅本专栏 ✨✨
博客目录
一.主要开源 API
SqlParser 是一个 SQL 语句解析器。它将 SQL 转换为 Java 类的可遍历层次结构。
1.guava 下的 graph 包
graph 包下的类,解决 DAG 矢量图问题(算子之间的顺序关系),不是本文重点,主要讲 jsqlparser
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<version>31.0.1-jre</version>
</dependency>
package com.google.common.graph;
2.jsqlparser
算子 sql 解析与生成
jsqlparser-4.2.jar
二.DDL 算子实现过程
思路
需求: DDL 算子的目的是通过一些特殊的操作,最终生成一个新的 sql
jsqlparser 功能:每一个 sql 都可以用 jsqlparser 进行解析
基于以上两点,我们在开发每一个 DDL 算子的时候,可以写一些简单的 sql,比如筛选算子,其功能就是对已知的表或者结果集添加 where 条件,筛选得到我们想要的结果,我们可以写个简单的 sql,比如:
select id,name from city where name='张三'
假设这条 sql 就是我们的筛选算子最终得到的结果,那么如何通过 jsqlparser 生成这样的一条 sql 呢?
我们可以把这条 sql 放在测试类,通过 jsqlparser 相关的 api 得到这条 sql 的对象信息
@Test
public void test() throws Exception {
String sql = "select id,name from city where name='张三'";
Select select = (Select) CCJSqlParserUtil.parse(sql);
PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
}
该对象包含的信息如图所示
- 查询的列 id name
- where
- join
- group by
- order by
- 等…
我们可以通过测试类的结果,反向推出筛选算子的具体实现(实现的时候注意兼容性和扩展性,比如两表 join 是多表 join 的特例,要以多表 join 的实现逻辑来实现)
//获取前置节点的sql
PlainSelect preSql = prePlainSelect();
PlainSelect newSql = new PlainSelect();
String tableAlias = tableAlias();
//设置别名
SubSelect subSelect = new SubSelect();
subSelect.setSelectBody(preSql);
subSelect.setUseBrackets(true);
subSelect.setAlias(new Alias(tableAlias));
newSql.setFromItem(subSelect);
.............
其他算子的实现同筛选算子这个例子,因为我们第一次接触到 jsqlparser 的时候,并不是特别熟悉 jsqlparser 的对象的结构,我们可以通过写测试类先用 jsqlparser 解析,然后再反向写出 jsqlparser 生成 sql 对象的过程,每一个算子的实现,都会对 jsqlparser 的 api 有一个更深入的认识
按照上面这个思路,我们可以支持很多单个算子的实现,那么 jsqlparser 能否实现俄罗斯套娃呢?
答案是肯定的,前提条件是每个单独算子的健壮性和可扩展性,如果能保证单独算子的健壮性和可扩展性,无论算子之间如何嵌套,都是能够支持的.
三.解决算子 bug 思路
- 先通过传入的节点 id,定位到是哪个算子出错
- 然后先看下这个错误的 sql 是什么样的,在日志中有打印
- 再思考下正确的 sql 应该怎么写
- debug 修改下算子的实现,over
- 利用这个思路解决算子相关的 bug 会非常快,如果慢只是因为没有用 jsqlparser 拼过 sql,不熟悉对象结构
四.关于 jsqlparser 的扩展
目前代码中只用到了一处扩展,就是在 where 条件的时候,如果 or 和 and 同时存在,如何给 or 和 and 条件添加自定义括号呢?这个在 jsqlparser 中是没有 API 的,但我们可以对 jsqlparser 进行扩展
扩展的逻辑非常简单,只需要重写 toString 方法,其实 jsqlparser 最终生成的是一个对象,对象的 toString 方法输出的是一个 String 类型的,在数据库可执行的 sql,基于这个原理,jsqlparser 的每一个组件,如 where,join,group by 等组件的 toString 方法都是通过 java 对象去拼接构造,然后生成我们想要的 String-sql
基于这个简单的扩展,我们可以对其进行更加丰富的扩展,目前还未遇到相关需求
4.1.对 and 进行扩展
package com.deepexi.datasense.ddl.operator.core.extend;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
public class AndExpressionExtend extends AndExpression {
public AndExpressionExtend() {
}
public AndExpressionExtend(Expression leftExpression, Expression rightExpression) {
this.setLeftExpression(leftExpression);
this.setRightExpression(rightExpression);
}
@Override
public String toString() {
return "(" + this.getLeftExpression() + " " + this.getStringExpression() + " " + this.getRightExpression() + ")";
}
}
4.2.对 or 进行扩展
package com.deepexi.datasense.ddl.operator.core.extend;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
public class OrExpressionExtend extends OrExpression {
public OrExpressionExtend() {
}
public OrExpressionExtend(Expression leftExpression, Expression rightExpression) {
this.setLeftExpression(leftExpression);
this.setRightExpression(rightExpression);
}
@Override
public String toString() {
return "(" + this.getLeftExpression() + " " + this.getStringExpression() + " " + this.getRightExpression() + ")";
}
}
五.jsqlparser 是万能的吗?
答案是否定的,jsqlparser 不支持某些数据库的特定的一些函数,比如 clickhouse 的 array join 函数是解析不了的
六.测试类
6.1.简单工具类
/**
* jsqlparser解析SQL工具类
* PlainSelect类不支持union、union all等请使用SetOperationList接口
*/
public class SqlParserTool {
/**
* 由于jsqlparser没有获取SQL类型的原始工具,并且在下面操作时需要知道SQL类型,所以编写此工具方法
*
* @param sql sql语句
* @return sql类型,
* @throws JSQLParserException
*/
public static SqlTypeEnum getSqlType(String sql) throws JSQLParserException {
Statement sqlStmt = CCJSqlParserUtil.parse(new StringReader(sql));
if (sqlStmt instanceof Alter) {
return SqlTypeEnum.ALTER;
} else if (sqlStmt instanceof CreateIndex) {
return SqlTypeEnum.CREATEINDEX;
} else if (sqlStmt instanceof CreateTable) {
return SqlTypeEnum.CREATETABLE;
} else if (sqlStmt instanceof CreateView) {
return SqlTypeEnum.CREATEVIEW;
} else if (sqlStmt instanceof Delete) {
return SqlTypeEnum.DELETE;
} else if (sqlStmt instanceof Drop) {
return SqlTypeEnum.DROP;
} else if (sqlStmt instanceof Execute) {
return SqlTypeEnum.EXECUTE;
} else if (sqlStmt instanceof Insert) {
return SqlTypeEnum.INSERT;
} else if (sqlStmt instanceof Merge) {
return SqlTypeEnum.MERGE;
} else if (sqlStmt instanceof Replace) {
return SqlTypeEnum.REPLACE;
} else if (sqlStmt instanceof Select) {
return SqlTypeEnum.SELECT;
} else if (sqlStmt instanceof Truncate) {
return SqlTypeEnum.TRUNCATE;
} else if (sqlStmt instanceof Update) {
return SqlTypeEnum.UPDATE;
} else if (sqlStmt instanceof Upsert) {
return SqlTypeEnum.UPSERT;
} else {
return SqlTypeEnum.NONE;
}
}
/**
* 获取sql操作接口,与上面类型判断结合使用
* example:
* String sql = "create table a(a string)";
* SqlTypeEnum sqlType = SqlParserTool.getSqlType(sql);
* if(sqlType.equals(SqlTypeEnum.SELECT)){
* Select statement = (Select) SqlParserTool.getStatement(sql);
* }
*
* @param sql
* @return
* @throws JSQLParserException
*/
public static Statement getStatement(String sql) throws JSQLParserException {
Statement sqlStmt = CCJSqlParserUtil.parse(new StringReader(sql));
return sqlStmt;
}
/**
* 获取tables的表名
*
* @param statement
* @return
*/
public static List<String> getTableList(Select statement) {
TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
List<String> tableList = tablesNamesFinder.getTableList(statement);
return tableList;
}
/**
* 获取join层级
*
* @param selectBody
* @return
*/
public static List<Join> getJoins(SelectBody selectBody) {
if (selectBody instanceof PlainSelect) {
List<Join> joins = ((PlainSelect) selectBody).getJoins();
return joins;
}
return new ArrayList<Join>();
}
/**
* @param selectBody
* @return
*/
public static List<Table> getIntoTables(SelectBody selectBody) {
if (selectBody instanceof PlainSelect) {
List<Table> tables = ((PlainSelect) selectBody).getIntoTables();
return tables;
}
return new ArrayList<Table>();
}
/**
* @param selectBody
* @return
*/
public static void setIntoTables(SelectBody selectBody, List<Table> tables) {
if (selectBody instanceof PlainSelect) {
((PlainSelect) selectBody).setIntoTables(tables);
}
}
/**
* 获取limit值
*
* @param selectBody
* @return
*/
public static Limit getLimit(SelectBody selectBody) {
if (selectBody instanceof PlainSelect) {
Limit limit = ((PlainSelect) selectBody).getLimit();
return limit;
}
return null;
}
/**
* 为SQL增加limit值
*
* @param selectBody
* @param l
*/
public static void setLimit(SelectBody selectBody, long l) {
if (selectBody instanceof PlainSelect) {
Limit limit = new Limit();
limit.setRowCount(new LongValue(String.valueOf(l)));
((PlainSelect) selectBody).setLimit(limit);
}
}
/**
* 获取FromItem不支持子查询操作
*
* @param selectBody
* @return
*/
public static FromItem getFromItem(SelectBody selectBody) {
if (selectBody instanceof PlainSelect) {
FromItem fromItem = ((PlainSelect) selectBody).getFromItem();
return fromItem;
} else if (selectBody instanceof WithItem) {
SqlParserTool.getFromItem(((WithItem) selectBody));
}
return null;
}
/**
* 获取子查询
*
* @param selectBody
* @return
*/
public static SubSelect getSubSelect(SelectBody selectBody) {
if (selectBody instanceof PlainSelect) {
FromItem fromItem = ((PlainSelect) selectBody).getFromItem();
if (fromItem instanceof SubSelect) {
return ((SubSelect) fromItem);
}
} else if (selectBody instanceof WithItem) {
SqlParserTool.getSubSelect(((WithItem) selectBody));
}
return null;
}
/**
* 判断是否为多级子查询
*
* @param selectBody
* @return
*/
public static boolean isMultiSubSelect(SelectBody selectBody) {
if (selectBody instanceof PlainSelect) {
FromItem fromItem = ((PlainSelect) selectBody).getFromItem();
if (fromItem instanceof SubSelect) {
SelectBody subBody = ((SubSelect) fromItem).getSelectBody();
if (subBody instanceof PlainSelect) {
FromItem subFromItem = ((PlainSelect) subBody).getFromItem();
if (subFromItem instanceof SubSelect) {
return true;
}
}
}
}
return false;
}
/**
* 获取查询字段
*
* @param selectBody
* @return
*/
public static List<SelectItem> getSelectItems(SelectBody selectBody) {
if (selectBody instanceof PlainSelect) {
List<SelectItem> selectItems = ((PlainSelect) selectBody).getSelectItems();
return selectItems;
}
return null;
}
public static void main(String[] args) throws JSQLParserException {
Statement sqlStmt = CCJSqlParserUtil.parse(new StringReader("show databases"));
}
}
6.2.简单测试
@Slf4j
@SpringBootTest
@RunWith(SpringRunner.class)
public class JsqlparserTest {
@Test
public void testSelect() throws JSQLParserException {
// 使用工具类把SQL转换为Select对象
Select select = (Select) CCJSqlParserUtil.parse("SELECT username,age,sex FROM user");
SelectBody selectBody = select.getSelectBody();
System.err.println(selectBody);
}
}
6.3.测试类
@Slf4j
@SpringBootTest
@RunWith(SpringRunner.class)
public class JsqlparserTest {
/**
* 使用工具类把SQL转换为Select对象
*/
@Test
public void testSelect() throws JSQLParserException {
// 使用工具类把SQL转换为Select对象
Select select = (Select) CCJSqlParserUtil.parse("select username,age,sex from user");
SelectBody selectBody = select.getSelectBody();
System.err.println(selectBody);
}
/**
* 设置别名
*/
@Test
public void testSelectSql() {
String sql = "select username,age,sex from user";
CCJSqlParserManager parserManager = new CCJSqlParserManager();
Select select = null;
try {
select = (Select) parserManager.parse(new StringReader(sql));
} catch (JSQLParserException e) {
e.printStackTrace();
}
SelectBody selectBody = select.getSelectBody();
PlainSelect plainSelect = parseSelectBody(selectBody);
if (Objects.nonNull(plainSelect)) {
FromItem fromItem = plainSelect.getFromItem();
Alias alias = fromItem.getAlias();
if (Objects.isNull(alias)) {
String name = "自定义别名";
fromItem.setAlias(new Alias(name));
List<SelectItem> items = plainSelect.getSelectItems();
for (SelectItem item : items) {
SelectExpressionItem selectExpressionItem = (SelectExpressionItem) item;
Expression expression = selectExpressionItem.getExpression();
Column column = (Column) expression;
Table tableInfo = new Table();
tableInfo.setAlias(new Alias(name));
column.setTable(tableInfo);
}
}
}
log.info(plainSelect.toString());
}
protected static PlainSelect parseSelectBody(SelectBody selectBody) {
if (selectBody instanceof PlainSelect) {
return (PlainSelect) selectBody;
} else {
System.out.println(selectBody.getClass() + ":遇到无法解析的对象:" + Thread.currentThread().getStackTrace()[1].getMethodName());
return null;
}
}
/**
* 不支持union操作
*/
@Test
public void testSelectSqlUnion() throws JSQLParserException {
String sql = "select t1.name from table1 t1 union all select t2.b,t3.c from table2 t2,table3 t3 where t2.b = t3.c";
Statement stmt = CCJSqlParserUtil.parse(sql);
Select select = (Select) stmt;
SelectBody selectBody = select.getSelectBody();
//多select不能直接解析
SetOperationList setOperationList = (SetOperationList) selectBody;
PlainSelect plainSelect = (PlainSelect) selectBody;//抛错
}
/**
* 解析sql
*/
@Test
public void testSelectSql2() throws JSQLParserException {
//只能再包一层
String sql = "select * from (select t1.name from table1 t1 union all select t2.b,t3.c from table2 t2,table3 t3 where t2.b = t3.c)";
Statement stmt = CCJSqlParserUtil.parse(sql);
Select select = (Select) stmt;
SelectBody selectBody = select.getSelectBody();
PlainSelect plainSelect = (PlainSelect) selectBody;
//From分析
FromItem fromItem = plainSelect.getFromItem();
//子查询不能直接获取到tableName
// Table tableFrom = (Table) fromItem; // table.getAlias().getName(), table.getName()
// join分析
List<Join> joins = plainSelect.getJoins();
if (CollectionUtils.isNotEmpty(joins)) {
for (Join join : joins) {
FromItem rightItem = join.getRightItem();
Table table = (Table) rightItem; // (table.getAlias().getName(), table.getName());
// on 条件分析
Expression onExpression = join.getOnExpression(); //和where一样
// where 分析
Expression where = plainSelect.getWhere();
// where 有多种情况,一种就是简单的a = b,
//另外就是 a = b or ... and,
// AndExpression
//OrExpression
//XorExpression
if (where instanceof EqualsTo) {
EqualsTo equalsTo = (EqualsTo) where;
Expression rightExpression = equalsTo.getRightExpression();
Expression leftExpression = equalsTo.getLeftExpression();
if (rightExpression instanceof Column && leftExpression instanceof Column) {
Column rightColumn = (Column) rightExpression;
Column leftColumn = (Column) leftExpression;
System.out.println(rightColumn.getTable().toString() + "表的" + rightColumn.getColumnName() + "字段 -> " + leftColumn.getTable().toString() + "表的" + leftColumn.getColumnName() + "字段");
}
} else if (where instanceof AndExpression) {
AndExpression andExpression = (AndExpression) where;
Expression leftExpression = andExpression.getLeftExpression();
Expression rightExpression = andExpression.getRightExpression();
}
}
}
}
/**
* 测试查询返回增加一列
*/
@Test
public void testAddSelectColumn() throws Exception {
Select select = (Select) CCJSqlParserUtil.parse("select name from user where id = 1");
SelectUtils.addExpression(select, new Column("mail"));
Assert.assertEquals(select.toString(), "SELECT name, mail FROM user WHERE id = 1");
}
/**
* 测试查询语句增加where条件
*/
@Test
public void testAddWhereCondition() throws Exception {
Select select = (Select) CCJSqlParserUtil.parse("select name from user");
PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
if (plainSelect.getWhere() == null) {
EqualsTo equalsTo = new EqualsTo();
equalsTo.setLeftExpression(new Column("id"));
equalsTo.setRightExpression(new LongValue(1000L));
plainSelect.setWhere(equalsTo);
}
Assert.assertEquals(select.toString(), "SELECT name FROM user WHERE id = 1000");
}
/**
* 测试增加where查询条件
*/
@Test
public void testAddCondition() throws Exception {
Select select = (Select) CCJSqlParserUtil.parse("select name from user where id = 1000");
PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
// 原where表达式
Expression where = plainSelect.getWhere();
// 新增的查询条件表达式
EqualsTo equalsTo = new EqualsTo();
equalsTo.setLeftExpression(new Column("name"));
equalsTo.setRightExpression(new StringValue("'张三'"));
// 用and链接条件
AndExpression and = new AndExpression(where, equalsTo);
// 设置新的where条件
plainSelect.setWhere(and);
Assert.assertEquals(select.toString(), "SELECT name FROM user WHERE id = 1000 AND name = '张三'");
}
/**
* 测试null条件
*/
@Test
public void testNullCondition() throws Exception {
Select select = (Select) CCJSqlParserUtil.parse("select name from user where id = 1000");
PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
// 原where表达式
Expression where = plainSelect.getWhere();
// 新增的null判断条件
IsNullExpression isNullExpression = new IsNullExpression();
isNullExpression.setLeftExpression(new Column("name"));
isNullExpression.setNot(true);
// 用and链接条件
AndExpression and = new AndExpression(where, isNullExpression);
// 设置新的where条件
plainSelect.setWhere(and);
Assert.assertEquals(select.toString(), "SELECT name FROM user WHERE id = 1000 AND name IS NOT NULL");
}
/**
* 获取表名
*/
@Test
public void testNullCondition11() throws JSQLParserException {
Statement statement = CCJSqlParserUtil.parse("select * from public.customer union all select * from public.user");
TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
List<String> tableList = tablesNamesFinder.getTableList(statement);
System.out.println(tableList);
if (statement instanceof Commit) {
Commit commit = (Commit) statement;
System.out.println(commit.getClass());
}
}
}
七.源码结构
https://github.com/JSQLParser/JSqlParser
expression
sql 构建相关类
- Alias
- EqualsTo
- InExpression 等
parser
sql 解析相关类
- CCJSqlParser
- CCJSqlParserUtil
schema
数据库 schema 相关的类 ,比如表、列等
- Column
- Database
- MultiPartName
- Sequence
- Server
- Synonym
- Table
statement
封装了数据库操作对象,create、insert、delete、select 等
- alter
- create
- delete
- drop
- execute
- grant
- insert
- merge
- replace
- select
- update
- upsert
util
各种工具类、不同 DB 版本、SQL 标准等处理类
- SelectUtils
- DatabaseType
注意点
- union 操作想得到 PlainSelect 需要自己再包一层 select * from
觉得有用的话点个赞 👍🏻
呗。
❤️❤️❤️本人水平有限,如有纰漏,欢迎各位大佬评论批评指正!😄😄😄
💘💘💘如果觉得这篇文对你有帮助的话,也请给个点赞、收藏下吧,非常感谢!👍 👍 👍
🔥🔥🔥Stay Hungry Stay Foolish 道阻且长,行则将至,让我们一起加油吧!🌙🌙🌙
更多推荐
所有评论(0)