使用Shardingsphere和dynamic-datasource实现多数据源切换 解决sql主库占用

使用Shardingsphere和dynamic-datasource实现多数据源切换 分表分库 解决sql主库占用
pom

		<dependency>
			<groupId>org.apache.shardingsphere</groupId>
			<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
			<version>4.1.1</version>
		</dependency>
		<!--依赖dynamic-datasource-->
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
			<version>3.2.1</version>
		</dependency>

properties

##shardingsphere jdbc分片策略
#设置严格模式,默认false不启动. 启动后在未匹配到指定数据源时候回抛出异常,不启动会使用默认数据源.
spring.datasource.dynamic.strict=true
spring.datasource.dynamic.datasource.master.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.dynamic.datasource.master.type=com.zaxxer.hikari.HikariDataSource
spring.datasource.dynamic.datasource.master.url=jdbc:sqlserver://10.0.8.77:1433;DatabaseName=heinz;loginTimeout=30;sendStringParametersAsUnicode=false
spring.datasource.dynamic.datasource.master.username=sa
spring.datasource.dynamic.datasource.master.password=EytccEIFpNj3uw5SU1kK

# 配置数据源，给数据源起名ds-1...此处可配置多数据源
spring.shardingsphere.datasource.names=ds-1
spring.shardingsphere.datasource.ds-1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds-1.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.shardingsphere.datasource.ds-1.jdbc-url=jdbc:sqlserver://10.0.8.77:1433;DatabaseName=heinz;loginTimeout=30;sendStringParametersAsUnicode=false
spring.shardingsphere.datasource.ds-1.username=sa
spring.shardingsphere.datasource.ds-1.password=EytccEIFpNj3uw5SU1kK
# 指定表的分布
spring.shardingsphere.sharding.tables.multistage_level_qr_code.actual-data-nodes=ds-1.multistage_level_qr_code_$->{0..4}
spring.shardingsphere.sharding.tables.code_circulation.actual-data-nodes=ds-1.code_circulation_$->{0..4}
spring.shardingsphere.sharding.tables.recombine_detail.actual-data-nodes=ds-1.recombine_detail_$->{0..4}
# 指定分片策略为complex策略，使用复杂秘钥分片算法
spring.shardingsphere.sharding.tables.multistage_level_qr_code.logic-table=multistage_level_qr_code
spring.shardingsphere.sharding.tables.multistage_level_qr_code.table-strategy.complex.sharding-columns=id,ipc_code
spring.shardingsphere.sharding.tables.multistage_level_qr_code.table-strategy.complex.algorithm-class-name=com.sigmatrix.configuration.ComplexKeysShardingAlgorithmImpl
# 指定分片策略为standard策略，使用精准分片算法
spring.shardingsphere.sharding.tables.code_circulation.logic-table=code_circulation
spring.shardingsphere.sharding.tables.code_circulation.table-strategy.standard.sharding-column=qr_code
spring.shardingsphere.sharding.tables.code_circulation.table-strategy.standard.precise-algorithm-class-name=com.sigmatrix.configuration.PreciseShardingAlgorithmImpl
spring.shardingsphere.sharding.tables.recombine_detail.logic-table=recombine_detail
spring.shardingsphere.sharding.tables.recombine_detail.table-strategy.standard.sharding-column=code
spring.shardingsphere.sharding.tables.recombine_detail.table-strategy.standard.precise-algorithm-class-name=com.sigmatrix.configuration.PreciseShardingAlgorithmImpl
# 显示sql执行语句
spring.shardingsphere.props.sql.show=true

Dynamic-datasource的动态数据源配置类

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.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringBootConfiguration;
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 {
    /**
     * 分表数据源名称
     */
    public static final String SHARDING_DATA_SOURCE_NAME = "sharding";
    /**
     * 动态数据源配置项
     */
    @Autowired
    private DynamicDataSourceProperties dynamicDataSourceProperties;
    
    @Lazy
    @Resource
    DataSource shardingDataSource;

    /**
     * 将shardingDataSource放到了多数据源（dataSourceMap）中
     * 注意有个版本的bug，3.1.1版本 不会进入loadDataSources 方法，这样就一直造成数据源注册失败
     */
    @Bean
    public DynamicDataSourceProvider dynamicDataSourceProvider() {
        Map<String, DataSourceProperty> datasourceMap = dynamicDataSourceProperties.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(dynamicDataSourceProperties.getPrimary());
        dataSource.setStrict(dynamicDataSourceProperties.getStrict());
        dataSource.setStrategy(dynamicDataSourceProperties.getStrategy());
        dataSource.setProvider(dynamicDataSourceProvider);
        dataSource.setP6spy(dynamicDataSourceProperties.getP6spy());
        dataSource.setSeata(dynamicDataSourceProperties.getSeata());
        return dataSource;
    }
}

访问没有分表的数据时使用默认的普通数据源，访问分表的数据时使用@DS("sharding")注解，
@DS(“slave”) 括号内即是application.properties中配置的数据源名称，不加注解默认是访问主库master，可加在service方法上，也可加在mapper方法上，但强烈不建议同时在service和mapper注解。 (可能会有问题)
@DS 可以注解在方法上和类上，同时存在方法注解优先于类上注解