Shading - JDBC source code analysis (3) - SQL parsing Select

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 code

MySQLSelectParser# parse () method

public final SelectStatement parseQuery () {// query(); // parseOrderBy parseOrderBy(); customizedSelect(); appendDerivedColumns(); appendDerivedOrderBy();return selectStatement;
    }
Copy the code

The 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 code

Due 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 code

After 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 code

After 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 code

Private 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 code

3-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 code

3-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 code

3-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 code

We 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 code

The 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 🙂

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Shading – JDBC source code analysis (3) – SQL parsing Select

preface

SQLParsingEngine#parse()

MySQLSelectParser# parse () method

The query () :

Conclusion:

Finally:

Shading – JDBC source code analysis (3) – SQL parsing Select

preface

SQLParsingEngine#parse()

MySQLSelectParser# parse () method

The query () :

Conclusion:

Finally:

Related Posts

Follow me to Dachang. – Basics

And the interviewer of the core department of Meituan studied the interview questions for an hour

Dynamic log level: small function, big function