Sz-Admin

切换日光/暗黑模式

多数据源的实现 ​

mybatis-flex默认已经支持多数据源的实现，因此我们稍加改造即可。

更多多数据源的方法支持请参考 Mybatis-Flex文档。

Sz-Admin示例代码请参照datasource分支： Github地址

  1. 修改配置文件，删除mysql.yml中的数据源配置，改为使用mybatis-flex.yml实现

    yml
    spring:
  datasource:  #
    driver-class-name: com.mysql.cj.jdbc.Driver #
    url: jdbc:mysql://127.0.0.1:3306/sz_admin_preview?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true #
    username: root #
    password: Yanfa2023@ #
    hikari: #
      pool-name: HikariCP #
      minimum-idle: 5 #
      idle-timeout: 600000 #
      maximum-pool-size: 10 #
      auto-commit: true #
      max-lifetime: 1800000 #
      connection-timeout: 30000 #
      connection-test-query: SELECT 1 #
  liquibase:
    change-log: classpath:db/changelog/changelog-master.xml
    enabled: true
    yml
    mybatis-flex:
  configuration:
    map-underscore-to-camel-case: true
    jdbc-type-for-null: null
    auto-mapping-behavior: full
    auto-mapping-unknown-column-behavior: none
    cache-enabled: false
  global-config:
    deleted-value-of-logic-delete: "T"
    normal-value-of-logic-delete: "F"
  datasource:  #
    primary:  # 主数据源
      type: com.zaxxer.hikari.HikariDataSource  #
      driver-class-name: com.mysql.cj.jdbc.Driver  #
      url: jdbc:mysql://127.0.0.1:3306/sz_flow?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true  #
      username: root  #
      password: Yanfa2023@  #
      pool-name: HikariCPA  #
      minimum-idle: 5  #
      idle-timeout: 600000  #
      maximum-pool-size: 10  #
      auto-commit: true  #
      max-lifetime: 1800000  #
      connection-timeout: 30000  #
      connection-test-query: SELECT 1  #
    preview:  # 从数据源
      type: com.zaxxer.hikari.HikariDataSource  #
      driver-class-name: com.mysql.cj.jdbc.Driver  #
      url: jdbc:mysql://127.0.0.1:3306/sz_admin_preview?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true  #
      username: root  #
      password: Yanfa2023@  #
      pool-name: HikariCPB  #
      minimum-idle: 5  #
      idle-timeout: 600000  #
      maximum-pool-size: 10  #
      auto-commit: true  #
      max-lifetime: 1800000  #
      connection-timeout: 30000  #
      connection-test-query: SELECT 1  #

  2. 可以通过设置 PO 实体的 @Table 注解中的 dataSource 属性来指定所使用的数据源。

    TIP

    以 SysUser 表为例，如需切换数据源为 preview，只需在实体类的 @Table 注解中设置 dataSource="preview" 即可。

    java
    @Data
@Table(value = "sys_user", onInsert = TableSysUserListener.class, onUpdate = TableSysUserListener.class)
@Table(value = "sys_user", onInsert = TableSysUserListener.class, onUpdate = TableSysUserListener.class, dataSource = "preview")
@Schema(description = "系统用户表")
public class SysUser implements Serializable {

    @Serial
    private static final long serialVersionUID = 1L;

    @Id(keyType = KeyType.Auto)
    @Schema(description = "id")
    private Long id;

    @Schema(description = "用户名")
    private String username;

    @Schema(description = "密码")
    private String pwd;

    @Schema(description = "手机号")
    private String phone;

    @Schema(description = "昵称")
    private String nickname;

    @Schema(description = "性别(0 未知 1 男 2 女)")
    private Integer sex;

    @Schema(description = "生日")
    private String birthday;

    @Schema(description = "头像地址")
    private String logo;

    @Schema(description = "年龄")
    private Integer age;

    @Schema(description = "身份证")
    private String idCard;

    @Schema(description = "邮箱地址")
    private String email;

    @Schema(description = "账户状态 (如 冻结；禁言；正常。 关联字典表account_status)")
    private String accountStatusCd;

    @Schema(description = "标签（自定义关联到字典表）")
    private String userTagCd;

    @Schema(description = "最近一次登录时间")
    private LocalDateTime lastLoginTime;

    @Schema(description = "创建时间")
    private LocalDateTime createTime;

    @Schema(description = "更新时间")
    private LocalDateTime updateTime;

    @Column(isLogicDelete = true)
    @Schema(description = "是否删除")
    private String delFlag;

    @Schema(description = "创建人")
    private Long createId;

    @Schema(description = "更新人")
    private Long updateId;

}

  3. Flyway 已被禁用，并将在 v1.3.0-beta 版本中移除。后续将采用 Liquibase 进行数据库管理。需要注意的是，Liquibase 默认仅作用于 primary 数据源，如需支持多数据源，请根据实际需求进行相应配置。

    java
    @SpringBootApplication
@EnableAspectJAutoProxy
@RequiredArgsConstructor
public class AdminApplication {

    @Value("${app.version}")
    private String appVersion;

    @Deprecated(since = "v1.3.0-beta", forRemoval = true)
    private final FlywayProperties flywayProperties;

    @Deprecated(since = "v1.3.0-beta", forRemoval = true)
    private final Flyway frameworkFlyway;

    @Deprecated(since = "v1.3.0-beta", forRemoval = true)
    private final Flyway businessFlyway;

    @Getter
    private static String version;

    @PostConstruct
    public void init() {
        setVersion(appVersion); // 通过辅助方法设置静态字段
        initFlyway();
        // initFlyway();
    }

    @Deprecated(since = "v1.3.0-beta", forRemoval = true)
    private void initFlyway() {
        FlywayProperties.FlywayConfig business = flywayProperties.getBusiness();
        FlywayProperties.FlywayConfig framework = flywayProperties.getFramework();
        if (framework.isEnabled())
            frameworkFlyway.migrate();
        if (business.isEnabled())
            businessFlyway.migrate();
    }

    private static void setVersion(String appVersion) {
        AdminApplication.version = appVersion;
    }

    public static void main(String[] args) {
        SpringApplication.run(AdminApplication.class, args);
        String template = """
                                                   __                _
                                                  |  ]              (_)
                 .--.   ____  ______  ,--.    .--.| |  _ .--..--.   __   _ .--.
                ( (`\\] [_   ]|______|`'_\\ : / /'`\\' | [ `.-. .-. | [  | [ `.-. |
                 `'.'.  .' /_        // | |,| \\__/  |  | | | | | |  | |  | | | |
                [\\__) )[_____]       \\'-;__/ '.__.;__][___||__||__][___][___||__]
                ------------------%s  (v%s)-------------------
                """;
        String result = String.format(template, "https://szadmin.cn", getVersion());
        System.out.println(result);
    }

}

  4. 修改SQL日志规则，增加对 dsName （数据源名称）的支持。

    java
    @Slf4j
public class PrintSQL {

    private PrintSQL() {
        throw new IllegalStateException("Utility class");
    }

    public static void print() {
        // 开启审计功能
        AuditManager.setAuditEnable(true);
        // 自定义sql打印 或 自定义审计功能，也可结合logback将sql日志输出到独立文件中。
        // 详见https://mybatis-flex.com/zh/core/audit.html
        // 设置 SQL 审计收集器
        AuditManager.setMessageCollector(auditMessage -> log.info("{} ---- {}ms, row:{}", // [!code --] formatSQL(auditMessage.getFullSql()), auditMessage.getElapsedTime(),
                auditMessage.getQueryCount()));
        AuditManager.setMessageCollector(
                auditMessage -> log.info("{} ---- {}ms, row:{}, dsName:{}", formatSQL(auditMessage.getFullSql()), auditMessage.getElapsedTime(),
                auditMessage.getQueryCount(),auditMessage.getDsName()));
    }

    public static String formatSQL(String sql) {

        return sql.replaceAll("\\s+", " ").replace("\\r", " ").replace("\\n", " ");
    }

}

  5. 启动服务，访问接口，验证日志。

    查看日志我们不难发现，配置文件中设置的 pool-name： HikariCPA 与HikariCPB 已经生效。

    java
    .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/

 :: Spring Boot ::                (v3.5.0)

...
    
  __  __       _           _   _       _____ _           
 |  \/  |_   _| |__   __ _| |_(_)___  |  ___| | _____  __
 | |\/| | | | | '_ \ / _` | __| / __| | |_  | |/ _ \ \/ /
 | |  | | |_| | |_) | (_| | |_| \__ \ |  _| | |  __/>  < 
 |_|  |_|\__, |_.__/ \__,_|\__|_|___/ |_|   |_|\___/_/\_\
         |___/ v1.10.9 https://mybatis-flex.com
// 2025-07-17 08:50:31.289 [main] 16300 [admin-service] INFO  c.z.hikari.HikariDataSource:109 - HikariCPA - Starting...
// 2025-07-17 08:50:31.375 [main] 16300 [admin-service] INFO  c.z.hikari.pool.HikariPool:575 - HikariCPA - Added connection com.mysql.cj.jdbc.ConnectionImpl@78e386a5
// 2025-07-17 08:50:31.375 [main] 16300 [admin-service] INFO  c.z.hikari.HikariDataSource:122 - HikariCPA - Start completed.
                       
...
                       
2025-07-17 08:50:35.123 [main] 16300 [admin-service] INFO  c.s.p.l.AppStartListener:26  -  ===================== app is running finish ... =====================
                                   __                _
                                  |  ]              (_)
 .--.   ____  ______  ,--.    .--.| |  _ .--..--.   __   _ .--.
( (`\] [_   ]|______|`'_\ : / /'`\' | [ `.-. .-. | [  | [ `.-. |
 `'.'.  .' /_        // | |,| \__/  |  | | | | | |  | |  | | | |
[\__) )[_____]       \'-;__/ '.__.;__][___||__||__][___][___||__]
------------------https://szadmin.cn  (v1.2.4-beta-SNAPSHOT)-------------------

...
   
//  2025-07-17 08:51:03.025 [tomcat-handler-5] 16300 [admin-service] INFO  c.z.hikari.HikariDataSource:109 - HikariCPB - Starting...
// 2025-07-17 08:51:03.033 [tomcat-handler-5] 16300 [admin-service] INFO  c.z.hikari.pool.HikariPool:575 - HikariCPB - Added connection com.mysql.cj.jdbc.ConnectionImpl@9ef1194
// 2025-07-17 08:51:03.033 [tomcat-handler-5] 16300 [admin-service] INFO  c.z.hikari.HikariDataSource:122 - HikariCPB - Start completed.

  6. 通过验证 SQL 日志，可以直观地查看实际生效的数据源。

    IMPORTANT

    以触发 sys_user表相关业务查询为例，观察 SQL 日志可发现，sys_user 表的相关语句的 dsName 为 preview，而其他非 sys_user 表的语句则显示 dsName 为 primary。

    java
    2025-07-17 09:06:31.621 [tomcat-handler-12] 16300 [admin-service] INFO  com.sz.logger.PrintSQL:29  - SELECT `sys_dept`.`id`, `sys_dept`.`name`, COUNT(CASE WHEN `sys_user`.`del_flag` = 'F' THEN `sys_user_dept`.`user_id` END) AS `total` FROM `sys_dept` LEFT JOIN `sys_user_dept` ON `sys_dept`.`id` = `sys_user_dept`.`dept_id` LEFT JOIN `sys_user` ON (`sys_user_dept`.`user_id` = `sys_user`.`id`) AND `sys_user`.`del_flag` = 'F' WHERE `sys_dept`.`del_flag` = 'F' GROUP BY `sys_dept`.`id`, `sys_dept`.`name` ORDER BY `sys_dept`.`deep` ASC, `sys_dept`.`sort` ASC ---- 1ms, row:19, dsName:primary
2025-07-17 09:06:31.623 [tomcat-handler-12] 16300 [admin-service] INFO  com.sz.logger.PrintSQL:29  - SELECT COUNT(*) FROM `sys_user` WHERE `del_flag` = 'F' ---- 0ms, row:1, dsName:primary
2025-07-17 09:06:31.625 [tomcat-handler-12] 16300 [admin-service] INFO  com.sz.logger.PrintSQL:29  - SELECT COUNT(DISTINCT su.id ) FROM sys_user su LEFT JOIN sys_user_dept sud ON su.id = sud.user_id LEFT JOIN sys_dept_closure sdc ON sud.dept_id = sdc.ancestor_id LEFT JOIN sys_dept sd ON sud.dept_id = sd.id WHERE su.del_flag = 'F' AND ( sdc.descendant_id IS NULL OR sd.del_flag = 'T' ) ---- 1ms, row:1, dsName:preview
2025-07-17 09:06:31.722 [tomcat-handler-14] 16300 [admin-service] INFO  com.sz.logger.PrintSQL:29  - SELECT count(0) FROM sys_user su WHERE su.del_flag = 'F' ---- 1ms, row:1, dsName:preview
2025-07-17 09:06:31.724 [tomcat-handler-14] 16300 [admin-service] INFO  com.sz.logger.PrintSQL:29  - SELECT * FROM sys_user su WHERE su.del_flag = 'F' ORDER BY su.create_time ASC LIMIT 10  ---- 1ms, row:10, dsName:preview
2025-07-17 09:06:31.727 [tomcat-handler-14] 16300 [admin-service] INFO  com.sz.logger.PrintSQL:29  - SELECT user_id, GROUP_CONCAT(dept_id ORDER BY dept_id) AS dept_ids, GROUP_CONCAT(CONCAT(dept_id, ':', dept_name) ORDER BY dept_id) AS dept_infos FROM (SELECT sud.user_id, sd.id AS dept_id, sd.name AS dept_name FROM sys_user_dept sud JOIN sys_dept sd ON sud.dept_id = sd.id WHERE sd.del_flag = "F" ) AS subquery WHERE user_id IN ( 1 , 2 , 4 , 5 , 6 , 7 , 8 , 9 , 11 , 15 ) GROUP BY user_id; ---- 1ms, row:3, dsName:preview
2025-07-17 09:06:31.729 [tomcat-handler-14] 16300 [admin-service] INFO  com.sz.logger.PrintSQL:29  - SELECT user_id, GROUP_CONCAT(role_id ORDER BY role_id) AS role_ids, GROUP_CONCAT(CONCAT( role_id, ':', role_name )) AS role_infos FROM ( SELECT sur.user_id, sur.role_id, sr.role_name FROM sys_user_role sur LEFT JOIN sys_role sr ON sur.role_id = sr.id WHERE sr.del_flag = "F" ) AS subquery WHERE user_id IN ( 1 , 2 , 4 , 5 , 6 , 7 , 8 , 9 , 11 , 15 ) GROUP BY user_id; ---- 1ms, row:5, dsName:preview