以下是本人的开源项目santr,一个类似antlr的语法分析器:
URL: https://github.com/szlwin/santr
本人基于santr工具对SQL进行解析,首先建立语法树,并将其保存为ExprSQL.ls,其具体内容如下(注:其未全部实现SQL语法,只实现了较常用的语法,且语法并不正确,只是作为示例):
grammar ExprSQL;
prog : sqlExpr;
@@KEYWORD :
K_SELECT 'select'
K_FROM 'from'
k_WHERE 'where'
K_AS 'as'
K_LEFT 'left'
K_RIGHT 'right'
K_INNER 'inner'
K_JOIN 'join'
K_CROSS 'cross'
K_NATURAL 'natural'
K_OUTER 'outer'
K_FULL 'full'
K_ON 'on'
K_AND 'and'
K_OR 'or'
K_LIKE 'like'
K_L '\'%\''
K_UNION 'union'
K_ALL 'all'
K_EXCEPT 'except'
K_DISTINCT 'distinct'
K_NULL 'null'
K_GROUP 'group'
K_BY 'by'
K_HAVING 'having'
K_ORDER 'order'
K_OFFSET 'offset'
K_LIMIT 'limit'
K_BETWEEN 'between'
K_NOT 'not'
K_IN 'in'
K_IS 'is'
K_DELETE 'delete'
K_INSERT 'insert'
K_INTO 'into'
K_UPDATE 'update'
K_SET 'set'
K_EXISTS 'exists'
;
sqlExpr: selectExpr
| deleteExpr
| insertExpr
| updateExpr
;
selectExpr : K_SELECT columnInfo K_FROM tableInfo (condition)? (groupInfo)? (pageInfo)? (queryInfo)*
;
insertExpr : K_INSERT K_INTO ID '(' iTargetInfo ')' 'values' '(' ivalueInfo ')'
;
iTargetInfo: expr (',' expr)*
;
ivalueInfo : expr (',' expr)*
;
updateExpr : K_UPDATE tableElement K_SET uValueInfo (condition)?
;
uValueInfo: signleValueInfo (',' signleValueInfo)*
;
signleValueInfo : columnElement '=' expr
;
deleteExpr : K_DELETE K_FROM tableInfo (condition)?
;
condition: k_WHERE sqlCondition
;
groupInfo: K_GROUP K_BY columnElement (',' columnElement)* (nextInfo)?
;
nextInfo:orderInfo
| havingInfo
;
queryInfo : (K_UNION|K_EXCEPT) (K_ALL|K_DISTINCT)? selectExpr
;
orderInfo: K_ORDER K_BY columnElement (',' columnElement)*
;
havingInfo: K_HAVING (sqlCondition)+
;
columnInfo : column (',' column)*
;
column : '*'
| ID '.*'
| columnElement ('as' ID)?
;
tableInfo : table (',' table)*
;
table : tableElement (JOIN_INFO)*
;
tableElement : ID (ID)?
| '(' selectExpr ')' ID
;
JOIN_INFO : (K_FULL|K_LEFT|K_RIGHT|K_INNER) (K_OUTER)? K_JOIN table K_ON expr
| K_CROSS K_JOIN table
| K_UNION K_JOIN table
;
sqlCondition : expr
;
expr : expr ('*'|'/') expr
| expr ('+'|'-') expr
| expr ('='|'!='|'>='|'<='|'>'|'<') expr
| expr K_LIKE likeExpr
| expr (K_AND|K_OR) expr
| K_NULL
| isInfo
| betweenInfo
| inInfo
| existsInfo
| fun
| ID ('.' ID)*
| '#' ID ('.' ID)*
| INT
| '(' exprElement ')'
| STRINGTEXT
;
isInfo :expr K_IS (K_NOT)? K_NULL
;
betweenInfo:expr (K_NOT)? K_BETWEEN expr K_AND expr
;
inInfo :expr (K_NOT)? K_IN '(' inValue ')'
;
existsInfo : (K_NOT)? K_EXISTS '(' selectExpr ')'
;
inValue : expr (',' expr)*
| selectExpr
;
likeExpr : K_L expr K_L
| expr K_L
;
exprElement : expr
|selectExpr
;
columnElement : fun
| '(' selectExpr ')'
| ID ('.' ID)?
;
fun: ID '(' (array)? ')'
;
array: param (',' param)*
;
param: ID
| INT
| expr
| '*'
;
pageInfo : (K_OFFSET INT)? K_LIMIT INT
;
@ID : #STRING;
@INT : #NUMBER;
@STRINGTEXT : '\'' #STRING '\'';
WS : [ \t\r\n];
之后编写以下程序对SQL进行解析:
import santr.common.context.LexerUtil;
import santr.v4.parser.ExpressParser;
import santr.view.parser.TreeViewer;
public class TestSQLExpr {
public static void main(String[] args) throws Exception {
//路径自己定义
LexerUtil.load("sqlExpr", "demo/v2/ExprSQL.ls");
//编写SQL语句
String sql= "select * from c left join (select * from b) b on c.id=b.id where c like '%' #asd '%' "
+ "and aa like '%' aa '%' group by a.sd,sd.dd having sd.dd>1 and df.dd =2 offset 1 limit 1 "
+ "union all select * from a union all select * from a";
ExpressParser lexerExecuter = new ExpressParser();
lexerExecuter.parser("sqlExpr", sql);
TreeViewer viewer = new TreeViewer(lexerExecuter.getTree());
viewer.open();
}
}
执行以上程序后可得到以下结果: