Shulou(Shulou.com)06/02 Report--

This article mainly introduces "SQL rewriting usage in sharding-jdbc". In daily operation, I believe many people have doubts about the usage of SQL rewriting in 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 about "SQL rewriting usage in sharding-jdbc". Next, please follow the editor to study!

This paper mainly uses SELECT I. * FROM t_order_1 o, t_order_item_1 I WHERE o.order_id = i.order_id and o.order_id =? And o.user_id =? A simple query statement to analyze how ss rewrites sql roughly. Different types of sql rewriting need to check the corresponding sql token generator.

Such as paging to view OffsetTokenGenerator

1.BaseShardingEngine#shard performs rewriting, mainly looking at the rewriteAndConvert method

@ RequiredArgsConstructorpublic abstract class BaseShardingEngine {/ / sharding rule private final ShardingRule shardingRule; / / sharding parameter private final ShardingProperties shardingProperties; / / sharding metadata / / routing hook private final SPIRoutingHook routingHook = new SPIRoutingHook (); / * Shard. * * @ param sql SQL * @ param parameters parameters of SQL * @ return SQL route result * / public SQLRouteResult shard (final String sql, final List parameters) {List clonedParameters = cloneParameters (parameters); SQLRouteResult result = executeRoute (sql, clonedParameters); / / sql rewrite, how to Hint without rewriting sql result.getRouteUnits () .addAll (HintManager.isDatabaseShardingOnly ()? Convert (sql, clonedParameters, result): rewriteAndConvert (clonedParameters, result); if (shardingProperties.getValue (ShardingPropertiesConstant.SQL_SHOW)) {boolean showSimple = shardingProperties.getValue (ShardingPropertiesConstant.SQL_SIMPLE); SQLLogger.logSQL (sql, showSimple, result.getOptimizedStatement (). GetSQLStatement (), result.getRouteUnits ());} return result;}. Private Collection convert (final String sql, final List parameters, final SQLRouteResult sqlRouteResult) {Collection result = new LinkedHashSet (); for (RoutingUnit each: sqlRouteResult.getRoutingResult (). GetRoutingUnits ()) {result.add (new RouteUnit (each.getDataSourceName (), new SQLUnit (sql, parameters));} return result } private Collection rewriteAndConvert (final List parameters, final SQLRouteResult sqlRouteResult) {/ / rewrite engine SQLRewriteEngine rewriteEngine = new SQLRewriteEngine (shardingRule, sqlRouteResult, parameters, sqlRouteResult.getRoutingResult (). IsSingleRouting ()); Collection result = new LinkedHashSet () / / traversing the routing unit, / / if t _ order and t_order_item are bound table relationships Then there is only one t_order for (RoutingUnit each: sqlRouteResult.getRoutingResult (). GetRoutingUnits ()) {/ / add the sql rewritten routing unit result.add (new RouteUnit (each.getDataSourceName ()), / / encapsulate the rewritten sql unit rewriteEngine.generateSQL (each, getLogicAndActualTables (each)) SqlRouteResult.getOptimizedStatement (). GetSQLStatement (). GetTables (). GetTableNames () } return result;} private Map getLogicAndActualTables (final RoutingUnit routingUnit, final Collection parsedTableNames) {Map result = new HashMap (); / / traversal table unit for (TableUnit each: routingUnit.getTableUnits ()) {String logicTableName = each.getLogicTableName () .toLowerCase () / / add logical table: real table t_order:t_order_0 result.put (logicTableName, each.getActualTableName ()) / / add the remaining parsing tables according to the binding table / / for example, if t_order and t_order_item are bound tables, and the parsing tables are t_order and t_order_item, then add t_order_item:t_order_item_0 result.putAll (getLogicAndActualTablesFromBindingTable (routingUnit.getMasterSlaveLogicDataSourceName (), each, parsedTableNames));} / / return the real table return result corresponding to the logical table } private Map getLogicAndActualTablesFromBindingTable (final String dataSourceName, final TableUnit tableUnit, final Collection parsedTableNames) {Map result = new LinkedHashMap (); / / obtain the corresponding binding tables t_order, t_order_item Optional bindingTableRule = shardingRule.findBindingTableRule (tableUnit.getLogicTableName ()) according to the logical table; if (bindingTableRule.isPresent ()) {result.putAll (dataSourceName, tableUnit, parsedTableNames, bindingTableRule.get () } return result;} private Map getLogicAndActualTablesFromBindingTable (final String dataSourceName, final TableUnit tableUnit, final Collection parsedTableNames, final BindingTableRule bindingTableRule) {Map result = new LinkedHashMap (); / / traverses parsed tables t_order, t_order_item for (String each: parsedTableNames) {String tableName = each.toLowerCase () / / parsing table and logical table do not want to wait, and parsing table is bound table if (! tableName.equals (tableUnit.getLogicTableName (). ToLowerCase ()) & & bindingTableRule.hasLogicTable (tableName)) {/ / add the real table corresponding to parsing table result.put (tableName, bindingTableRule.getBindingActualTable (dataSourceName, tableName, tableUnit.getActualTableName ());}} return result }}

two。 Rewrite SQL,SQLRewriteEngine#generateSQL

Public final class SQLRewriteEngine {/ / regular private final BaseRule baseRule; / / optimized Statement private final OptimizedStatement optimizedStatement; / / token private final List sqlTokens; / / sql builder private final SQLBuilder sqlBuilder; / / Parameter builder private final ParameterBuilder parameterBuilder; public SQLRewriteEngine (final ShardingRule shardingRule, final SQLRouteResult sqlRouteResult, final List parameters, final boolean isSingleRoute) {baseRule = shardingRule This.optimizedStatement = getEncryptedOptimizedStatement (shardingRule.getEncryptRule (). GetEncryptorEngine (), sqlRouteResult.getOptimizedStatement ()); / / placeholder parameter values parameterBuilder = createParameterBuilder (parameters, sqlRouteResult); / / create sql token, mainly through token to generate real sql sqlTokens = createSQLTokens (isSingleRoute); / / sql builder sqlBuilder = new SQLBuilder (optimizedStatement.getSQLStatement (). GetLogicSQL (), sqlTokens);}. Private List createSQLTokens (final boolean isSingleRoute) {List result = new LinkedList (); / / rewrite the SQL core to generate corresponding types of token based on the parsed segment, such as TableTokenGenerator- > TableToken / / basic token generation engine result.addAll (new BaseTokenGenerateEngine (). GenerateSQLTokens (optimizedStatement, parameterBuilder, baseRule, isSingleRoute)); / / Library and table rules if (baseRule instanceof ShardingRule) {ShardingRule shardingRule = (ShardingRule) baseRule Result.addAll (new ShardingTokenGenerateEngine (). GenerateSQLTokens (optimizedStatement, parameterBuilder, shardingRule, isSingleRoute); result.addAll (new EncryptTokenGenerateEngine (). GenerateSQLTokens (optimizedStatement, parameterBuilder, shardingRule.getEncryptRule (), isSingleRoute);} else if (baseRule instanceof EncryptRule) {result.addAll (new EncryptTokenGenerateEngine (). GenerateSQLTokens (optimizedStatement, parameterBuilder, (EncryptRule) baseRule, isSingleRoute)) } / / sort, which is mainly sorted according to the parsed startIndex, used to ensure the correctness of the sql token Collections.sort (result); return result;} / * Generate SQL. * * @ return sql unit * / public SQLUnit generateSQL () {return new SQLUnit (sqlBuilder.toSQL (), parameterBuilder.getParameters ());} / * * Generate SQL. * * @ param routingUnit routing unit * @ param logicAndActualTables logic and actual tables * @ return sql unit * / public SQLUnit generateSQL (final RoutingUnit routingUnit, final Map logicAndActualTables) {/ / encapsulates the sql unit and mainly generates sql return new SQLUnit (sqlBuilder.toSQL (routingUnit, logicAndActualTables), parameterBuilder.getParameters (routingUnit)) based on the real table corresponding to token index and logical table;}}

3. Build SQL,SQLBuilder#toSQL

@ RequiredArgsConstructorpublic final class SQLBuilder {/ / logical sql private final String logicSQL; / / sql token private final List sqlTokens; / * Convert to SQL. * * @ return SQL * / public String toSQL () {return toSQL (null, Collections.emptyMap ());} / * * Convert to SQL. * * @ param routingUnit routing unit * @ param logicAndActualTables logic and actual map * @ return SQL * / public String toSQL (final RoutingUnit routingUnit, final Map logicAndActualTables) {if (sqlTokens.isEmpty ()) {return logicSQL;} return createLogicSQL (routingUnit, logicAndActualTables);} private String createLogicSQL (final RoutingUnit routingUnit, final Map logicAndActualTables) {StringBuilder result = new StringBuilder () / / intercept the logic sql from 0 to the first token start index / / such as: SELECT i.* FROM t_order_1 o, t_order_item_1 I WHERE o.order_id = i.order_id and o.order_id =? And o.user_id =? / / take the above sql as an example, sqlTokens is: / / [TableToken (startIndex=16,stopIndex=22,tableName=t_order), TableToken (startIndex=27,stopIndex=38,tableName=t_order_item)] result.append (logicSQL.substring (0, sqlTokens.get (0). GetStartIndex () / / the result of intercepting is select * from / / traversing token for (SQLToken each: sqlTokens) {/ / take rewriting table as an example / / here it is rewritten to real table result.append (getSQLTokenLiterals (each, routingUnit, logicAndActualTables) according to logical table); / / result is t_order_0 / / alias result.append (getConjunctionLiterals (each)) is processed here / / the result is o,} return result.toString ();} private String getSQLTokenLiterals (final SQLToken sqlToken, final RoutingUnit routingUnit, final Map logicAndActualTables) {/ / determine whether the token is changeable (Alterable), call the toString method of the corresponding token / / if it is Alterable, return the real table corresponding to the logical table, that is, t_order:t_order_0, and return t_order_0 return sqlToken instanceof Alterable? ((Alterable) sqlToken) .toString (routingUnit, logicAndActualTables): sqlToken.toString ();} private String getConjunctionLiterals (final SQLToken sqlToken) {/ / TableToken (startIndex=16,stopIndex=22,tableName=t_order) / / TableToken (startIndex=27,stopIndex=38,tableName=t_order_item) / / find the index of the current sqlToken / / the first traversal currentSQLTokenIndex is 0 int currentSQLTokenIndex = sqlTokens.indexOf (sqlToken) / / calculate the end position to be intercepted / / the first traversal stopIndex is 27 int stopIndex = sqlTokens.size ()-1 = = currentSQLTokenIndex? LogicSQL.length (): sqlTokens.get (currentSQLTokenIndex + 1) .getStartIndex () / / calculate the starting position to be intercepted / / determine whether the starting position of the current sqlToken is greater than the logical sql length, and if the starting position is greater than the logical sql length, it is the logical sql length, otherwise the starting position of the current sqlToken / / traversing the startIndex:23 stopIndex:27 for the first time, the interception result is o, return logicSQL.substring (getStartIndex (sqlToken) > logicSQL.length ()? LogicSQL.length (): getStartIndex (sqlToken), stopIndex);} private int getStartIndex (final SQLToken sqlToken) {/ / determine whether token can be replaced, such as alias return sqlToken instanceof Substitutable? ((Substitutable) sqlToken) .getStopIndex () + 1: sqlToken.getStartIndex ();}} at this point, the study of "rewriting usage of SQL in sharding-jdbc" is over, hoping to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.