后端开发: 数据库分库分表sharding-jdbc

原理和开源库

原理: 通过解析sql语句, 根据分库分表的规则，替换掉表名和库名
文档资料
https://github.com/apache/incubator-shardingsphere/
https://shardingsphere.apache.org/

对应的SQL解析器为SQLParsingEngine, 有兴趣可以细看一下
完整示例
https://github.com/wzjwhut/spring-example-all/tree/master/sharding-jdbc

使用方法

准备工具

安装mysql， 准备好需要的数据库和表
https://github.com/wzjwhut/spring-jdbc-sharding/blob/master/src/main/resources/config.sql

maven依赖

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

配置DataSource

使用jdbc-sharding的data source将spring的data source包裹起来

@Configuration
@Log4j2
public class DataSourceConfig {

    /** 使用sharding-jdbc的源 */
    @Bean
    DataSource createDataSource() throws SQLException, NoSuchFieldException, IllegalAccessException {
        log.info("build data source");

        //收集分库列表
        Map<String, DataSource> dataSourceMap = new LinkedHashMap<>();
        dataSourceMap.put("test", createDataSource("test"));
        dataSourceMap.put("test_0", createDataSource("test_0"));
        dataSourceMap.put("test_1", createDataSource("test_1"));
        
        //city表的分库分表规则
        TableRuleConfiguration cityRule = new TableRuleConfiguration();
        cityRule.setLogicTable("city");
        cityRule.setActualDataNodes("test_${0..1}.city_${0..1}");

        //分库策略
        cityRule.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration(
                "name",
                new ModuleDatabaseShardingAlgorithm()));

        //分表策略
        cityRule.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration(
                "name",
                new ModuleTableShardingAlgorithm(),null));




        ShardingRuleConfiguration ruleConfig = new ShardingRuleConfiguration();
        ruleConfig.setDefaultDataSourceName("test");
        ruleConfig.getTableRuleConfigs().addAll(Arrays.asList(cityRule));

        DataSource dataSource = ShardingDataSourceFactory.createDataSource(dataSourceMap,
                ruleConfig, Collections.EMPTY_MAP, null);
        return dataSource;
    }

    private static DataSource createDataSource(final String dataSourceName) {
        log.info("create data source: {}", dataSourceName);
        HikariDataSource result = new HikariDataSource();
        result.setJdbcUrl(String.format("jdbc:mysql://localhost:3306/%s", dataSourceName));
        result.setDriverClassName(Driver.class.getName());
        result.setUsername("root");
        result.setPassword("123456");
        return result;
    }
}