Sharding-Jdbc多路由策略实现

Sharding-Jdbc多路由策略实现

技术背景

一般情况下，使用Sharding-Jdbc都是直接设置分片键，进行对应的增删改查都是根据对应的分片逻辑进行数据库、数据表的定位。但是有些情况下无法传入对应的分片键，又想指定路由节点。这种时候就会使用强制路由策略，但是默认Sharding-Jdbc配置是只支持单逻辑表单路由规则配置，这个时候我们就可以对应的实现多个逻辑表，映射相同的一套物理表。实现多路由策略。

具体实现如下

表结构：order，分片键id。对表进行分库分8个库，每个库一个order表
1、定义对应的复合路由规则和强制路由规则

public class MerLongDBShardingAlgorithm extends MerAbstractShardingAlgorithm<Long> {

    /**
     * 表数量，所有的分库加起来的数量
     */
    private final int tableCnt;

    /**
     * 每个库中的表数量
     */
    private final int tablePerDB;

    /**
     * 结果格式化
     */
    private final String resultFormat;

    public MerLongDBShardingAlgorithm(int tableCnt, int dbCnt, String dsPrefix) {
        this.tableCnt = tableCnt;
        this.tablePerDB = tableCnt / dbCnt;
        this.resultFormat = dsPrefix + "%0" + Integer.toString(dbCnt - 1).length() + "d";
        log.info("ShardingAlgorithm DB Init >>>>>> dsPrefix={}, dbCnt={}, tableCnt={}", dsPrefix, dbCnt, tableCnt);
    }

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
        long index = ((preciseShardingValue.getValue() % 1000) % tableCnt) / tablePerDB;
        return String.format(resultFormat, index);
    }
}

public class CommentDataSourceHintAlgorithm implements HintShardingAlgorithm<Long> {
    private final int tableCnt = 8;

    private static Map<Long ,String> dateSourceMap = new HashMap<Long ,String>(){
        {
            put(0L,"datasource_0");
            put(1L,"datasource_1");
            put(2L,"datasource_2");
            put(3L,"datasource_3");
            put(4L,"datasource_4");
            put(5L,"datasource_5");
            put(6L,"datasource_6");
            put(7L,"datasource_7");
        }
    };

    @Override
    public Collection<String> doSharding(Collection<String> collection, HintShardingValue<Long> hintShardingValue) {
        Collection<String> databaseSet = Sets.newConcurrentHashSet();
        Collection<Long> values = hintShardingValue.getValues();
        for(Long value : values){
            databaseSet.add(dateSourceMap.get(value));
        }
        //TODO 根据传进来的分片值 路由到对应的物理库
        return databaseSet;
    }
}

2、配置对应的shardingDataSource

public class ShardingDataSourceConfig {

    @Bean(name = "datasource_0")
    public DynamicDataSource masterDataSource0() {
        return new DynamicDataSource();
    }
    @Bean(name = "datasource_1")
    public DynamicDataSource masterDataSource1() {
        return new DynamicDataSource();
    }
    @Bean(name = "datasource_2")
    public DynamicDataSource masterDataSource2() {
        return new DynamicDataSource();
    }
    @Bean(name = "datasource_3")
    public DynamicDataSource masterDataSource3() {
        return new DynamicDataSource();
    }
    @Bean(name = "datasource_4")
    public DynamicDataSource masterDataSource4() {
        return new DynamicDataSource();
    }
    @Bean(name = "datasource_5")
    public DynamicDataSource masterDataSource5() {
        return new DynamicDataSource();
    }
    @Bean(name = "datasource_6")
    public DynamicDataSource masterDataSource6() {
        return new DynamicDataSource();
    }
    @Bean(name = "datasource_7")
    public DynamicDataSource masterDataSource7() {
        return new DynamicDataSource();
    }

    @Bean(name = "commentLongDBShardingAlgorithm")
    public ComplexKeysShardingAlgorithm<Long> MerLongDBShardingAlgorithm(){
        return new MerLongDBShardingAlgorithm(8,8,"datasource_");
    }

