龙空技术网

JSQLParse优秀的sql解析工具

回不去的蓝天白云 525

前言:

此刻兄弟们对“sqljava解析”可能比较讲究,同学们都需要剖析一些“sqljava解析”的相关知识。那么小编在网上收集了一些有关“sqljava解析””的相关内容,希望你们能喜欢,看官们一起来了解一下吧!

第一次接触JSQLParse工具是看mybatis-plus分页插件里面用到,在mybatis-generator已经依赖了jsqlparser,所以我们不需要额外引入jar

<dependency>            <groupId>com.baomidou</groupId>            <artifactId>mybatis-plus-generator</artifactId>						<version>3.3.2</verion> </dependency>

如果使用

Select的查询体有两种情况,一种是没有union转化为PlainSelect,另一种有union的会转化为多了Select实体SetOperationList

public String concatOrderBy(String originalSql, IPage<?> page) {        if (CollectionUtils.isNotEmpty(page.orders())) {            try {                List<OrderItem> orderList = page.orders();              	//SQL解析工具,解析select语句,转化为Select实体                Select selectStatement = (Select) CCJSqlParserUtil.parse(originalSql);                if (selectStatement.getSelectBody() instanceof PlainSelect) {                    PlainSelect plainSelect = (PlainSelect) selectStatement.getSelectBody();                    List<OrderByElement> orderByElements = plainSelect.getOrderByElements();                    List<OrderByElement> orderByElementsReturn = addOrderByElements(orderList, orderByElements);                    plainSelect.setOrderByElements(orderByElementsReturn);                    return plainSelect.toString();                } else if (selectStatement.getSelectBody() instanceof SetOperationList) {                    SetOperationList setOperationList = (SetOperationList) selectStatement.getSelectBody();                    List<OrderByElement> orderByElements = setOperationList.getOrderByElements();                    List<OrderByElement> orderByElementsReturn = addOrderByElements(orderList, orderByElements);                    setOperationList.setOrderByElements(orderByElementsReturn);                    return setOperationList.toString();                } else if (selectStatement.getSelectBody() instanceof WithItem) {                    // todo: don't known how to resole                    return originalSql;                } else {                    return originalSql;                }            } catch (JSQLParserException e) {                logger.warn("failed to concat orderBy from IPage, exception=" + e.getMessage());            }        }        return originalSql;    }

获取in的参数:

 @Test    public void testInExpressionProblem() throws JSQLParserException {        final List<Object> exprList = new ArrayList<>();        Select select = (Select) CCJSqlParserUtil.parse("select * from foo where x in (?,?,?)");        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();        Expression where = plainSelect.getWhere();        where.accept(new ExpressionVisitorAdapter() {            @Override            public void visit(InExpression expr) {                super.visit(expr);                exprList.add(expr.getLeftExpression());                exprList.add(expr.getRightItemsList());            }        });        System.out.println("exprList = " + exprList);    }        输入结果:    exprList = [x, (?, ?, ?)]
@Testpublic void testInExpression() throws JSQLParserException {    final List<Object> exprList = new ArrayList<>();    Select select = (Select) CCJSqlParserUtil.            parse("select * from foo where (a,b) in (select a,b from foo2)");    PlainSelect plainSelect = (PlainSelect) select.getSelectBody();    Expression where = plainSelect.getWhere();    where.accept(new ExpressionVisitorAdapter() {        @Override        public void visit(InExpression expr) {            super.visit(expr);            exprList.add(expr.getLeftExpression());            exprList.add(expr.getLeftItemsList());            exprList.add(expr.getRightItemsList());        }    });    System.out.println("exprList = " + exprList);}输入结果:exprList = [null, (a, b), (SELECT a, b FROM foo2)]

获取所有表:

@Testpublic void tableSetDatabaseIssue812() throws JSQLParserException {    String sql = "SELECT * FROM MY_TABLE1 as T1, MY_TABLE2, (SELECT * FROM MY_DB.TABLE3) LEFT OUTER JOIN MY_TABLE4 "            + " WHERE ID = (SELECT MAX(ID) FROM MY_TABLE5) AND ID2 IN (SELECT * FROM MY_TABLE6)";    Select select = (Select) CCJSqlParserUtil.parse(sql);    StringBuilder buffer = new StringBuilder();    ExpressionDeParser expressionDeParser = new ExpressionDeParser();    SelectDeParser deparser = new SelectDeParser(expressionDeParser, buffer) {        @Override        public void visit(Table tableName) {            System.out.println(tableName);        }    };    deparser.visit((PlainSelect) select.getSelectBody());}输入结果:MY_TABLE1 AS T1MY_TABLE2MY_DB.TABLE3MY_TABLE4

获取union语句相关所有表:

@Testpublic void testUnion() throws JSQLParserException {    String statement = "SELECT * FROM mytable WHERE mytable.col = 9 UNION "            + "SELECT * FROM mytable3 WHERE mytable3.col = ? UNION " + "SELECT * FROM mytable2 LIMIT 3, 4";    Select select = (Select) parserManager.parse(new StringReader(statement));    SetOperationList setList = (SetOperationList) select.getSelectBody();    System.out.println("setList = " + setList);    List<SelectBody> selectBodyList = setList.getSelects();    for (SelectBody body : selectBodyList) {        PlainSelect ps = (PlainSelect) body;        System.out.println("ps = " + ps.getFromItem());    }}输入结果:setList = SELECT * FROM mytable WHERE mytable.col = 9 UNION SELECT * FROM mytable3 WHERE mytable3.col = ? UNION SELECT * FROM mytable2 LIMIT 3, 4ps = mytableps = mytable3ps = mytable2

获取where条件以及动态添加条件:

@Testpublic void testWhere() throws JSQLParserException {    String statement = "SELECT * FROM tab1 t WHERE t.name = '张三' ";    Statement parsed = parserManager.parse(new StringReader(statement));    Select select = (Select) parsed;    if (select.getSelectBody() instanceof PlainSelect) {        PlainSelect plainSelect = (PlainSelect) select.getSelectBody();        //获取where条件        Expression where = plainSelect.getWhere();        System.out.println("where = " + where);        //表别名        String tableAlias = null;        FromItem fromItem = plainSelect.getFromItem();        if (fromItem instanceof Table) {            Table table = (Table) fromItem;            //表名称            String fullyQualifiedName = table.getFullyQualifiedName();            System.out.println("fullyQualifiedName = " + fullyQualifiedName);            if (table.getAlias() != null) {                tableAlias = table.getAlias().getName();                System.out.println("tableAlias = " + tableAlias);            }        }        //动态添加where条件,如果表有别名则加上        String addWhereSql = "age = 18";        if (tableAlias != null) {            addWhereSql = tableAlias + "." + addWhereSql;        }        Expression whereExpression = CCJSqlParserUtil.parseCondExpression(addWhereSql);        plainSelect.setWhere(new AndExpression(where,whereExpression));        //最终sql结果如下:        System.out.println("plainSelect = " + plainSelect);    }}输入结果:where = t.name = '张三'fullyQualifiedName = tab1tableAlias = tplainSelect = SELECT * FROM tab1 t WHERE t.name = '张三' AND t.age = 18

以上只是常用的一小部分,大家可以尝试者自己写一些,除了获取Select还可以获取Insert、update等语句

标签: #sqljava解析