4.SpringBoot整合ShardingJdbc分库分表

基础信息请参照https://www.jianshu.com/p/865c18d006a5

拆分规则

将user表分别按照用户的性别分到两个数据库中，然后在每个数据库中又按照用户的手机号查分落到不同的表中。

用户表的er图如下

编写通过手机号的分片规则

有关自定义分片规则参照https://shardingsphere.apache.org/document/current/cn/features/sharding/concept/sharding/

public class UserPreciseShardingAlgorithm implements PreciseShardingAlgorithm<String> {

    @Override
    public String doSharding(Collection<String> tableNames, PreciseShardingValue<String> shardingValue) {
        return tableNames.stream()
                .filter(table -> {
                    String data = shardingValue.getValue();
                    String endWord = data.substring(data.length() - 1);
                    return table.endsWith(Integer.parseInt(endWord) % 2 + "");
                })
                .findAny()
                .orElseThrow(UnsupportedOperationException::new);
    }

}

配置信息

dataSources:
  ds0: !!com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3306/order1?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
    username: root
    password:
  ds1: !!com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3306/order2?serverTimezone=CTT&useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
    username: root
    password:
shardingRule:
  tables:
    t_user:
      actualDataNodes: ds${0..1}.t_user${0..1}
      databaseStrategy:
        inline:
          shardingColumn: sex
          algorithmExpression: ds${sex % 2}
      tableStrategy:
        standard:
          shardingColumn: phone
          preciseAlgorithmClassName: org.ywb.subdbtable.sharding.UserPreciseShardingAlgorithm
      keyGenerator:
        type: SNOWFLAKE
        column: id
  bindingTables:
    - t_user
  defaultDataSourceName: ds0

props:
  sql.show: true

配置数据源

@Configuration
public class DataSourceConfiguration {

    @Resource
    private ShardingDataSourceYamlConfig shardingDataSourceYamlConfig;

    @Bean
    @Primary
    public DataSource dataSource() throws IOException, SQLException {
        return YamlShardingDataSourceFactory.createDataSource(shardingDataSourceYamlConfig.getShardingFileByProfileActive());
    }
}

代码地址：https://github.com/xiao-ren-wu/sharding-all/tree/master/sub-db-table