In the last article we looked at sharding-JDBC parsing SELECT statements (SQL parsing SELECT). Today we’ll look at SQL routing.

Disclaimer: This article is based on version 1.5.m1

Sequence diagram:

Perform logical

Select statement; Select statement;

SELECT o.order_id FROM order o WHERE o.order_id = 4

Before the analysis, first look at the configuration of sub-database sub-table:

 Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.put("ds_0", null);
        dataSourceMap.put("ds_1", null);
        DataSourceRule dataSourceRule = new DataSourceRule(dataSourceMap);
        TableRule orderTableRule = TableRule.builder("order").actualTables(Lists.newArrayList("order_0"."order_1")).dataSourceRule(dataSourceRule).build();
        TableRule orderItemTableRule = TableRule.builder("order_item").actualTables(Lists.newArrayList("order_item_0"."order_item_1")).dataSourceRule(dataSourceRule).build();
        TableRule orderAttrTableRule = TableRule.builder("order_attr").actualTables(Lists.newArrayList("ds_0.order_attr_a"."ds_1.order_attr_b")).dataSourceRule(dataSourceRule)
                .tableShardingStrategy(new TableShardingStrategy("order_id", new OrderAttrShardingAlgorithm())).build();
        shardingRule = ShardingRule.builder().dataSourceRule(dataSourceRule).tableRules(Lists.newArrayList(orderTableRule, orderItemTableRule, orderAttrTableRule))
                .bindingTableRules(Collections.singletonList(new BindingTableRule(Arrays.asList(orderTableRule, orderItemTableRule))))
                .databaseShardingStrategy(new DatabaseShardingStrategy("order_id", new OrderShardingAlgorithm()))
                .tableShardingStrategy(new TableShardingStrategy("order_id", new OrderShardingAlgorithm())).build();
Copy the code

The order table is divided into two libraries, two tables, with order_ID as the shard key

1. StatementRoutingEngine

  • Constructor: Create SQLRouter with ShardingContext
  • The SQLRouter parses the SQL (internally calling the SQLParsingEngine from the previous article) and routes it
public StatementRoutingEngine(final ShardingContext shardingContext) { sqlRouter = SQLRouterFactory.createSQLRouter(shardingContext); ** @param logicSQL Logical SQL * @returnRoute result */ public SQLRouteResult Route (Final String logicSQL) {SQLStatement SQLStatement = SQLRoute. parse(logicSQL, 0);return sqlRouter.route(logicSQL, Collections.emptyList(), sqlStatement);
    }
Copy the code

Here to determine whether only branch library, if only branch library, then new UnparsingSQLRouter, do not need to go to SQL parsing logic (directly to the specific library, SQL can be executed), otherwise new ParsingSQLRouter

** @param shardingContext Data source runtime context * @returnSQLRouter */ public static SQLRouter createSQLRouter(final ShardingContext ShardingContext) {return HintManagerHolder.isDatabaseShardingOnly() ? new UnparsingSQLRouter(shardingContext) : new ParsingSQLRouter(shardingContext);
    }
Copy the code

2, ParsingSQLRouter# route:

public SQLRouteResult route(final String logicSQL, final List<Object> parameters, final SQLStatement sqlStatement) {
        final Context context = MetricsContext.start("Route SQL");
        SQLRouteResult result = new SQLRouteResult(sqlStatement);
        if(sqlStatement instanceof InsertStatement && null ! = ((InsertStatement) sqlStatement).getGeneratedKey()) { (InsertStatement) sqlStatement, result); } // RoutingResult RoutingResult = route(parameters, sqlStatement); . Metricscontext.stop (context);logSQLRouteResult(result, parameters);
        return result;
    }
Copy the code

2-1. Obtain a logical table from the table object parsed in sqlStatement

If there is a single table, go SimpleRoutingEngine#route; otherwise, go ComplexRoutingEngine#route

