前段時間寫了一篇基于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: 8001spring: 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.Drivermybatis-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: 1pagehelper: #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;@Datapublic 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;@Mapperpublic 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;@Servicepublic 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實現本地庫與線上庫的定時同步

到此這篇關于Spring Boot + Mybatis-Plus實現多數據源的文章就介紹到這了,更多相關Spring Boot Mybatis-Plus多數據源內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！