preface
In the last article we looked at some of the classes involved in SQL parsing and some of the methods used. Today we’ll look at the Select statement and see how Sharding-JDBC parsing works.
SELECT o.order_id FROM order o WHERE o.user_id = XXXX
Let’s analyze the query statement above.
SQLParsingEngine#parse()
public SQLStatement parseSQLParser SQLParser = getSQLParser(); sqlParser.skipIfEqual(Symbol.SEMI);if(sqlParser.equalAny(DefaultKeyword.WITH)) { skipWith(sqlParser); SQLStatementParser (parse());if (sqlParser.equalAny(DefaultKeyword.SELECT)) {
return SelectParserFactory.newInstance(sqlParser).parse();
}
if (sqlParser.equalAny(DefaultKeyword.INSERT)) {
return InsertParserFactory.newInstance(shardingRule, sqlParser).parse();
}
if (sqlParser.equalAny(DefaultKeyword.UPDATE)) {
return UpdateParserFactory.newInstance(sqlParser).parse();
}
if (sqlParser.equalAny(DefaultKeyword.DELETE)) {
return DeleteParserFactory.newInstance(sqlParser).parse();
}
throw new SQLParsingUnsupportedException(sqlParser.getLexer().getCurrentToken().getType());
}
Copy the code- Obtain SQLParser, this article uses MySQL as an example, return MySQLParser;
private SQLParser getSQLParser() {
switch (dbType) {
case H2:
case MySQL:
return new MySQLParser(sql, shardingRule);
case Oracle:
return new OracleParser(sql, shardingRule);
case SQLServer:
return new SQLServerParser(sql, shardingRule);
case PostgreSQL:
returnnew PostgreSQLParser(sql, shardingRule); default: throw new UnsupportedOperationException(dbType.name()); }}Copy the code- The first word for the SELECT, so go SelectParserFactory. NewInstance (sqlParser)
/** * Determines whether the current lexical token type is equal to one of the incoming values. ** @param tokenTypes The lexical token type to be determined * @returnPublic final Boolean equalAny(final TokenType... tokenTypes) {for (TokenType each : tokenTypes) {
if (each == lexer.getCurrentToken().getType()) {
return true; }}return false;
}
Copy the code- The input parameter is MySQLParser, which returns MySQLSelectParser and finally calls the parse() method of MySQLSelectParser
** @param sqlParser SQL parser * @returnPublic static AbstractSelectParser newInstance(final SQLParser SQLParser) {if (sqlParser instanceof MySQLParser) {
return new MySQLSelectParser(sqlParser);
}
if (sqlParser instanceof OracleParser) {
return new OracleSelectParser(sqlParser);
}
if (sqlParser instanceof SQLServerParser) {
return new SQLServerSelectParser(sqlParser);
}
if (sqlParser instanceof PostgreSQLParser) {
return new PostgreSQLSelectParser(sqlParser);
}
throw new UnsupportedOperationException(String.format("Cannot support sqlParser class [%s].", sqlParser.getClass()));
}
Copy the codeMySQLSelectParser# parse () method
public final SelectStatement parseQuery () {// query(); // parseOrderBy parseOrderBy(); customizedSelect(); appendDerivedColumns(); appendDerivedOrderBy();return selectStatement;
}
Copy the codeThe query () :
@Override
public void query() {
if(getSqlParser().equalAny(DefaultKeyword.SELECT)) { getSqlParser().getLexer().nextToken(); // parseDistinct parseDistinct(); // Skip some words getSqlParser().skipall (mysqlkey.high_priority, defaultkey.straight_join, mysqlkey.sql_small_result, MySQLKeyword.SQL_BIG_RESULT, MySQLKeyword.SQL_BUFFER_RESULT, MySQLKeyword.SQL_CACHE, MySQLKeyword.SQL_NO_CACHE, MySQLKeyword.SQL_CALC_FOUND_ROWS); // parse select items parseSelectList(); // Jump from skipToFrom(); } parseFrom(); / / parsingwhereConditions parseWhere (); // parseGroupBy parseGroupBy(); // parseOrderBy parseOrderBy(); / / parsinglimit
parseLimit();
if (getSqlParser().equalAny(DefaultKeyword.PROCEDURE)) {
throw new SQLParsingUnsupportedException(getSqlParser().getLexer().getCurrentToken().getType());
}
queryRest();
}
Copy the codeDue to the limited space, only analysis of parseSelectList, parseFrom, parseWhere, parseOrderBy this several with the beginning of SQL strongly related methods, other methods similar, I believe that their analysis will be more harvest 😊
- parseSelectList():
protected final void parseSelectList() {
do{// parseSelectItem parseSelectItem(); // loop condition: current lexical is ", "}while(sqlParser.skipIfEqual(Symbol.COMMA)); / / sets the position of the select finally end selectStatement setSelectListLastPosition (sqlParser. GetLexer () getCurrentToken () getEndPosition () sqlParser.getLexer().getCurrentToken().getLiterals().length()); }Copy the code private void parseSelectItem() {// Check whether the current is ROW_NUMBER,false
if (isRowNumberSelectItem()) {
selectStatement.getItems().add(parseRowNumberSelectItem());
return; } sqlParser.skipIfEqual(DefaultKeyword.CONNECT_BY_ROOT); String literals = sqlParser.getLexer().getCurrentToken().getLiterals(); // Whether to Select *false
if (isStarSelectItem(literals)) {
selectStatement.getItems().add(parseStarSelectItem());
return; } // whether is an aggregate function (Max, Sum etc)false
if (isAggregationSelectItem()) {
selectStatement.getItems().add(parseAggregationSelectItem(literals));
return; } StringBuilder expression = new StringBuilder(); Token lastToken = null; // Loop condition: after select, before from this part of each wordwhile(! sqlParser.equalAny(DefaultKeyword.AS) && ! sqlParser.equalAny(Symbol.COMMA) && ! sqlParser.equalAny(DefaultKeyword.FROM) && ! sqlParser.equalAny(Assist.END)) { String value = sqlParser.getLexer().getCurrentToken().getLiterals(); int position = sqlParser.getLexer().getCurrentToken().getEndPosition() - value.length(); expression.append(value); lastToken = sqlParser.getLexer().getCurrentToken(); sqlParser.getLexer().nextToken(); / / for "." , the last word is the alias of the table, add the table tag objectif(sqlParser.equalAny(Symbol.DOT)) { selectStatement.getSqlTokens().add(new TableToken(position, value)); }} // Whether there is an alias judgment (o.xx)false
if (hasAlias(expression, lastToken)) {
selectStatement.getItems().add(parseSelectItemWithAlias(expression, lastToken));
return;
}
//add CommonSelectItem
selectStatement.getItems().add(new CommonSelectItem(SQLUtil.getExactlyValue(expression.toString()), sqlParser.parseAlias()));
}
Copy the codeAfter this step is complete, the List
items and sqlTokens of the SelectStatement are filled in.
public final void parseFrom() {// skip FROMif(sqlParser. SkipIfEqual (DefaultKeyword.FROM)) {// parseTable parseTable(); }}Copy the code public void parseTable() {// If it contains the "(" symbol, there is a sub-queryfalse
if (sqlParser.skipIfEqual(Symbol.LEFT_PAREN)) {
if(! selectStatement.getTables().isEmpty()) { throw new UnsupportedOperationException("Cannot support subquery for nested tables.");
}
selectStatement.setContainStar(false); / / skip useless nested parentheses sqlParser. SkipUselessParentheses (); Parse (); parse(); sqlParser.skipUselessParentheses();if(! selectStatement.getTables().isEmpty()) {return; } // parseTableFactor(); parseJoinTable(); }Copy the code protected final void parseTableFactor() { int beginPosition = sqlParser.getLexer().getCurrentToken().getEndPosition() - sqlParser.getLexer().getCurrentToken().getLiterals().length(); //order String literals = sqlParser.getLexer().getCurrentToken().getLiterals(); // o sqlParser.getLexer().nextToken(); // TODO includes Schema parsingif (sqlParser.skipIfEqual(Symbol.DOT)) {
sqlParser.getLexer().nextToken();
sqlParser.parseAlias();
return; } / / FIXME according to shardingRule filter table / / add TableToken tag selectStatement. GetSqlTokens (). The add (new TableToken (beginPosition, literals)); / / add the Table selectStatement. GetTables (). The add (new Table (SQLUtil. GetExactlyValue (literals), sqlParser. ParseAlias ())); }Copy the code/** * Resolve alias. O * * @ is returnedreturnAlias */ public Optional<String>parseAlias() {
if (skipIfEqual(DefaultKeyword.AS)) {
if (equalAny(Symbol.values())) {
return Optional.absent();
}
String result = SQLUtil.getExactlyValue(getLexer().getCurrentToken().getLiterals());
getLexer().nextToken();
returnOptional.of(result); } // o is marked as IDENTIFIER // TODO adds the configuration of which databases recognize which keywords as aliasesif (equalAny(Literals.IDENTIFIER, Literals.CHARS, DefaultKeyword.USER, DefaultKeyword.END, DefaultKeyword.CASE, DefaultKeyword.KEY, DefaultKeyword.INTERVAL, DefaultKeyword.CONSTRAINT)) {
// o
String result = SQLUtil.getExactlyValue(getLexer().getCurrentToken().getLiterals());
getLexer().nextToken();
return Optional.of(result);
}
return Optional.absent();
}
Copy the codeAfter parseFrom(), the Table and TableToken tokens are added
protected final void parseWhere() {
if (selectStatement.getTables().isEmpty()) {
return; } // parse query conditions sqlparser. parseWhere(selectStatement); parametersIndex = sqlParser.getParametersIndex(); }Copy the code** @param sqlStatement SQL statement object */ public final void parseWhere(final sqlStatement sqlStatement) {// Parse alias parseAlias(); / / whetherwheretagif(skipIfEqual(DefaultKeyword.WHERE)) { parseConditions(sqlStatement); }}Copy the codePrivate void parseConditions(final SQLStatement SQLStatement) {// Loop conditions: Each word is separated by and (YeswhereConditional semantics)do {
parseComparisonCondition(sqlStatement);
} while (skipIfEqual(DefaultKeyword.AND));
if(equalAny(DefaultKeyword.OR)) { throw new SQLParsingUnsupportedException(getLexer().getCurrentToken().getType()); }}Copy the code3-1. Parse SQL expressions:
SQLPropertyExpression
3-2, the judgment condition, because is “=” so the walk is equal condition
public final void parseComparisonCondition(final SQLStatement sqlStatement) { skipIfEqual(Symbol.LEFT_PAREN); //1, SQL expression list left = parseExpression(sqlStatement); //2if(equalAny(symbol.eq)) {// Condition parseEqualCondition(sqlStatement, left); skipIfEqual(Symbol.RIGHT_PAREN);return; } / /inconditionsif (equalAny(DefaultKeyword.IN)) {
parseInCondition(sqlStatement, left);
skipIfEqual(Symbol.RIGHT_PAREN);
return; } / / between conditionsif (equalAny(DefaultKeyword.BETWEEN)) {
parseBetweenCondition(sqlStatement, left);
skipIfEqual(Symbol.RIGHT_PAREN);
return;
}
if (equalAny(Symbol.LT, Symbol.GT, Symbol.LT_EQ, Symbol.GT_EQ)) {
if (left instanceof SQLIdentifierExpression && sqlStatement instanceof SelectStatement
&& isRowNumberCondition((SelectStatement) sqlStatement, ((SQLIdentifierExpression) left).getName())) {
parseRowNumberCondition((SelectStatement) sqlStatement);
} else if (left instanceof SQLPropertyExpression && sqlStatement instanceof SelectStatement
&& isRowNumberCondition((SelectStatement) sqlStatement, ((SQLPropertyExpression) left).getName())) {
parseRowNumberCondition((SelectStatement) sqlStatement);
} else{ parseOtherCondition(sqlStatement); }}else if (equalAny(DefaultKeyword.LIKE)) {
parseOtherCondition(sqlStatement);
}
skipIfEqual(Symbol.RIGHT_PAREN);
}
Copy the code** @param sqlStatement SQL statement object * @returnExpression */ public final SQLExpression parseExpression(final SQLStatement SQLStatement) {int beginPosition = getLexer().getCurrentToken().getEndPosition(); SQLExpression result = parseExpression();if (result instanceof SQLPropertyExpression) {
setTableToken(sqlStatement, beginPosition, (SQLPropertyExpression) result);
}
return result;
}
Copy the code/** ** parse the expression. ** @returnPublic final SQLExpression */ // TODO improves Expression parsingparseExpression() { String literals = getLexer().getCurrentToken().getLiterals(); SQLIdentifierExpression final SQLExpression expression = getExpression(literals); // The left of the = sign returns SQLIdentifierExpression final SQlexexpression = getExpression(literals); //true
if (skipIfEqual(Literals.IDENTIFIER)) {
//true
if(skipIfEqual(Symbol.DOT)) { //user_id String property = getLexer().getCurrentToken().getLiterals(); getLexer().nextToken(); // Finally returns SQLPropertyExpressionreturn skipIfCompositeExpression() ? new SQLIgnoreExpression() : new SQLPropertyExpression(new SQLIdentifierExpression(literals), property);
}
if (equalAny(Symbol.LEFT_PAREN)) {
skipParentheses();
skipRestCompositeExpression();
return new SQLIgnoreExpression();
}
return skipIfCompositeExpression() ? new SQLIgnoreExpression() : expression;
}
getLexer().nextToken();
return skipIfCompositeExpression() ? new SQLIgnoreExpression() : expression;
}
Copy the code private SQLExpression getExpression(final String literals) {
if (equalAny(Symbol.QUESTION)) {
increaseParametersIndex();
return new SQLPlaceholderExpression(getParametersIndex() - 1);
}
if (equalAny(Literals.CHARS)) {
return new SQLTextExpression(literals);
}
if (equalAny(Literals.INT)) {
return new SQLNumberExpression(NumberUtil.getExactlyNumber(literals, 10));
}
if (equalAny(Literals.FLOAT)) {
return new SQLNumberExpression(Double.parseDouble(literals));
}
if (equalAny(Literals.HEX)) {
returnnew SQLNumberExpression(NumberUtil.getExactlyNumber(literals, 16)); } / /true
if (equalAny(Literals.IDENTIFIER)) {
return new SQLIdentifierExpression(SQLUtil.getExactlyValue(literals));
}
return new SQLIgnoreExpression();
}
Copy the code3-3. Parse the expression on the right of =, where *SQLNumberExpression is displayed
private void parseEqualCondition(final SQLStatement sqlStatement, final SQLExpression left) {
getLexer().nextToken();
//SQLNumberExpression
SQLExpression right = parseExpression(sqlStatement);
//true// TODO if there are multiple tables and the column belongs to the condition, the binding table will be resolved laterif((sqlStatement.getTables().isSingleTable() || left instanceof SQLPropertyExpression) && (right instanceof SQLNumberExpression | | right instanceof SQLTextExpression | | right instanceof SQLPlaceholderExpression)) {/ / handle the column Optional<Column> column = find(sqlStatement.getTables(), left); / / add conditionif(column.isPresent()) { sqlStatement.getConditions().add(new Condition(column.get(), right), shardingRule); }}}Copy the code3-4. Only shard columns can be added to condition
@param condition; @param shardingRule; @param condition; For example, if there is a condition equal to the operation, and only a condition with the same column containing the = symbol exists, then the existing condition is not added. Condition public void add(final condition condition, final ShardingRule ShardingRule)if(shardingRule.isShardingColumn(condition.getColumn())) { conditions.put(condition.getColumn(), condition); }}Copy the codeWe set the shard column to user_id. After executing the shard column, our SQL assignment results are as follows:
"Column(name=user_id, tableName=order)" -> "Condition(column=Column(name=user_id, tableName=order), operator=EQUAL, positionValueMap={0=1000}, positionIndexMap={})"
Copy the codeThe final selectStatement is as follows:
Conclusion:
SQL parsing is in filling SQLStatement (SQL statement object), the table name, SQL tags, the where condition (shard column, the condition is <, > or =) parsing, for the use of the routing time, put a SQLStatement inheritance diagram below: I view a larger version
Finally:
Welcome to follow my public account and share your thoughts on programming, investment and life from time to time 🙂
