JSqlParser简单使用小记
项目计划采用数据权限控制，拟采用mybatis加JSqlParser做sql拦截和处理，因此研究了一下JSqlParser的使用，如下：
1. select语句查询字段
public static List<String> test_select_items(String sql) throws JSQLParserException { CCJSqlParserManager parserManager = new CCJSqlParserManager(); Select select = (Select) parserManager.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 (int i = 0; i < selectitems.size(); i++) { str_items.add(selectitems.get(i).toString()); } } return str_items; }
2.获取表名，注：调用此方法，显示的表名为去重后的表名
public static List<String> test_select_table(String sql) throws JSQLParserException { Statement statement = (Statement) CCJSqlParserUtil.parse(sql); Select selectStatement = (Select) statement;
//获取表名 TablesNamesFinder tablesNamesFinder = new TablesNamesFinder(); List<String> tableList = tablesNamesFinder .getTableList(selectStatement); return tableList; }
3. 获取关联关系
public static List<String> test_select_join(String sql) throws JSQLParserException { Statement 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) {
//如果关联后面是子查询，可以通过便利join集合，获取FromItem rightItem = join.getRightItem();
//判断fromItem属于哪种类型，如果是subSelect类型就是子查询
for (int i = 0; i < joinList.size(); i++) {
tablewithjoin.add(joinList.get(i).toString());
//注意 ， leftjoin rightjoin 等等的to string()区别
}
}
return tablewithjoin;
}
4. 查询where
public static String test_select_where(String sql) throws JSQLParserException { String columnNames = null; String allColumnNames = null; CCJSqlParserManager parserManager = new CCJSqlParserManager(); Select select = (Select) parserManager.parse(new StringReader(sql)); PlainSelect plain = (PlainSelect) select.getSelectBody(); Expression where_expression = plain.getWhere(); String str = null; if (where_expression != null) { /*str = where_expression.toString(); ExpressionVisitorImpl expressionVisitor = new ExpressionVisitorImpl(); where_expression.accept(expressionVisitor);*/ Set<String> sets = getParser(where_expression); StringBuffer st = new StringBuffer(); sets.stream().forEach(set -> { st.append(set + ","); }); } return str; }
private static Set<String> getParser(Expression expression) {
//初始化接受获得的字段信息
if (expression instanceof BinaryExpression) {
//获得左边表达式
Expression leftExpression = ((BinaryExpression) expression).getLeftExpression();
//获得左边表达式为Column对象，则直接获得列名
if (leftExpression instanceof Column) {
columnName = ((Column) leftExpression).getColumnName();
set.add(columnName);
} else if (leftExpression instanceof InExpression) {
parserInExpression(leftExpression);
} else if (leftExpression instanceof IsNullExpression) {
parserIsNullExpression(leftExpression);
} else if (leftExpression instanceof BinaryExpression) {//递归调用
getParser(leftExpression);
} else if (expression instanceof Parenthesis) {//递归调用
Expression expression1 = ((Parenthesis) expression).getExpression();
getParser(expression1);
}
//获得右边表达式，并分解
Expression rightExpression = ((BinaryExpression) expression).getRightExpression();
if (rightExpression instanceof BinaryExpression) {
parserBinaryExpression(rightExpression);
} else if (rightExpression instanceof InExpression) {
parserInExpression(rightExpression);
} else if (rightExpression instanceof IsNullExpression) {
parserIsNullExpression(rightExpression);
} else if (rightExpression instanceof Parenthesis) {//递归调用
Expression expression1 = ((Parenthesis) rightExpression).getExpression();
getParser(expression1);
}
} else if (expression instanceof InExpression) {
parserInExpression(expression);
} else if (expression instanceof IsNullExpression) {
parserIsNullExpression(expression);
} else if (expression instanceof Parenthesis) {//递归调用
Expression expression1 = ((Parenthesis) expression).getExpression();
getParser(expression1);
}
return set;
}
/**
* 解析in关键字左边的条件
*
* @param expression
*/
public static void parserInExpression(Expression expression) {
Expression leftExpression = ((InExpression) expression).getLeftExpression();
if (leftExpression instanceof Column) {
columnName = ((Column) leftExpression).getColumnName();
set.add(columnName);
}
}
/**
* 解析is null 和 is not null关键字左边的条件
*
* @param expression
*/
public static void parserIsNullExpression(Expression expression) {
Expression leftExpression = ((IsNullExpression) expression).getLeftExpression();
if (leftExpression instanceof Column) {
columnName = ((Column) leftExpression).getColumnName();
set.add(columnName);
}
}
public static void parserBinaryExpression(Expression expression) {
Expression leftExpression = ((BinaryExpression) expression).getLeftExpression();
if (leftExpression instanceof Column) {
columnName = ((Column) leftExpression).getColumnName();
set.add(columnName);
}
}
5. 获取from ，如果from关键字后面是子查询语句，则递归调用原处理语句
private static String test_select_from(String sql) throws JSQLParserException { CCJSqlParserManager parserManager = new CCJSqlParserManager(); Select select = (Select) parserManager.parse(new StringReader(sql)); // 访问from PlainSelect plain = (PlainSelect) select.getSelectBody(); // 访问from FromItem fromItem = plain.getFromItem(); if (fromItem instanceof SubSelect) { System.out.println("-----------------子查询开始-----------------"); SelectBody selectBody = ((SubSelect) fromItem).getSelectBody(); System.out.println("子查询"+selectBody.toString()); test_select(selectBody.toString()); System.out.println("-----------------子查询结束-----------------"); } return fromItem.toString(); }
6.整体执行语句如下：
public static void test_select(String sql) throws JSQLParserException { // *********select body items内容 List<String> str_items = Prasing_Test.test_select_items(sql); System.out.println("查询字段为："+str_items.toString()); // **********select table List<String> tableList = Prasing_Test.test_select_table(sql); System.out.println("表名为："+tableList.toString()); String fromTable = Prasing_Test.test_select_from(sql); System.out.println("from 表名："+fromTable); // **********select table with join List<String> tablewithjoin = Prasing_Test.test_select_join(sql); for (String join:tablewithjoin) { System.out.println("连接方式为："+join); } // // *******select where String str = Prasing_Test.test_select_where(sql); System.out.println("where 条件"+str); // // ******select group by List<String> str_groupby = Prasing_Test.test_select_groupby(sql); System.out.println("group by 字段为："+str_groupby.toString()); // //**************select order by List<String> str_orderby = Prasing_Test.test_select_orderby(sql); System.out.println("order by 条件为："+str_orderby.toString()); }
后续补充。