    @Bean("datasource")
    public DataSource shardingDataSource(@Qualifier(value = "datasource_0")DynamicDataSource datasource_0,
                                                 @Qualifier(value = "datasource_1")DynamicDataSource datasource_1,
                                                 @Qualifier(value = "datasource_2")DynamicDataSource datasource_2,
                                                 @Qualifier(value = "datasource_3")DynamicDataSource datasource_3,
                                                 @Qualifier(value = "datasource_4")DynamicDataSource datasource_4,
                                                 @Qualifier(value = "datasource_5")DynamicDataSource datasource_5,
                                                 @Qualifier(value = "datasource_6")DynamicDataSource datasource_6,
                                                 @Qualifier(value = "datasource_7")DynamicDataSource datasource_7,
                                                 @Qualifier(value = "commentLongDBShardingAlgorithm")ComplexKeysShardingAlgorithm<Long> commentLongDBShardingAlgorithm) throws SQLException {

        Map<String, DataSource> dataSourceMap = new HashMap<>();
        dataSourceMap.put("datasource_0", datasource_0);
        dataSourceMap.put("datasource_1", datasource_1);
        dataSourceMap.put("datasource_2", datasource_2);
        dataSourceMap.put("datasource_3", datasource_3);
        dataSourceMap.put("datasource_4", datasource_4);
        dataSourceMap.put("datasource_5", datasource_5);
        dataSourceMap.put("datasource_6", datasource_6);
        dataSourceMap.put("datasource_7", datasource_7);

        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        //绑定分片表，主要用来路由
        shardingRuleConfig.getBindingTableGroups().add("goods_comment, order_comment");
        //设置默认数据源分片策略
        //shardingRuleConfig.setDefaultDatabaseShardingStrategyConfig(new HintShardingStrategyConfiguration(new CommentDataSourceHintAlgorithm()));
        Collection<TableRuleConfiguration> tableRuleConfigs = shardingRuleConfig.getTableRuleConfigs();
        TableRuleConfiguration orderCommentConfiguration = new TableRuleConfiguration("order_comment",
                "datasource_$->{0..7}.order");
        orderCommentConfiguration.setDatabaseShardingStrategyConfig(
                new ComplexShardingStrategyConfiguration("id", commentLongDBShardingAlgorithm));
        tableRuleConfigs.add(orderCommentConfiguration);

         //逻辑表名为order_comment1使用强制路由策略
        TableRuleConfiguration orderCommentHintConfiguration = new TableRuleConfiguration("order1","datasource_$->{0..7}.order");
        orderCommentHintConfiguration.setDatabaseShardingStrategyConfig(
                new HintShardingStrategyConfiguration(new CommentDataSourceHintAlgorithm()));
        tableRuleConfigs.add(orderCommentHintConfiguration);


        //创建ShardingDataSource数据源
        return ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new Properties());
    }
}

3、正常调用直接带入对应的分片键Id，强制路由需要通过HintManager传入对应的路由value

HintManager hintManager = HintManager.getInstance();
                try{
                    //强制路由更新订单
                    hintManager.setDatabaseShardingValue(1);
                    orderCommentDao.updateOrderStatus(orderNos,status);
                }catch(Exception e){
                    log.error(CommentErrorCode.FAIL_UPDATE_ORDER_COMMENT_STATUS.getErrorCode(), "fail updateStatus，orderNos: " + orderNos + "/n" + e);
                }finally {
                    hintManager.close();
                }

SQL语句中的表名为逻辑表名，sharding-Jdbc会自动根据逻辑表名启用对应的路由规则：

UPDATE order1
		set order_status = #{status},
			update_time = now()
		WHERE order_no in
		  <foreach item="orderNo" collection="orderNos" open="(" separator="," close=")">
			  #{orderNo}
		  </foreach>

SELECT count(1) FROM order WHERE id = #{id}