【SpringBoot】11 多数据源（MyBatis：dynamic-datasource）

大都督D

已于 2024-08-22 10:14:46 修改

阅读量927

点赞数 11

分类专栏： SpringBoot 文章标签： spring boot mybatis 后端 MySQL Oracle PostgreSQL dynamicDS

于 2024-08-22 10:12:40 首次发布

本文链接：https://blog.csdn.net/weixin_44088274/article/details/141419263

版权

SpringBoot 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

介绍

多数据源：指的是一个单一应用程序中涉及了两个及以上的数据库，这种配置允许应用程序根据业务需求灵活地管理和操作不同的数据库。

需求

一个应用服务中，连接多个数据库，有本地的也有远程的，有MysQL、Oracle、PostgreSQL（按需配置，可以配置同样的数据源，也可以配置不用的数据源）。

依赖

pom.xml

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>3.5.1</version>
</dependency>

效果图

项目启动时，载入的三个数据源。
在这里插入图片描述
用户接口（wx数据库）

角色接口（alipay数据库）

权限接口（oct数据库）

代码实现

配置文件中连接了三个数据库，wx数据库是MySQL，alipay数据库是Oracle，oct数据库是PostgreSQL。wx数据库是在本地，alipay数据库和oct数据库是在远程，可以按需配置。
注：项目启动程序就会去创建与所有配置数据库之间的连接，如果连不上则会启动失败。

配置

application.yml

server:
  port: 8888

spring:
  application:
    name: system
  thymeleaf:
    prefix: classpath:/templates/ #前缀，默认为classpath:/templates/
    suffix: .html #后缀，默认为.html
#  单个数据库
#  datasource:
#    driver-class-name: com.mysql.cj.jdbc.Driver
#    url: jdbc:mysql://localhost:3306/system?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
#    username: root
#    password: root
# 多个数据库
  datasource:
    dynamic:
#      primary: master #默认主库
      strict: true
      datasource:
        wx: #MySQL
          driver-class-name: com.mysql.jdbc.Driver
          url: jdbc:mysql://localhost:3306/wx?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
          username: root
          password: root
        alipay: #Oracle
          driver-class-name: oracle.jdbc.OracleDriver
          url: jdbc:oracle:thin:@remote:1521:sid #remote是对应机器服务器的远程地址，sid是具体的数据库实例
#          driver-class-name: com.mysql.jdbc.Driver
#          url: jdbc:mysql://localhost:3306/alipay?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
          username: root
          password: root
        oct: #PostgreSQL
          driver-class-name: org.postgresql.Driver
          url: jdbc:postgresql://remote:5432/postgres #remote是对应机器服务器的远程地址
#          driver-class-name: com.mysql.jdbc.Driver
#          url: jdbc:mysql://localhost:3306/oct?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8
          username: root
          password: root

  mail:
    #    host: smtp.163.com
    host: smtp.qq.com
    port: 465 #587
    #    username: xxx@163.com
    username: xxx@qq.com
    password: xxx #授权码
    default-encoding: UTF-8
    properties:
      mail:
        debug: true
        smtp:
          socketFactory:
            class: javax.net.ssl.SSLSocketFactory
          ssl:
            enable: true

schedule:
  cron:  0/5 * * * * ?  #5s执行一次
  wxFlag: true
  alipayFlag: false
  octFlag: false

logging:
  config: classpath:log4j2.yml
  level:
    com.lm.system.mapper: debug

代码

所有的User相关的方法默认读取wx的MySQL数据库，只要在类上加上 @DS(“wx”) 注解。如果是接口中多个方法用到多个不同的数据源时，可在方法上加@DS(“wx”)注解，该注解是按就近原则进行加载。

User（wxDB）

UserMapper.java

@DS("wx")
public interface UserMapper {}

Role（alipayDB）

Role.java

package com.lm.system.common;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

/**
 * @author DUHAOLIN
 * @date 2024/8/21
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Role {

    @TableId(value = "role_id", type = IdType.INPUT)
    private Integer roleId; //自增长
    private String roleName;
    private String description;
    private Integer status; //可用状态，0不可用，1可用
    private Date createTime;
    private Date updateTime;

}

RoleMapper.java

package com.lm.system.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.lm.system.common.Role;

import java.util.List;

/**
 * @author DUHAOLIN
 * @date 2024/8/21
 */
@DS("alipay")
public interface RoleMapper {

    List<Role> queryRoles();

}

RoleMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lm.system.mapper.RoleMapper">

    <resultMap id="beans" type="com.lm.system.common.Role">
        <id property="roleId" column="role_id" jdbcType="INTEGER" />
        <result property="roleName" column="role_name" jdbcType="VARCHAR" />
        <result property="status" column="status" jdbcType="INTEGER" />
        <result property="description" column="description" jdbcType="VARCHAR" />
        <result property="createTime" column="create_time" jdbcType="DATE" />
        <result property="updateTime" column="update_time" jdbcType="DATE" />
    </resultMap>

    <select id="queryRoles" resultMap="beans">
        SELECT * FROM T_ROLE
    </select>


</mapper>

RoleController.java

package com.lm.system.controller;

import com.lm.system.common.ResultBody;
import com.lm.system.common.Role;
import com.lm.system.mapper.RoleMapper;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.util.List;

/**
 * @author DUHAOLIN
 * @date 2024/8/21
 */
@RestController
@Api(tags = "角色接口")
@RequestMapping("role")
public class RoleController {

    @Resource
    private RoleMapper roleMapper;

    @GetMapping("query")
    @ApiOperation("获取角色信息")
    public String queryRoles() {
        List<Role> roles = roleMapper.queryRoles();
        return ResultBody
                .build(roles == null ? HttpStatus.NO_CONTENT : HttpStatus.OK)
                .setData(roles)
                .setCount(roles == null ? 0 : 1)
                .getReturn();
    }

}

Permission（octDB）

Permission.java

package com.lm.system.common;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

/**
 * @author DUHAOLIN
 * @date 2024/8/21
 */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Permission {

    @TableId(value = "permission_id", type = IdType.INPUT)
    private Integer permissionId; //自增长
    private String permissionName;
    private String description;
    private Date createTime;
    private Date updateTime;

}

PermissionMapper.java

package com.lm.system.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.lm.system.common.Permission;

import java.util.List;

/**
 * @author DUHAOLIN
 * @date 2024/8/21
 */
public interface PermissionMapper {

    @DS("oct")
    List<Permission> queryPermissions();

}

Permission.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lm.system.mapper.PermissionMapper">

    <resultMap id="beans" type="com.lm.system.common.Permission">
        <id property="permissionId" column="permission_id" jdbcType="INTEGER" />
        <result property="permissionName" column="permission_name" jdbcType="VARCHAR" />
        <result property="description" column="description" jdbcType="VARCHAR" />
        <result property="createTime" column="create_time" jdbcType="DATE" />
        <result property="updateTime" column="update_time" jdbcType="DATE" />
    </resultMap>

    <select id="queryPermissions" resultMap="beans">
        SELECT * FROM T_PERMISSION
    </select>

</mapper>

PermissionController.java

package com.lm.system.controller;

import com.lm.system.common.Permission;
import com.lm.system.common.ResultBody;
import com.lm.system.mapper.PermissionMapper;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;
import java.util.List;

/**
 * @author DUHAOLIN
 * @date 2024/8/21
 */
@RestController
@Api(tags = "权限接口")
@RequestMapping("permission")
public class PermissionController {

    @Resource
    private PermissionMapper permissionMapper;

    @GetMapping("query")
    @ApiOperation("获取权限信息")
    public String queryPermission() {
        List<Permission> permissions = permissionMapper.queryPermissions();
        return ResultBody
                .build(permissions == null ? HttpStatus.NO_CONTENT : HttpStatus.OK)
                .setData(permissions)
                .setCount(permissions == null ? 0 : 1)
                .getReturn();
    }

}

SQL

t_user.sql

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50734
 Source Host           : localhost:3306
 Source Schema         : wx

 Target Server Type    : MySQL
 Target Server Version : 50734
 File Encoding         : 65001

 Date: 21/08/2024 16:49:21
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_user
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `gender` varchar(2) NOT NULL COMMENT ,
  `deleted` tinyint(1) NOT NULL COMMENT '0未删除，1已删除',
  `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
  `update_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES (1, 'Tom', 18, '男', 0, '2024-08-21 16:47:45', '2024-08-21 16:47:45');
INSERT INTO `t_user` VALUES (2, 'Joe', 20, '女', 0, '2024-08-21 16:47:58', '2024-08-21 16:47:58');
INSERT INTO `t_user` VALUES (3, 'Jim', 33, '女', 0, '2024-08-21 16:48:12', '2024-08-21 16:48:12');

SET FOREIGN_KEY_CHECKS = 1;

t_role.sql

--------------------------------------------------------
--  文件已创建 - 星期三-八月-21-2024   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for Table T_ROLE
--------------------------------------------------------

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for T_ROLE
-- ----------------------------
DROP TABLE IF EXISTS `T_ROLE`;
CREATE TABLE `T_ROLE`  (
  `ROLE_ID` int(11) NOT NULL AUTO_INCREMENT,
  `ROLE_NAME` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `DESCRIPTION` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `STATUS` tinyint(1) NOT NULL COMMENT '可用状态，0不可用，1可用',
  `CREATE_TIME` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
  `UPDATE_TIME` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0),
  PRIMARY KEY (`ROLE_ID`) USING BTREE
);

-- ----------------------------
-- Records of T_ROLE
-- ----------------------------
INSERT INTO `T_ROLE` VALUES (-1, '管理员', '拥有所有权限', 1, '2024-08-21 17:04:01', '2024-08-21 17:04:01');
INSERT INTO `T_ROLE` VALUES (1, '财务', '拥有查看和操作权限', 1, '2024-08-21 17:04:14', '2024-08-21 17:04:14');
INSERT INTO `T_ROLE` VALUES (2, '观察员', '拥有查看的账号', 1, '2024-08-21 17:05:19', '2024-08-21 17:05:19');

SET FOREIGN_KEY_CHECKS = 1;

t_permission.sql

/*
 Navicat Premium Data Transfer

 Source Server         : oct
 Source Server Type    : PostgreSQL
 Source Server Version : 120001
 Source Host           : remote:port
 Source Catalog        : oct
 Source Schema         : public

 Target Server Type    : PostgreSQL
 Target Server Version : 120001
 File Encoding         : 65001

 Date: 21/08/2024 16:53:21
*/


-- ----------------------------
-- Table structure for t_permission
-- ----------------------------
DROP TABLE IF EXISTS "public"."t_permission";
CREATE TABLE "public"."t_permission" (
  "permission_id" varchar(30) COLLATE "pg_catalog"."default" NOT NULL,
  "permission_name" varchar(30) COLLATE "pg_catalog"."default" NOT NULL,
  "description" varchar(150) COLLATE "pg_catalog"."default",
  "create_time" timestamp(0) COLLATE "pg_catalog"."default" NOT NULL,
  "update_time" timestamp(0) COLLATE "pg_catalog"."default" NOT NULL
)
;

-- ----------------------------
-- Records of oct
-- ----------------------------
INSERT INTO "public"."t_permission" VALUES (1, '用户管理', '管理用户账号', '2024-08-22 09:35:18', '2024-08-22 09:35:18');
INSERT INTO "public"."t_permission" VALUES (2, '角色管理', '管理用户角色', '2024-08-22 09:35:50',    '2024-08-22 09:35:50');
INSERT INTO "public"."t_permission" VALUES (3, '权限管理', '管理角色权限', '2024-08-22 09:36:05',    '2024-08-22 09:36:05');

-- ----------------------------
-- Primary Key structure for table t_permission
-- ----------------------------
ALTER TABLE "public"."t_permission" ADD CONSTRAINT "t_permission_pk" PRIMARY KEY ("id", "option");