Routing condition

In ParsingSQLRouter.java, the following conditions are used to determine whether a simple routing or a complex routing is needed.

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)
            || shardingRule.isAllInDefaultDataSource(tableNames)) {
        routingEngine = new SimpleRoutingEngine(shardingRule, parameters, tableNames.iterator().next(), sqlStatement);
    } else {
        // TODO config for cartesian set
        routingEngine = new ComplexRoutingEngine(shardingRule, parameters, tableNames, sqlStatement);
    }
    return routingEngine.route();
}

• Is there only one table - tableNames.size()
  

ShardingRule.builder()
.dataSourceRule(dataSourceRule)
.tableRules(Arrays.asList(orderTableRule, userTableRule))
.databaseShardingStrategy(*** ***).tableShardingStrategy(*** ***) .build();

• Are they all bound tables - shardingRule.isAllBindingTables(tableNames)
  

ShardingRule.builder()
.dataSourceRule(dataSourceRule)
.tableRules(Arrays.asList(orderTableRule, orderItemTableRule, userTableRule))
.bindingTableRules(Collections.singletonList(new BindingTableRule(Arrays.asList(orderTableRule, orderItemTableRule))))
. *** ***;       

• Are they all in the default data source - shardingRule.isAllInDefaultDataSource(tableNames)
  

public boolean isAllInDefaultDataSource(final Collection<String> logicTables) {
    for (String each : logicTables) {
        if (tryFindTableRule(each).isPresent()) {
            return false;
        }
    }
    return !logicTables.isEmpty();
}

public Optional<TableRule> tryFindTableRule(final String logicTableName) {
    for (TableRule each : tableRules) {
        if (each.getLogicTable().equalsIgnoreCase(logicTableName)) {
            return Optional.of(each);
        }
    }
    return Optional.absent();
}   

Constructing Complex Routing

In summary, if the three conditions are not satisfied, the complex routing Complex Routing Engine is used to construct this scenario:
T_order and t_order_item sub-database sub-table and bind the table relationship, add a new sub-database sub-table t_user; Sharding Rule is as follows:

ShardingRule shardingRule = ShardingRule.builder()
        .dataSourceRule(dataSourceRule)
        .tableRules(Arrays.asList(orderTableRule, orderItemTableRule, userTableRule))
        .bindingTableRules(Collections.singletonList(new BindingTableRule(Arrays.asList(orderTableRule, orderItemTableRule))))
        .databaseShardingStrategy(new DatabaseShardingStrategy("user_id", new ModuloDatabaseShardingAlgorithm()))
        .tableShardingStrategy(new TableShardingStrategy("order_id", new ModuloTableShardingAlgorithm()))
        .build();

The SQL executed is:

SELECT od.user_id, od.order_id, oi.item_id, od.status 
FROM `t_user` tu 
join t_order od on tu.user_id=od.user_id 
join t_order_item oi on od.order_id=oi.order_id 
where tu.`status`='VALID' and tu.user_id=?

ComplexRoutingEngine

The core logic of the complex routing engine is to divide it into several simple routes, and then calculate Cartesian product. The core source code of the complex routing engine is as follows:

@RequiredArgsConstructor
@Slf4j
public final class ComplexRoutingEngine implements RoutingEngine {

    // Sub-database and sub-table rules
    private final ShardingRule shardingRule;

    // SQL request parameter, lard with a user_id value of 10
    private final List<Object> parameters;

    // Set of logical tables: t_order, t_order_item, t_user, three logical tables
    private final Collection<String> logicTables;

    // SQL parsing results
    private final SQLStatement sqlStatement;

    // The Core Logic of Complex Routing
    @Override
    public RoutingResult route() {
        Collection<RoutingResult> result = new ArrayList<>(logicTables.size());
        Collection<String> bindingTableNames = new TreeSet<>(String.CASE_INSENSITIVE_ORDER);
        // Traversal logical table set
        for (String each : logicTables) {
            Optional<TableRule> tableRule = shardingRule.tryFindTableRule(each);
            // If the traversal table is configured with the database partition rule
            if (tableRule.isPresent()) {
                // If the binding relation table has been processed, then no further processing is needed, such as t_order processing, because t_order_item is a binding relationship with it, then no further processing is needed.
                if (!bindingTableNames.contains(each)) {
                    // Construct a simple routing rule based on the current traversal logical table
                    result.add(new SimpleRoutingEngine(shardingRule, parameters, tableRule.get().getLogicTable(), sqlStatement).route());
                }

                // According to the current logical table, find all the corresponding binding tables, such as t_order and t_order_item according to t_order; if the. bindingTableRules(***t_point, t_point_detail***) are configured, then t_point and t_point_detail can be queried according to t_point, the purpose of which is that N binding tables only need to route one binding table, because the routing between binding tables is related. It's exactly the same.
                Optional<BindingTableRule> bindingTableRule = shardingRule.findBindingTableRule(each);
                if (bindingTableRule.isPresent()) {
                    bindingTableNames.addAll(Lists.transform(bindingTableRule.get().getTableRules(), new Function<TableRule, String>() {

                        @Override
                        public String apply(final TableRule input) {
                            return input.getLogicTable();
                        }
                    }));
                }
            }
        }
        log.trace("mixed tables sharding result: {}", result);
        // If the routing is complex, but the result is empty, then an exception is thrown
        if (result.isEmpty()) {
            throw new ShardingJdbcException("Cannot find table rule and default data source with logic tables: '%s'", logicTables);
        }
        // If the size of the result is 1, return directly.
        if (1 == result.size()) {
            return result.iterator().next();
        }
        // Calculating the Cartesian product of the just routing result set is the final complex routing result.
        return new CartesianRoutingEngine(result).route();
    }
}

The result s of debug are as follows:

CartesianRoutingEngine

As mentioned above, after finding a simple routing result set, the Cartesian product is the final routing result of complex routing. The core source code of Cartesian Routing Engine is as follows:

@RequiredArgsConstructor
@Slf4j
public final class CartesianRoutingEngine implements RoutingEngine {

    private final Collection<RoutingResult> routingResults;

    @Override
    public CartesianRoutingResult route() {
        CartesianRoutingResult result = new CartesianRoutingResult();
        // The analysis of getDataSourceLogicTablesMap() refers to the following analysis
        for (Entry<String, Set<String>> entry : getDataSourceLogicTablesMap().entrySet()) {
            // According to the data source-logical table, the actual set of tables is obtained, i.e. [["t_user"],["t_order_0","t_order_1"]]
            List<Set<String>> actualTableGroups = getActualTableGroups(entry.getKey(), entry.getValue());
            // To encapsulate logical table names, TableUnit's attributes are: data source name, logical table name, and actual table name (these three attributes determine the final table accessed)
            List<Set<TableUnit>> tableUnitGroups = toTableUnitGroups(entry.getKey(), actualTableGroups);
            // Calculate the Cartesian product of all actual tables
            result.merge(entry.getKey(), getCartesianTableReferences(Sets.cartesianProduct(tableUnitGroups)));
        }
        log.trace("cartesian tables sharding result: {}", result);
        return result;
    }

    // Get the Map of Data Source-Logic Table Set
    private Map<String, Set<String>> getDataSourceLogicTablesMap() {
        // The key here is to get the intersection of data sources (the t_user logical table is routed to the data source ds_jdbc_0 in the analysis above, and the t_order table is routed to the data source ds_jdbc_0 and ds_jdbc_1, and the data source intersection is ds_jdbc_0).
        Collection<String> intersectionDataSources = getIntersectionDataSources();
        Map<String, Set<String>> result = new HashMap<>(routingResults.size());
        for (RoutingResult each : routingResults) {
            for (Entry<String, Set<String>> entry : each.getTableUnits().getDataSourceLogicTablesMap(intersectionDataSources).entrySet()) {
                if (result.containsKey(entry.getKey())) {
                    result.get(entry.getKey()).addAll(entry.getValue());
                } else {
                    result.put(entry.getKey(), entry.getValue());
                }
            }
        }
        // The final result is a Map consisting of a set of data sources and logical tables. Here is {"ds_jdbc_0":["t_order", "t_user"]}
        return result;
    }
    ... ...
}

The results of Cartesian product are as follows:

The results of sql.show are as follows. You can see the two actual SQLs rewritten: t_user&t_order_0 and t_user&t_order_1 (t_order_item and t_order are binding tables, which can be consistent):

[INFO ] 2018-05-08 11:13:02,044 --main-- [Sharding-JDBC-SQL] Logic SQL: SELECT od.user_id, od.order_id, oi.item_id, od.status FROM `t_user` tu join t_order od on tu.user_id=od.user_id join t_order_item oi on od.order_id=oi.order_id where tu.`status`='VALID' and tu.user_id=? 
... ...
[INFO ] 2018-05-08 11:13:02,059 --main-- [Sharding-JDBC-SQL] Actual SQL: ds_jdbc_0 ::: SELECT od.user_id, od.order_id, oi.item_id, od.status FROM t_user tu join t_order_0 od on tu.user_id=od.user_id join t_order_item_0 oi on od.order_id=oi.order_id where tu.`status`='VALID' and tu.user_id=? ::: [10] 
[INFO ] 2018-05-08 11:13:02,059 --main-- [Sharding-JDBC-SQL] Actual SQL: ds_jdbc_0 ::: SELECT od.user_id, od.order_id, oi.item_id, od.status FROM t_user tu join t_order_1 od on tu.user_id=od.user_id join t_order_item_1 oi on od.order_id=oi.order_id where tu.`status`='VALID' and tu.user_id=? ::: [10]