An example of ANTLR4 SQL usage of sharding-jdbc

This article mainly introduces "examples of ANTLR4 SQL usage of sharding-jdbc". In daily operation, I believe many people have doubts about the examples of ANTLR4 SQL usage of sharding-jdbc. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "examples of ANTLR4 SQL usage of sharding-jdbc"! Next, please follow the editor to study!

Sharding mainly uses ANTLR4 to parse SQL. Take mysql as an example, you can learn the following three points before analyzing the source code:

Antlr4, how to write .g4 syntax files

For mysql syntax, please refer to https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-data-manipulation.html

You can refer to https://github.com/antlr/grammars-v4/blob/master/mysql for writing mysql G4 files.

Source code analysis

1. Parsing entry ParsingSQLRouter#parse

/ * * parsing sql * * @ param logicSQL logic sql * @ param useCache whether the parsed result is cached * @ return * / @ Override public SQLStatement parse (final String logicSQL, final boolean useCache) {/ / pre-parsed hook, such as call chain etx parsingHook.start (logicSQL) Try {/ / parse SQL SQLStatement result = new ShardingSQLParseEntry (databaseType, shardingMetaData.getTable (), parsingResultCache) .parse (logicSQL, useCache); / / hook parsingHook.finishSuccess (result, shardingMetaData.getTable ()) after successful parsing; return result / / CHECKSTYLE:OFF} catch (final Exception ex) {/ / CHECKSTYLE:ON / / failed to resolve the hook parsingHook.finishFailure (ex); throw ex;}} public final class ShardingSQLParseEntry extends SQLParseEntry {private final DatabaseType databaseType; private final ShardingTableMetaData shardingTableMetaData Public ShardingSQLParseEntry (final DatabaseType databaseType, final ShardingTableMetaData shardingTableMetaData, final ParsingResultCache parsingResultCache) {super (parsingResultCache); this.databaseType = databaseType; this.shardingTableMetaData = shardingTableMetaData } / * get parsing engine encapsulated object according to sql * / @ Override protected SQLParseEngine getSQLParseEngine (final String sql) {/ / Parameter 1: singleton Load statement, extract, filter profile / / Parameter 2: database Type / / Parameter 3: need to parse sql / / Parameter 4: sharded table metadata return new SQLParseEngine (ShardingParseRuleRegistry.getInstance (), databaseType, sql, shardingTableMetaData) }}

2.ShardingParseRuleRegistry.getInstance ()-> ParseRuleRegistry#initParseRuleDefinition loads statement, extracts, and filters configuration files

