SpringBoot案例：整合sharding-jdbc中间件，实现数据分库分表

一、水平分割

1、水平分库

2、水平分表

二、Shard-jdbc 中间件

1、架构图

2、特点

三、项目演示

1、项目结构

2、数据库配置

一台基础库映射（shard_one） 两台库做分库分表（shard_two，shard_three）。 表使用：table_one，table_two

3、核心代码块

数据源配置文件

数据库分库策略

/**
 * 数据库映射计算
 */
 public class DataSourceAlg implements PreciseShardingAlgorithm<string> {
      private static Logger LOG = LoggerFactory.getLogger(DataSourceAlg.class);
      
      @Override
      public String doSharding(Collection<string> names, PreciseShardingValue<string> value) {
           LOG.debug("分库算法参数 {},{}",names,value);
           int hash = HashUtil.rsHash(String.valueOf(value.getValue()));
           return "ds_" + ((hash % 2) + 2) ;
      }
 }

数据表1分表策略

/**
 * 分表算法
 */
 public class TableOneAlg implements PreciseShardingAlgorithm<string> {
      private static Logger LOG = LoggerFactory.getLogger(TableOneAlg.class);
      
      /**
       * 该表每个库分5张表
       */
      @Override
      public String doSharding(Collection<string> names, PreciseShardingValue<string> value) {
           LOG.debug("分表算法参数 {},{}",names,value);
           int hash = HashUtil.rsHash(String.valueOf(value.getValue()));
           return "table_one_" + (hash % 5+1);
      }
 }

数据表2分表策略

/**
 * 分表算法
 */
 public class TableTwoAlg implements PreciseShardingAlgorithm<string> {

      private static Logger LOG = LoggerFactory.getLogger(TableTwoAlg.class);
      
      /**
       * 该表每个库分5张表
       */
      @Override
      public String doSharding(Collection<string> names, PreciseShardingValue<string> value) {
           LOG.debug("分表算法参数 {},{}",names,value);
           int hash = HashUtil.rsHash(String.valueOf(value.getValue()));
           return "table_two_" + (hash % 5+1);
      }
 }

数据源集成配置

/**
 * 数据库分库分表配置
 */
 @Configuration
 public class ShardJdbcConfig {
      // 省略了 druid 配置，源码中有
      /**
       * Shard-JDBC 分库配置
       */
      @Bean
      public DataSource dataSource (@Autowired DruidDataSource dataOneSource, 
      @Autowired DruidDataSource dataTwoSource,
      @Autowired DruidDataSource dataThreeSource) throws Exception {
           ShardingRuleConfiguration shardJdbcConfig = new ShardingRuleConfiguration();
           shardJdbcConfig.getTableRuleConfigs().add(getTableRule01());
           shardJdbcConfig.getTableRuleConfigs().add(getTableRule02());
           shardJdbcConfig.setDefaultDataSourceName("ds_0");
           
           Map<string,datasource> dataMap = new LinkedHashMap<>();
           dataMap.put("ds_0",dataOneSource);
           dataMap.put("ds_2",dataTwoSource);
           dataMap.put("ds_3",dataThreeSource);
           Properties prop = new Properties();
           
           return ShardingDataSourceFactory.createDataSource(dataMap, shardJdbcConfig, new HashMap<>(), prop);
      }
      
      /**
       * Shard-JDBC 分表配置
       */
      private static TableRuleConfiguration getTableRule01() {
           TableRuleConfiguration result = new TableRuleConfiguration();
           result.setLogicTable("table_one");
           result.setActualDataNodes("ds_${2..3}.table_one_${1..5}");
           result.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("phone", new DataSourceAlg()));
           result.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("phone", new TableOneAlg()));
           return result;
      }
      
      private static TableRuleConfiguration getTableRule02() {
           TableRuleConfiguration result = new TableRuleConfiguration();
           result.setLogicTable("table_two");
           result.setActualDataNodes("ds_${2..3}.table_two_${1..5}");
           result.setDatabaseShardingStrategyConfig(new StandardShardingStrategyConfiguration("phone", new DataSourceAlg()));
           result.setTableShardingStrategyConfig(new StandardShardingStrategyConfiguration("phone", new TableTwoAlg()));
           return result;
      }
 }

测试代码执行流程

@RestController
public class ShardController {
     @Resource
     private ShardService shardService ;
     
     /**
      * 1、建表流程
      */
     @RequestMapping("/createTable")
     public String createTable (){
          shardService.createTable();
          return "success" ;
     }
     
     /**
      * 2、生成表 table_one 数据
      */
     @RequestMapping("/insertOne")
     public String insertOne (){
          shardService.insertOne();
          return "SUCCESS" ;
     }
     
     /**
      * 3、生成表 table_two 数据
      */
     @RequestMapping("/insertTwo")
     public String insertTwo (){
          shardService.insertTwo();
          return "SUCCESS" ;
     }
     
     /**
      * 4、查询表 table_one 数据
      */
     @RequestMapping("/selectOneByPhone/{phone}")
     public TableOne selectOneByPhone (@PathVariable("phone") String phone){
          return shardService.selectOneByPhone(phone);
     }
     
     /**
      * 5、查询表 table_one 数据
      */
     @RequestMapping("/selectTwoByPhone/{phone}")
     public TableTwo selectTwoByPhone (@PathVariable("phone") String phone){
          return shardService.selectTwoByPhone(phone);
     }
}