SpringBoot配置多数据源和分库分表

以前说过使用自定义注解：SpringBoot配置多数据源 - 简书 (jianshu.com)
来使用多数据源。现在项目持久层都用的Mybatis-Plus框架，里面也推荐了配置多数据源的插件dynamic-datasource。所以我现在都用这个，里面注解都是现成的。

1. 分库分表

项目中一般是3种情况。

1. 分库，使用主从数据库进行读写分离，减小连接数压力；
1. 分表，即单表数据量过大，按规则拆分数据：table(0..n)；
1. 分库分表同时使用，上面2种情况结合；

当项目比较简单，只有分库需求时，我们可以使用dynamic-datasource，在调用dao接口时，手动或使用注解指定数据源。
但是当涉及到分库分表时，还是需要sharding-jdbc的。sharding-jdbc可以配置分库读写的方案（配置好以后读写会自动走指定的库），也可以配置分表（分片）读写数据的规则。
sharding-jdbc也有一些小bug，好像分表时不能自己创建新表，需要手动先建好，具体没遇到过，不清除具体问题。但是并不影响我们使用，而且一般也把dynamic-datasource和sharding-jdbc整合到一块使用，来解决分库分表的方案。

2. 简单的分库实现`dynamic-datasource`

注意：因为我们使用dynamic-datasource配置数据源，所以SpringBoot需要排除自动装配数据源。如下：

@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
@MapperScan("xyz.zrb.test.mysql.dao")
public class MysqlShardingTestApplication {

    public static void main(String[] args) {
        SpringApplication.run(MysqlShardingTestApplication.class, args);
    }

}

2.1 pom文件

pom.xml

<dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
      <groupId>com.mysql</groupId>
      <artifactId>mysql-connector-j</artifactId>
      <scope>runtime</scope>
    </dependency>
   
    <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-spring-boot3-starter</artifactId>
      <version>3.5.7</version>
    </dependency>
    <dependency>
      <groupId>com.github.xiaoymin</groupId>
      <artifactId>knife4j-openapi3-jakarta-spring-boot-starter</artifactId>
      <version>4.4.0</version>
    </dependency>

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

    <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>dynamic-datasource-spring-boot3-starter</artifactId>
      <version>4.3.1</version>
    </dependency>
    
    <dependency>
      <groupId>cn.hutool</groupId>
      <artifactId>hutool-all</artifactId>
      <version>5.8.32</version>
    </dependency>
    
    <dependency>
      <groupId>com.belerweb</groupId>
      <artifactId>pinyin4j</artifactId>
      <version>2.5.1</version>
    </dependency>

  </dependencies>

2.2 application.yml

application.yml

# 1.server
server:
  port: 8080
  servlet: 
    context-path: /test
# 3. spring
spring: 
  application:
    name: test
  datasource:
    dynamic: 
      #设置默认的数据源或者数据源组,默认值即为master
      primary: master
      #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
      strict: false
      datasource:
        master:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://192.168.1.21:3316/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8&allowMultiQueries=true&autoReconnect=true
          username: test
          password: "123abc"
        slave:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://192.168.1.22:3326/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8&allowMultiQueries=true&autoReconnect=true
          username: test
          password: "123abc"
      hikari:
        # 最大连接池数量
        maxPoolSize: 50
        # 最小空闲线程数量
        minIdle: 10
        # 配置获取连接等待超时的时间
        connectionTimeout: 30000
        # 校验超时时间
        validationTimeout: 5000
        # 空闲连接存活最大时间，默认10分钟
        idleTimeout: 600000
        # 此属性控制池中连接的最长生命周期，值0表示无限生命周期，默认30分钟
        maxLifetime: 1800000
        # 连接测试query（配置检测连接是否有效）
        connectionTestQuery: SELECT 1
        # 多久检查一次连接的活性
        keepaliveTime: 30000
