使用Shardingsphere和dynamic-datasource实现多数据源切换

之前使用Shardingsphere做的数据库分表，但是会发现有很多的sql语法都不能使用了，而且使用其他办法会大大影响性能。所以现在决定引入dynamic-datasource来实现多数据源的切换。
思路：配置一个数据源，为主数据源，使用dynamic-datasource来管理数据源的切换，并且把Shardingsphere管理的数据源加入进去。也就是对分表的SQL使用sharding jdbc数据源，对不涉及到分表的SQL，使用普通数据源。

关键依赖

		
			org.apache.shardingsphere
			sharding-jdbc-spring-boot-starter
			4.1.1
		
		
		
			com.baomidou
			dynamic-datasource-spring-boot-starter
			3.2.1
		

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管理。

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;

    
    @Bean
    public DynamicDataSourceProvider dynamicDataSourceProvider() {
        Map datasourceMap = dynamicDataSourceProperties.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(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 可以注解在方法上和类上，同时存在方法注解优先于类上注解