JSqlParser
关于SqlParser引言:
Java 生态中较为流行的 SQL Parser 有以下几种:
- fdb-sql-parser 是 FoundationDB 在被 Apple 收购前开源的 SQL Parser(不支持很复杂的SQL),目前已无人维护。
- jsqlparser 是基于 JavaCC 的开源 SQL Parser,是 General SQL Parser 的 Java 实现版本。
- Apache calcite 是一款开源的动态数据管理框架,它具备 SQL 解析、SQL 校验、查询优化、SQL 生成以及数据连接查询等功能,常用于为大数据工具提供 SQL 能力,例如 Hive、Flink 等。calcite 对标准 SQL 支持良好,但是对传统的关系型数据方言支持度较差。
- alibaba druid 是阿里巴巴开源的一款 JDBC 数据库连接池,但其为监控而生的理念让其天然具有了 SQL Parser 的能力。其自带的 Wall Filer、StatFiler 等都是基于 SQL Parser 解析的 AST。并且支持多种数据库方言。
一.基本介绍
1.定义:JSqlParser是一个SQL语句解析器。它将SQL转换为Java类的可遍历层次结构。
2.工具地址:目前在github上开源。https://github.com/JSQLParser/JSqlParser
3.支持的数据库类型:支持Oracle,SqlServer,MySQL,PostgreSQL等常用数据库。但各种数据库系统的SQL语法都在动态变化,可以解析大部分(不是全部)。Github 官网截图:
4.支持的语法类型:
目前,JSqlParser最新版本为4.6(2023-2-24),支持的语法已在官网上做了全部列举(部分截图展示:)
5.两大作用:解析sql + 生成SQL
二.结构分析
1.总体结构:这里以4.1版本为例进行分析介绍
2.具体包介绍:
-
expression:SQL构建相关类,比如EqualsTo、InExpression等表达式用于构建SQL。
-
parser: SQL解析相关类,比如CCJSqlParserUtil。
-
schema:主要存放数据库schema相关的类 ,比如表、列等。
-
statement:封装了数据库操作对象,create、insert、delete、select等
-
util: 各种工具类、不同DB版本、SQL标准等处理类,如SelectUtils、DatabaseType等。
三.具体使用
1.解析sql
JSqlParser可以解析SQL为JAVA对象,以便于获取SQL中的相关信息或可进行修改。
一般使用CCJSqlParserUtil工具类直接解析SQL;根据SQL类型转换为增删改查对象,再获取或修改对象中相关信息。
增删改查代码示例:
/**
* Insert 解析
*
* @throws JSQLParserException
*/
@Test
public void testInsertParser() throws JSQLParserException {
String insertSql = "INSERT INTO test (c1,c2) VALUES ('001','002')";
Statement statement = CCJSqlParserUtil.parse(insertSql);
if (statement instanceof Insert) {
Insert insert = (Insert) statement;
// 添加新列
insert.addColumns(new Column("c3 "));
// 添加新插入值
ExpressionList expressionList = (ExpressionList) insert.getItemsList();
expressionList.getExpressions().add(new StringValue("003"));
System.err.println(insert); //INSERT INTO test (c1, c2, c3 ) VALUES (001, 002, '003')
}
}
/**
* Update 解析
*
* @throws JSQLParserException
*/
@Test
public void testUpdateParser() throws JSQLParserException {
String updateSql = "UPDATE test SET c1 = '001' WHERE c2 = '003' ";
Statement statement = CCJSqlParserUtil.parse(updateSql);
if (statement instanceof Update) {
Update update = (Update) statement;
Expression where = update.getWhere(); // 获取WHERE表达式
System.err.println(where);
List<Column> columns = update.getColumns(); // 获取修改列
columns.forEach(System.out::println);
}
}
@Test
//解析查询
public void testSelectParser() throws JSQLParserException {
String SQL002 = "SELECT t1.a , t1.b FROM tab1 AS t1 JOIN tab2 t2 ON t1.user_id = t2.user_id"; // 多表SQL
// 1.解析表名
CCJSqlParserManager parserManager = new CCJSqlParserManager();
Statement statement = null; // 解析SQL为Statement对象
statement = parserManager.parse(new StringReader(SQL002));
TablesNamesFinder tablesNamesFinder = new TablesNamesFinder(); // 创建表名发现者对象
List<String> tableNameList = tablesNamesFinder.getTableList(statement); // 获取到表名列表
if (!CollectionUtils.isEmpty(tableNameList)) {
tableNameList.forEach(System.err::println); // 循环打印解析到的表名 tab1 tab2
}
// 2.解析查询元素 列,函数等
Select select = null;
select = (Select) CCJSqlParserUtil.parse(SQL002);
PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
List<SelectItem> selectItems = plainSelect.getSelectItems();
selectItems.forEach(System.err::println); // t1.a , t1.b
// 3.解析WHERE条件
String SQL_WHERE = "SELECT * FROM tableName WHERE ID = 8";
PlainSelect plainSelectWhere = null;
plainSelectWhere=(PlainSelect)((Select)CCJSqlParserUtil.parse(SQL_WHERE)).getSelectBody();
EqualsTo equalsTo = (EqualsTo) plainSelectWhere.getWhere();
Expression leftExpression = equalsTo.getLeftExpression();
Expression rightExpression = equalsTo.getRightExpression();
System.err.println(leftExpression); // ID
System.err.println(rightExpression); // 8
// 4.解析Join
List<Join> joins = plainSelect.getJoins();
joins.forEach(e -> {
Expression onExpression = e.getOnExpression();
System.err.println(onExpression); // 获取ON 表达式 t1.user_id = t2.user_id
});
// 5.解析IN
String SQL_IN = "SELECT * FROM tableName WHERE ID IN (8,9,10)";
PlainSelect plainSelectIn = (PlainSelect) ((Select) CCJSqlParserUtil.parse(SQL_IN)).getSelectBody();
InExpression inExpression = (InExpression) plainSelectIn.getWhere();
Expression leftExpression1 = inExpression.getLeftExpression();
ItemsList rightItemsList = inExpression.getRightItemsList();
System.err.println(leftExpression1);
System.err.println(rightItemsList); // (8, 9, 10)
}
2.构建sql
即可以通过Java代码进行SQL构建。
示例:
/**
* 构建插入语句
*/
@Test
public void buildInsertSql() {
// 创建表对象设置表名
Table table = new Table();
table.setName("table");
// 创建插入对象
Insert insert = new Insert();
insert.setTable(table); // 设置插入对象的表对象
// 设置插入列
List<Column> columnList = Arrays.asList(new Column("col1"), new Column("col2"));
insert.setColumns(columnList);
// 设置插入值
MultiExpressionList multiExpressionList = new MultiExpressionList();
multiExpressionList.addExpressionList(Arrays.asList(new StringValue("1"), new StringValue("2")));
insert.setItemsList(multiExpressionList);
System.err.println(insert); // INSERT INTO table (col1, col2) VALUES ('1', '2')
}
/**
* 单表SQL查询
*
* @throws JSQLParserException
*/
@Test
public void testSelectOneTable() throws JSQLParserException {
// 单表全量
Table table = new Table("test");
Select select = SelectUtils.buildSelectFromTable(table);
System.err.println(select); // SELECT * FROM test
// 指定列查询
Select buildSelectFromTableAndExpressions = SelectUtils.buildSelectFromTableAndExpressions(new Table("test"), new Column("col1"), new Column("col2"));
System.err.println(buildSelectFromTableAndExpressions); // SELECT col1, col2 FROM test
// WHERE =
EqualsTo equalsTo = new EqualsTo(); // 等于表达式
equalsTo.setLeftExpression(new Column(table, "user_id")); // 设置表达式左边值
equalsTo.setRightExpression(new StringValue("123456"));// 设置表达式右边值
PlainSelect plainSelect = (PlainSelect) select.getSelectBody(); // 转换为更细化的Select对象
plainSelect.setWhere(equalsTo);
System.err.println(plainSelect);// SELECT * FROM test WHERE test.user_id = '123456'
// WHERE != <>
NotEqualsTo notEqualsTo = new NotEqualsTo();
notEqualsTo.setLeftExpression(new Column(table, "user_id")); // 设置表达式左边值
notEqualsTo.setRightExpression(new StringValue("123456"));// 设置表达式右边值
PlainSelect plainSelectNot = (PlainSelect) select.getSelectBody();
plainSelectNot.setWhere(notEqualsTo);
System.err.println(plainSelectNot);// SELECT * FROM test WHERE test.user_id <> '123456'
// 其他运算符, 参考上面代码添加表达式即可
GreaterThan gt = new GreaterThan(); // ">"
GreaterThanEquals geq = new GreaterThanEquals(); // ">="
MinorThan mt = new MinorThan(); // "<"
MinorThanEquals leq = new MinorThanEquals();// "<="
IsNullExpression isNull = new IsNullExpression(); // "is null"
isNull.setNot(true);// "is not null"
LikeExpression nlike = new LikeExpression();
nlike.setNot(true); // "not like"
Between bt = new Between();
bt.setNot(true);// "not between"
// WHERE LIKE
LikeExpression likeExpression = new LikeExpression(); // 创建Like表达式对象
likeExpression.setLeftExpression(new Column("username")); // 表达式左边
likeExpression.setRightExpression(new StringValue("张%")); // 右边表达式
PlainSelect plainSelectLike = (PlainSelect) select.getSelectBody();
plainSelectLike.setWhere(likeExpression);
System.err.println(plainSelectLike); // SELECT * FROM test WHERE username LIKE '张%'
// WHERE IN
Set<String> deptIds = Sets.newLinkedHashSet(); // 创建IN范围的元素集合
deptIds.add("0001");
deptIds.add("0002");
ItemsList itemsList = new ExpressionList(deptIds.stream().map(StringValue::new).collect(Collectors.toList())); // 把集合转变为JSQLParser需要的元素列表
InExpression inExpression = new InExpression(new Column("dept_id "), itemsList); // 创建IN表达式对象,传入列名及IN范围列表
PlainSelect plainSelectIn = (PlainSelect) select.getSelectBody();
plainSelectIn.setWhere(inExpression);
System.err.println(plainSelectIn); // SELECT * FROM test WHERE dept_id IN ('0001', '0002')
// WHERE BETWEEN AND
Between between = new Between();
between.setBetweenExpressionStart(new LongValue(18)); // 设置起点值
between.setBetweenExpressionEnd(new LongValue(30)); // 设置终点值
between.setLeftExpression(new Column("age")); // 设置左边的表达式,一般为列
PlainSelect plainSelectBetween = (PlainSelect) select.getSelectBody();
plainSelectBetween.setWhere(between);
System.err.println(plainSelectBetween); // SELECT * FROM test WHERE age BETWEEN 18 AND 30
// WHERE AND 多个条件结合,都需要成立
AndExpression andExpression = new AndExpression(); // AND 表达式
andExpression.setLeftExpression(equalsTo); // AND 左边表达式
andExpression.setRightExpression(between); // AND 右边表达式
PlainSelect plainSelectAnd = (PlainSelect) select.getSelectBody();
plainSelectAnd.setWhere(andExpression);
System.err.println(plainSelectAnd); // SELECT * FROM test WHERE test.user_id = '123456' AND age BETWEEN 18 AND 30
// WHERE OR 多个条件满足一个条件成立返回
OrExpression orExpression = new OrExpression();// OR 表达式
orExpression.setLeftExpression(equalsTo); // OR 左边表达式
orExpression.setRightExpression(between); // OR 右边表达式
PlainSelect plainSelectOr = (PlainSelect) select.getSelectBody();
plainSelectOr.setWhere(orExpression);
System.err.println(plainSelectOr); // SELECT * FROM test WHERE test.user_id = '123456' OR age BETWEEN 18 AND 30
// ORDER BY 排序
OrderByElement orderByElement = new OrderByElement(); // 创建排序对象
orderByElement.isAsc(); // 设置升序排列 从小到大
orderByElement.setExpression(new Column("col01")); // 设置排序字段
PlainSelect plainSelectOrderBy = (PlainSelect) select.getSelectBody();
plainSelectOrderBy.addOrderByElements(orderByElement);
System.err.println(plainSelectOrderBy); // SELECT * FROM test WHERE test.user_id = '123456' OR age BETWEEN 18 AND 30 ORDER BY col01
}