Springboot2.X 集成 Sharding-JDBC3.X

之前的项目中集成了springboot2.x + dangdang sharding-jdbc 1.5.4.1
pom依赖

		<dependency>
			<groupId>com.dangdang</groupId>
			<artifactId>sharding-jdbc-core</artifactId>
			<version>1.5.4.1</version>
		</dependency>

具体的设置参考博客 ： 数据库分库分表sharding-jdbc 这里不做过多的赘述

我们知道sharding-jdbc 目前已经更名为 shardingsphere,而上面的依赖最后一次更新是在2017年,比较的老旧,也不支持where条件下的 OR 查询等， 无奈之下只好重新更换新的依赖，

		<dependency>
			<groupId>io.shardingsphere</groupId>
			<artifactId>sharding-jdbc-core</artifactId>
			<version>3.1.0</version>
		</dependency>

sharding-sphere规则信息和配置信息 参考这个博客

shardingsphere 分片具体实现如下（可参考官网文档）:

    // 配置真实数据源
    Map<String, DataSource> dataSourceMap = new HashMap<>();
    
    // 配置第一个数据源
    BasicDataSource dataSource1 = new BasicDataSource();
    dataSource1.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource1.setUrl("jdbc:mysql://localhost:3306/ds0");
    dataSource1.setUsername("root");
    dataSource1.setPassword("");
    dataSourceMap.put("ds0", dataSource1);
    
    // 配置第二个数据源
    BasicDataSource dataSource2 = new BasicDataSource();
    dataSource2.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource2.setUrl("jdbc:mysql://localhost:3306/ds1");
    dataSource2.setUsername("root");
    dataSource2.setPassword("");
    dataSourceMap.put("ds1", dataSource2);
    
    // 配置Order表规则
    TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration();
    orderTableRuleConfig.setLogicTable("t_order");
    orderTableRuleConfig.setActualDataNodes("ds${0..1}.t_order${0..1}");
    
    // 配置分库 + 分表策略
    orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("user_id", "ds${user_id % 2}"));
    orderTableRuleConfig.setTableShardingStrategyConfig(new InlineShardingStrategyConfiguration("order_id", "t_order${order_id % 2}"));
    
    // 配置分片规则
    ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
    shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig);
    
    // 省略配置order_item表规则...
    // ...
    
    // 获取数据源对象
    DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap, shardingRuleConfig, new ConcurrentHashMap(), new Properties());

到这里简单的数据源配置就ok了,但是有时候我们事先并不知道数据源的具体地址,这些是在某些xml中动态配置的,而且可能我们仅仅有个别的表需要分库分表,其他的表又有可能分布在不同的数据节点上；
比如: (手动画图，凑合看吧)

如上图情景，我们需要动态的配置各自表所在的数据源，

思路:

1. 从配置文件中读取所有的数据源信息，设置别名
   2.配置数据表与别名的关联关系
   3.不同的分库分表策略需要配置各自的ruleConfig
   4.动态配置 actualDataNodes,根据自己的需求自行拼接

直接上代码吧，

@Configuration
public class DynamicDataSourceConfigV2 {

    private static final Logger logger = LogManager.getLogger(DynamicDataSourceConfigV2.class);

    /** 目前只有报警表需要分库分表 */
    private final static String LOGIC_TABLE = "alarms";

    /** 目前的报警分为16张表,后缀如下 */
    private final static char[] tables_suffix = {'0','1','2','3','4','5','6','7','8','9','a','b','c','d','e','f'};

    private List<DataSource> allDataSource = new LinkedList<>();

    private String defaultDsAlias;

    /**
     * alarm表进行了分库操作,所以它对应的有多个库,
     * 其它的表只有一个库, List中只有一个数据源(别名).
     */
    private HashMap<String, List<String>> dsMappingTableName = new HashMap<>();

