背景

有一个业务目前是单库单表的,随着业务的发展需要拓展为多库多表的形式,而且还需要将数据从原先的实例转移到分库分表后的DB中

  1. CREATE TABLE `t_topic_2` (
  2. `id` bigint(20) DEFAULT NULL,
  3. `topic` varchar(20) DEFAULT NULL,
  4. `state` int(11) DEFAULT NULL,
  5. `connect` int(11) DEFAULT NULL,
  6. `is_print_log` tinyint(4) DEFAULT NULL
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8

本地测试过后的配置如下

  1. spring:
  2. application:
  3. name: test
  4. shardingsphere:
  5. mode:
  6. type: Memory
  7. props:
  8. sql-show: true
  9. datasource:
  10. names: ds0, ds1
  11. ds0:
  12. type: com.zaxxer.hikari.HikariDataSource
  13. driver-class-name: com.mysql.jdbc.Driver
  14. jdbc-url: jdbc:mysql://localhost:3306/test
  15. username: root
  16. password: 12345678
  17. ds1:
  18. type: com.zaxxer.hikari.HikariDataSource
  19. driver-class-name: com.mysql.jdbc.Driver
  20. jdbc-url: jdbc:mysql://localhost:3306/test2
  21. username: root
  22. password: 12345678
  23. rules:
  24. sharding:
  25. tables:
  26. t_topic:
  27. actualDataNodes: ds${0..1}.t_topic_${0..2}
  28. tableStrategy:
  29. standard:
  30. shardingColumn: id
  31. shardingAlgorithmName: table-inline
  32. default-database-strategy:
  33. standard:
  34. sharding-column: id
  35. sharding-algorithm-name: id-inline-database-strategy
  36. sharding-algorithms:
  37. # id-inline-database-strategy是自定义的名称,
  38. # !!!最好别以下划线分隔,会有问题
  39. id-inline-database-strategy:
  40. type: INLINE
  41. props:
  42. # 该表达式是指把 id 值是偶数路由到 ds0,奇数路由到 ds1
  43. algorithm-expression: ds$->{id % 2}
  44. table-inline:
  45. type: INLINE
  46. props:
  47. algorithm-expression: t_topic_$->{id % 3}
  48. #
  49. debug: true
  50. mybatis-plus:
  51. configuration:
  52. log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  53. type-aliases-package: com.dym.entity
  54. mapper-locations: classpath*:/mapper/*.xml

动态数据源

  1. spring:
  2. datasource:
  3. dynamic:
  4. allowed-public-only: true
  5. strict: true
  6. primary: first
  7. datasource:
  8. first:
  9. driverClassName: com.mysql.jdbc.Driver
  10. type: com.zaxxer.hikari.HikariDataSource
  11. url: jdbc:mysql://localhost:3306/test
  12. username: root
  13. password: chenshun
  14. shardingsphere:
  15. mode:
  16. type: Memory
  17. props:
  18. sql-show: true
  19. datasource:
  20. //动态数据源,参与下边的分库
  21. names: ds0, ds1
  22. ds0:
  23. type: com.zaxxer.hikari.HikariDataSource
  24. driver-class-name: com.mysql.jdbc.Driver
  25. jdbc-url: jdbc:mysql://localhost:3306/test1
  26. username: root
  27. password: chenshun
  28. ds1:
  29. type: com.zaxxer.hikari.HikariDataSource
  30. driver-class-name: com.mysql.jdbc.Driver
  31. jdbc-url: jdbc:mysql://localhost:3306/test2
  32. username: root
  33. password: chenshun
  34. rules:
  35. //分片规则
  36. sharding:
  37. tables:
  38. t_topic:
  39. actualDataNodes: ds${0..1}.t_topic_${0..2}
  40. tableStrategy:
  41. standard:
  42. shardingColumn: id
  43. shardingAlgorithmName: table-inline
  44. default-database-strategy:
  45. standard:
  46. sharding-column: id
  47. sharding-algorithm-name: id-inline-database-strategy
  48. sharding-algorithms:
  49. id-inline-database-strategy:
  50. type: INLINE
  51. props:
  52. algorithm-expression: ds$->{id % 2}
  53. table-inline:
  54. type: INLINE
  55. props:
  56. algorithm-expression: t_topic_$->{id % 3}
  1. package io.github.chenshun00.springcloud.provider.config;
  2. /**
  3. * @author chenshun00@gmail.com
  4. * @since 2022/5/22 2:40 PM
  5. */
  6. import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
  7. import com.baomidou.dynamic.datasource.provider.AbstractDataSourceProvider;
  8. import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;
  9. import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
  10. import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceAutoConfiguration;
  11. import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
  12. import org.springframework.beans.factory.annotation.Autowired;
  13. import org.springframework.boot.SpringBootConfiguration;
  14. import org.springframework.boot.autoconfigure.AutoConfigureBefore;
  15. import org.springframework.context.annotation.Bean;
  16. import org.springframework.context.annotation.Configuration;
  17. import org.springframework.context.annotation.Lazy;
  18. import org.springframework.context.annotation.Primary;
  19. import javax.annotation.Resource;
  20. import javax.sql.DataSource;
  21. import java.util.Map;
  22. /**
  23. * 动态数据源配置:
  24. *
  25. * 使用{@link com.baomidou.dynamic.datasource.annotation.DS}注解,切换数据源
  26. *
  27. * <code>@DS(DataSourceConfiguration.SHARDING_DATA_SOURCE_NAME)</code>
  28. *
  29. * @author fanqie
  30. * @date 2021/8/16 下午12:36
  31. */
  32. @Configuration
  33. @AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class, SpringBootConfiguration.class})
  34. public class DataSourceConfiguration {
  35. /**
  36. * 分表数据源名称
  37. */
  38. public static final String SHARDING_DATA_SOURCE_NAME = "sharding";
  39. /**
  40. * 动态数据源配置项
  41. */
  42. @Autowired
  43. private DynamicDataSourceProperties dynamicDataSourceProperties;
  44. /**
  45. * shardingjdbc有四种数据源,需要根据业务注入不同的数据源
  46. *
  47. * <p>1. 未使用分片, 脱敏的名称(默认): shardingDataSource;
  48. * <p>2. 主从数据源: masterSlaveDataSource;
  49. * <p>3. 脱敏数据源:encryptDataSource;
  50. * <p>4. 影子数据源:shadowDataSource
  51. *
  52. * shardingjdbc默认就是 shardingDataSource
  53. * 如果需要设置其他的可以使用
  54. * @Resource(value="") 设置
  55. */
  56. @Lazy
  57. @Resource
  58. DataSource shardingDataSource;
  59. /**
  60. * 将shardingDataSource放到了多数据源(dataSourceMap)中
  61. * 注意有个版本的bug,3.1.1版本 不会进入loadDataSources 方法,这样就一直造成数据源注册失败
  62. */
  63. @Bean
  64. public DynamicDataSourceProvider dynamicDataSourceProvider() {
  65. Map<String, DataSourceProperty> datasourceMap = dynamicDataSourceProperties.getDatasource();
  66. return new AbstractDataSourceProvider() {
  67. @Override
  68. public Map<String, DataSource> loadDataSources() {
  69. Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
  70. // 将 shardingjdbc 管理的数据源也交给动态数据源管理
  71. dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, shardingDataSource);
  72. return dataSourceMap;
  73. }
  74. };
  75. }
  76. /**
  77. * 将动态数据源设置为首选的
  78. * 当spring存在多个数据源时, 自动注入的是首选的对象
  79. * 设置为主要的数据源之后,就可以支持shardingjdbc原生的配置方式了
  80. *
  81. * @return
  82. */
  83. @Primary
  84. @Bean
  85. public DataSource dataSource(DynamicDataSourceProvider dynamicDataSourceProvider) {
  86. DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
  87. dataSource.setPrimary(dynamicDataSourceProperties.getPrimary());
  88. dataSource.setStrict(dynamicDataSourceProperties.getStrict());
  89. dataSource.setStrategy(dynamicDataSourceProperties.getStrategy());
  90. dataSource.setProvider(dynamicDataSourceProvider);
  91. dataSource.setP6spy(dynamicDataSourceProperties.getP6spy());
  92. dataSource.setSeata(dynamicDataSourceProperties.getSeata());
  93. return dataSource;
  94. }
  95. }