一.简单介绍与使用
简介
SqlParser是一个SQL语句解析器。它将SQL转换为Java类的可遍历层次结构。
引入依赖
<dependency>
<groupId>com.github.jsqlparser</groupId>
<artifactId>jsqlparser</artifactId>
<version>4.2</version>
</dependency>
测试类
@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);
}
}
源码结构
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
简单工具类
/**
* 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"));
}
}
测试类
@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());
}
}
}