    /**
     * 此项就是本集群所有数据源的对应关系
     * 数据源别名对应数据源
     * String : 别名(ds0,ds1...)
     * DataSource: 数据源
     */
    private HashMap<String,DataSource> aliasDs = new HashMap<>();


    final ClusterConfig clusterConfig;

    @Value("${local.test}")
    private boolean localTest;

    @Autowired
    public DynamicDataSourceConfigV2(ClusterConfig clusterConfig){
        this.clusterConfig = clusterConfig;
        logger.info("初始化 DynamicDataSourceConfigV2 ");
        List<MysqlService> allMysqlInfo = clusterConfig.currentCluster().getMysql();
        int ds = 0;
        String alias;
        for (MysqlService mysql : allMysqlInfo) {
            String url = mysql.getUrl();
            String username = mysql.getUsername();
            String password = mysql.getPassword();
            logger.fatal("main mysql url: {}, username: {}, password: {}",
                    url, username, password);
            DataSource dataSource = commonDataSource(url,username,password);
            // 当前数据源别名
            alias = "ds"+ds;
            aliasDs.put(alias,dataSource);
            // 之前cluster.xml中的默认数据库的配置依然有效
            if(mysql.isDefault() || this.defaultDsAlias == null){
                this.defaultDsAlias = alias;
            }
            allDataSource.add(dataSource);
            List<String> tables = mysql.getTables();
            if (tables != null) {
                for (String tableName : tables) {
                    List<String> list = dsMappingTableName.get(tableName);
                    if (list == null) {
                        list = new ArrayList<>();
                        if (StringUtils.isNotBlank(tableName)) {
                            this.dsMappingTableName.put(tableName, list);
                        }
                    }
                    list.add(alias);
                }
            } else {
                logger.warn("not set tables");
            }
            ds++;
        }
    }

    @Bean("shardingDataSource")
    public DataSource shardingDataSource() throws SQLException {
        // mybatis 的相关日志开启debug
        if(localTest){
            logger.info("mybatis 开启debug模式");
            LogFactory.useStdOutLogging();
        }
        return buildDataSourceV2();
    }

    /**
     * 需要手动配置事务管理器
     * @param shardingDataSource
     * @return
     */
    @Bean
    public DataSourceTransactionManager transactitonManager(@Qualifier("shardingDataSource") DataSource shardingDataSource){
        logger.info("需要手动配置事务管理器");
        return new DataSourceTransactionManager(shardingDataSource);
    }

    @Bean("sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(@Qualifier("shardingDataSource") DataSource shardingDataSource) throws Exception {
        logger.info("需要手动配置SqlSessionFactory");
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(shardingDataSource);
        return bean.getObject();
    }

    private DataSource buildDataSourceV2() throws SQLException {
        Set<String> keys = dsMappingTableName.keySet();
        Collection<TableRuleConfiguration> tableRuleConfigurations = new LinkedList<>();
        // 遍历所有cluster.xml 中配置的表
        for(String tableName:keys){
            // 获取该表对应的数据源别名集
            List<String> list = getDataSource(tableName);
            TableRuleConfiguration tableRuleConfiguration;
            // 拼接actualDataNodes 注意去掉最后一个','字符
            StringBuilder builder = new StringBuilder();
            for(String alis:list){
                if(LOGIC_TABLE.equals(tableName)){
                    for(char ch :tables_suffix){
                        builder.append(alis).append(".").append(tableName).append("_").append(ch).append(",");
                    }
                }else{
                    builder.append(alis).append(".").append(tableName).append(",");
                }
            }
            // 移除最后一个字符
            int index = builder.length() - 1;
            builder = builder.deleteCharAt(index);

            tableRuleConfiguration = LOGIC_TABLE.equals(tableName) ? getAlarmTableRuleConfiguration(LOGIC_TABLE,builder.toString())
                    : getOtherTableRuleConfiguration(tableName,builder.toString());
            logger.info("tableName:{},builder:{}",tableName,builder.toString());
            tableRuleConfigurations.add(tableRuleConfiguration);
        }

        // 分片规则配置初始化
        ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration();
        // 设置默认数据库(未能命中下面定制的规则时会使用该数据源)
        shardingRuleConfig.setDefaultDataSourceName(defaultDsAlias);

        Properties properties = new Properties();
        // 开启sharding-jdbc的sql显示功能,默认是关闭的
        properties.setProperty("sql.show",Boolean.TRUE.toString());

        shardingRuleConfig.getTableRuleConfigs().addAll(tableRuleConfigurations);
        DataSource dataSource = ShardingDataSourceFactory.createDataSource(aliasDs,shardingRuleConfig,
                new ConcurrentHashMap(),properties);
        return dataSource;
    }

    /**
     * 报警表的分表分库策略
     * @param logicTable
     * @param ActualDataNodes      格式:db_${0..1}.gps_${0..1}   相当于 db_0.gps_0,db_0.gps_1,db_1.gps_0,db_1.gps_1
     * @return
     */
    private TableRuleConfiguration getAlarmTableRuleConfiguration(String logicTable,String ActualDataNodes) {
        TableRuleConfiguration result = new TableRuleConfiguration();
        // 设置分表的基础表(前缀部分)
        result.setLogicTable(logicTable);
        // 数据节点
        result.setActualDataNodes(ActualDataNodes);
        // 分库策略
        result.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("device_id",
                new ModuleDatabaseShardingAlgorithmV2()));

        // 分表策略
        result.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("device_id",
                new ModuleTableShardingAlgorithmV2(),new ModuleTableRangeShardingAlgorithmV2()));

        return result;
    }

    /**
     * 其他表的分表分库策略
     * @return
     */
    private TableRuleConfiguration getOtherTableRuleConfiguration(String logicTable,String ActualDataNodes) {
        TableRuleConfiguration result = new TableRuleConfiguration();
        // 设置分表的基础表(前缀部分)
        result.setLogicTable(logicTable);
        // 数据节点
        result.setActualDataNodes(ActualDataNodes);
        return result;
    }

    /**
     * 根据tableName 获取数据源别名
     * @param tableName
     * @return
     */
    public List<String> getDataSource(String tableName) {
        logger.info("get DataSource by table name: {}", tableName);
        List<String> list;
        if(StringUtils.isBlank(tableName)){
            logger.info("not found table : {}, use default", tableName);
            list =  new ArrayList<>(1);
            list.add(defaultDsAlias);
        }else{
            list = dsMappingTableName.get(tableName);
            if(list == null){
                logger.info("not found session facotry, {}", tableName);
                list =  new ArrayList<>(1);
                list.add(defaultDsAlias);
            }
        }
        return list;
    }

    /**
     * 获取所有的报警分表
     * @param logicTable
     * @return
     */
    private List<String> listAlarmTableNames(String logicTable){
        List<String> tableNames = new ArrayList<>(tables_suffix.length);
        for(char suffix :tables_suffix){
            StringBuilder builder = new StringBuilder();
            builder.append(logicTable).append("_").append(suffix);
            tableNames.add(builder.toString());
        }
        return tableNames;
    }

    private DataSource commonDataSource(String url, String username, String password){
        DataSourceBuilder builder = DataSourceBuilder.create();
        builder.driverClassName(com.mysql.jdbc.Driver.class.getName());
        builder.url(url);
        builder.username(username);
        builder.password(password);
        return builder.build();
    }

}

这里有个坑需要注意一下,有关KeyGeneratorColumnName强制要求是Number类型,如果我们希望字段是String等类型时,不要指定有关KeyGenerator的东西,比如:

tableRuleConfiguration.setKeyGeneratorColumnName("xxx")
tableRuleConfiguration.setKeyGenerator(xxx)

否则会报错，血泪史。。。

完事，如有疑问，欢迎沟通