SQL解释器(JSqlParser)

概述

JSqlParser能够解析SQL语句并翻译成一个Java类层次结构。它具有扩展的SQL语法，不仅限于一个数据库，而且还支持Oracle，SqlServer，MySQL，PostgreSQL等许多数据库。

快速上手

1、添加依赖

JSqlParser的依赖已经添加到了盘古common包中，使用时，引入common包即可。

<!--maven依赖 -->
<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>3.2</version>
</dependency>

2、使用方法

解析SQL语句前需要先创建数据库操作类。CCJSqlParserManager类是用于创建对数据库操作接口类Statement。 Statement包含了对数据库的查询、插入、修改等多种操作。 此外，JSqlParser中也提供了工具类CCJSqlParserUtil，通过parse方法，也可以创建Statement类。 下面介绍几种使用方式
▪️查询字段

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;
}

select
▪️查询 join

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

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

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

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

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;
}

▪️查询 子查询

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语句

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();

▪️表达式解析

//表达式
Expression expression = CCJSqlParserUtil.parseExpression("a+b*c");
//条件表达式
Expression expression = CCJSqlParserUtil.parseCondExpression("A='123'");

▪将别名应用于所有表达式

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添加一列或表达式

Select select = (Select) CCJSqlParserUtil.parse("SELECT A FROM TABLE1");
SelectUtils.addExpression(select, new Column("B"));
System.out.println(select);//SELECT A, B FROM TABLE1