SQL解释器(JSqlParser)

概述

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

适用范围

Pangea v2.0.2+

快速上手

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

▪️查询 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

参考文档

1. 官方文档
2. Java Doc
3. GitHub Wiki
4. JSqlParser-SQL语法解释器
5. JSqlParser示例