Spring Boot + Mybatis-Plus实现多数据源的方法

前段时间写了一篇基于mybatis实现的多数据源博客。感觉不是很好，这次打算加入git，来搭建一个基于Mybatis-Plus的多数据源项目
Mybatis-Plus就是香

前言：该项目分为master数据源与local数据源。假定master数据源为线上数据库，local为本地数据库。后续我们将通过xxl-job的方式，将线上(master)中的数据同步到本地(local)中

项目git地址 抽时间把项目提交到git仓库，方便大家直接克隆
sql文件已置于项目中，数据库使用的mysql

创建项目

我这里使用的idea，正常创建spring boot项目就行了。

sql

-- 创建master数据库
CREATE DATABASE db_master;

USE db_master;
-- 用户表
CREATE TABLE users(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键id',
username VARCHAR(20) NOT NULL COMMENT '用户名',
pwd VARCHAR(50) NOT NULL COMMENT '密码',
phone CHAR(11) NOT NULL COMMENT '手机号',
email VARCHAR(50) COMMENT '邮箱',
gender CHAR(1) COMMENT '性别 1-->男 0-->女',
age INT COMMENT '年龄',
created_time TIMESTAMP NULL DEFAULT NULL COMMENT '创建时间',
updated_time TIMESTAMP NULL DEFAULT NULL COMMENT '修改时间',
deleted_time TIMESTAMP NULL DEFAULT NULL COMMENT '删除时间',
type INT DEFAULT 1 COMMENT '状态 1-->启用 2-->禁用 3-->软删除'
)ENGINE=INNODB;

INSERT INTO users(username,pwd,phone,email,gender,age) VALUES('supperAdmin','admin123','13000000000','super@admin.com',1,20);
INSERT INTO users(username,pwd,phone,email,gender,age) VALUES('admin','admin','13200840033','admin@163.com',0,14);
INSERT INTO users(username,pwd,phone,email,gender,age) VALUES('wanggang','wanggang','13880443322','wang@gang.cn',1,36);
INSERT INTO users(username,pwd,phone,email,gender,age) VALUES('xiaobao','xiaobao','18736450102','xiao@bao.com',0,22);
INSERT INTO users(username,pwd,phone,email,gender,age) VALUES('zhaoxing','zhaoxing','18966004400','zhao@xing.com',1,29);
INSERT INTO users(username,pwd,phone,email,gender,age) VALUES('chenyun','chenyun','13987613540','chen@yun.com',1,25);
INSERT INTO users(username,pwd,phone,email,gender,age) VALUES('ouyangruixue','ouyangruixue','15344773322','ouyang@163.com',0,24);

-- 创建local数据库
CREATE DATABASE db_local;

USE db_local;
-- 本地库用户表
CREATE TABLE users(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键id',
username VARCHAR(20) NOT NULL COMMENT '用户名',
pwd VARCHAR(50) NOT NULL COMMENT '密码',
phone CHAR(11) NOT NULL COMMENT '手机号',
email VARCHAR(50) COMMENT '邮箱',
gender CHAR(1) COMMENT '性别 1-->男 0-->女',
age INT COMMENT '年龄',
created_time TIMESTAMP NULL DEFAULT NULL COMMENT '创建时间',
updated_time TIMESTAMP NULL DEFAULT NULL COMMENT '修改时间',
deleted_time TIMESTAMP NULL DEFAULT NULL COMMENT '删除时间',
type INT DEFAULT 1 COMMENT '状态 1-->启用 2-->禁用 3-->软删除'
)ENGINE=INNODB;
-- 本地库测试表
CREATE TABLE local_test(
id INT PRIMARY KEY AUTO_INCREMENT,
str VARCHAR(20)
)ENGINE=INNODB;
INSERT INTO local_test(str) VALUES ('Hello');
INSERT INTO local_test(str) VALUES ('World');
INSERT INTO local_test(str) VALUES ('Mysql');
INSERT INTO local_test(str) VALUES ('^.^');

pom文件

目前只引入了lombok、mybatis-plus、mysql相关的依赖

<dependency>
     <groupId>org.springframework.boot</groupId>
     <artifactId>spring-boot-starter-web</artifactId>
   </dependency>
   <dependency>
     <groupId>org.springframework.boot</groupId>
     <artifactId>spring-boot-starter-test</artifactId>
     <scope>test</scope>
   </dependency>
<!-- lombok -->
   <dependency>
     <groupId>org.projectlombok</groupId>
     <artifactId>lombok</artifactId>
     <optional>true</optional>
   </dependency>
   <!-- Mybatis-Plus -->
   <dependency>
     <groupId>com.baomidou</groupId>
     <artifactId>mybatis-plus-boot-starter</artifactId>
     <version>3.2.0</version>
   </dependency>
   <dependency>
     <groupId>mysql</groupId>
     <artifactId>mysql-connector-java</artifactId>
     <scope>runtime</scope>
   </dependency>

application.yml

properties删除，yml创建。其实无所谓yml还是properties

server:
port: 8001

spring:
datasource:
 master:
  url: jdbc:mysql://localhost:3306/db_master?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&allowMultiQueries=true
  username: root
  password: 123456
  driver-class-name: com.mysql.cj.jdbc.Driver

local:
  url: jdbc:mysql://localhost:3306/db_local?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&allowMultiQueries=true
  username: root
  password: 123456
  driver-class-name: com.mysql.cj.jdbc.Driver

mybatis-plus:
mapper-locations: classpath:mappers/*/*Mapper.xml
#实体扫描，多个package用逗号或者分号分隔
typeAliasesPackage: com.niuniu.sys.module
check-config-location: true
configuration:
 #是否开启自动驼峰命名规则（camel case）映射
 map-underscore-to-camel-case: true
 #全局地开启或关闭配置文件中的所有映射器已经配置的任何缓存
 cache-enabled: false
 call-setters-on-nulls: true
 #配置JdbcTypeForNull, oracle数据库必须配置
 jdbc-type-for-null: 'null'
 #MyBatis 自动映射时未知列或未知属性处理策略 NONE：不做任何处理 (默认值), WARNING：以日志的形式打印相关警告信息, FAILING：当作映射失败处理，并抛出异常和详细信息
 auto-mapping-unknown-column-behavior: warning
global-config:
 banner: false
 db-config:
  #主键类型 0:"数据库ID自增", 1:"未设置主键类型",2:"用户输入ID (该类型可以通过自己注册自动填充插件进行填充)", 3:"全局唯一ID (idWorker), 4:全局唯一ID (UUID), 5:字符串全局唯一ID (idWorker 的字符串表示)";
  id-type: auto
  #字段验证策略 IGNORED:"忽略判断", NOT_NULL:"非NULL判断", NOT_EMPTY:"非空判断", DEFAULT 默认的,一般只用于注解里(1. 在全局里代表 NOT_NULL,2. 在注解里代表 跟随全局)
  field-strategy: NOT_EMPTY
  #数据库大写下划线转换
  capital-mode: true
  #逻辑删除值
  logic-delete-value: 0
  #逻辑未删除值
  logic-not-delete-value: 1

pagehelper:            #pagehelper分页插件
helperDialect: mysql     #设置数据库方言
reasonable: true
supportMethodsArguments: true
params: count=countSql

目录结构

由于该项目分为两个数据源。在各个分层中，你将分别见到master、local。

备注：由于users表在两个数据源中完全相同，将Users实体置于model层公用

关键代码

在config包中，我们需要对master、local的数据源进行配置，使其可以通过不同的请求，自动切换数据源

另外 由于该项目使用mybatis-plus，在创建SqlSessionFactory的时候，请使用MybatisSqlSessionFactoryBean进行bean对象的创建

local数据源

package com.demo.config;

import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.demo.dao.local",
   sqlSessionFactoryRef = "localSqlSessionFactory")
public class LocalDataSourceConfig {
 @Value("${spring.datasource.local.driver-class-name}")
 private String driverClassName;
 @Value("${spring.datasource.local.url}")
 private String url;
 @Value("${spring.datasource.local.username}")
 private String username;
 @Value("${spring.datasource.local.password}")
 private String password;

@Bean(name = "localDataSource")
 public DataSource localDataSource() {
   HikariDataSource dataSource = new HikariDataSource();
   dataSource.setUsername(username);
   dataSource.setPassword(password);
   dataSource.setJdbcUrl(url);
   dataSource.setDriverClassName(driverClassName);
   dataSource.setMaximumPoolSize(10);
   dataSource.setMinimumIdle(5);
   dataSource.setPoolName("localDataSourcePool");
   return dataSource;
 }

/**
  * local数据源
  */
 @Bean(name = "localSqlSessionFactory")
 public SqlSessionFactory sqlSessionFactory(@Qualifier("localDataSource") DataSource dataSource) throws Exception {
   MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
   bean.setDataSource(dataSource);
   // 设置Mybatis全局配置路径
   bean.setConfigLocation(new ClassPathResource("mybatis-config.xml"));
   bean.setMapperLocations(
       new PathMatchingResourcePatternResolver().getResources("classpath*:mappers/local/*.xml"));
   return bean.getObject();
 }

@Bean(name = "localTransactionManager")
 public DataSourceTransactionManager transactionManager(@Qualifier("localDataSource") DataSource dataSource) {
   return new DataSourceTransactionManager(dataSource);
 }

@Bean(name = "localSqlSessionTemplate")
 public SqlSessionTemplate testSqlSessionTemplate(
     @Qualifier("localSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
   return new SqlSessionTemplate(sqlSessionFactory);
 }
}

master数据源

package com.demo.config;

import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.demo.dao.master",
   sqlSessionFactoryRef = "masterSqlSessionFactory")
public class MasterDataSourceConfig {
 @Value("${spring.datasource.master.driver-class-name}")
 private String driverClassName;
 @Value("${spring.datasource.master.url}")
 private String url;
 @Value("${spring.datasource.master.username}")
 private String username;
 @Value("${spring.datasource.master.password}")
 private String password;

@Bean(name = "masterDataSource")
 @Primary
 public DataSource masterDataSource() {
   HikariDataSource dataSource = new HikariDataSource();
   dataSource.setUsername(username);
   dataSource.setPassword(password);
   dataSource.setJdbcUrl(url);
   dataSource.setDriverClassName(driverClassName);
   dataSource.setMaximumPoolSize(10);
   dataSource.setMinimumIdle(5);
   dataSource.setPoolName("masterDataSource");
   return dataSource;
 }

/**
  * master数据源
  */
 @Bean(name = "masterSqlSessionFactory")
 @Primary
 public SqlSessionFactory sqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {
   MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
   bean.setDataSource(dataSource);
   bean.setConfigLocation(new ClassPathResource("mybatis-config.xml"));
   bean.setMapperLocations(
       new PathMatchingResourcePatternResolver().getResources("classpath*:mappers/master/*.xml"));
   return bean.getObject();
 }

@Bean(name = "masterTransactionManager")
 @Primary
 public DataSourceTransactionManager transactionManager(@Qualifier("masterDataSource") DataSource dataSource) {
   return new DataSourceTransactionManager(dataSource);
 }

@Bean(name = "masterSqlSessionTemplate")
 @Primary
 public SqlSessionTemplate testSqlSessionTemplate(
     @Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) throws Exception {
   return new SqlSessionTemplate(sqlSessionFactory);
 }
}

部分代码

Users实体类

package com.demo.model;

import lombok.Data;
import java.util.Date;

@Data
public class Users {
 private Integer id;
 private String username;
 private String pwd;
 private String phone;
 private String email;
 private Integer gender;
 private Integer age;
 private Date created_time;
 private Date updated_time;
 private Date deleted_time;
 private Integer type;
}

UsersMapper

package com.demo.dao.master;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.demo.model.Users;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface UsersMapper extends BaseMapper<Users> {
}

UsersService

package com.demo.service.master;

import com.baomidou.mybatisplus.extension.service.IService;
import com.demo.model.Users;

public interface UsersService extends IService<Users> {
}

UsersServiceImpl

package com.demo.service.master.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.demo.dao.master.UsersMapper;
import com.demo.model.Users;
import com.demo.service.master.UsersService;
import org.springframework.stereotype.Service;

@Service
public class UsersServiceImpl extends ServiceImpl<UsersMapper, Users> implements UsersService {
}

UsersController

package com.demo.controller.master;

import com.demo.model.Users;
import com.demo.service.master.UsersService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/master")
public class UsersController {
 @Autowired
 private UsersService userssService;

@RequestMapping("/users")
 public List<Users> list() {
   return userssService.list();
 }
}

最终的项目结构

运行结果

至此Spring Boot + Mybatis-Plus已经完成了多数据源的实现。
最后再写一点我遇到的坑吧
1、master与local中不要出现同名的文件
2、数据源配置中SqlSessionFactoryBean替换为MybatisSqlSessionFactoryBean

下一篇将会围绕xxl-job任务调度中心，介绍如何通过xxl-job实现本地库与线上库的定时同步

来源：https://blog.csdn.net/weixin_42905671/article/details/109827177