shardingsphere动态数据源

项目中使用到了mybatis-plus，多数据源切换用的是dynamic-datasource-spring-boot-starter，只要加@DS注解就可以切换，非常方便，但随着业务越来越复杂，数据量越来越多，就涉及到读写分离分库分表了，我们选择shardingsphere无代码侵入解决方案，将shardingsphere交给dynamic-datasource管理，既可以方便切换数据源又可以使用shardingsphere读写分离分库分表等功能。

pom引入依赖

    com.baomidou
    dynamic-datasource-spring-boot-starter
    3.1.1


    org.apache.shardingsphere
    sharding-jdbc-spring-boot-starter
    4.1.1


    org.apache.shardingsphere
    sharding-jdbc-spring-namespace
    4.0.0-RC1


    com.baomidou
    mybatis-plus-boot-starter
    3.4.1

动态数据源配置

import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.provider.AbstractDataSourceProvider;
import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceAutoConfiguration;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import org.apache.shardingsphere.shardingjdbc.jdbc.adapter.AbstractDataSourceAdapter;
import org.apache.shardingsphere.shardingjdbc.spring.boot.SpringBootConfiguration;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.AutoConfigureBefore;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Lazy;
import org.springframework.context.annotation.Primary;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Map;


@Configuration
@AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class,
        SpringBootConfiguration.class})
public class DataSourceConfiguration {
    
    private static final String SHARDING_DATA_SOURCE_NAME = "shardingjdbc";
    
    @Autowired
    private DynamicDataSourceProperties properties;

    
    @Lazy
    @Resource(name = "shardingDataSource")
    AbstractDataSourceAdapter shardingDataSource;

    @Bean
    public DynamicDataSourceProvider dynamicDataSourceProvider() {
        Map datasourceMap = properties.getDatasource();
        return new AbstractDataSourceProvider() {
            @Override
            public Map loadDataSources() {
                Map dataSourceMap = createDataSourceMap(datasourceMap);
                // 将 shardingjdbc 管理的数据源也交给动态数据源管理
                dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, shardingDataSource);
                return dataSourceMap;
            }
        };
    }

    
    @Primary
    @Bean
    public DataSource dataSource(DynamicDataSourceProvider dynamicDataSourceProvider) {
        DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
       // dataSource.setPrimary(properties.getPrimary());
        //这里设置sharding管理的数据源为默认数据源，
       //如果设置其它则在yml文件里配置primary:数据源
        dataSource.setPrimary(SHARDING_DATA_SOURCE_NAME);
        dataSource.setStrict(properties.getStrict());
        dataSource.setStrategy(properties.getStrategy());
        dataSource.setProvider(dynamicDataSourceProvider);
        dataSource.setP6spy(properties.getP6spy());
        dataSource.setSeata(properties.getSeata());
        return dataSource;
    }
}

application.yml,这里shardingsphere配置的读写分离策略

server:
  port: 9099
  servlet:
    context-path: /quarant/server

spring:
  shardingsphere:
    datasource:
      #配置真实数据源
      names: ds0,ds1
      ds0:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://172.18.11.9:3306/quarant_db?serverTimezone=Asia/Shanghai&useLegacyDatetimeCode=false&useSSL=false&nullNamePatternMatchesAll=true&useUnicode=true&characterEncoding=UTF-8
        username: root
        password: Dcqc@123456
        type: com.zaxxer.hikari.HikariDataSource
        initialSize: 5
        minIdle: 10
        maxActive: 50
        maxWait: 60000
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        maxEvictableIdleTimeMillis: 900000
        validationQuery: SELECt 1 FROM DUAL
      ds1:
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://172.18.11.9:3306/quarant_db1?serverTimezone=Asia/Shanghai&useLegacyDatetimeCode=false&useSSL=false&nullNamePatternMatchesAll=true&useUnicode=true&characterEncoding=UTF-8
        username: root
        password: Dcqc@123456
        type: com.zaxxer.hikari.HikariDataSource
        initialSize: 5
        minIdle: 10
        maxActive: 50
        maxWait: 60000
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        maxEvictableIdleTimeMillis: 900000
        validationQuery: SELECt 1 FROM DUAL
    sharding:
      master-slave-rules:
        master-test0: # 哪一个主节点
          master-datasource-name: ds0                                          # 指定主节点名字
          slave-data-source-names: ds0,ds1                           # 指定读节点名字,多个读节点用逗号分开
        # 配置从库选择策略，提供轮询与随机，这里选择用轮询
    masterslave:
      load-balance-algorithm-type: round_robin
    props:
      sql:
        show: false   # 日志显示SQL
  datasource:
    # 动态数据源配置
    dynamic:
      datasource:
        slave:
          driver-class-name: com.mysql.jdbc.Driver
          url: jdbc:mysql://172.18.11.9:3306/quarant_db?serverTimezone=Asia/Shanghai&useLegacyDatetimeCode=false&useSSL=false&nullNamePatternMatchesAll=true&useUnicode=true&characterEncoding=UTF-8
          username: root
          password: Dcqc@123456
        health:
          driver-class-name: org.postgresql.Driver
          url: jdbc:postgresql://localhost:5432/postgres?currentSchema=rcw
          username: postgres
          password: admin
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      min-idle: 5
      max-pool-size: 100
      auto-commit: true
      idle-timeout: 60000
      max-lifetime: 1800000
      connection-timeout: 30000
      connection-test-query: SELECT 1

 默认是shardingsphere配置的两个数据源，如果想切换数据源使用@DS注解即可轻松切换。

    @GetMapping("/querydb3")

//数据源db3
    @DS("db3")
    public String querydb3() {
        QuarantineInfo quarantineInfo = quarantineInfoService.getById(53);
        return JSON.toJSONString(quarantineInfo);
    }

//默认数据源

    @GetMapping("/query")
    public String query() {
        QuarantineInfo quarantineInfo = quarantineInfoService.getById(53);
        return JSON.toJSONString(quarantineInfo);
    }