概述
该项目主要描述了Mybatis Plus多数据源的配置和使用,其中详细记录的配置过程,整个过程从数据库表的创建到数据初始化都有详细的记录说明。
另外,如果存在同一个表在多个数据源中都存在的情况,有一些注意事项,下面看文章通过例子说明。
详细步骤
by-mybatis-plus项目一
不同的表在不同的数据源中,不存在同一个表在多个数据源的情况。
数据库脚本
- 在数据库1:database1中新建数据库表,并插入初始化数据
CREATE DATABASE `datasource1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
use `datasource1`;
CREATE TABLE `user_info` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户名',
`user_sex` TINYINT(2) NOT NULL COMMENT '用户性别(0 女性 1男性 2其他)',
`user_age` INT NOT NULL COMMENT '用户年龄',
`user_address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户地址',
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期',
`mtime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户信息表';
INSERT INTO `datasource1`.`user_info` (`user_name`, `user_sex`, `user_age`, `user_address`) VALUES ('张三', 1, 18, '北京');
INSERT INTO `datasource1`.`user_info` (`user_name`, `user_sex`, `user_age`, `user_address`) VALUES ('张颜', 0, 18, '乌鲁木齐');
INSERT INTO `datasource1`.`user_info` (`user_name`, `user_sex`, `user_age`, `user_address`) VALUES ('李四', 1, 12, '上海');
INSERT INTO `datasource1`.`user_info` (`user_name`, `user_sex`, `user_age`, `user_address`) VALUES ('王五', 1, 18, '北京');
INSERT INTO `datasource1`.`user_info` (`user_name`, `user_sex`, `user_age`, `user_address`) VALUES ('王武', 1, 23, '北京');
INSERT INTO `datasource1`.`user_info` (`user_name`, `user_sex`, `user_age`, `user_address`) VALUES ('王媛', 0, 43, '成都');
INSERT INTO `datasource1`.`user_info` (`user_name`, `user_sex`, `user_age`, `user_address`) VALUES ('李康', 1, 36, '广州');
INSERT INTO `datasource1`.`user_info` (`user_name`, `user_sex`, `user_age`, `user_address`) VALUES ('王梅', 0, 16, '合肥');
INSERT INTO `datasource1`.`user_info` (`user_name`, `user_sex`, `user_age`, `user_address`) VALUES ('李雪华', 0, 23, '东京');
INSERT INTO `datasource1`.`user_info` (`user_name`, `user_sex`, `user_age`, `user_address`) VALUES ('张熏然', 0, 19, '纽约');
INSERT INTO `datasource1`.`user_info` (`user_name`, `user_sex`, `user_age`, `user_address`) VALUES ('何莉莉', 0, 32, '深圳');
INSERT INTO `datasource1`.`user_info` (`user_name`, `user_sex`, `user_age`, `user_address`) VALUES ('李茉', 0, 23, '东莞');
- 在数据库2:database2中新建数据库表,并插入初始化数据
CREATE DATABASE `datasource2` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
use `datasource2`;
CREATE TABLE `user_login_log` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`user_id` bigint NOT NULL COMMENT '登录用户id',
`user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户名',
`activity_type` varchar(255) NOT NULL COMMENT '活动时间:in登入,out登出',
`activity_trigger_time` timestamp NOT NULL COMMENT '不懂触发时间(登录或者登出时间)',
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期',
`mtime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户登录信息记录表';
INSERT INTO `datasource2`.`user_login_log` (`user_id`, `user_name`, `activity_type`, `activity_trigger_time`) VALUES (1, '张三', 'in', '2024-08-06 16:38:43');
INSERT INTO `datasource2`.`user_login_log` (`user_id`, `user_name`, `activity_type`, `activity_trigger_time`) VALUES (1, '张三', 'out', '2024-08-06 16:39:43');
INSERT INTO `datasource2`.`user_login_log` (`user_id`, `user_name`, `activity_type`, `activity_trigger_time`) VALUES (1, '张三', 'in', '2024-08-06 16:48:43');
INSERT INTO `datasource2`.`user_login_log` (`user_id`, `user_name`, `activity_type`, `activity_trigger_time`) VALUES (1, '张三', 'out', '2024-08-06 16:59:43');
INSERT INTO `datasource2`.`user_login_log` (`user_id`, `user_name`, `activity_type`, `activity_trigger_time`) VALUES (2, '李四', 'in', '2024-08-06 16:38:43');
INSERT INTO `datasource2`.`user_login_log` (`user_id`, `user_name`, `activity_type`, `activity_trigger_time`) VALUES (2, '李四', 'out', '2024-08-06 16:39:43');
INSERT INTO `datasource2`.`user_login_log` (`user_id`, `user_name`, `activity_type`, `activity_trigger_time`) VALUES (2, '李四', 'in', '2024-08-06 16:48:43');
INSERT INTO `datasource2`.`user_login_log` (`user_id`, `user_name`, `activity_type`, `activity_trigger_time`) VALUES (2, '李四', 'out', '2024-08-06 16:59:43');
INSERT INTO `datasource2`.`user_login_log` (`user_id`, `user_name`, `activity_type`, `activity_trigger_time`) VALUES (3, '王五', 'in', '2024-08-06 16:38:43');
INSERT INTO `datasource2`.`user_login_log` (`user_id`, `user_name`, `activity_type`, `activity_trigger_time`) VALUES (3, '王五', 'out', '2024-08-06 16:39:43');
INSERT INTO `datasource2`.`user_login_log` (`user_id`, `user_name`, `activity_type`, `activity_trigger_time`) VALUES (3, '王五', 'in', '2024-08-06 16:48:43');
INSERT INTO `datasource2`.`user_login_log` (`user_id`, `user_name`, `activity_type`, `activity_trigger_time`) VALUES (3, '王五', 'out', '2024-08-06 16:59:43');
INSERT INTO `datasource2`.`user_login_log` (`user_id`, `user_name`, `activity_type`, `activity_trigger_time`) VALUES (3, '王五', 'in', '2024-08-06 17:38:43');
INSERT INTO `datasource2`.`user_login_log` (`user_id`, `user_name`, `activity_type`, `activity_trigger_time`) VALUES (3, '王五', 'out', '2024-08-06 17:39:43');
INSERT INTO `datasource2`.`user_login_log` (`user_id`, `user_name`, `activity_type`, `activity_trigger_time`) VALUES (3, '王五', 'in', '2024-08-06 18:48:43');
INSERT INTO `datasource2`.`user_login_log` (`user_id`, `user_name`, `activity_type`, `activity_trigger_time`) VALUES (3, '王五', 'out', '2024-08-06 18:59:43');
添加maven依赖
<dependencies>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
</dependencies>
编写配置文件
spring:
datasource:
dynamic:
primary: datasource1 #设置默认的数据源或者数据源组,默认值即为datasource1
strict: false #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
datasource:
datasource1:
url: jdbc:mysql://127.0.0.1:33060/datasource1?characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&autoReconnect=true&rewriteBatchedStatements=true&allowMultiQueries=true&useSSL=false
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
datasource2:
url: jdbc:mysql://127.0.0.1:33060/datasource2?characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&autoReconnect=true&rewriteBatchedStatements=true&allowMultiQueries=true&useSSL=false
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
在 service类 和 mapper类 上添加数据源注解
- 数据源1:datasource1
@Mapper
@DS("datasource1")
public interface UserInfoMapper extends BaseMapper<UserInfo> {
}
@Service
@DS("datasource1")
public class UserInfoServiceImpl extends ServiceImpl<UserInfoMapper, UserInfo> implements UserInfoService {
}
- 数据源2:datasource2
@Mapper
@DS("datasource2")
public interface UserLoginLogMapper extends BaseMapper<UserLoginLog> {
}
@Service
@DS("datasource2")
public class UserLoginLogServiceImpl extends ServiceImpl<UserLoginLogMapper, UserLoginLog> implements UserLoginLogService {
}
编写业务代码
@Controller
public class BasicController {
@Resource
private UserInfoService userInfoService;
@Resource
private UserLoginLogService userLoginLogService;
private static final String LOGIN = "in";
// http://127.0.0.1:8080/get/user/login/info?id=1
@RequestMapping("/get/user/login/info")
@ResponseBody
public String getUserLoginInfo(@RequestParam(name = "id", required = true) String id) {
UserInfo one = userInfoService.lambdaQuery().eq(UserInfo::getId, id).one();
System.out.println("查询到id=" + id + "的登录用户信息:" + one);
List<UserLoginLog> list = userLoginLogService.lambdaQuery()
.eq(Objects.nonNull(one), UserLoginLog::getUserId, one.getId())
.eq(UserLoginLog::getActivityType, LOGIN)
.list();
System.out.println("查询到id=" + id + "的用户一共登录了:" + list.size() + "次");
return "用户id= " + id + ",名叫:" + one.getUserName() + ",一共登录了系统" + list.size() + "次";
}
}
启动服务、通过浏览器调用接口
请求 http://127.0.0.1:8080/get/user/login/info?id=1 ,查看执行结果
by-mybatis-plus项目二
某一个表存在于多个数据源中,即本项目中user_login_log表既在datasource1中又在datasource2中,UserLoginLogService在查询的时候既需要从datasource1又需要从datasource2中查询数据
数据库脚本
在数据库1中补充user_login_log表,并初始化数据
use `datasource1`;
CREATE TABLE `user_login_log` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
`user_id` bigint NOT NULL COMMENT '登录用户id',
`user_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户名',
`activity_type` varchar(255) NOT NULL COMMENT '活动时间:in登入,out登出',
`activity_trigger_time` timestamp NOT NULL COMMENT '不懂触发时间(登录或者登出时间)',
`ctime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期',
`mtime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户登录信息记录表';
INSERT INTO `datasource1`.`user_login_log` (`user_id`, `user_name`, `activity_type`, `activity_trigger_time`) VALUES (1, '张三', 'in', '2024-08-06 16:38:43');
INSERT INTO `datasource1`.`user_login_log` (`user_id`, `user_name`, `activity_type`, `activity_trigger_time`) VALUES (1, '张三', 'out', '2024-08-06 16:59:43');
INSERT INTO `datasource1`.`user_login_log` (`user_id`, `user_name`, `activity_type`, `activity_trigger_time`) VALUES (2, '李四', 'in', '2024-08-06 16:38:43');
INSERT INTO `datasource1`.`user_login_log` (`user_id`, `user_name`, `activity_type`, `activity_trigger_time`) VALUES (2, '李四', 'out', '2024-08-06 16:39:43');
在多数据源库的表user_login_log对应的mapper.java上取消@DS(“datasource2”)注解
@Mapper
// @DS("datasource2")
public interface UserLoginLogMapper extends BaseMapper<UserLoginLog> {
}
在多数据源的表user_login_log对应的service中添加方法,方法需要指定使用的数据源
@Service
@DS("datasource2")
public class UserLoginLogServiceImpl extends ServiceImpl<UserLoginLogMapper, UserLoginLog> implements UserLoginLogService {
@DS("datasource2")
@Override
public List<UserLoginLog> getDataSource(long userId, String activityType) {
return lambdaQuery().eq(UserLoginLog::getUserId, userId).eq(UserLoginLog::getActivityType, activityType).list();
}
@DS("datasource1")
@Override
public List<UserLoginLog> getAntherDataSource(long userId, String activityType) {
return lambdaQuery().eq(UserLoginLog::getUserId, userId).eq(UserLoginLog::getActivityType, activityType).list();
}
}
注:因为uer_login_log该表在datasource1和datasource2数据源中都存在,所以在mapper.java中需要取消@DS注解
- 那么在service的方法中就需要指定数据源
- 在service的上层调用方如controller中必须调用service的方法,不能跳过service类中方法而直接在controller中通过
userLoginLogService.lambdaQuery()的方式调用,这样调用系统会直接查询yml配置的"primary: datasource1"的默认数据源
编写controller类测试
@Controller
public class BasicController {
@Resource
private UserInfoService userInfoService;
@Resource
private UserLoginLogService userLoginLogService;
private static final String LOGIN = "in";
/**
* 说明: 该方法中通过mybatis plus的多数据源调用两个库的数据查询
* userInfoService使用数据源datasource1
* userLoginLogService使用数据源datasource2
* 测试请求地址: <a href="http://127.0.0.1:8080/get/user/login/info?id=1">测试请求地址</a>
*
* @param id 用户id
* @return 查询结果
*/
@RequestMapping("/get/user/login/info")
@ResponseBody
public String getUserLoginInfo(@RequestParam(name = "id") Long id) {
UserInfo one = userInfoService.lambdaQuery().eq(UserInfo::getId, id).one();
System.out.println("查询到id=" + id + "的登录用户信息:" + one);
/*
* 如果在userLoginLogService中有方法指定了@DS其他数据源,那么在上层方法中不能跳过userLoginLogService的方法
* 直接使用lambdaQuery(),只能在userLoginLogService中使用lambdaQuery(),因为如果直接使用lambdaQuery()的话,
* 因为mapper.java没有配置数据源(原因见下个方法注释),会直接使用yml配置的primary: datasource1数据源
*/
List<UserLoginLog> list = userLoginLogService.lambdaQuery()
.eq(Objects.nonNull(one), UserLoginLog::getUserId, one.getId())
.eq(UserLoginLog::getActivityType, LOGIN)
.list();
System.out.println("lambdaQuery()方式查询到id=" + id + "的用户一共登录了:" + list.size() + "次");
List<UserLoginLog> dataSource = userLoginLogService.getDataSource(one.getId(), LOGIN);
System.out.println("调用service中方法的方式查询到id=" + id + "的用户一共登录了:" + dataSource.size() + "次");
return "用户id= " + id + ",名叫:" + one.getUserName() + ",一共登录了系统" + dataSource.size() + "次";
}
/**
* 说明:该方法中通过mybatis plus的多数据源调用两个库的数据查询,其中且有一个库在两边都存在并进行查询
* userInfoService 配置数据源datasource1
* userLoginLogService 默认配置数据源datasource2,但是在某些方法上使用数据源datasource1
* 测试请求地址: <a href="http://127.0.0.1:8080/get/different/in/same/service?id=1">测试请求地址</a>
*
* @param id 用户id
* @return 查询结果
*/
@RequestMapping("/get/different/in/same/service")
@ResponseBody
public String getDifferentInInSameService(@RequestParam(name = "id") Long id) {
UserInfo one = userInfoService.lambdaQuery().eq(UserInfo::getId, id).one();
System.out.println("查询到id=" + id + "的登录用户信息:" + one);
/*
* 重要!!!
* 说明,如果在某个service中有方法使用其他数据源,那么注意:
* 1、在mapper.java中不能使用@DS注解,如果使用,所有的请求都会走mapper.java中指定的数据源
* 2、在service中,需要走默认数据源的方法,需要手动编写方法,不能在层如controller中直接通过lambdaQuery()方式调用,否则会和另外指定数据源的方法走一个数据源去
*/
// List<UserLoginLog> list = userLoginLogService.lambdaQuery()
// .eq(Objects.nonNull(one), UserLoginLog::getUserId, one.getId())
// .eq(UserLoginLog::getActivityType, LOGIN)
// .list();
List<UserLoginLog> list = userLoginLogService.getDataSource(id, LOGIN);
System.out.println("database2登录:" + list.size() + "次");
List<UserLoginLog> antherDataSource = userLoginLogService.getAntherDataSource(id, LOGIN);
System.out.println("database1登录:" + antherDataSource.size() + "次");
int size = list.size() + antherDataSource.size();
System.out.println("查询到id=" + id + "的用户一共登录了:" + size + "次");
return "用户id= " + id + ",名叫:" + one.getUserName() + ",一共登录了系统" + size + "次";
}
}
启动服务、进行测试
请求 http://127.0.0.1:8080/get/user/login/info?id=1 ,查看执行结果
idea后台打印日志:
请求 http://127.0.0.1:8080/get/different/in/same/service?id=1 ,查看执行结果
idea后台打印日志: