整合动态数据源

引入依赖
添加版本号
编写配置
创建数据库
测试
测试结果
切换说明
Druid 监控

我们使用的是baomidou 提供的多数据源整合。
github官网：https://github.com/baomidou/dynamic-datasource-spring-boot-starter
苞米豆官网：https://dynamic-datasource.com/guide/integration/Druid.html#%E5%8F%82%E6%95%B0%E9%85%8D%E7%BD%AE

引入依赖

        <!-- druid 数据库连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
        </dependency>
        <!-- Dynamic DataSource 动态数据源 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
        </dependency>

添加版本号

由于 druid-spring-boot-starter、dynamic-datasource-spring-boot-starter 都不是 SpringBoot 官网整合依赖，因此需要在父工程中添加版本号

            <properties>
                                <druid.version>1.2.4</druid.version>
                        <dynamic-ds.version>3.2.1</dynamic-ds.version>
            </properties>
                        <!-- Druid -->
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>${druid.version}</version>
            </dependency>
            <!-- Dynamic DataSource -->
            <dependency>
                <groupId>com.baomidou</groupId>
                <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
                <version>${dynamic-ds.version}</version>
            </dependency>

编写配置

编辑 nacos 配置中心上的 ruoyi-system-dev.yml 文件，将原来的数据源配置删除或注释掉。新增多数据源配置

spring:
  autoconfigure:
    exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure
  # dynamic-datasource 动态数据源的配置
  datasource:
    druid:
      stat-view-servlet:
        enabled: true
        loginUsername: admin
        loginPassword: 123456
    dynamic:
      druid:
        initial-size: 5
        min-idle: 5
        maxActive: 20
        maxWait: 60000
        timeBetweenEvictionRunsMillis: 60000
        minEvictableIdleTimeMillis: 300000
        validationQuery: SELECT 1 FROM DUAL
        testWhileIdle: true
        testOnBorrow: false
        testOnReturn: false
        poolPreparedStatements: true
        maxPoolPreparedStatementPerConnectionSize: 20
        filters: stat,wall,slf4j
        connectionProperties: druid.stat.mergeSql\=true;druid.stat.slowSqlMillis\=5000
      datasource:
        master:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://localhost:3306/ry-cloud?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
          username: root
          password: 123456
        slave_1:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://localhost:3306/ry-cloud-2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
          username: root
          password: 123456
# 数据库连接基本信息
# spring:
#   datasource:
#     url: jdbc:mysql://localhost:3306/ry-cloud?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
#     driver-class-name: com.mysql.cj.jdbc.Driver
#     username: root
#     password: 123456
# mybatis 配置
mybatis:
  # 别名配置
  type-aliases-package: com.ruoyi.system
  # 映射文件位置配置
  mapper-locations: classpath*:mapper/**/*.xml

配置文件说明：

排除原生Druid的快速配置类

spring:
autoconfigure:
 exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure

配置了两个数据源

   datasource:
     master:
       driver-class-name: com.mysql.cj.jdbc.Driver
       url: jdbc:mysql://localhost:3306/ry-cloud?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
       username: root
       password: 123456
     slave_1:
       driver-class-name: com.mysql.cj.jdbc.Driver
       url: jdbc:mysql://localhost:3306/ry-cloud-2?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
       username: root
       password: 123456

druid 监控设置
```
 druid:
   stat-view-servlet:
     enabled: true
     loginUsername: admin
     loginPassword: 123456
```
创建数据库
创建一个slave_1 数据库名为：ry-cloud-2 , 字符编码 UTF8，脚本为： ```sql SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0;

— Table structure for sys_user

DROP TABLE IF EXISTS sys_user; CREATE TABLE sys_user ( user_id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘用户ID’, dept_id bigint(20) NULL DEFAULT NULL COMMENT ‘部门ID’, user_name varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘用户账号’, nick_name varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT ‘用户昵称’, user_type varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT ‘00’ COMMENT ‘用户类型（00系统用户）’, email varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT ‘’ COMMENT ‘用户邮箱’, phonenumber varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT ‘’ COMMENT ‘手机号码’, sex char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT ‘0’ COMMENT ‘用户性别（0男 1女 2未知）’, avatar varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT ‘’ COMMENT ‘头像地址’, password varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT ‘’ COMMENT ‘密码’, status char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT ‘0’ COMMENT ‘帐号状态（0正常 1停用）’, del_flag char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT ‘0’ COMMENT ‘删除标志（0代表存在 2代表删除）’, login_ip varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT ‘’ COMMENT ‘最后登录IP’, login_date datetime(0) NULL DEFAULT NULL COMMENT ‘最后登录时间’, create_by varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT ‘’ COMMENT ‘创建者’, create_time datetime(0) NULL DEFAULT NULL COMMENT ‘创建时间’, update_by varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT ‘’ COMMENT ‘更新者’, update_time datetime(0) NULL DEFAULT NULL COMMENT ‘更新时间’, remark varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT ‘备注’, PRIMARY KEY (user_id) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = ‘用户信息表’ ROW_FORMAT = Compact;

— Records of sys_user

INSERT INTO sys_user VALUES (1, 103, ‘admin’, ‘若依’, ‘00’, ‘ry@163.com’, ‘15888888888’, ‘1’, ‘’, ‘$2a$10$7JB720yubVSZvUI0rEqK/.VqGOZTH.ulu33dHOiBE8ByOhJIrdAu2’, ‘0’, ‘0’, ‘127.0.0.1’, ‘2018-03-16 11:33:00’, ‘admin’, ‘2018-03-16 11:33:00’, ‘ry’, ‘2018-03-16 11:33:00’, ‘管理员’); INSERT INTO sys_user VALUES (2, 105, ‘ry’, ‘若依2’, ‘00’, ‘ry@qq.com’, ‘15666666666’, ‘1’, ‘’, ‘$2a$10$7JB720yubVSZvUI0rEqK/.VqGOZTH.ulu33dHOiBE8ByOhJIrdAu2’, ‘0’, ‘0’, ‘127.0.0.1’, ‘2018-03-16 11:33:00’, ‘admin’, ‘2018-03-16 11:33:00’, ‘ry’, ‘2018-03-16 11:33:00’, ‘测试员’); INSERT INTO sys_user VALUES (3, 103, ‘lcy’, ‘lcy’, ‘00’, ‘’, ‘13000000001’, ‘0’, ‘’, ‘123456’, ‘0’, ‘2’, ‘’, NULL, ‘ry’, ‘2021-04-10 15:22:05’, ‘’, ‘2021-04-10 15:42:48’, ‘1’); INSERT INTO sys_user VALUES (4, 103, ‘小y’, ‘lcy’, ‘00’, ‘’, ‘13000000000’, ‘0’, ‘’, ‘$2a$10$Dzaub8pDnp5Tuicz2fex7ercsYBwvhFISe8T.uGJbmqtpUN/FH2x6’, ‘0’, ‘2’, ‘’, NULL, ‘ry’, ‘2021-04-10 18:17:28’, ‘’, NULL, ‘1’);

SET FOREIGN_KEY_CHECKS = 1;

<a name="O07Ng"></a>
## 切换数据库
将 @DS(**"slave_1"**) 标注在 mapper层或 service 层的类上 或者方法上<br />例如：
```sql
package com.ruoyi.system.mapper;
import com.baomidou.dynamic.datasource.annotation.DS;
import com.ruoyi.system.api.domain.SysUser;
@DS("slave_1")
public interface SysUserMapper {
    public SysUser selectUserByUserName(String userName);
}

测试

运行之前的测试类

    @Test
    public void selectUserByUserName() {
        SysUser user = userMapper.selectUserByUserName("ry");
        System.out.println(user);
    }

测试结果

切换说明

默认情况下，连接的是 master 数据源，如需切换到 slave 数据库，那么就需要在 mapper 或 service 类上，添加
@DS(“slave_1”) 注解即可

Druid 监控

监控页面：http://localhost:9201/druid/index.html
由于项目中的端口是 9201，因此这边的端口也是 9201；登录的账号密码在 ruoyi-system-dev.yml 文件中已经设置过了，我们来看下：

    druid:
      stat-view-servlet:
        enabled: true
        loginUsername: admin
        loginPassword: 123456