# mybatis
mybatis-plus:
  type-aliases-package: xyz.zrb.test.mysql.entity
  # 指定mybatis映射文件的位置
  mapper-locations: classpath:mapper/*.xml
  configuration:
    # 开启驼峰命名转换（开启后需要注意字段写法，不然返回值为null）
    map-underscore-to-camel-case: true
    # 配置mybatis日志输出
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-config:
      id-type: AUTO

2.3 方法或类上注解使用

@Service
public class UserService {
    
    @Resource
    private UserDao userDao;
    
    @DS("slave")
    public Result<List<User>> userList() {
        List<User> list = userDao.selectList(new QueryWrapper<User>());
        if (list.isEmpty()) {
            return new Result<List<User>>().error(CodeMsg.DATABASE_RESULT_NULL);
        }
        return new Result<List<User>>().success(list);
    }
    
    @DS("master")
    public Result<Integer> add() {
        String[] nameArr = new String[] {"一南","安诺","嘉遇","简一","初桐","清歌","秋壹","以安","婉沁","知梦","清墨","苏念","意羡","知鸢","禾凝","妍初","星空","初暖","南汐","初恩","乐晗","晚柠","佳觅","静笙","沁柠","音婉","暮雪","清婉","歆甜","芮以","乐潼","浅汐","伊洛","若柒","洛灵","瑾夏","姝白","清念","堇禾","清越","鹿溪","洛枳","安浅","初宁","星蔓","云澈","念言","清曼","初尧","洛尘","叙白","南念","宸灏","蒋元","书韵","少晞","千屹","黎初","乐允","鹿闵","嘉述",
                "斯辰","元祎","景硕","宥谦","璟木","桦君","轩泓","延梵","诺勋","亦錙","锦晟","羽琛","文钦","今晏","子黔","若琛","少煊","熙迩","昱珩","淳屹","希维","溪远","少钦","铭泽","奕轩","北柯","林栖","嘉衡","席予","奕承","江舸","泽锡","方遒","君赫","允川","以洵","怀瑾","靖萱","安唯","诗芮","倪梦","珞怡","哲娴","歆月","宾昕","昱霏","家琪","怡瑄","静楠","馨妍","颖初","雨乔","雪倩","希菲","琳僮","佳雪","彦贞","冉湘","倩兮","宸佳","莉娅","茗羽","璐烨","诺茜","娴梦",
                "安迅","朵愫","洁璐","菁紫","书琦","可艺","如涟","曼莹","慕如","琦雪","若秋","浣淇","无洛","朝苏","叶轩","云霆","一郝","寂云","飞扬","少宣","南风","言希","洛冰","牧尘","洛楠","之翊","竹溪","寒靖","苏哲","知许","博秋","修远","墨尘","墨离","逸鸿","倾川","云吴","南弦","瑞诚","俊衡","允之","和星","蓝千","扶摇","丹丞","墨情","宸枫","旭希","星逸","黎川","曜扬","遥清","曼珠","婉露","雅舒","馨梨","茹珍","芊芸","紫翠","霄若","雪烟","诗云","妙菡","雨绮","迎梦",
                "娜语","尔岚","音醉","傲琪","雅霜","觅翠","沐仙","琪柏","紫青","思敏","云柏","欣雅","曦梦","惜海","菲慕","幽倚","凌青","甜妮","琪芸","芳青","丹雯","漫珊","听枫","翠芙","曼香","丽馨","听邑","予浅","温妤","念卿","佳吟","杖书","洛颜","沐竹","初羽","沫欢","瑶也","星洛","乐渝","予诺","星冉","若笙","沫晗","予柠","思柠","一珞","沐熙","晏溪","栀柔","念惜","洛心","芮伊","星眠","浅沐","梦莱","若姿","年戚","楚容","月暄","清允","禾婉","璃书","今安","可夏","予希",
                "木冉","少禹","锦佑","泽川","星牧","祈安","景林","南乔","玥依","芊雅","晚棠","言柒","允初","时笙","乐安","以柠","玮艺","恩瑶","听白","太一","向沂","希儒","奕清","君尧","瑾舟","苏阳","茉怡","初含","令颜","柠一","芊一","一芙","一凝","可璇","芮玑","言权","衡屿","语苏","颜可","沫希","芊允","若瑶","米朵","星忆","一纯","倾缘","盼瑾","禾菀","千初","景琛","靖宇","君泽","乾宇","漠白","凤歌","丹青","尘轩","问之","允羡","初晗","芳沁","初予","沁一","云霓","文婕",
                "苏美","锦惜","北茉","清筱","青玥","知沐","令曦","妍可","沐言","敬予","安忆","南叙"};
        User u = new User();
        u.setAge(RandomUtil.randomInt(18, 65));
        u.setMobile(RandomUtil.randomNumbers(11));
        u.setName(nameArr[RandomUtil.randomInt(0, nameArr.length)]);
        u.setPassword(Base64.getEncoder().encodeToString("123".getBytes()));
        u.setSex(RandomUtil.randomInt(1, 3));
        u.setEmail(PinyinUtil.getPinyin(u.getName(), "") + "@163.com");
        u.setStatus(1);
        u.setUsername(RandomUtil.randomString(RandomUtil.randomInt(8, 20)));
        u.setCtime(new Date());
        userDao.insert(u);
        
        return new Result<Integer>().success("Ok");
    }
    
    @DS("master")
    public Result<Integer> del(Integer id) {
        int n = userDao.deleteById(id);
        return new Result<Integer>().success(n);
    }
}

3. 分库分表方案

分库分表方案是dynamic-datasource和sharding-jdbc整合使用。主要难点是在配置多数据源和 sharding-jdbc的分库分表规则配置上。