shardingsphere分库分表集成多数据源

淩亂°似流年 2024-03-25 09:53 41阅读 0赞

如果有分库分表，又有切换数据源的需求可以使用多数据源dynamic-datasource与shardingsphere集成的场景方案。

**1、pom.xml添加maven依赖**

<dependency>
      <groupId>org.apache.shardingsphere</groupId>
      <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
      <version>5.1.2</version>
    </dependency>

**2、分别配置shardingjdbc和多数据源 yaml配置 同Nacos配置**

# spring配置
    spring: 
      redis:
        host: localhost
        port: 6379
        password: 
      # 分库分表配置
      shardingsphere:
        datasource:
          ds0:
            driver-class-name: com.mysql.cj.jdbc.Driver
            type: com.zaxxer.hikari.HikariDataSource
            username: root
            password: password
            jdbc-url: jdbc:mysql://localhost:3306/yz-log?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
          names: ds0
        props:
          sql-show: true
        rules:
          sharding:            #根据http请求进行分表存储
            sharding-algorithms:
              table-inline:
                props:
                  algorithm-expression: oper_log_$->{request_method}
                type: INLINE
            tables:
              oper_log:
                actual-data-nodes: ds0.oper_log_GET,ds0.oper_log_POST,ds0.oper_log_PUT,ds0.oper_log_DELETE
                table-strategy:
                  standard:
                    sharding-algorithm-name: table-inline
                    sharding-column: request_method
        # 动态数据源配置
      datasource:
        dynamic:
          datasource:
            master:        #数据源1
              username: root
              password: password
              url: jdbc:mysql://localhost:3306/yz_cloud?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
              driver-class-name: com.mysql.cj.jdbc.Driver
            order:        #数据源2
              username: root
              password: password
              url: jdbc:mysql://localhost:3306/yz_order?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
              driver-class-name: com.mysql.cj.jdbc.Driver

**3、添加一个多数据测试库yz-order和表sys\_order**

DROP DATABASE IF EXISTS `yz_order`;
    
    CREATE DATABASE  `yz_order` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
    
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    USE `yz_order`;
    
    -- ----------------------------
    -- 订单信息表sys_order
    -- ----------------------------
    DROP TABLE IF EXISTS sys_order;
    CREATE TABLE sys_order
    (
      order_id      BIGINT(20)      NOT NULL AUTO_INCREMENT    COMMENT '订单ID',
      user_id       BIGINT(64)      NOT NULL                   COMMENT '用户编号',
      STATUS        CHAR(1)         NOT NULL                   COMMENT '状态（0交易成功 1交易失败）',
      order_no      VARCHAR(64)     DEFAULT NULL               COMMENT '订单流水',
      PRIMARY KEY (order_id)
    ) ENGINE=INNODB COMMENT = '订单信息表';

4、**添加sys\_order测试代码**

**SysOrder.java**

/**
     * 订单对象 tb_order
     * 
     * @author yz
     */
    public class SysOrder extends BaseEntity
    {
        private static final long serialVersionUID = 1L;
    
        /** 订单编号 */
        private Long orderId;
    
        /** 用户编号 */
        private Long userId;
    
        /** 状态 */
        private String status;
    
        /** 订单编号 */
        private String orderNo;
    
    }

**省略SysOrderMapper.java**

**省略ISysOrderService.java**

**以下SysOrderServiceImpl.java**

/**
     * 订单Service业务层处理
     * 
     * @author yz
     */
    @Service
    public class SysOrderServiceImpl implements ISysOrderService
    {
        @Autowired
        private SysOrderMapper myShardingMapper;
    
        /**
         * 查询订单
         * 
         * @param orderId 订单编号
         * @return 订单信息
         */
        @Override
        @DS("order")
        public SysOrder selectSysOrderById(Long orderId)
        {
            return myShardingMapper.selectSysOrderById(orderId);
        }
    
        /**
         * 查询订单列表
         * 
         * @param sysOrder 订单信息
         * @return 订单列表
         */
        @Override
        @DS("order")
        public List<SysOrder> selectSysOrderList(SysOrder sysOrder)
        {
            return myShardingMapper.selectSysOrderList(sysOrder);
        }
    
        /**
         * 新增订单
         * 
         * @param sysOrder 订单
         * @return 结果
         */
        @Override
        @DS("order")
        public int insertSysOrder(SysOrder sysOrder)
        {
            return myShardingMapper.insertSysOrder(sysOrder);
        }
    }

**@DS("order")切换数据源 为了不迷糊请看以下**

# 动态多数据源配置
      datasource:
        dynamic:
          datasource:
            master:
              username: root
              password: 123456
              url: jdbc:mysql://localhost:3306/yz-cloud?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
              driver-class-name: com.mysql.cj.jdbc.Driver
            order: #没错就是我
              username: root
              password: 123456
              url: jdbc:mysql://localhost:3306/yz-order?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
              driver-class-name: com.mysql.cj.jdbc.Driver

**SysOrderController.java 测试**

/**
     * 订单 Controller
     * 
     * @author yz
     */
    @RestController
    @RequestMapping("/api/order")
    public class SysOrderController extends BaseController
    {
        @Autowired
        private ISysOrderService sysOrderService;
    
        @GetMapping("/add/{userId}")
        public int add(@PathVariable("userId") Long userId)
        {
            SysOrder sysOrder = new SysOrder();
            sysOrder.setUserId(userId);
            sysOrder.setStatus("0");
            sysOrder.setOrderNo("123456789");
            return sysOrderService.insertSysOrder(sysOrder);
        }
    
        @GetMapping("/list")
        public List<SysOrder> list(SysOrder sysOrder)
        {
            return sysOrderService.selectSysOrderList(sysOrder);
        }
    
        @GetMapping("/query/{orderId}")
        public SysOrder query(@PathVariable("orderId") Long orderId)
        {
            return sysOrderService.selectSysOrderById(orderId);
        }
    }

**5、新增动态数据源配置DataSourceConfiguration.java**

/**
     * 动态数据源配置
     *
     * @author yz
     */
    @Configuration
    public class DataSourceConfiguration
    {
        /**
         * 分表数据源名称
         */
        private static final String SHARDING_DATA_SOURCE_NAME = "sharding";
    
        /**
         * 动态数据源配置项
         */
        @Autowired
        private DynamicDataSourceProperties properties;
    
        /**
         * shardingjdbc有四种数据源，需要根据业务注入不同的数据源
         *
         * <p>1. 未使用分片, 脱敏的名称(默认): shardingDataSource;
         * <p>2. 主从数据源: masterSlaveDataSource;
         * <p>3. 脱敏数据源：encryptDataSource;
         * <p>4. 影子数据源：shadowDataSource
         *
         */
        @Resource
        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(SHARDING_DATA_SOURCE_NAME, shardingSphereDataSource);
                    return dataSourceMap;
                }
            };
        }
    
        /**
         * 将动态数据源设置为首选的
         * 当spring存在多个数据源时, 自动注入的是首选的对象
         * 设置为主要的数据源之后，就可以支持shardingjdbc原生的配置方式了
         */
        @Primary
        @Bean
        public DataSource dataSource()
        {
            DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
            dataSource.setPrimary(properties.getPrimary());
            dataSource.setStrict(properties.getStrict());
            dataSource.setStrategy(properties.getStrategy());
            dataSource.setP6spy(properties.getP6spy());
            dataSource.setSeata(properties.getSeata());
            return dataSource;
        }

1.  **新增yz-log数据库和oper\_log\_xxx四张表**

