前言:
此刻兄弟们对“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解析