Private void initParseRuleDefinition () {/ / load the META-INF/parsing-rule-definition/extractor-rule-definition.xml configuration file ExtractorRuleDefinitionEntity generalExtractorRuleEntity = extractorRuleLoader.load (RuleDefinitionFileConstant.getExtractorRuleDefinitionFile ()) using JAXB; / / load the META-INF/parsing-rule-definition/filler-rule-definition.xml configuration file FillerRuleDefinitionEntity generalFillerRuleEntity = fillerRuleLoader.load (RuleDefinitionFileConstant.getFillerRuleDefinitionFile ()) using JAXB / / add the corresponding type (sharding, masterslave, encrypt) configuration file / / META-INF/parsing-rule-definition/sharding/filler-rule-definition.xml FillerRuleDefinitionEntity featureGeneralFillerRuleEntity = fillerRuleLoader.load (RuleDefinitionFileConstant.getFillerRuleDefinitionFile (getType () / / load the corresponding configuration file for (DatabaseType each: SQLParserFactory.getAddOnDatabaseTypes ()) {/ / META-INF/parsing-rule-definition/sharding.mysql/filler-rule-definition.xml / / databaseType:rules fillerRuleDefinitions.put (each, createFillerRuleDefinition (generalFillerRuleEntity, featureGeneralFillerRuleEntity, each) according to the database type) / META-INF/parsing-rule-definition/sharding.mysql/extractor-rule-definition.xml / / META-INF/parsing-rule-definition/sharding.mysql/sql-statement-rule-definition.xml / / databaseType:rules sqlStatementRuleDefinitions.put (each, createSQLStatementRuleDefinition (generalExtractorRuleEntity, each)) }} private FillerRuleDefinition createFillerRuleDefinition (final FillerRuleDefinitionEntity generalFillerRuleEntity, final FillerRuleDefinitionEntity featureGeneralFillerRuleEntity, final DatabaseType databaseType) {return new FillerRuleDefinition (generalFillerRuleEntity, featureGeneralFillerRuleEntity, fillerRuleLoader.load (RuleDefinitionFileConstant.getFillerRuleDefinitionFile (getType (), databaseType)) } private SQLStatementRuleDefinition createSQLStatementRuleDefinition (final ExtractorRuleDefinitionEntity generalExtractorRuleEntity, final DatabaseType databaseType) {/ / package all extractors together / / id:extractor ExtractorRuleDefinition extractorRuleDefinition = new ExtractorRuleDefinition (generalExtractorRuleEntity, extractorRuleLoader.load (RuleDefinitionFileConstant.getExtractorRuleDefinitionFile (getType (), databaseType) / / sql-statement-rule-definition.xml / / Context:SQLStatementRule / / SQLStatementRule encapsulates the extractor return new SQLStatementRuleDefinition corresponding to statement (statementRuleLoader.load (RuleDefinitionFileConstant.getSQLStatementRuleDefinitionFile (getType (), databaseType), extractorRuleDefinition);}

3.SQLParseEntry#parse, where the abstract SQLParseEntry, mainly has different entrances (EncryptSQLParseEntry, MasterSlaveSQLParseEntry, ShardingSQLParseEntry)

@ RequiredArgsConstructorpublic abstract class SQLParseEntry {private final ParsingResultCache parsingResultCache; / * Parse SQL. * * @ param sql SQL * @ param useCache use cache or not * @ return SQL statement * / public final SQLStatement parse (final String sql, final boolean useCache) {/ / get parsed SQLStatement Optional cachedSQLStatement = getSQLStatementFromCache (sql, useCache) from the cache; if (cachedSQLStatement.isPresent ()) {return cachedSQLStatement.get () } / / parse SQLStatement result = getSQLParseEngine (sql). Parse (); / / cache if (useCache) {parsingResultCache.put (sql, result);} return result;} private Optional getSQLStatementFromCache (final String sql, final boolean useCache) {return useCache? Optional.fromNullable (parsingResultCache.getSQLStatement (sql)): Optional.absent ();} / / get SQLParseEngine protected abstract SQLParseEngine getSQLParseEngine (String sql) based on the getSQLParseEngine of the subclass ShardingSQLParseEntry;}

4.SQLParseEngine#parse, including parsing, extracting, and filling SQLStatement

Public SQLParseEngine (final ParseRuleRegistry parseRuleRegistry, final DatabaseType databaseType, final String sql, final ShardingTableMetaData shardingTableMetaData) {DatabaseType trunkDatabaseType = DatabaseTypes.getTrunkDatabaseType (databaseType.getName ()); / / sql parsing engine parserEngine = new SQLParserEngine (parseRuleRegistry, trunkDatabaseType, sql); / / sql extraction engine extractorEngine = new SQLSegmentsExtractorEngine (); / / sql padding engine fillerEngine = new SQLStatementFillerEngine (parseRuleRegistry, trunkDatabaseType, sql, shardingTableMetaData) } / * Parse SQL. * * @ return SQL statement * / public SQLStatement parse () {/ / parse sql SQLAST ast = parserEngine.parse () using ANTLR4; / / extract the token from ast and encapsulate it into the corresponding segment, such as TableSegment, IndexSegment Collection sqlSegments = extractorEngine.extract (ast); Map parameterMarkerIndexes = ast.getParameterMarkerIndexes (); / / fill SQLStatement return fillerEngine.fill (sqlSegments, parameterMarkerIndexes.size (), ast.getSqlStatementRule ()) }

5.SQLParserEngine#parse, parsing SQL, encapsulating AST (Abstract Syntax Tree abstract syntax tree)

Public SQLAST parse () {/ / SPI uses ANTLR4 parsing to get SQLParser (MySQLParserEntry) execution to get the parsing tree ParseTree parseTree = SQLParserFactory.newInstance (databaseType, sql). Execute (). GetChild (0); if (parseTree instanceof ErrorNode) {throw new SQLParsingException (String.format ("Unsupported SQL of `% s`", sql)) } / / get the StatementContext in the configuration file, such as CreateTableContext, SelectContext SQLStatementRule sqlStatementRule = parseRuleRegistry.getSQLStatementRule (databaseType, parseTree.getClass (). GetSimpleName ()); if (null = = sqlStatementRule) {throw new SQLParsingException ("Unsupported SQL of `% s`", sql)) } / / encapsulate ast (Abstract Syntax Tree Abstract Syntax Tree) return new SQLAST ((ParserRuleContext) parseTree, getParameterMarkerIndexes ((ParserRuleContext) parseTree), sqlStatementRule);} / * Recursively get all parameter placeholders * * @ param rootNode root node * @ return * / private Map getParameterMarkerIndexes (final ParserRuleContext rootNode) {Collection placeholderNodes = ExtractorUtils.getAllDescendantNodes (rootNode, RuleName.PARAMETER_MARKER) Map result = new HashMap (placeholderNodes.size (), 1); int index = 0; for (ParserRuleContext each: placeholderNodes) {result.put (each, index++);} return result;}

6. Create a SQLParser using SQLParserFactory#newInstance

/ * New instance of SQL parser. * * @ param databaseType database type * @ param sql SQL * @ return SQL parser * / public static SQLParser newInstance (final DatabaseType databaseType Final String sql) {/ / SPI load all extended for (SQLParserEntry each: NewInstanceServiceLoader.newServiceInstances (SQLParserEntry.class)) {/ / determine the database type if (DatabaseTypes.getActualDatabaseType (each.getDatabaseType ()) = = databaseType) {/ / parse sql return createSQLParser (sql, each) }} throw new UnsupportedOperationException (String.format ("Cannot support database type'% s'", databaseType));} @ SneakyThrows private static SQLParser createSQLParser (final String sql, final SQLParserEntry parserEntry) {/ / lexical analyzer Lexer lexer = parserEntry.getLexerClass () .getConstructor (CharStream.class) .newInstance (CharStreams.fromString (sql)) / / parser return parserEntry.getParserClass () .getConstructor (TokenStream.class) .newInstance (new CommonTokenStream (lexer));}

7. Taking select as an example, the process of SQL parsing, extraction and filling in the fourth step is analyzed.

Use idea's antlr4 plug-in to parse SQL; using Sharding's mysql .g4 file as shown in the figure:

Referring to the figure above, use the sharding parse parsing module to extract (extractor) the parameters corresponding to ParserRuleContext and encapsulate them into Segment.

8.SQLSegmentsExtractorEngine#extract, refer to the figure in part 7, and generate the corresponding type of Segment according to the extractors corresponding to SQLStatementRule- > tableReferences, columns, selectItems, where, predicate, groupBy, orderBy, limit and subqueryPredicate.

Public final class SQLSegmentsExtractorEngine {/ * * Extract SQL segments. * * @ param ast SQLAST * @ return SQL segments * / public Collection extract (final SQLAST ast) {Collection result = new LinkedList (); / / traverses the Context corresponding extractor and encapsulates the corresponding type of Segment, such as TableSegment, IndexSegment / / with SELECT i.* FROM t_order o, t_order_item I WHERE o.order_id = i.order_id and o.order_id =? For example / / SelectContext- > SQLStatementRule / / SQLStatementRule- > tableReferences, columns, selectItems, where, predicate, groupBy, orderBy, limit, subqueryPredicate / / analyze nine extractors for (SQLSegmentExtractor each: ast.getSqlStatementRule (). GetExtractors ()) {/ / divided into two types / / 1. Single tree, directly extract token; under a single RuleName see sql parsed syntax tree comparison is relatively clear if (each instanceof OptionalSQLSegmentExtractor) {Optional