以mysql为例，先看一下sql的写法: 官网地址：请点击我
SELECT
[ALL | DISTINCT | DISTINCTROW ]
[HIGH_PRIORITY]
[STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[PARTITION partition_list]
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[WINDOW window_name AS (window_spec)
[, window_name AS (window_spec)] ...]
[ORDER BY {col_name | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[INTO OUTFILE 'file_name'
[CHARACTER SET charset_name]
export_options
| INTO DUMPFILE 'file_name'
| INTO var_name [, var_name]]
[FOR {UPDATE | SHARE} [OF tbl_name [, tbl_name] ...] [NOWAIT | SKIP LOCKED]
| LOCK IN SHARE MODE]]
sql关联查询的格式如下:
table_references:
escaped_table_reference [, escaped_table_reference] ...
escaped_table_reference:
table_reference
| { OJ table_reference }
table_reference:
table_factor
| join_table
table_factor:
tbl_name [PARTITION (partition_names)]
[[AS] alias] [index_hint_list]
| table_subquery [AS] alias [(col_list)]
| ( table_references )
join_table:
table_reference [INNER | CROSS] JOIN table_factor [join_condition]
| table_reference STRAIGHT_JOIN table_factor
| table_reference STRAIGHT_JOIN table_factor ON conditional_expr
| table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor
join_condition:
ON conditional_expr
| USING (column_list)
index_hint_list:
index_hint [, index_hint] ...
index_hint:
USE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
| IGNORE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
| FORCE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
index_list:
index_name [, index_name]
结果集合并:
SELECT ...
UNION [ALL | DISTINCT] SELECT ...
[UNION [ALL | DISTINCT] SELECT ...]
sql的解析过程如官方文档sql写法解析过程一直，流程图如下:
未命名文件 (9).png
举个例子：以mysql的查询sql为例，看看语法解析器的解析过程:
@Test
public void selectTest() {
ShardingRule shardingRule = createShardingRuleByLocalHost();
String selectSQL="SELECT t1.a as name, t2.b as age, COUNT(t2.c) as cnt FROM test_table1 t1 left join test_table2 t2 on t1.a=t2.a where t1a > 0 and t2.b<100 GROUP BY t1.a,t1.b ORDER BY t1.a,t1.t DESC";
SQLParsingEngine statementParser = new SQLParsingEngine(DatabaseType.MySQL,selectSQL, shardingRule, null);
SelectStatement selectStatement = (SelectStatement) statementParser.parse(false);
System.out.println(selectStatement);
}
sql语法解析器的解析过程，获取MySQLSelectParser，并解析。和上一篇insert都一致。
/**SQLParsingEngine.java**/
public SQLStatement parse(final boolean useCache) {
Optional<SQLStatement> cachedSQLStatement = getSQLStatementFromCache(useCache);
if (cachedSQLStatement.isPresent()) {
return cachedSQLStatement.get();
}
LexerEngine lexerEngine = LexerEngineFactory.newInstance(dbType, sql);
lexerEngine.nextToken();
SQLStatement result = SQLParserFactory.newInstance(dbType, lexerEngine.getCurrentToken().getType(), shardingRule, lexerEngine, shardingMetaData).parse();
if (useCache) {
ParsingResultCache.getInstance().put(sql, result);
}
return result;
}
在MySQLSelectParser的解析过程中，首先会解析sql，归并子查询，组装返回的statement，具体过程参照文章上方图，按照官方sql写法，一步一步解析，最终解析完成。
public final SelectStatement parse() {
//解析sql
SelectStatement result = parseInternal();
//是否有子查询
if (result.containsSubQuery()) {
//合并子查询statement
result = result.mergeSubQueryStatement();
}
// TODO move to rewrite
appendDerivedColumns(result);
appendDerivedOrderBy(result);
return result;
}
private SelectStatement parseInternal() {
SelectStatement result = new SelectStatement();
lexerEngine.nextToken();
//解析内部sql
parseInternal(result);
return result;
}
//具体过程见上图
@Override
protected void parseInternal(final SelectStatement selectStatement) {
parseDistinct();
parseSelectOption();
parseSelectList(selectStatement, getItems());
parseFrom(selectStatement);
parseWhere(getShardingRule(), selectStatement, getItems());
parseGroupBy(selectStatement);
parseHaving();
parseOrderBy(selectStatement);
parseLimit(selectStatement);
parseSelectRest();
}
接下来一个一个看看，到底分别都是如何解析的
parseDistinct() 解析distinct等其他关键字
如果sql中有distinct，或者distinctrow，则抛异常，不支持该语句
protected final void parseDistinct() {
selectClauseParserFacade.getDistinctClauseParser().parse();
}
public final void parse() {
//跳过ALL关键字
lexerEngine.skipAll(DefaultKeyword.ALL);
Collection<Keyword> distinctKeywords = new LinkedList<>();
distinctKeywords.add(DefaultKeyword.DISTINCT);
distinctKeywords.addAll(Arrays.asList(getSynonymousKeywordsForDistinct()));
//如果有distinct 或者DISTINCTROW，则抛异常，不支持distinct语句
lexerEngine.unsupportedIfEqual(distinctKeywords.toArray(new Keyword[distinctKeywords.size()]));
}
//获取关键字
@Override
protected Keyword[] getSynonymousKeywordsForDistinct() {
return new Keyword[] {MySQLKeyword.DISTINCTROW};
}
parseSelectOption() select其他选项解析
跳过HIGH_PRIORITY，STRAIGHT_JOIN，SQL_BIG_RESULT,SQL_SMALL_RESULT,SQL_BIG_RESULT,SQL_BUFFER_RESULT,SQL_CACHE,SQL_NO_CACHE,SQL_CALC_FOUND_ROWS关键字。
private void parseSelectOption() {
selectOptionClauseParser.parse();
}
public void parse() {
//跳过所有的关键字
lexerEngine.skipAll(MySQLKeyword.HIGH_PRIORITY, MySQLKeyword.STRAIGHT_JOIN,
MySQLKeyword.SQL_SMALL_RESULT, MySQLKeyword.SQL_BIG_RESULT, MySQLKeyword.SQL_BUFFER_RESULT, MySQLKeyword.SQL_CACHE, MySQLKeyword.SQL_NO_CACHE, MySQLKeyword.SQL_CALC_FOUND_ROWS);
}
parseSelectList返回字段的list解析
选项之间,隔开，直到解析最后一个分词不是,结束，循坏处理。
protected final void parseSelectList(final SelectStatement selectStatement, final List<SelectItem> items) {
selectClauseParserFacade.getSelectListClauseParser().parse(selectStatement, items);
}
public void parse(final SelectStatement selectStatement, final List<SelectItem> items) {
do {
selectStatement.getItems().add(parseSelectItem(selectStatement));
//分词结束符是，则跳过继续解析其他关键字
} while (lexerEngine.skipIfEqual(Symbol.COMMA));
//select list的最后结束位置
selectStatement.setSelectListLastPosition(lexerEngine.getCurrentToken().getEndPosition() - lexerEngine.getCurrentToken().getLiterals().length());
items.addAll(selectStatement.getItems());
}
在解释分词的时候，跳过其他的关键字，然后判断是否是*,或者MAX,MIN,SUM,AVG,COUNT函数，或者是普通返回字段，按照不同格式解析。
private SelectItem parseSelectItem(final SelectStatement selectStatement) {
//跳过需要跳过的分词
lexerEngine.skipIfEqual(getSkippedKeywordsBeforeSelectItem());
SelectItem result;
//如果是rowNumber选项，默认返回false
if (isRowNumberSelectItem()) {
result = parseRowNumberSelectItem(selectStatement);
//如果是* SQL格式如下：Select *， 如果是select t.*，则不走这里的逻辑
} else if (isStarSelectItem()) {
selectStatement.setContainStar(true);
result = parseStarSelectItem();
//如果是MAX,MIN,SUM,AVG,COUNT
} else if (isAggregationSelectItem()) {
result = parseAggregationSelectItem(selectStatement);
parseRestSelectItem(selectStatement);
//其他 select t.*, slect a,b....这样的格式走这里
} else {
result = parseCommonOrStarSelectItem(selectStatement);
}
return result;
}
解析带*的语句逻辑，个人理解，组装StarSelectItem即可，有些逻辑不太了解，后续再更。
private SelectItem parseStarSelectItem() {
//获取下一个分词
lexerEngine.nextToken();
//判断是否是其他关键字，这块逻辑没看懂什么场景下使用，个人觉得直接跳过下一个分词即可。
aliasExpressionParser.parseSelectItemAlias();
//组装返回StarSelectItem
return new StarSelectItem(Optional.<String>absent());
}
public Optional<String> parseSelectItemAlias() {
//处理as的逻辑
if (lexerEngine.skipIfEqual(DefaultKeyword.AS)) {
return parseWithAs();
}
//处理等于其他关键字的逻辑
if (lexerEngine.equalAny(getDefaultAvailableKeywordsForSelectItemAlias()) || lexerEngine.equalAny(getCustomizedAvailableKeywordsForSelectItemAlias())) {
return parseAlias();
}
//返回null，没有别名
return Optional.absent();
}
//解析别名
private Optional<String> parseAlias() {
//获取别名，并返回
String result = SQLUtil.getExactlyValue(lexerEngine.getCurrentToken().getLiterals());
lexerEngine.nextToken();
return Optional.of(result);
}
处理MAX,MIN,SUM,AVG,COUNT函数的逻辑如下：
判断当前分词是否是属于MAX,MIN,SUM,AVG,COUNT
private boolean isAggregationSelectItem() {
return lexerEngine.equalAny(DefaultKeyword.MAX, DefaultKeyword.MIN, DefaultKeyword.SUM, DefaultKeyword.AVG, DefaultKeyword.COUNT);
}
//解析函数 比如count(1),max(age)这样的函数处理逻辑。
private SelectItem parseAggregationSelectItem(final SelectStatement selectStatement) {
//获取类型
AggregationType aggregationType = AggregationType.valueOf(lexerEngine.getCurrentToken().getLiterals().toUpperCase());
lexerEngine.nextToken();
//组装AggregationSelectItem
return new AggregationSelectItem(aggregationType, lexerEngine.skipParentheses(selectStatement), aliasExpressionParser.parseSelectItemAlias());
}
//这块逻辑理论上不会执行吧。个人认为
private String parseRestSelectItem(final SelectStatement selectStatement) {
StringBuilder result = new StringBuilder();
//如果等于其他运算符，则组装
while (lexerEngine.equalAny(Symbol.getOperators())) {
result.append(lexerEngine.getCurrentToken().getLiterals());
lexerEngine.nextToken();
SelectItem selectItem = parseCommonOrStarSelectItem(selectStatement);
result.append(selectItem.getExpression());
}
return result.toString();
}
parseCommonOrStarSelectItem通用解析
根据.，()做不同的处理逻辑，解析别名，返回分词组装SelectItem。
private SelectItem parseCommonOrStarSelectItem(final SelectStatement selectStatement) {
//获取当前分词
String literals = lexerEngine.getCurrentToken().getLiterals();
int position = lexerEngine.getCurrentToken().getEndPosition() - literals.length();
StringBuilder result = new StringBuilder();
result.append(literals);
lexerEngine.nextToken();
//如果有左括号
if (lexerEngine.equalAny(Symbol.LEFT_PAREN)) {
//获取括号及括号里的所有信息
result.append(lexerEngine.skipParentheses(selectStatement));
//如果是.分割，则.前面为表名或表的别名，后面为字段名或者* 比如select t1.name from t1
} else if (lexerEngine.equalAny(Symbol.DOT)) {
//获取表名
String tableName = SQLUtil.getExactlyValue(literals);
//如果能找到表，则为表名，添加TableToken
if (shardingRule.tryFindTableRuleByLogicTable(tableName).isPresent() || shardingRule.findBindingTableRule(tableName).isPresent()) {
selectStatement.getSqlTokens().add(new TableToken(position, 0, literals));
}
//拼.
result.append(lexerEngine.getCurrentToken().getLiterals());
//获取一下分词
lexerEngine.nextToken();
if (lexerEngine.equalAny(Symbol.STAR)) {
return parseStarSelectItem(literals);
}
//拼接下一分词
result.append(lexerEngine.getCurrentToken().getLiterals());
//sql:select t1.name from t1 则result=t1.name
lexerEngine.nextToken();
}
return new CommonSelectItem(SQLUtil.getExactlyValue(result
+ parseRestSelectItem(selectStatement)), aliasExpressionParser.parseSelectItemAlias());
}
//解析别名，如果有as，则返回as后面的下一分词
public Optional<String> parseSelectItemAlias() {
if (lexerEngine.skipIfEqual(DefaultKeyword.AS)) {
return parseWithAs();
}
if (lexerEngine.equalAny(getDefaultAvailableKeywordsForSelectItemAlias()) || lexerEngine.equalAny(getCustomizedAvailableKeywordsForSelectItemAlias())) {
return parseAlias();
}
return Optional.absent();
}
parseFrom from语句解析器
protected final void parseFrom(final SelectStatement selectStatement) {
//不支持into语句
lexerEngine.unsupportedIfEqual(DefaultKeyword.INTO);
//跳过from语句
if (lexerEngine.skipIfEqual(DefaultKeyword.FROM)) {
//解析表名
parseTable(selectStatement);
}
}
private void parseTable(final SelectStatement selectStatement) {
//如果是子查询
if (lexerEngine.skipIfEqual(Symbol.LEFT_PAREN)) {
selectStatement.setSubQueryStatement(parseInternal());
if (lexerEngine.equalAny(DefaultKeyword.WHERE, Assist.END)) {
return;
}
}
//表引用关系解析
selectClauseParserFacade.getTableReferencesClauseParser().parse(selectStatement, false);
}
//TableReferencesClauseParser.java
public final void parse(final SQLStatement sqlStatement, final boolean isSingleTableOnly) {
do {
//解析表引用关系，如果有,则继续解析，对应场景 from from table1,table2
//table1解析完成，需要继续解析table2
parseTableReference(sqlStatement, isSingleTableOnly);
} while (lexerEngine.skipIfEqual(Symbol.COMMA));
}
//MySQLTableReferencesClauseParser.java
@Override
protected void parseTableReference(final SQLStatement sqlStatement, final boolean isSingleTableOnly) {
//
parseTableFactor(sqlStatement, isSingleTableOnly);
parsePartition();
parseIndexHint(sqlStatement);
}
再具体解析时，首先会获取第一分词，然后看下一个分词是不是.,如果是.，则第一分词是schema的名称，下一分词为表名称，不然第一分词是表名称。
protected final void parseTableFactor(final SQLStatement sqlStatement, final boolean isSingleTableOnly) {
final int beginPosition = lexerEngine.getCurrentToken().getEndPosition() - lexerEngine.getCurrentToken().getLiterals().length();
String literals = lexerEngine.getCurrentToken().getLiterals();
int skippedSchemaNameLength = 0;
lexerEngine.nextToken();
//如果是.
if (lexerEngine.skipIfEqual(Symbol.DOT)) {
//schema接口
skippedSchemaNameLength = literals.length() + Symbol.DOT.getLiterals().length();
//返回分词信息，这里是表名
literals = lexerEngine.getCurrentToken().getLiterals();
}
//格式化表名称
String tableName = SQLUtil.getExactlyValue(literals);
if (Strings.isNullOrEmpty(tableName)) {
return;
}
//解析别名
Optional<String> alias = aliasExpressionParser.parseTableAlias();
if (isSingleTableOnly || shardingRule.tryFindTableRuleByLogicTable(tableName).isPresent() || shardingRule.findBindingTableRule(tableName).isPresent()
|| shardingRule.getShardingDataSourceNames().getDataSourceNames().contains(shardingRule.getShardingDataSourceNames().getDefaultDataSourceName())) {
//添加TableToken分词
sqlStatement.getSqlTokens().add(new TableToken(beginPosition, skippedSchemaNameLength, literals));
//添加Table分词
sqlStatement.getTables().add(new Table(tableName, alias));
}
//强制索引解析
parseForceIndex(tableName, sqlStatement);
//join查询
parseJoinTable(sqlStatement);
//状态不一致
if (isSingleTableOnly && !sqlStatement.getTables().isSingleTable()) {
throw new UnsupportedOperationException("Cannot support Multiple-Table.");
}
}
在解析join语句时，首先判断是否是join子查询，如果是，则不支持。
private void parseJoinTable(final SQLStatement sqlStatement) {
//如果是inner,outer,left,right,full,cross, natural,join关键字
while (parseJoinType()) {
//且下一分词如果是(开头，则表示是子查询，抛异常不处理。
if (lexerEngine.equalAny(Symbol.LEFT_PAREN)) {
throw new UnsupportedOperationException("Cannot support sub query for join table.");
}
parseTableFactor(sqlStatement, false);
//判断join结束
parseJoinCondition(sqlStatement);
}
}
在判断join结束的时候，实质是跳过on分词，解析on table1.a=table2.b这样的语句，分析前一个表达式和第二个表达式。
private void parseJoinCondition(final SQLStatement sqlStatement) {
//如果分词是on，则直接跳过
if (lexerEngine.skipIfEqual(DefaultKeyword.ON)) {
do {
//解析第一个表达式
basicExpressionParser.parse(sqlStatement);
//只接受=
lexerEngine.accept(Symbol.EQ);
//解析第一个表达式
basicExpressionParser.parse(sqlStatement);
//当有and分词时，继续解析
//对应sql中，from table1.a left join table2.b on a.name=b.name and a.age=b.age
} while (lexerEngine.skipIfEqual(DefaultKeyword.AND));
//如果sql中有using分词，则跳过所有括号里的所有信息
} else if (lexerEngine.skipIfEqual(DefaultKeyword.USING)) {
lexerEngine.skipParentheses(sqlStatement);
}
}
表关联结束之后，整个from语句就解析完成了，接下来的就是where语句了。
parseWhere where语句解析
protected final void parseWhere(final ShardingRule shardingRule, final SelectStatement selectStatement, final List<SelectItem> items) {
selectClauseParserFacade.getWhereClauseParser().parse(shardingRule, selectStatement, items);
}
//WhereClauseParser.java
public void parse(final ShardingRule shardingRule, final SQLStatement sqlStatement, final List<SelectItem> items) {
aliasExpressionParser.parseTableAlias();
//如果是where
if (lexerEngine.skipIfEqual(DefaultKeyword.WHERE)) {
//解析where条件
parseWhere(shardingRule, sqlStatement, items);
}
}
private void parseWhere(final ShardingRule shardingRule, final SQLStatement sqlStatement, final List<SelectItem> items) {
//or 条件解析
OrCondition orCondition = parseOr(shardingRule, sqlStatement, items).optimize();
if (1 != orCondition.getAndConditions().size() || !(orCondition.getAndConditions().get(0).getConditions().get(0) instanceof NullCondition)) {
sqlStatement.getConditions().getOrCondition().getAndConditions().addAll(orCondition.getAndConditions());
}
}
//根据or分词，遍历解析
private OrCondition parseOr(final ShardingRule shardingRule, final SQLStatement sqlStatement, final List<SelectItem> items) {
OrCondition result = new OrCondition();
do {
判断分词是否是(开头
if (lexerEngine.skipIfEqual(Symbol.LEFT_PAREN)) {
//子查询，递归
OrCondition subOrCondition = parseOr(shardingRule, sqlStatement, items);
lexerEngine.skipIfEqual(Symbol.RIGHT_PAREN);
//跳过)括号
OrCondition orCondition = null;
//如果下一分词是And ，则继续解析and
if (lexerEngine.skipIfEqual(DefaultKeyword.AND)) {
//and解析
orCondition = parseAnd(shardingRule, sqlStatement, items);
}
//合并结果
result.getAndConditions().addAll(merge(subOrCondition, orCondition).getAndConditions());
} else {
//解析and条件
OrCondition orCondition = parseAnd(shardingRule, sqlStatement, items);
result.getAndConditions().addAll(orCondition.getAndConditions());
}
} while (lexerEngine.skipIfEqual(DefaultKeyword.OR));
return result;
}
//and解析
private OrCondition parseAnd(final ShardingRule shardingRule, final SQLStatement sqlStatement, final List<SelectItem> items) {
OrCondition result = new OrCondition();
do {
//跳过左括号
if (lexerEngine.skipIfEqual(Symbol.LEFT_PAREN)) {
//解析or
OrCondition subOrCondition = parseOr(shardingRule, sqlStatement, items);
lexerEngine.skipIfEqual(Symbol.RIGHT_PAREN);
//合并结果
result = merge(result, subOrCondition);
} else {
Condition condition = parseComparisonCondition(shardingRule, sqlStatement, items);
skipsDoubleColon();
result = merge(result, new OrCondition(condition));
}
//遇到and 继续解析
} while (lexerEngine.skipIfEqual(DefaultKeyword.AND));
return result;
}
private Condition parseComparisonCondition(final ShardingRule shardingRule, final SQLStatement sqlStatement, final List<SelectItem> items) {
Condition result;
//解析第一个分词的表达式
SQLExpression left = basicExpressionParser.parse(sqlStatement);
//如果后面是=
if (lexerEngine.skipIfEqual(Symbol.EQ)) {
result = parseEqualCondition(shardingRule, sqlStatement, left);
return result;
}
//in
if (lexerEngine.skipIfEqual(DefaultKeyword.IN)) {
result = parseInCondition(shardingRule, sqlStatement, left);
return result;
}
//between
if (lexerEngine.skipIfEqual(DefaultKeyword.BETWEEN)) {
result = parseBetweenCondition(shardingRule, sqlStatement, left);
return result;
}
//
result = new NullCondition();
if (sqlStatement instanceof SelectStatement && isRowNumberCondition(items, left)) {
//<
if (lexerEngine.skipIfEqual(Symbol.LT)) {
parseRowCountCondition((SelectStatement) sqlStatement, false);
return result;
}
//<=
if (lexerEngine.skipIfEqual(Symbol.LT_EQ)) {
parseRowCountCondition((SelectStatement) sqlStatement, true);
return result;
}
//>
if (lexerEngine.skipIfEqual(Symbol.GT)) {
parseOffsetCondition((SelectStatement) sqlStatement, false);
return result;
}
//>=
if (lexerEngine.skipIfEqual(Symbol.GT_EQ)) {
parseOffsetCondition((SelectStatement) sqlStatement, true);
return result;
}
}
List<Keyword> otherConditionOperators = new LinkedList<>(Arrays.asList(getCustomizedOtherConditionOperators()));
otherConditionOperators.addAll(
Arrays.asList(Symbol.LT, Symbol.LT_EQ, Symbol.GT, Symbol.GT_EQ, Symbol.LT_GT, Symbol.BANG_EQ, Symbol.BANG_GT, Symbol.BANG_LT, DefaultKeyword.LIKE, DefaultKeyword.IS));
if (lexerEngine.skipIfEqual(otherConditionOperators.toArray(new Keyword[otherConditionOperators.size()]))) {
lexerEngine.skipIfEqual(DefaultKeyword.NOT);
parseOtherCondition(sqlStatement);
}
if (lexerEngine.skipIfEqual(DefaultKeyword.NOT)) {
parseNotCondition(sqlStatement);
}
return result;
}
parseGroupBy(selectStatement) group分词解析
按照,分割，一个一个解析处理
//GroupByClauseParser.java
public final void parse(final SelectStatement selectStatement) {
//如果不是group分词，则返回
if (!lexerEngine.skipIfEqual(DefaultKeyword.GROUP)) {
return;
}
//group 后面必须是by分词
lexerEngine.accept(DefaultKeyword.BY);
while (true) {
//添加group分词，并解析
addGroupByItem(basicExpressionParser.parse(selectStatement), selectStatement);
//如果不是,则跳出，对应 group by table1.a,tableb.b
if (!lexerEngine.equalAny(Symbol.COMMA)) {
break;
}
lexerEngine.nextToken();
}
lexerEngine.skipAll(getSkippedKeywordAfterGroupBy());
selectStatement.setGroupByLastPosition(lexerEngine.getCurrentToken().getEndPosition() - lexerEngine.getCurrentToken().getLiterals().length());
}
private void addGroupByItem(final SQLExpression sqlExpression, final SelectStatement selectStatement) {
lexerEngine.unsupportedIfEqual(getUnsupportedKeywordBeforeGroupByItem());
//排序方向
OrderDirection orderDirection = OrderDirection.ASC;
if (lexerEngine.equalAny(DefaultKeyword.ASC)) {
lexerEngine.nextToken();
} else if (lexerEngine.skipIfEqual(DefaultKeyword.DESC)) {
orderDirection = OrderDirection.DESC;
}
OrderItem orderItem;
if (sqlExpression instanceof SQLPropertyExpression) {
//获取排序字段，对应 group by table1.a 的场景
SQLPropertyExpression sqlPropertyExpression = (SQLPropertyExpression) sqlExpression;
orderItem = new OrderItem(SQLUtil.getExactlyValue(sqlPropertyExpression.getOwner().getName()), SQLUtil.getExactlyValue(sqlPropertyExpression.getName()), orderDirection, OrderDirection.ASC,
selectStatement.getAlias(SQLUtil.getExactlyValue(sqlPropertyExpression.getOwner().getName() + "." + SQLUtil.getExactlyValue(sqlPropertyExpression.getName()))));
} else if (sqlExpression instanceof SQLIdentifierExpression) {
//获取排序字段，对应group by a 的场景
SQLIdentifierExpression sqlIdentifierExpression = (SQLIdentifierExpression) sqlExpression;
orderItem = new OrderItem(SQLUtil.getExactlyValue(sqlIdentifierExpression.getName()),
orderDirection, OrderDirection.ASC, selectStatement.getAlias(SQLUtil.getExactlyValue(sqlIdentifierExpression.getName())));
} else if (sqlExpression instanceof SQLIgnoreExpression) {
SQLIgnoreExpression sqlIgnoreExpression = (SQLIgnoreExpression) sqlExpression;
orderItem = new OrderItem(sqlIgnoreExpression.getExpression(), orderDirection, OrderDirection.ASC, selectStatement.getAlias(sqlIgnoreExpression.getExpression()));
} else {
return;
}
//添加到items中
selectStatement.getGroupByItems().add(orderItem);
}
parseHaving(),目前默认不支持
protected final void parseHaving() {
selectClauseParserFacade.getHavingClauseParser().parse();
}
public final class HavingClauseParser implements SQLClauseParser {
private final LexerEngine lexerEngine;
/**
* Parse having.
*/
public void parse() {
//不支持having
lexerEngine.unsupportedIfEqual(DefaultKeyword.HAVING);
}
}
parseOrderBy(selectStatement) order by解析
protected final void parseOrderBy(final SelectStatement selectStatement) {
selectClauseParserFacade.getOrderByClauseParser().parse(selectStatement);
}
//OrderByClauseParser.java
public final void parse(final SelectStatement selectStatement) {
if (!lexerEngine.skipIfEqual(DefaultKeyword.ORDER)) {
return;
}
List<OrderItem> result = new LinkedList<>();
//跳过SIBLINGS
lexerEngine.skipIfEqual(OracleKeyword.SIBLINGS);
//接受by
lexerEngine.accept(DefaultKeyword.BY);
do {
//按照,持续解析，直到最后出现的分词不是,
result.add(parseSelectOrderByItem(selectStatement));
//,继续解析分词
} while (lexerEngine.skipIfEqual(Symbol.COMMA));
selectStatement.getOrderByItems().addAll(result);
}
parseLimit(selectStatement)
private void parseLimit(final SelectStatement selectStatement) {
limitClauseParser.parse(selectStatement);
}
//MySQLLimitClauseParser.java
public void parse(final SelectStatement selectStatement) {
if (!lexerEngine.skipIfEqual(MySQLKeyword.LIMIT)) {
return;
}
int valueIndex = -1;
int valueBeginPosition = lexerEngine.getCurrentToken().getEndPosition();
int value;
boolean isParameterForValue = false;
//是否是int类型数据
if (lexerEngine.equalAny(Literals.INT)) {
//获取offset
value = Integer.parseInt(lexerEngine.getCurrentToken().getLiterals());
valueBeginPosition = valueBeginPosition - (value + "").length();
} else if (lexerEngine.equalAny(Symbol.QUESTION)) {
valueIndex = selectStatement.getParametersIndex();
value = -1;
valueBeginPosition--;
isParameterForValue = true;
} else {
throw new SQLParsingException(lexerEngine);
}
lexerEngine.nextToken();
//判断是不是逗号，如果是则跳过
if (lexerEngine.skipIfEqual(Symbol.COMMA)) {
//获取
selectStatement.setLimit(getLimitWithComma(valueIndex, valueBeginPosition, value, isParameterForValue, selectStatement));
return;
}
if (lexerEngine.skipIfEqual(MySQLKeyword.OFFSET)) {
selectStatement.setLimit(getLimitWithOffset(valueIndex, valueBeginPosition, value, isParameterForValue, selectStatement));
return;
}
if (isParameterForValue) {
selectStatement.increaseParametersIndex();
} else {
selectStatement.getSqlTokens().add(new RowCountToken(valueBeginPosition, value));
}
Limit limit = new Limit(DatabaseType.MySQL);
limit.setRowCount(new LimitValue(value, valueIndex, false));
selectStatement.setLimit(limit);
}
private Limit getLimitWithComma(final int index, final int valueBeginPosition, final int value, final boolean isParameterForValue, final SelectStatement selectStatement) {
int rowCountBeginPosition = lexerEngine.getCurrentToken().getEndPosition();
int rowCountValue;
int rowCountIndex = -1;
boolean isParameterForRowCount = false;
//判断是否是int类型
if (lexerEngine.equalAny(Literals.INT)) {
rowCountValue = Integer.parseInt(lexerEngine.getCurrentToken().getLiterals());
rowCountBeginPosition = rowCountBeginPosition - (rowCountValue + "").length();
} else if (lexerEngine.equalAny(Symbol.QUESTION)) {
rowCountIndex = -1 == index ? selectStatement.getParametersIndex() : index + 1;
rowCountValue = -1;
rowCountBeginPosition--;
isParameterForRowCount = true;
} else {
throw new SQLParsingException(lexerEngine);
}
lexerEngine.nextToken();
if (isParameterForValue) {
selectStatement.increaseParametersIndex();
} else {
selectStatement.getSqlTokens().add(new OffsetToken(valueBeginPosition, value));
}
if (isParameterForRowCount) {
selectStatement.increaseParametersIndex();
} else {
selectStatement.getSqlTokens().add(new RowCountToken(rowCountBeginPosition, rowCountValue));
}
Limit result = new Limit(DatabaseType.MySQL);
result.setRowCount(new LimitValue(rowCountValue, rowCountIndex, false));
result.setOffset(new LimitValue(value, index, true));
return result;
}
parseSelectRest() select其他部分的sql解析
不支持UNION，INTERSECT，MINUS，EXCEPT
protected final void parseSelectRest() {
selectClauseParserFacade.getSelectRestClauseParser().parse();
}
public final void parse() {
Collection<Keyword> unsupportedRestKeywords = new LinkedList<>();
unsupportedRestKeywords.addAll(Arrays.asList(DefaultKeyword.UNION, DefaultKeyword.INTERSECT, DefaultKeyword.EXCEPT, DefaultKeyword.MINUS));
unsupportedRestKeywords.addAll(Arrays.asList(getUnsupportedKeywordsRest()));
lexerEngine.unsupportedIfEqual(unsupportedRestKeywords.toArray(new Keyword[unsupportedRestKeywords.size()]));
}
public final void parse() {
Collection<Keyword> unsupportedRestKeywords = new LinkedList<>();
//不支持UNION，INTERSECT，MINUS，EXCEPT
unsupportedRestKeywords.addAll(Arrays.asList(DefaultKeyword.UNION, DefaultKeyword.INTERSECT, DefaultKeyword.EXCEPT, DefaultKeyword.MINUS));
unsupportedRestKeywords.addAll(Arrays.asList(getUnsupportedKeywordsRest()));
lexerEngine.unsupportedIfEqual(unsupportedRestKeywords.toArray(new Keyword[unsupportedRestKeywords.size()]));
}
以一下sql为例：
SELECT t1.a as name, t2.b as age, COUNT(t2.c) as cnt FROM test_table1 t1 left join test_table2 t2 on t1.a=t2.a where t1.a='d' or (t1.a !='name' and t2.b<100) GROUP BY t1.a,t1.b ORDER BY t1.a asc,t1.t DESC limit 0,1
解析结果如图：
selectStatement.png
fyi
