shardingsphere5.x整合springboot+dynamic-datasource多数据源实战

2023-01-19 11:06:32

本文是在springboot整合分库分表的基础上添加了多数据源，建议先看上一篇shardingsphere5.x整合springboot分库分表实战_任人人人呢的博客-CSDN博客

pom.xml配置：

<!--shardingsphere分库分表依赖-->
<dependency>
	<groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
	<version>5.1.1</version>
</dependency>

<!--mybatis-plus依赖-->
<dependency>
	<groupId>com.baomidou</groupId>
	<artifactId>mybatis-plus-boot-starter</artifactId>
	<version>3.4.0</version>
</dependency>

<!-- dynamic多数据源 -->
<dependency>
	<groupId>com.baomidou</groupId>
	<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
	<version>3.1.1</version>
</dependency>

yml配置：

spring:
  datasource:
    # 动态数据源配置
    dynamic:
      datasource:
        tour:
          type: com.alibaba.druid.pool.DruidDataSource
          driver-class-name: com.mysql.jdbc.Driver
          url: jdbc:mysql://localhost:3306/tour_business?serverTimezone=GMT%2b8:00&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false&allowMultiQueries=true&useSSL=false
          username: root
          password: root
        avalon:
          type: com.alibaba.druid.pool.DruidDataSource
          driver-class-name: com.mysql.jdbc.Driver
          url: jdbc:mysql://localhost/avalon_ads?serverTimezone=GMT%2b8:00&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false&allowMultiQueries=true&useSSL=false
          username: root
          password: root
      # 指定默认数据源名称
      primary: tour
  shardingsphere:
    datasource:
      common:
        driver-class-name: com.mysql.jdbc.Driver
        type: com.alibaba.druid.pool.DruidDataSource
      names: db0,db1
      db0:
        url: jdbc:mysql://localhost:3306/test
        username: root
        password: root
        type: com.alibaba.druid.pool.DruidDataSource
      db1:
        url: jdbc:mysql://localhost:3306/zkq_oms_test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
        username: root
        password: root
        type: com.alibaba.druid.pool.DruidDataSource
    #默认数据源，未分片的表默认执行库
    sharding:
        default-data-source-name: db1
    rules:
     sharding:
        key-generators:
          #此处必须要配置，否则会导致报错，因为shardingsphere-jdbc-core-spring-boot-starter需要加载此项配置，官网的demo例子有错
          #分布式序列算法：https://shardingsphere.apache.org/document/current/cn/user-manual/common-config/builtin-algorithm/keygen/
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 123
        sharding-algorithms:
          #分片算法：https://shardingsphere.apache.org/document/current/cn/user-manual/common-config/builtin-algorithm/sharding/
          table-inline:
            type: MOD
            props:
              sharding-count: 10
        tables:
          orders:
            # 配置orders表的分表的规则
            actual-data-nodes: db0.orders_$->{0..9}
            table-strategy:
              standard:
                sharding-column: order_no
                sharding-algorithm-name: table-inline
    enabled: true
    # 展示修改以后的sql语句
    props:
      sql-show: true


mybatis:
    configuration:
        map-underscore-to-camel-case: true
        log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    type-aliases-package: com.example.demo.entity
    mapper-locations: classpath:mapper/*.xml

添加多数据源配置类：

package com.example.demo;

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.sql.DataSource;
import java.util.Map;


@Configuration
@AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class,
        SpringBootConfiguration.class})
public class DataSourceConfiguration {

    /**
     * 动态数据源配置项
     * 这里会根据yml文件的配置自动加载配置，将多个数据源信息放到datasourceMap中
     */
    @Autowired
    private DynamicDataSourceProperties properties;

    /**
     * 使用shardingSphereDataSource 自动装载的 DataSource
     * 5.1.1版本自动装载的shardingSphereDataSource beanName="shardingSphereDataSource"
     * 要加@Lazy
     */
    @Lazy
    @Autowired
    private DataSource shardingSphereDataSource;


    @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(DBConstants.SHARDING, shardingSphereDataSource);
                return dataSourceMap;
            }
        };
    }

    /**
     * 将动态数据源设置为首选的
     * 当spring存在多个数据源时, 自动注入的是首选的对象
     * 设置为主要的数据源之后，就可以支持shardingjdbc原生的配置方式了
     *
     * @return
     */
    @Primary
    @Bean
    public DataSource dataSource(DynamicDataSourceProvider dynamicDataSourceProvider) {
        DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
        dataSource.setPrimary(properties.getPrimary());
        dataSource.setStrict(properties.getStrict());
        dataSource.setStrategy(properties.getStrategy());
        dataSource.setProvider(dynamicDataSourceProvider);
        dataSource.setP6spy(properties.getP6spy());
        dataSource.setSeata(properties.getSeata());
        return dataSource;
    }
}

添加多数据源常量类：

package com.example.demo;


public class DBConstants {

    /**
     * 数据源分组 - 巡店库
     * 这里的tour是yml中的 spring.datasource.dynamic.datasource.tour
     */
    public static final String TOUR = "tour";

    /**
     * 数据源分组 - 阿瓦隆库
     * 这里的avalon是yml中的 spring.datasource.dynamic.datasource.avalon
     */
    public static final String AVALON = "avalon";

    /**
     * 数据源分组 - 分库分表
     */
    public static final String SHARDING = "sharding";
}

Mapper文件


@Repository
public interface CommonDealerMapper {

    //使用多数据源默认的tour库
    CommonDealerEntity getCommonDealer(@Param("clientCode") String clientCode);

    //使用多数据源的avalon库
    @DS(value = DBConstants.AVALON)
    AvalonCommonDealer getShuCangCommonDealer(@Param("clientCode") String clientCode);
}


@Repository
public interface OrdersMapper extends BaseMapper<Orders> {

    //使用shardingsphere中分表数据源
	@DS(value = DBConstants.SHARDING)
	Orders getOrderByNo(String orderNo);

	@DS(value = DBConstants.SHARDING)
	List<Orders> getOrderList();
}



@Repository
public interface ZkqClientMapper {

    //使用shardingsphere中默认数据源，未分片的表默认执行库
    @DS(value = DBConstants.SHARDING)
    Map<String, Object> selectByPrimaryKey(Long id);
}
  • 作者：任人人人呢
  • 原文链接：https://blog.csdn.net/weixin_42130396/article/details/128456446
    更新时间：2023-01-19 11:06:32