shardingsphere动态数据源

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

今朝花落悲颜色

3622人浏览 · 2022-06-21 17:02:26

今朝花落悲颜色 · 2022-06-21 17:02:26 发布

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

pom引入依赖

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>3.1.1</version><!--建议使用此版本，高版移除了某些参数-->
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-namespace</artifactId>
    <version>4.0.0-RC1</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.4.1</version>
</dependency>

动态数据源配置

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;

/**
 * 动态数据源配置：
 *
 * 使用{@link com.baomidou.dynamic.datasource.annotation.DS}注解，切换数据源
 *
 * <code>@DS(DataSourceConfiguration.SHARDING_DATA_SOURCE_NAME)</code>
 *
 * @author ws
 * @date 2022/6/21 15:19
 */
@Configuration
@AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class,
        SpringBootConfiguration.class})
public class DataSourceConfiguration {
    /**
     * 分表数据源名称
     */
    private static final String SHARDING_DATA_SOURCE_NAME = "shardingjdbc";
    /**
     * 动态数据源配置项
     */
    @Autowired
    private DynamicDataSourceProperties properties;

    /**
     * shardingjdbc有四种数据源，需要根据业务注入不同的数据源
     *
     * <p>1. 未使用分片, 脱敏的名称(默认): shardingDataSource;
     * <p>2. 主从数据源: masterSlaveDataSource;
     * <p>3. 脱敏数据源：encryptDataSource;
     * <p>4. 影子数据源：shadowDataSource
     *
     */
    @Lazy
    @Resource(name = "shardingDataSource")
    AbstractDataSourceAdapter shardingDataSource;

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

    /**
     * 将动态数据源设置为首选的
     * 当spring存在多个数据源时, 自动注入的是首选的对象
     * 设置为主要的数据源之后，就可以支持shardingjdbc原生的配置方式了
     *
     * @return
     */
    @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);
}