盘古低代码SQL解释器(JSqlParser)
概述
JSqlParser能够解析SQL语句并翻译成一个Java类层次结构。它具有扩展的SQL语法，不仅限于一个数据库，而且还支持Oracle，SqlServer，MySQL，PostgreSQL等许多数据库。
适用范围
Pangea v2.0.2+
快速上手
1、添加依赖
JSqlParser的依赖已经添加到了盘古common包中，使用时，引入common包即可。
html
<!--maven依赖 -->
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>3.2</version>
</dependency>2、使用方法
解析SQL语句前需要先创建数据库操作类。CCJSqlParserManager类是用于创建对数据库操作接口类Statement。 Statement包含了对数据库的查询、插入、修改等多种操作。 此外，JSqlParser中也提供了工具类CCJSqlParserUtil，通过parse方法，也可以创建Statement类。 下面介绍几种使用方式
▪️查询字段
html
private static List<String> test_select_items(String sql) throws JSQLParserException {
CCJSqlParserManager parserManager = new CCJSqlParserManager();
Select select = (Select) CCJSqlParserUtil.parse(new StringReader(sql));
PlainSelect plain = (PlainSelect) select.getSelectBody();
List<SelectItem> selectitems = plain.getSelectItems();
List<String> str_items = new ArrayList<String>();
if (selectitems != null) {
for (SelectItem selectitem : selectitems) {
str_items.add(selectitem.toString());
}
}
return str_items;
}▪️查询 join
html
private static List<String> test_select_join(String sql) throws JSQLParserException {
Statement statement = CCJSqlParserUtil.parse(sql);
Select selectStatement = (Select) statement;
PlainSelect plain = (PlainSelect) selectStatement.getSelectBody();
List<Join> joinList = plain.getJoins();
List<String> tablewithjoin = new ArrayList<String>();
if (joinList != null) {
for (Join join : joinList) {
join.setLeft(false);
tablewithjoin.add(join.toString());
//注意 ， leftjoin rightjoin 等等的to string()区别
}
}
return tablewithjoin;
}▪️查询表名 table
html
private static List<String> test_select_table(String sql) throws JSQLParserException {
Statement statement = CCJSqlParserUtil.parse(sql);
Select selectStatement = (Select) statement;
TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
return tablesNamesFinder.getTableList(selectStatement);
}▪️查询 where
html
private static String test_select_where(String sql) throws JSQLParserException {
CCJSqlParserManager parserManager = new CCJSqlParserManager();
Select select = (Select) parserManager.parse(new StringReader(sql));
PlainSelect plain = (PlainSelect) select.getSelectBody();
Expression where_expression = plain.getWhere();
return where_expression.toString();
}▪️查询 group by
html
private static List<String> test_select_groupby(String sql) throws JSQLParserException {
CCJSqlParserManager parserManager = new CCJSqlParserManager();
Select select = (Select) parserManager.parse(new StringReader(sql));
PlainSelect plain = (PlainSelect) select.getSelectBody();
List<Expression> GroupByColumnReferences = plain.getGroupByColumnReferences();
List<String> str_groupby = new ArrayList<String>();
if (GroupByColumnReferences != null) {
for (Expression groupByColumnReference : GroupByColumnReferences) {
str_groupby.add(groupByColumnReference.toString());
}
}
return str_groupby;
}▪️查询 order by
html
private static List<String> test_select_orderby(String sql) throws JSQLParserException {
CCJSqlParserManager parserManager = new CCJSqlParserManager();
Select select = (Select) parserManager.parse(new StringReader(sql));
PlainSelect plain = (PlainSelect) select.getSelectBody();
List<OrderByElement> OrderByElements = plain.getOrderByElements();
List<String> str_orderby = new ArrayList<String>();
if (OrderByElements != null) {
for (OrderByElement orderByElement : OrderByElements) {
str_orderby.add(orderByElement.toString());
}
}
return str_orderby;
}▪️查询 子查询
html
private static Map test_select_subselect(SelectBody selectBody) throws JSQLParserException {
Map<String, String> map = new HashMap<String, String>();
if (selectBody instanceof PlainSelect) {
List<SelectItem> selectItems = ((PlainSelect) selectBody).getSelectItems();
for (SelectItem selectItem : selectItems) {
if (selectItem.toString().contains("(") && selectItem.toString().contains(")")) {
map.put("selectItemsSubselect", selectItem.toString());
}
}
Expression where = ((PlainSelect) selectBody).getWhere();
String whereStr = where.toString();
if (whereStr.contains("(") && whereStr.contains(")")) {
int firstIndex = whereStr.indexOf("(");
int lastIndex = whereStr.lastIndexOf(")");
CharSequence charSequence = whereStr.subSequence(firstIndex, lastIndex + 1);
map.put("whereSubselect", charSequence.toString());
}
FromItem fromItem = ((PlainSelect) selectBody).getFromItem();
if (fromItem instanceof SubSelect) {
map.put("fromItemSubselect", fromItem.toString());
}
} else if (selectBody instanceof WithItem) {
SqlParser.test_select_subselect(((WithItem) selectBody).getSelectBody());
}
return map;
}▪️查询多个sql语句
html
String sqls = "SELECT * FROM TABLE1;SELECT * FROM TABLE2";
//方法1
Statements statements = CCJSqlParserUtil.parseStatements(sqls);
//方法2
CCJSqlParser ccjSqlParser = new CCJSqlParser(sqls);
Statements statements = ccjSqlParser.Statements();
List<Statement> statementList = statements.getStatements();▪️表达式解析
html
//表达式
Expression expression = CCJSqlParserUtil.parseExpression("a+b*c");
//条件表达式
Expression expression = CCJSqlParserUtil.parseCondExpression("A='123'");▪将别名应用于所有表达式
html
Select select = (Select) CCJSqlParserUtil.parse("SELECT A,B,C FROM TABLE1");//此处的运行时类是Select
SelectBody selectBody = select.getSelectBody();
AddAliasesVisitor addAliasesVisitor = new AddAliasesVisitor();
addAliasesVisitor.setPrefix("B");//设置前缀（如不进行设置默认为“A”）
selectBody.accept(addAliasesVisitor);
System.out.println(selectBody.toString());//SELECT A AS B1, B AS B2, C AS B3 FROM TABLE1▪向SELECT添加一列或表达式
html
Select select = (Select) CCJSqlParserUtil.parse("SELECT A FROM TABLE1");
SelectUtils.addExpression(select, new Column("B"));
System.out.println(select);//SELECT A, B FROM TABLE1