2019獨角獸企業(yè)重金招聘Python工程師標(biāo)準(zhǔn)>>>

說在前面

本文轉(zhuǎn)自“天河聊技術(shù)”微信公眾號

?

本次介紹的是sharding-jdbc的源碼解析部分的sql解析，這一部分主要是把邏輯sql語句裝在到sharding-jdbc的數(shù)據(jù)模型中，為后期的sql路由處理做基礎(chǔ)工作。

?

sql解析源碼解析

sharding-jdbc針對不同的數(shù)據(jù)庫有的不同的具體的解析器的實現(xiàn)，本次我們主要針對mysql為主來跟蹤下源碼怎么實現(xiàn)的sql解析邏輯，首先找到sql解析引擎類

com.dangdang.ddframe.rdb.sharding.parsing.SQLParsingEngine這個類，主要方法是這個

/*** 解析SQL.* * @return SQL語句對象*/ public SQLStatement parse() {//業(yè)務(wù)方法AbstractSQLParser sqlParser = getSQLParser();//獲取到對應(yīng)的數(shù)據(jù)庫parser對象sqlParser.skipIfEqual(Symbol.SEMI);//跳過;if (sqlParser.equalAny(DefaultKeyword.WITH)) {skipWith(sqlParser);}if (sqlParser.equalAny(DefaultKeyword.SELECT)) {//這里是工廠方法設(shè)計模式實現(xiàn)return SelectParserFactory.newInstance(sqlParser).parse();

進入到這個方法?SelectParserFactory.newInstance(sqlParser)內(nèi)，創(chuàng)建select語句的sql解析器

/*** 創(chuàng)建Select語句解析器.* * @param sqlParser SQL解析器* @return Select語句解析器*/ public static AbstractSelectParser newInstance(final AbstractSQLParser sqlParser) {if (sqlParser instanceof MySQLParser) {return new MySQLSelectParser(sqlParser);}

進入到parse方法

SelectParserFactory.newInstance(sqlParser).parse(); select解析器中的主要邏輯是這個方法@Override public final SelectStatement parse() {//select語句解析方法sqlParser.getLexer().nextToken();parseDistinct();parseBeforeSelectList();parseSelectList();parseFrom();parseWhere();customizedBetweenWhereAndGroupBy();parseGroupBy();customizedBetweenGroupByAndOrderBy();parseOrderBy();customizedSelect();processUnsupportedTokens();// TODO move to rewriteappendDerivedColumns();//解析聚合函數(shù)appendDerivedOrderBy();return selectStatement; }

進入到這個方法

parseDistinct();

從這個方法看出是不支持distinct關(guān)鍵字的

private void parseDistinct() {sqlParser.skipAll(DefaultKeyword.ALL);Collection<Keyword> distinctKeywords = Lists.newLinkedList(getCustomizedDistinctKeywords());distinctKeywords.add(DefaultKeyword.DISTINCT);//不支持distinct關(guān)鍵字if (getSqlParser().equalAny(distinctKeywords.toArray(new Keyword[distinctKeywords.size()]))) {throw new SQLParsingUnsupportedException(getSqlParser().getLexer().getCurrentToken().getType());} }

進入到這個方法

parseSelectList(); private void parseSelectList() {do { // 解析select后面的字段parseSelectItem();} while (sqlParser.skipIfEqual(Symbol.COMMA));selectStatement.setSelectListLastPosition(sqlParser.getLexer().getCurrentToken().getEndPosition() - sqlParser.getLexer().getCurrentToken().getLiterals().length());}

進入到這個方法

parseSelectItem(); private void parseSelectItem() {sqlParser.skipIfEqual(getSkipKeywordsBeforeSelectItem()); // 是否是rownumber的列if (isRowNumberSelectItem()) { // mysql不支持rownumberselectStatement.getItems().add(parseRowNumberSelectItem());return;} String literals = sqlParser.getLexer().getCurrentToken().getLiterals(); // 去掉select后的特殊字符，是否查詢表的所有字段if (Symbol.STAR.getLiterals().equals(SQLUtil.getExactlyValue(literals))) {selectStatement.getItems().add(parseStarSelectItem());return;}

進入到parseStarSelectItem方法

// 解析表全部列private SelectItem parseStarSelectItem() {if (!containSubquery) {containStarForOutQuery = true;}sqlParser.getLexer().nextToken();selectStatement.setContainStar(true);return new CommonSelectItem(Symbol.STAR.getLiterals(), sqlParser.parseAlias());}

進入到這個parseAlias方法

public Optional<String> parseAlias() { // 別名之前必須要有as關(guān)鍵字的，否則解析不到if (skipIfEqual(DefaultKeyword.AS)) {if (equalAny(Symbol.values())) {return Optional.absent();}String result = SQLUtil.getExactlyValue(getLexer().getCurrentToken().getLiterals());getLexer().nextToken();return Optional.of(result);}

這里解析別名是解析as關(guān)鍵字的，解析不到這個關(guān)鍵字會報錯

往上返回到parseSelectItem這個方法

// 解析聚合函數(shù)if (isAggregationSelectItem()) {selectStatement.getItems().add(parseAggregationSelectItem(literals));return;}

聚合選擇項類型

public enum AggregationType {MAX, MIN, SUM, COUNT, AVG }

進入到

parseAggregationSelectItem（）方法內(nèi) private SelectItem parseAggregationSelectItem(final String literals) { // sqlParser.skipParentheses()解析聚合函數(shù)后面的小括號內(nèi)的詞法標(biāo)記return new AggregationSelectItem(AggregationType.valueOf(literals.toUpperCase()), sqlParser.skipParentheses(), sqlParser.parseAlias());}

返回到parseSelectItem方法

書否包含.if (sqlParser.equalAny(Symbol.DOT)) {selectStatement.getSqlTokens().add(new TableToken(position, value));} /*** SQL語句對象抽象類.** @author zhangliang*/ @RequiredArgsConstructor @Getter @ToString public abstract class AbstractSQLStatement implements SQLStatement {// sql類型private final SQLType type;// 表集合private final Tables tables = new Tables();// 條件集合private final Conditions conditions = new Conditions();// sql標(biāo)記對象集合private final List<SQLToken> sqlTokens = new LinkedList<>(); if (hasAlias(expression, lastToken)) { // 解析有別名的select選擇項selectStatement.getItems().add(parseSelectItemWithAlias(expression, lastToken));return;}selectStatement.getItems().add(new CommonSelectItem(SQLUtil.getExactlyValue(expression.toString()), sqlParser.parseAlias()));

往上返回到這個方法

com.dangdang.ddframe.rdb.sharding.parsing.parser.statement.dql.select.AbstractSelectParser#parse

// 解析from后的語句parseFrom();

進入到這個方法

private void parseFrom() {if (getSqlParser().equalAny(DefaultKeyword.INTO)) {//不支持select intothrow new SQLParsingUnsupportedException(DefaultKeyword.INTO);}if (sqlParser.skipIfEqual(DefaultKeyword.FROM)) {parseTable();//解析表} }

從這里可以看出是不支持select...into這種sql語句寫法

進入到這個方法

com.dangdang.ddframe.rdb.sharding.parsing.parser.statement.dql.select.AbstractSelectParser#parseTable

private void parseTable() { // 解析(里面的內(nèi)容if (sqlParser.skipIfEqual(Symbol.LEFT_PAREN)) {if (!selectStatement.getTables().isEmpty()) {//不支持子查詢throw new UnsupportedOperationException("Cannot support subquery for nested tables.");}containSubquery = true;selectStatement.setContainStar(false); // 跳過嵌套的無用的小括號sqlParser.skipUselessParentheses();parse();sqlParser.skipUselessParentheses();if (getSqlParser().equalAny(DefaultKeyword.WHERE, Assist.END)) {return;}} //解析表customizedParseTableFactor(); // 解析關(guān)聯(lián)的表parseJoinTable();}

解析關(guān)聯(lián)的表

protected void parseJoinTable() {if (sqlParser.skipJoin()) {parseTable();if (sqlParser.skipIfEqual(DefaultKeyword.ON)) {do { // 解析table的條件parseTableCondition(sqlParser.getLexer().getCurrentToken().getEndPosition());sqlParser.accept(Symbol.EQ);parseTableCondition(sqlParser.getLexer().getCurrentToken().getEndPosition() - sqlParser.getLexer().getCurrentToken().getLiterals().length());} while (sqlParser.skipIfEqual(DefaultKeyword.AND));} else if (sqlParser.skipIfEqual(DefaultKeyword.USING)) {sqlParser.skipParentheses();}parseJoinTable();}}

回到這個方法

com.dangdang.ddframe.rdb.sharding.parsing.parser.statement.dql.select.AbstractSelectParser#parse

// 解析where后的語句parseWhere(); /*** 解析查詢條件.** @param sqlStatement SQL語句對象*/public final void parseWhere(final SQLStatement sqlStatement) { // 解析別名parseAlias();if (skipIfEqual(DefaultKeyword.WHERE)) { // 解析where后的條件表達式parseConditions(sqlStatement);}} private void parseConditions(final SQLStatement sqlStatement) {do {parseComparisonCondition(sqlStatement);} while (skipIfEqual(DefaultKeyword.AND));//解析where后的and條件if (equalAny(DefaultKeyword.OR)) {//不支持orthrow new SQLParsingUnsupportedException(getLexer().getCurrentToken().getType());} }

從這里可以看出是不支持or操作的

進入解析比較表達式的這個方法

// TODO 解析組合exprpublic final void parseComparisonCondition(final SQLStatement sqlStatement) {skipIfEqual(Symbol.LEFT_PAREN); // 解析表達式SQLExpression left = parseExpression(sqlStatement);if (equalAny(Symbol.EQ)) {//解析=表達式parseEqualCondition(sqlStatement, left);skipIfEqual(Symbol.RIGHT_PAREN);return;}if (equalAny(DefaultKeyword.IN)) {//解析in表達式parseInCondition(sqlStatement, left);skipIfEqual(Symbol.RIGHT_PAREN);return;} // 解析between表達式if (equalAny(DefaultKeyword.BETWEEN)) {parseBetweenCondition(sqlStatement, left);skipIfEqual(Symbol.RIGHT_PAREN);return;}

返回到這個方法的解析group by的這一行代碼

@Overridepublic final SelectStatement parse() {//select語句解析方法sqlParser.getLexer().nextToken(); // 解析distinct關(guān)鍵字parseDistinct();parseBeforeSelectList(); // 解析select選擇項parseSelectList();// 解析from后的語句parseFrom(); // 解析where后的語句parseWhere();customizedBetweenWhereAndGroupBy(); // 解析group by語句parseGroupBy();customizedBetweenGroupByAndOrderBy();parseOrderBy();customizedSelect();processUnsupportedTokens();// TODO move to rewriteappendDerivedColumns();//解析聚合函數(shù)appendDerivedOrderBy();return selectStatement;} protected void parseGroupBy() {//解析group byif (sqlParser.skipIfEqual(DefaultKeyword.GROUP)) {sqlParser.accept(DefaultKeyword.BY);while (true) { // 解析group by項addGroupByItem(sqlParser.parseExpression(selectStatement));if (!sqlParser.equalAny(Symbol.COMMA)) {break;}sqlParser.getLexer().nextToken();}while (sqlParser.equalAny(DefaultKeyword.WITH) || sqlParser.getLexer().getCurrentToken().getLiterals().equalsIgnoreCase("ROLLUP")) {sqlParser.getLexer().nextToken();}if (sqlParser.skipIfEqual(DefaultKeyword.HAVING)) {//不支持havingthrow new UnsupportedOperationException("Cannot support Having");}selectStatement.setGroupByLastPosition(sqlParser.getLexer().getCurrentToken().getEndPosition() - getSqlParser().getLexer().getCurrentToken().getLiterals().length());} else if (sqlParser.skipIfEqual(DefaultKeyword.HAVING)) {throw new UnsupportedOperationException("Cannot support Having");}}

從上面的這個方法實現(xiàn)可以看出是不支持having這種操作的

進入到組裝排序項的這個方法

protected final void addGroupByItem(final SQLExpression sqlExpression) { // 默認(rèn)升序排序OrderType orderByType = OrderType.ASC;if (sqlParser.equalAny(DefaultKeyword.ASC)) {sqlParser.getLexer().nextToken();} else if (sqlParser.skipIfEqual(DefaultKeyword.DESC)) {orderByType = OrderType.DESC;}

可以看出是默認(rèn)升序排序的

返回到這個方法的解析order by的這行代碼

@Overridepublic final SelectStatement parse() {//select語句解析方法sqlParser.getLexer().nextToken(); // 解析distinct關(guān)鍵字parseDistinct();parseBeforeSelectList(); // 解析select選擇項parseSelectList();// 解析from后的語句parseFrom(); // 解析where后的語句parseWhere();customizedBetweenWhereAndGroupBy(); // 解析group by語句parseGroupBy();customizedBetweenGroupByAndOrderBy(); // 解析order by語句parseOrderBy();customizedSelect();processUnsupportedTokens();// TODO move to rewriteappendDerivedColumns();//解析聚合函數(shù)appendDerivedOrderBy();return selectStatement;} protected final void parseOrderBy() {//解析order byif (!sqlParser.skipIfEqual(DefaultKeyword.ORDER)) {return;}List<OrderItem> result = new LinkedList<>();sqlParser.skipIfEqual(DefaultKeyword.SIBLINGS);sqlParser.accept(DefaultKeyword.BY);do { // 解析查詢排序選擇項OrderItem orderItem = parseSelectOrderByItem();if (!containSubquery || containStarForOutQuery) {result.add(orderItem);}}while (sqlParser.skipIfEqual(Symbol.COMMA));selectStatement.getOrderByItems().addAll(result);} private OrderItem parseSelectOrderByItem() {SQLExpression sqlExpression = sqlParser.parseExpression(selectStatement);OrderType orderByType = OrderType.ASC;//默認(rèn)升序if (sqlParser.skipIfEqual(DefaultKeyword.ASC)) {orderByType = OrderType.ASC;} else if (sqlParser.skipIfEqual(DefaultKeyword.DESC)) {orderByType = OrderType.DESC;}

默認(rèn)升序

返回到這個方法

@Overridepublic final SelectStatement parse() {//select語句解析方法sqlParser.getLexer().nextToken(); // 解析distinct關(guān)鍵字parseDistinct();parseBeforeSelectList(); // 解析select選擇項parseSelectList();// 解析from后的語句parseFrom(); // 解析where后的語句parseWhere();customizedBetweenWhereAndGroupBy(); // 解析group by語句parseGroupBy();customizedBetweenGroupByAndOrderBy(); // 解析order by語句parseOrderBy();customizedSelect(); // 解析不支持的操作processUnsupportedTokens();// TODO move to rewriteappendDerivedColumns();//解析聚合函數(shù)appendDerivedOrderBy();return selectStatement;}

processUnsupportedTokens();這行代碼，解析不支持的操作

private void processUnsupportedTokens() {//不支持unionif (sqlParser.equalAny(DefaultKeyword.UNION, DefaultKeyword.EXCEPT, DefaultKeyword.INTERSECT, DefaultKeyword.MINUS)) {throw new SQLParsingUnsupportedException(sqlParser.getLexer().getCurrentToken().getType());} }

?

總結(jié)下sql解析用到的數(shù)據(jù)模型

/*** 支持的數(shù)據(jù)庫類型.* * @author zhangliang*/ public enum DatabaseType {H2("H2"), MySQL("MySQL"), Oracle("Oracle"), SQLServer("Microsoft SQL Server"), PostgreSQL("PostgreSQL"); /*** Select SQL語句對象.** @author zhangliang*/ @Getter @Setter @ToString(callSuper = true) public final class SelectStatement extends DQLStatement {private boolean containStar;//是否查詢所有字段 select *private int selectListLastPosition;private int groupByLastPosition;private final List<SelectItem> items = new LinkedList<>();//選擇項對象private final List<OrderItem> groupByItems = new LinkedList<>();//排序項對象private final List<OrderItem> orderByItems = new LinkedList<>(); /*** 分頁對象.** @author zhangliang* @author caohao*/ @RequiredArgsConstructor @Getter @Setter @ToString public final class Limit {private final boolean rowCountRewriteFlag;private LimitValue offset; /*** 排序項.** @author zhangliang*/ @Getter @Setter @EqualsAndHashCode @ToString public final class OrderItem {private final Optional<String> owner;private final Optional<String> name;private final OrderType type;private int index = -1;private Optional<String> alias; /*** 排序類型.** @author zhangliang*/ public enum OrderType {ASC, DESC } /*** 選擇項.** @author zhangliang*/ @RequiredArgsConstructor @Getter @ToString public final class CommonSelectItem implements SelectItem {// 表達式private final String expression;// 別名private final Optional<String> alias; } *** 聚合選擇項.** @author zhangliang*/ @RequiredArgsConstructor @Getter @EqualsAndHashCode @ToString public final class AggregationSelectItem implements SelectItem {// 聚合選擇項內(nèi)容private final AggregationType type;private final String innerExpression;private final Optional<String> alias;private final List<AggregationSelectItem> derivedAggregationSelectItems = new ArrayList<>(2);@Setterprivate int index = -1; /*** 聚合函數(shù)類型.** @author zhangliang*/ public enum AggregationType {MAX, MIN, SUM, COUNT, AVG } /*** SQL類型.* * @author zhangliang*/ public enum SQLType {DQL, DML, DDL } /*** 表集合對象.* * @author zhangliang*/ @ToString public final class Tables {private final List<Table> tables = new ArrayList<>(); /*** 條件對象集合.** @author zhangliang*/ @RequiredArgsConstructor @ToString public final class Conditions {private final Map<Column, Condition> conditions = new LinkedHashMap<>(); /*** 表標(biāo)記對象.** @author zhangliang*/ @RequiredArgsConstructor @Getter @ToString public final class TableToken implements SQLToken {private final int beginPosition;private final String originalLiterals; /*** 排序類型.** @author zhangliang*/ public enum OrderType {ASC, DESC }

?

說到最后

以上sql解析實現(xiàn)的源碼解析，僅供參考。

轉(zhuǎn)載于:https://my.oschina.net/u/3775437/blog/1674183

總結(jié)

以上是生活随笔為你收集整理的sharding-jdbc源码解析之sql解析的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。