druid的SQLParser解析HQL语句
http://my.oschina.net/taaaa/blog/74657
让druid的SQLParser解析HQL语句,生成SELECT ……FROM…..WHERE ……的形式,闲话少说,直接上例子:
让druid的SQLParser解析HQL语句,生成SELECT ……FROM…..WHERE ……的形式,闲话少说,直接上例子:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
Java代码 package com.alibaba.druid; import java.util.List; import com.alibaba.druid.sql.SQLUtils; import com.alibaba.druid.sql.ast.SQLExpr; import com.alibaba.druid.sql.ast.SQLStatement; import com.alibaba.druid.sql.ast.statement.SQLSelect; import com.alibaba.druid.sql.ast.statement.SQLSelectItem; import com.alibaba.druid.sql.ast.statement.SQLSelectQuery; import com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock; import com.alibaba.druid.sql.ast.statement.SQLSelectStatement; import com.alibaba.druid.sql.parser.SQLExprParser; import com.alibaba.druid.sql.parser.SQLParserUtils; import com.alibaba.druid.sql.parser.SQLSelectParser; import com.alibaba.druid.sql.parser.SQLStatementParser; import com.alibaba.druid.sql.visitor.SQLASTOutputVisitor; import com.alibaba.druid.util.JdbcUtils; public class ParseTest { public ParseTest() { // TODO Auto-generated constructor stub } public static void main(String[] args){ String sql ="select p, s.count as views, (select count(*) from Comments rc where rc.linkedId=p.id and rc.classcode='InfoPublishs') as commentNumber, (select count(*) from CollectIndexs rci where rci.toId=p.id and rci.classcode='InfoPublishs' and rci.type='favorite') as favorite FROM InfoPublishs p,UserScores s where p.id=s.linkedId and p.userInfo.id=s.userInfo.id and s.classCode='InfoPublishs' AND p.status=?1 ORDER BY p.createtime DESC"; StringBuffer select = new StringBuffer(); StringBuffer from = new StringBuffer(); StringBuffer where = new StringBuffer(); // parser得到AST SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, JdbcUtils.MYSQL); List stmtList = parser.parseStatementList(); // // 将AST通过visitor输出 SQLASTOutputVisitor visitor = SQLUtils.createFormatOutputVisitor(from, stmtList, JdbcUtils.MYSQL); SQLASTOutputVisitor whereVisitor = SQLUtils.createFormatOutputVisitor(where, stmtList, JdbcUtils.MYSQL); for (SQLStatement stmt : stmtList) { // stmt.accept(visitor); if(stmt instanceof SQLSelectStatement){ SQLSelectStatement sstmt = (SQLSelectStatement)stmt; SQLSelect sqlselect = sstmt.getSelect(); SQLSelectQueryBlock query = (SQLSelectQueryBlock)sqlselect.getQuery(); query.getFrom().accept(visitor); query.getWhere().accept(whereVisitor); } } System.out.println(from.toString()); System.out.println(select); System.out.println(where); } } |
输出结果是:
InfoPublishs p, UserScores s
p.id = s.linkedId
AND p.userInfo.id = s.userInfo.id
AND s.classCode = ‘InfoPublishs’
AND p.status = ?1
大家觉得怎么样呢?觉的有用就收着吧,没用就扔了。
上面例子有些问题, 不知道是版本还是什么原因,
下面这个例子, 如果真的要实用, 还得修改
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
Java代码 public static void sqlDecomposition(String query,boolean isPrefix) throws InterruptedException { //Map<String,AnalysisPool> apMap=new HashMap<String,AnalysisPool>(); StringBuffer from=new StringBuffer(); SQLStatementParser parser=SQLParserUtils.createSQLStatementParser(query,JdbcUtils.HIVE); List<SQLStatement> stmtList=parser.parseStatementList(); SQLASTOutputVisitor visitor=SQLUtils.createFormatOutputVisitor(from,stmtList,JdbcUtils.HIVE); List<SQLSelectItem> sL=new ArrayList<SQLSelectItem>(); SQLStatement stmt=stmtList.iterator().next(); SQLSelectStatement sstmt=(SQLSelectStatement)stmt; SQLSelect sqlselect=sstmt.getSelect(); SQLSelectQueryBlock sqb=(SQLSelectQueryBlock)sqlselect.getQuery(); SQLTableSource fromx=sqb.getFrom(); fromx.accept(visitor); sL=sqb.getSelectList(); StringBuffer sb=new StringBuffer(); for ( SQLSelectItem sqi : sL) { if (sqi.getExpr() instanceof SQLCaseExpr) { sb.append(sqi.getAlias()).append(","); } else { sb.append(sqi.toString()).append(","); } } System.out.println("* ???? : " + sb.toString().substring(0, sb.toString().length() - 1)); //sourceAnalysis(fromx,apMap); } |