Spring Boot + Mybatis-Plus實(shí)現(xiàn)多數(shù)據(jù)源的方法

更新時(shí)間：2020年11月20日 10:19:21 作者：Aben·Chen

這篇文章主要介紹了Spring Boot + Mybatis-Plus實(shí)現(xiàn)多數(shù)據(jù)源的方法，本文給大家介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下

前段時(shí)間寫了一篇基于mybatis實(shí)現(xiàn)的多數(shù)據(jù)源博客。感覺不是很好，這次打算加入git，來搭建一個(gè)基于Mybatis-Plus的多數(shù)據(jù)源項(xiàng)目
Mybatis-Plus就是香

前言：該項(xiàng)目分為master數(shù)據(jù)源與local數(shù)據(jù)源。假定master數(shù)據(jù)源為線上數(shù)據(jù)庫(kù)，local為本地?cái)?shù)據(jù)庫(kù)。后續(xù)我們將通過xxl-job的方式，將線上(master)中的數(shù)據(jù)同步到本地(local)中

~~項(xiàng)目git地址~~ 抽時(shí)間把項(xiàng)目提交到git倉(cāng)庫(kù)，方便大家直接克隆
sql文件已置于項(xiàng)目中，數(shù)據(jù)庫(kù)使用的mysql

創(chuàng)建項(xiàng)目

我這里使用的idea，正常創(chuàng)建spring boot項(xiàng)目就行了。

sql

-- 創(chuàng)建master數(shù)據(jù)庫(kù)
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 '手機(jī)號(hào)',
	email VARCHAR(50) COMMENT '郵箱',
	gender CHAR(1) COMMENT '性別 1-->男 0-->女',
	age INT COMMENT '年齡',
	created_time TIMESTAMP NULL DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
	updated_time TIMESTAMP NULL DEFAULT NULL COMMENT '修改時(shí)間',
	deleted_time TIMESTAMP NULL DEFAULT NULL COMMENT '刪除時(shí)間',
	type INT DEFAULT 1 COMMENT '狀態(tài) 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); 

-- 創(chuàng)建local數(shù)據(jù)庫(kù)
CREATE DATABASE db_local;

USE db_local;
-- 本地庫(kù)用戶表
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 '手機(jī)號(hào)',
	email VARCHAR(50) COMMENT '郵箱',
	gender CHAR(1) COMMENT '性別 1-->男 0-->女',
	age INT COMMENT '年齡',
	created_time TIMESTAMP NULL DEFAULT NULL COMMENT '創(chuàng)建時(shí)間',
	updated_time TIMESTAMP NULL DEFAULT NULL COMMENT '修改時(shí)間',
	deleted_time TIMESTAMP NULL DEFAULT NULL COMMENT '刪除時(shí)間',
	type INT DEFAULT 1 COMMENT '狀態(tài) 1-->啟用 2-->禁用 3-->軟刪除'
)ENGINE=INNODB;
-- 本地庫(kù)測(cè)試表
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相關(guān)的依賴

	<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創(chuàng)建。其實(shí)無所謂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
 #實(shí)體掃描，多個(gè)package用逗號(hào)或者分號(hào)分隔
 typeAliasesPackage: com.niuniu.sys.module
 check-config-location: true
 configuration:
  #是否開啟自動(dòng)駝峰命名規(guī)則（camel case）映射
  map-underscore-to-camel-case: true
  #全局地開啟或關(guān)閉配置文件中的所有映射器已經(jīng)配置的任何緩存
  cache-enabled: false
  call-setters-on-nulls: true
  #配置JdbcTypeForNull, oracle數(shù)據(jù)庫(kù)必須配置
  jdbc-type-for-null: 'null'
  #MyBatis 自動(dòng)映射時(shí)未知列或未知屬性處理策略 NONE：不做任何處理 (默認(rèn)值), WARNING：以日志的形式打印相關(guān)警告信息, FAILING：當(dāng)作映射失敗處理，并拋出異常和詳細(xì)信息
  auto-mapping-unknown-column-behavior: warning
 global-config:
  banner: false
  db-config:
   #主鍵類型 0:"數(shù)據(jù)庫(kù)ID自增", 1:"未設(shè)置主鍵類型",2:"用戶輸入ID (該類型可以通過自己注冊(cè)自動(dòng)填充插件進(jìn)行填充)", 3:"全局唯一ID (idWorker), 4:全局唯一ID (UUID), 5:字符串全局唯一ID (idWorker 的字符串表示)";
   id-type: auto
   #字段驗(yàn)證策略 IGNORED:"忽略判斷", NOT_NULL:"非NULL判斷", NOT_EMPTY:"非空判斷", DEFAULT 默認(rèn)的,一般只用于注解里(1. 在全局里代表 NOT_NULL,2. 在注解里代表 跟隨全局)
   field-strategy: NOT_EMPTY
   #數(shù)據(jù)庫(kù)大寫下劃線轉(zhuǎn)換
   capital-mode: true
   #邏輯刪除值
   logic-delete-value: 0
   #邏輯未刪除值
   logic-not-delete-value: 1

pagehelper:            #pagehelper分頁(yè)插件
 helperDialect: mysql     #設(shè)置數(shù)據(jù)庫(kù)方言
 reasonable: true
 supportMethodsArguments: true
 params: count=countSql

目錄結(jié)構(gòu)

由于該項(xiàng)目分為兩個(gè)數(shù)據(jù)源。在各個(gè)分層中，你將分別見到master、local。

在這里插入圖片描述

備注：由于users表在兩個(gè)數(shù)據(jù)源中完全相同，將Users實(shí)體置于model層公用

關(guān)鍵代碼

在config包中，我們需要對(duì)master、local的數(shù)據(jù)源進(jìn)行配置，使其可以通過不同的請(qǐng)求，自動(dòng)切換數(shù)據(jù)源

另外由于該項(xiàng)目使用mybatis-plus，在創(chuàng)建SqlSessionFactory的時(shí)候，請(qǐng)使用MybatisSqlSessionFactoryBean進(jìn)行bean對(duì)象的創(chuàng)建

在這里插入圖片描述

local數(shù)據(jù)源

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數(shù)據(jù)源
   */
  @Bean(name = "localSqlSessionFactory")
  public SqlSessionFactory sqlSessionFactory(@Qualifier("localDataSource") DataSource dataSource) throws Exception {
    MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
    bean.setDataSource(dataSource);
    // 設(shè)置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數(shù)據(jù)源

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數(shù)據(jù)源
   */
  @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實(shí)體類

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();
  }
}

最終的項(xiàng)目結(jié)構(gòu)

在這里插入圖片描述

運(yùn)行結(jié)果

在這里插入圖片描述

至此Spring Boot + Mybatis-Plus已經(jīng)完成了多數(shù)據(jù)源的實(shí)現(xiàn)。
最后再寫一點(diǎn)我遇到的坑吧
1、master與local中不要出現(xiàn)同名的文件
2、數(shù)據(jù)源配置中SqlSessionFactoryBean替換為MybatisSqlSessionFactoryBean

下一篇將會(huì)圍繞xxl-job任務(wù)調(diào)度中心，介紹如何通過xxl-job實(shí)現(xiàn)本地庫(kù)與線上庫(kù)的定時(shí)同步

到此這篇關(guān)于Spring Boot + Mybatis-Plus實(shí)現(xiàn)多數(shù)據(jù)源的文章就介紹到這了,更多相關(guān)Spring Boot Mybatis-Plus多數(shù)據(jù)源內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: