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

介绍

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

需求

一个应用服务中，连接多个数据库，有本地的也有远程的，有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: 8888spring:application:name: systemthymeleaf: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: truedatasource:wx: #MySQLdriver-class-name: com.mysql.jdbc.Driverurl: jdbc:mysql://localhost:3306/wx?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8username: rootpassword: rootalipay: #Oracledriver-class-name: oracle.jdbc.OracleDriverurl: 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%2B8username: rootpassword: rootoct: #PostgreSQLdriver-class-name: org.postgresql.Driverurl: 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%2B8username: rootpassword: rootmail:#    host: smtp.163.comhost: smtp.qq.comport: 465 #587#    username: xxx@163.comusername: xxx@qq.compassword: xxx #授权码default-encoding: UTF-8properties:mail:debug: truesmtp:socketFactory:class: javax.net.ssl.SSLSocketFactoryssl:enable: trueschedule:cron:  0/5 * * * * ?  #5s执行一次wxFlag: truealipayFlag: falseoctFlag: falselogging:config: classpath:log4j2.ymllevel: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 {@Resourceprivate 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 {@Resourceprivate 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 TransferSource Server         : localhostSource Server Type    : MySQLSource Server Version : 50734Source Host           : localhost:3306Source Schema         : wxTarget Server Type    : MySQLTarget Server Version : 50734File Encoding         : 65001Date: 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 TransferSource Server         : octSource Server Type    : PostgreSQLSource Server Version : 120001Source Host           : remote:portSource Catalog        : octSource Schema         : publicTarget Server Type    : PostgreSQLTarget Server Version : 120001File Encoding         : 65001Date: 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");