private RoutingResult route(final List<Object> parameters, final SQLStatement sqlStatement) {
        Collection<String> tableNames = sqlStatement.getTables().getTableNames();
        RoutingEngine routingEngine;
        if (1 == tableNames.size() || shardingRule.isAllBindingTables(tableNames)) {
            routingEngine = new SimpleRoutingEngine(shardingRule, parameters, tableNames.iterator().next(), sqlStatement);
        } else{// TODO configurable whether to perform Cartesian product routingEngine = new ComplexRoutingEngine(shardingRule, Parameters, tableNames, sqlStatement); }return routingEngine.route();
    }
Copy the code

2-2, SimpleRoutingEngine# route

Under normal circumstances are single table, we will analyze the situation of a single table

  1. Get our configured TableRule based on the logical table noun
  2. Obtain the real DB and table according to TableRule
  3. Assembly RoutingResult
public RoutingResult route() {
        TableRule tableRule = shardingRule.getTableRule(logicTableName);
        Collection<String> routedDataSources = routeDataSources(tableRule);
        Collection<String> routedTables = routeTables(tableRule, routedDataSources);
        return generateRoutingResult(tableRule, routedDataSources, routedTables);
    }
Copy the code
  • GetTableRule: first according to the logical table lookup, find a direct return, not find our default database is not exist, and then use the default database to help us create a TableRule without sub-database sub-table
** @param logicTableName Specifies the logical table name * @return*/ Public TableRule getTableRule(final String logicTableName) {Optional<TableRule> TableRule = tryFindTableRule(logicTableName);if (tableRule.isPresent()) {
            return tableRule.get();
        }
        if (dataSourceRule.getDefaultDataSource().isPresent()) {
            return createTableRuleWithDefaultDataSource(logicTableName, dataSourceRule);
        }
        throw new ShardingJdbcException("Cannot find table rule and default data source with logic table: '%s'", logicTableName);
    }
Copy the code
private TableRule createTableRuleWithDefaultDataSource(final String logicTableName, final DataSourceRule defaultDataSourceRule) {
        Map<String, DataSource> defaultDataSourceMap = new HashMap<>(1);
        defaultDataSourceMap.put(defaultDataSourceRule.getDefaultDataSourceName(), defaultDataSourceRule.getDefaultDataSource().get());
        return TableRule.builder(logicTableName)
                .dataSourceRule(new DataSourceRule(defaultDataSourceMap))
                .databaseShardingStrategy(new DatabaseShardingStrategy("", new NoneDatabaseShardingAlgorithm()))
                .tableShardingStrategy(new TableShardingStrategy("", new NoneTableShardingAlgorithm())).build();
    }
Copy the code
  • RouteDataSources :(comments are written in the code)
Private Collection<String> routeDataSources(Final TableRule TableRule) {1. Obtain the database sharding policy according to TableRule DatabaseShardingStrategy strategy = shardingRule.getDatabaseShardingStrategy(tableRule); If there is a mandatory route, use the value of the mandatory route directly. If there is no mandatory route, use the ShardingValue List<ShardingValue<? >> shardingValues = HintManagerHolder.isUseShardingHint() ? getDatabaseShardingValuesFromHint(strategy.getShardingColumns()) : getShardingValues(strategy.getShardingColumns());logBeforeRoute("database", logicTableName, tableRule.getActualDatasourceNames(), strategy.getShardingColumns(), shardingValues); 3, call the subdivision strategy calculation shard values Collection < String > result = strategy. DoStaticSharding (sqlStatement. GetType (), tableRule.getActualDatasourceNames(), shardingValues);logAfterRoute("database", logicTableName, result); Preconditions.checkState(! result.isEmpty(),"no database route info");
        return result;
    }
    
Copy the code
  • RouteTables (no difference)
private Collection<String> routeTables(final TableRule tableRule, final Collection<String> routedDataSources) { TableShardingStrategy strategy = shardingRule.getTableShardingStrategy(tableRule); List<ShardingValue<? >> shardingValues = HintManagerHolder.isUseShardingHint() ? getTableShardingValuesFromHint(strategy.getShardingColumns()) : getShardingValues(strategy.getShardingColumns());logBeforeRoute("table", logicTableName, tableRule.getActualTables(), strategy.getShardingColumns(), shardingValues);
        Collection<String> result = tableRule.isDynamic() ? strategy.doDynamicSharding(shardingValues)
                : strategy.doStaticSharding(sqlStatement.getType(), tableRule.getActualTableNames(routedDataSources), shardingValues);
        logAfterRoute("table", logicTableName, result); Preconditions.checkState(! result.isEmpty(),"no table route info");
        return result;
    }
Copy the code

The feeling of forced routing can be written separately to say that an article, so I will not analyze, write later, we do not look at the logic of forced routing.

  • getShardingValues
private List<ShardingValue<? >> getShardingValues(final Collection<String> shardingColumns) { List<ShardingValue<? >> result = new ArrayList<>(shardingColumns.size());for(String each : ShardingColumns) {// The SQL parse getConditions object (briefly analyzed in the previous article), which checks whether the shard column exists, Is converted to ShardingValue Optional < Condition > Condition. = sqlStatement getConditions (.), find (new Column (each logicTableName));if(condition.isPresent()) { result.add(condition.get().getShardingValue(parameters)); }}return result;
    }
Copy the code
  • getShardingValue

Operator: This can be interpreted as the operator of the conditional object (=, in, between).

** @param parameters parameter list * @returnPublic ShardingValue<? > getShardingValue(final List<Object> parameters) { List<Comparable<? >> conditionValues = getValues(parameters); switch (operator) {case EQUAL:
                returnnew ShardingValue<Comparable<? >>(column.getTableName(), column.getName(), conditionValues.get(0));case IN:
                return new ShardingValue<>(column.getTableName(), column.getName(), conditionValues);
            case BETWEEN:
                returnnew ShardingValue<>(column.getTableName(), column.getName(), Range.range(conditionValues.get(0), BoundType.CLOSED, conditionValues.get(1), BoundType.CLOSED)); default: throw new UnsupportedOperationException(operator.getExpression()); }}Copy the code
  • getValues

PositionValueMap: Stores conditional values, positionIndexMap: stores conditional values.

private List<Comparable<? >> getValues(final List<Object> parameters) { List<Comparable<? >> result = new LinkedList<>(positionValueMap.values());for (Entry<Integer, Integer> entry : positionIndexMap.entrySet()) {
            Object parameter = parameters.get(entry.getValue());
            if(! (parameter instanceof Comparable<? >)) { throw new ShardingJdbcException("Parameter `%s` should extends Comparable for sharding value.", parameter);
            }
            if(entry.getKey() < result.size()) { result.add(entry.getKey(), (Comparable<? >) parameter); }else {
                result.add((Comparable<?>) parameter);
            }
        }
        return result;
    }
Copy the code
  • ShardingStrategy# doStaticSharding:
** @param sqlType type of SQL statement * @param availableTargetNames collection of all available sharding resources * @param shardingValues collection of shardingValues * @return*/ public Collection<String>doStaticSharding(final SQLType sqlType, final Collection<String> availableTargetNames, final Collection<ShardingValue<? >> shardingValues) { Collection<String> result = new TreeSet<>(String.CASE_INSENSITIVE_ORDER);if(shardingValues.isEmpty()) { Preconditions.checkState(! isInsertMultiple(sqlType, availableTargetNames),"INSERT statement should contain sharding value.");
            result.addAll(availableTargetNames);
        } else {
            result.addAll(doSharding(shardingValues, availableTargetNames));
        }
        return result;
    }
Copy the code

Normally we watch implement SingleKeyShardingAlgorithm classes, define our own shard logic, return to the results of the calculated value

private Collection<String> doSharding(final Collection<ShardingValue<? >> shardingValues, final Collection<String> availableTargetNames) {if (shardingAlgorithm instanceof NoneKeyShardingAlgorithm) {
            return Collections.singletonList(((NoneKeyShardingAlgorithm) shardingAlgorithm).doSharding(availableTargetNames, shardingValues.iterator().next()));
        }
        if(shardingAlgorithm instanceof SingleKeyShardingAlgorithm) { SingleKeyShardingAlgorithm<? > singleKeyShardingAlgorithm = (SingleKeyShardingAlgorithm<? >) shardingAlgorithm; ShardingValue shardingValue = shardingValues.iterator().next(); switch (shardingValue.getType()) {case SINGLE:
                    return Collections.singletonList(singleKeyShardingAlgorithm.doEqualSharding(availableTargetNames, shardingValue));
                case LIST:
                    return singleKeyShardingAlgorithm.doInSharding(availableTargetNames, shardingValue);
                case RANGE:
                    returnsingleKeyShardingAlgorithm.doBetweenSharding(availableTargetNames, shardingValue); default: throw new UnsupportedOperationException(shardingValue.getType().getClass().getName()); }}if (shardingAlgorithm instanceof MultipleKeysShardingAlgorithm) {
            return ((MultipleKeysShardingAlgorithm) shardingAlgorithm).doSharding(availableTargetNames, shardingValues);
        }
        throw new UnsupportedOperationException(shardingAlgorithm.getClass().getName());
    }
Copy the code
  • The last

Filter out the actual DataNode and assemble the RoutingResult

private RoutingResult generateRoutingResult(final TableRule tableRule, final Collection<String> routedDataSources, final Collection<String> routedTables) {
        RoutingResult result = new RoutingResult();
        for (DataNode each : tableRule.getActualDataNodes(routedDataSources, routedTables)) {
            result.getTableUnits().getTableUnits().add(new TableUnit(each.getDataSourceName(), logicTableName, each.getTableName()));
        }
        return result;
    }
Copy the code

Finally:

Welcome to follow my public account and share your thoughts on programming, investment and life from time to time 🙂