shardingjdbc+springboot分库分表

分库分表是处理单库单表数据量过大的解决方案。

分为垂直拆分和水平拆分。

这里讲的是springboot+shardingjdbc垂直拆分的解决方案：

shardingjdbc版本：

<dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.0.0-RC1</version>
        </dependency>

 <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.3.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
 </parent>

配置文件：

spring:
  application:
    name: my-client1
  shardingsphere:
    datasource:
      names: antifake,antifake0//这里指定两个库的别名，对应下面配置文件的数据库
      antifake:
        type: com.alibaba.druid.pool.DruidDataSource//数据库连接池
        driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
        url: jdbc:sqlserver://xxx:1433;Databasename=xxx//数据库地址
        username: xxx//数据库用户名
        password: xxxx//数据库密码
      antifake0:
        type: com.alibaba.druid.pool.DruidDataSource//数据库连接池
        driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
        url: xxx;Databasename=xxx//第二个数据库地址
        username: xxx//用户名
        password: xxx//密码
          #        url: jdbc:sqlserver://siwa-test001.sqlserver.rds.aliyuncs.com:1433;Databasename=antifake
#        url: jdbc:sqlserver://rm-2ze79hao0ipj21pd0.sqlserver.rds.aliyuncs.com:1433;Databasename=antifake
#        username: hengyuan
#        password: 8ymZv7R$5zRd8H0wS156
      
    props:
      sql.show: true
    sharding:
      tables:
        anti_fake_code:
          actual-data-nodes: antifake0.anti_fake_code_$->{0..200}//这里是分表的策略，前面antifake0是库名，后面是这个库里需要分表的表名。
          #          key-generator:
          #            column: id
          #            type: SNOWFLAKE  #这里填的是分布式算法，官方文档有供选择
          table-strategy:    #这个标签下的要更具官方文档选择，容易报错
            standard:
              preciseAlgorithmClassName: com.ever.business.shardingjdbc.DeliveryOrderTableShardingAlgorithm//这里用的是自定义分表策略
              sharding-column: anti_fake_code  #要分片的列
        tracking_code:
          actual-data-nodes: antifake.tracking_code_$->{0..200}
          #          key-generator:
          #            column: id
          #            type: SNOWFLAKE  #这里填的是分布式算法，官方文档有供选择
          table-strategy:    #这个标签下的要更具官方文档选择，容易报错
            standard:
              preciseAlgorithmClassName: com.ever.business.shardingjdbc.DeliveryOrderTableShardingAlgorithm
              sharding-column: tracking_code  #要分片的列
        delivery_order:
          actual-data-nodes: antifake.delivery_order_$->{0..200}
          #          key-generator:
          #            column: id
          #            type: SNOWFLAKE  #这里填的是分布式算法，官方文档有供选择
          table-strategy:    #这个标签下的要更具官方文档选择，容易报错
            standard:
              preciseAlgorithmClassName: com.ever.business.shardingjdbc.DeliveryOrderTableShardingAlgorithm
              sharding-column: order_no  #要分片的列
              #spring.shardingsphere.sharding.tables.
              #customer_order.table-strategy.complex.
              #sharding-columns=order_id,customer_id

下面是自定义分表策略的类：

@Component
public class DeliveryOrderTableShardingAlgorithm implements PreciseShardingAlgorithm<String> {


    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<String> preciseShardingValue) {
        OrderDao orderDao = SpringContextUtil.getBean(OrderDao.class);
        //SBXMZS210909001  PO4508064028 4507177074
        String orderNo = preciseShardingValue.getValue();
        if (StringUtils.isNotBlank(orderNo)) {
            Long hashCode = orderDao.selectCheckSum(orderNo);
//            int hashCode = orderNo.hashCode();
            long divideValue = hashCode % 200;
            for (String each : collection) {
                if (each.endsWith(String.valueOf(Math.abs(divideValue)))) {
                    return each;
                }
            }
        }
        return null;
    }
}

这里用的算法是基于sqlserver数据库的hash取模取绝对值的算法，根据分表字段定义路由到哪张表。