DROP DATABASE IF EXISTS `yz-log`;
    
    CREATE DATABASE  `yz-log` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
    
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    USE `yz-log`;
    
    DROP TABLE IF EXISTS oper_log_get;
    CREATE TABLE oper_log_get (
      oper_id           BIGINT(20)      NOT NULL AUTO_INCREMENT    COMMENT '日志主键',
      title             VARCHAR(50)     DEFAULT ''                 COMMENT '模块标题',
      business_type     INT(2)          DEFAULT 0                  COMMENT '业务类型（0其它 1新增 2修改 3删除）',
      method            VARCHAR(100)    DEFAULT ''                 COMMENT '方法名称',
      request_method    VARCHAR(10)     DEFAULT ''                 COMMENT '请求方式',
      operator_type     INT(1)          DEFAULT 0                  COMMENT '操作类别（0其它 1后台用户 2手机端用户）',
      oper_name         VARCHAR(50)     DEFAULT ''                 COMMENT '操作人员',
      dept_name         VARCHAR(50)     DEFAULT ''                 COMMENT '部门名称',
      oper_url          VARCHAR(255)    DEFAULT ''                 COMMENT '请求URL',
      oper_ip           VARCHAR(128)    DEFAULT ''                 COMMENT '主机地址',
      oper_location     VARCHAR(255)    DEFAULT ''                 COMMENT '操作地点',
      oper_param        VARCHAR(2000)   DEFAULT ''                 COMMENT '请求参数',
      json_result       VARCHAR(2000)   DEFAULT ''                 COMMENT '返回参数',
      STATUS            INT(1)          DEFAULT 0                  COMMENT '操作状态（0正常 1异常）',
      error_msg         VARCHAR(2000)   DEFAULT ''                 COMMENT '错误消息',
      oper_time         DATETIME                                   COMMENT '操作时间',
      PRIMARY KEY (oper_id)
    ) ENGINE=INNODB AUTO_INCREMENT=100 COMMENT = 'get操作日志记录';
    
    DROP TABLE IF EXISTS oper_log_post;
    CREATE TABLE oper_log_post (
      oper_id           BIGINT(20)      NOT NULL AUTO_INCREMENT    COMMENT '日志主键',
      title             VARCHAR(50)     DEFAULT ''                 COMMENT '模块标题',
      business_type     INT(2)          DEFAULT 0                  COMMENT '业务类型（0其它 1新增 2修改 3删除）',
      method            VARCHAR(100)    DEFAULT ''                 COMMENT '方法名称',
      request_method    VARCHAR(10)     DEFAULT ''                 COMMENT '请求方式',
      operator_type     INT(1)          DEFAULT 0                  COMMENT '操作类别（0其它 1后台用户 2手机端用户）',
      oper_name         VARCHAR(50)     DEFAULT ''                 COMMENT '操作人员',
      dept_name         VARCHAR(50)     DEFAULT ''                 COMMENT '部门名称',
      oper_url          VARCHAR(255)    DEFAULT ''                 COMMENT '请求URL',
      oper_ip           VARCHAR(128)    DEFAULT ''                 COMMENT '主机地址',
      oper_location     VARCHAR(255)    DEFAULT ''                 COMMENT '操作地点',
      oper_param        VARCHAR(2000)   DEFAULT ''                 COMMENT '请求参数',
      json_result       VARCHAR(2000)   DEFAULT ''                 COMMENT '返回参数',
      STATUS            INT(1)          DEFAULT 0                  COMMENT '操作状态（0正常 1异常）',
      error_msg         VARCHAR(2000)   DEFAULT ''                 COMMENT '错误消息',
      oper_time         DATETIME                                   COMMENT '操作时间',
      PRIMARY KEY (oper_id)
    ) ENGINE=INNODB AUTO_INCREMENT=100 COMMENT = 'post操作日志记录';
    
    DROP TABLE IF EXISTS oper_log_put;
    CREATE TABLE oper_log_put (
      oper_id           BIGINT(20)      NOT NULL AUTO_INCREMENT    COMMENT '日志主键',
      title             VARCHAR(50)     DEFAULT ''                 COMMENT '模块标题',
      business_type     INT(2)          DEFAULT 0                  COMMENT '业务类型（0其它 1新增 2修改 3删除）',
      method            VARCHAR(100)    DEFAULT ''                 COMMENT '方法名称',
      request_method    VARCHAR(10)     DEFAULT ''                 COMMENT '请求方式',
      operator_type     INT(1)          DEFAULT 0                  COMMENT '操作类别（0其它 1后台用户 2手机端用户）',
      oper_name         VARCHAR(50)     DEFAULT ''                 COMMENT '操作人员',
      dept_name         VARCHAR(50)     DEFAULT ''                 COMMENT '部门名称',
      oper_url          VARCHAR(255)    DEFAULT ''                 COMMENT '请求URL',
      oper_ip           VARCHAR(128)    DEFAULT ''                 COMMENT '主机地址',
      oper_location     VARCHAR(255)    DEFAULT ''                 COMMENT '操作地点',
      oper_param        VARCHAR(2000)   DEFAULT ''                 COMMENT '请求参数',
      json_result       VARCHAR(2000)   DEFAULT ''                 COMMENT '返回参数',
      STATUS            INT(1)          DEFAULT 0                  COMMENT '操作状态（0正常 1异常）',
      error_msg         VARCHAR(2000)   DEFAULT ''                 COMMENT '错误消息',
      oper_time         DATETIME                                   COMMENT '操作时间',
      PRIMARY KEY (oper_id)
    ) ENGINE=INNODB AUTO_INCREMENT=100 COMMENT = 'put操作日志记录';
    
    DROP TABLE IF EXISTS oper_log_delete;
    CREATE TABLE oper_log_delete (
      oper_id           BIGINT(20)      NOT NULL AUTO_INCREMENT    COMMENT '日志主键',
      title             VARCHAR(50)     DEFAULT ''                 COMMENT '模块标题',
      business_type     INT(2)          DEFAULT 0                  COMMENT '业务类型（0其它 1新增 2修改 3删除）',
      method            VARCHAR(100)    DEFAULT ''                 COMMENT '方法名称',
      request_method    VARCHAR(10)     DEFAULT ''                 COMMENT '请求方式',
      operator_type     INT(1)          DEFAULT 0                  COMMENT '操作类别（0其它 1后台用户 2手机端用户）',
      oper_name         VARCHAR(50)     DEFAULT ''                 COMMENT '操作人员',
      dept_name         VARCHAR(50)     DEFAULT ''                 COMMENT '部门名称',
      oper_url          VARCHAR(255)    DEFAULT ''                 COMMENT '请求URL',
      oper_ip           VARCHAR(128)    DEFAULT ''                 COMMENT '主机地址',
      oper_location     VARCHAR(255)    DEFAULT ''                 COMMENT '操作地点',
      oper_param        VARCHAR(2000)   DEFAULT ''                 COMMENT '请求参数',
      json_result       VARCHAR(2000)   DEFAULT ''                 COMMENT '返回参数',
      STATUS            INT(1)          DEFAULT 0                  COMMENT '操作状态（0正常 1异常）',
      error_msg         VARCHAR(2000)   DEFAULT ''                 COMMENT '错误消息',
      oper_time         DATETIME                                   COMMENT '操作时间',
      PRIMARY KEY (oper_id)
    ) ENGINE=INNODB AUTO_INCREMENT=100 COMMENT = 'delete操作日志记录';

**7、省略service和mapper**

**修改OperLogServiceImpl.java，切换分库分表**

/**
     * 操作日志 服务层处理
     * 
     * @author yz
     */
    @Service
    public class OperLogServiceImpl implements IOperLogService
    {
        @Autowired
        private OperLogMapper operLogMapper;
    
        /**
         * 新增操作日志
         * 
         * @param operLog 操作日志对象
         * @return 结果
         */
        @Override
        @DS("sharding")
        public int insertOperlog(OperLog operLog)
        {
            return operLogMapper.insertOperlog(operLog);
        }
    
        /**
         * 查询系统操作日志集合
         * 
         * @param operLog 操作日志对象
         * @return 操作日志集合
         */
        @Override
        @DS("sharding")
        public List<OperLog> selectOperLogList(OperLog operLog)
        {
            return operLogMapper.selectOperLogList(operLog);
        }
    
        /**
         * 批量删除系统操作日志
         * 
         * @param operIds 需要删除的操作日志ID
         * @return 结果
         */
        @Override
        @DS("sharding")
        public int deleteOperLogByIds(Long[] operIds)
        {
            return operLogMapper.deleteOperLogByIds(operIds);
        }
    
        /**
         * 查询操作日志详细
         * 
         * @param operId 操作ID
         * @return 操作日志对象
         */
        @Override
        @DS("sharding")
        public OperLog selectOperLogById(Long operId)
        {
            return operLogMapper.selectOperLogById(operId);
        }
    
        /**
         * 清空操作日志
         */
        @Override
        @DS("sharding")
        public void cleanOperLog()
        {
            operLogMapper.cleanOperLog();
        }
    }

**@DS("sharding")其中sharding就是定义的分库分表 为了不迷糊且看以下**

# 分库分表配置
      shardingsphere:
        datasource:
          ds0:
            driver-class-name: com.mysql.cj.jdbc.Driver
            type: com.zaxxer.hikari.HikariDataSource
            username: root
            password: 123456
            jdbc-url: jdbc:mysql://localhost:3306/yz-log?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
          names: ds0
        props:
          sql-show: true
        rules:
          sharding:#没错就是我
            sharding-algorithms:
              table-inline:
                props:                    #以下根据http请求进入不同表 如GET 
                  algorithm-expression: sys_oper_log_$->{request_method}
                type: INLINE
            tables:
              oper_log:                #以下根据http请求进入不同表 如GET 
                actual-data-nodes: ds0.sys_oper_log_GET,ds0.sys_oper_log_POST,ds0.sys_oper_log_PUT,ds0.sys_oper_log_DELETE
                table-strategy:
                  standard:
                    sharding-algorithm-name: table-inline
                    sharding-column: request_method

**8、新增分库分表测试类TestOperlogController.java**

@RestController
    @RequestMapping("/api/operlog")
    public class TestOperlogController extends BaseController
    {
        @Autowired
        private IOperLogService operLogService;
    
        @GetMapping("/{method}")
        public int operlog(@PathVariable("method") String method)
        {
            OperLog operLog = new OperLog();
            operLog.setTitle("测试数据");
            operLog.setOperName("admin");
            operLog.setRequestMethod(method);
            return operLogService.insertOperlog(operLog);
        }
    
        @GetMapping("/list")
        public List<OperLog> list()
        {
            return operLogService.selectOperLogList(new OperLog());
        }
    }

**9、启动DemoApplication.java进行测试验证**

**订单数据库测试**

访问 [**http://localhost:8080/api/order/add/1** ][http_localhost_8080_api_order_add_1_]入到yz-order库sys\_order表

查询 [**http://localhost:8080/api/order/list**][http_localhost_8080_api_order_list]

查询 [**http://localhost:8080/api/order/query/1**][http_localhost_8080_api_order_query_1]

**分库分表测试**

访问 [**http://localhost:8080/api/operlog/GET** ][http_localhost_8080_api_operlog_GET_]入到yz-log库oper\_log\_get表

访问 [**http://localhost:8080/api/operlog/POST** ][http_localhost_8080_api_operlog_POST_]入到yz-log库oper\_log\_post表

访问 [**http://localhost:8080/api/operlog/PUT** ][http_localhost_8080_api_operlog_PUT_]入到yz-log库oper\_log\_put表

访问 [**http://localhost:8080/api/operlog/DELETE** ][http_localhost_8080_api_operlog_DELETE_]入到yz-log库oper\_log\_delete表

查询 [**http://localhost:8080/api/operlog/list**][http_localhost_8080_api_operlog_list]

[http_localhost_8080_api_order_add_1_]: http://localhost:9201/order/add/1
[http_localhost_8080_api_order_list]: http://localhost:9201/order/list
[http_localhost_8080_api_order_query_1]: http://localhost:9201/order/query/1
[http_localhost_8080_api_operlog_GET_]: http://localhost:9201/test/operlog/GET
[http_localhost_8080_api_operlog_POST_]: http://localhost:9201/test/operlog/POST
[http_localhost_8080_api_operlog_PUT_]: http://localhost:9201/test/operlog/PUT
[http_localhost_8080_api_operlog_DELETE_]: http://localhost:9201/test/operlog/DELETE
[http_localhost_8080_api_operlog_list]: http://localhost:9201/test/operlog/list