使用Mybatis Plus整合多數(shù)據(jù)源和讀寫分離的詳細(xì)過程
一、簡介
- 倆年前用AOP自己封裝過一個(gè)多數(shù)據(jù)源,連接地址:springboot + mybatis + druid + 多數(shù)據(jù)源 , 有興趣的可以看下;
- 當(dāng)時(shí)沒有處理多數(shù)據(jù)源嵌套的情況,現(xiàn)在發(fā)現(xiàn)mybatis plus比較好用,所以該篇文章寫下demo;
- mybatis-plus的官網(wǎng):MyBatis-Plus, 請參考多數(shù)據(jù)源的篇幅; 另外mybatis-plus已經(jīng)可以整合阿里的分布式事務(wù)組件seata了,demo待寫;
- 因?yàn)閙ybatis-plus相對來說還是要手動(dòng)處理的地方比較多,后面會考慮換成sharding-jdbc做多數(shù)據(jù)源和讀寫分離,后者完全接管,不需要自己去手動(dòng)處理;不過,有好有壞,后者用的時(shí)候需要將前面的沒有處理的因?yàn)檠訒r(shí)可能導(dǎo)致查不到的地方全部強(qiáng)制走主庫,而前者就不需要,什么時(shí)候接入都可以,但是后者可能會多寫兩行代碼,要多方面去權(quán)衡;
- 代碼github路徑: https://github.com/1956025812/ds-many
二、準(zhǔn)備
2.1 數(shù)據(jù)庫
- 準(zhǔn)備三個(gè)數(shù)據(jù)庫,用戶庫一主一從[模擬讀寫分離],商品庫[模擬多數(shù)據(jù)源]。user_master[默認(rèn)主庫], user_slave, goods
- 用戶主庫user_master的用戶表sys_user
CREATE TABLE `sys_user` ( `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵', `username` varchar(32) NOT NULL COMMENT '賬號', `password` varchar(128) NOT NULL COMMENT 'MD5加密的密碼', `nickname` varchar(128) DEFAULT NULL COMMENT '昵稱', `email` varchar(64) NOT NULL COMMENT '郵箱', `head_img_url` varchar(256) DEFAULT NULL COMMENT '頭像路徑', `state` tinyint(4) NOT NULL COMMENT '狀態(tài):0-刪除,1-啟用,2-禁用', `register_source` tinyint(4) NOT NULL COMMENT '注冊來源:1-系統(tǒng)注冊,2-用戶注冊,3-QQ,4-WX', `create_info` varchar(64) DEFAULT NULL COMMENT '創(chuàng)建信息', `create_time` datetime NOT NULL COMMENT '創(chuàng)建時(shí)間', `update_info` varchar(64) DEFAULT NULL COMMENT '修改信息', `update_time` datetime DEFAULT NULL COMMENT '修改時(shí)間', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8 COMMENT='用戶表'
用戶從庫user_slave的用戶表sys_user
CREATE TABLE `sys_user` ( `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵', `username` varchar(32) NOT NULL COMMENT '賬號', `password` varchar(128) NOT NULL COMMENT 'MD5加密的密碼', `nickname` varchar(128) DEFAULT NULL COMMENT '昵稱', `email` varchar(64) NOT NULL COMMENT '郵箱', `head_img_url` varchar(256) DEFAULT NULL COMMENT '頭像路徑', `state` tinyint(4) NOT NULL COMMENT '狀態(tài):0-刪除,1-啟用,2-禁用', `register_source` tinyint(4) NOT NULL COMMENT '注冊來源:1-系統(tǒng)注冊,2-用戶注冊,3-QQ,4-WX', `create_info` varchar(64) DEFAULT NULL COMMENT '創(chuàng)建信息', `create_time` datetime NOT NULL COMMENT '創(chuàng)建時(shí)間', `update_info` varchar(64) DEFAULT NULL COMMENT '修改信息', `update_time` datetime DEFAULT NULL COMMENT '修改時(shí)間', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8 COMMENT='用戶表'
商品庫goods的商品表goods
CREATE TABLE `goods` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `goods_name` varchar(256) NOT NULL COMMENT '商品名稱', `goods_remark` varchar(256) DEFAULT NULL COMMENT '商品描述', `status` tinyint(4) NOT NULL COMMENT '狀態(tài):0-刪除,1-上架,2-下架', `create_user` varchar(64) DEFAULT NULL COMMENT '創(chuàng)建人信息', `create_time` datetime NOT NULL COMMENT '創(chuàng)建時(shí)間', `update_user` varchar(64) DEFAULT NULL COMMENT '修改人信息', `update_time` datetime DEFAULT NULL COMMENT '修改時(shí)間', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='商品表'
2.2 代碼
pom依賴
<dependency> <groupId>com.baomidou</groupId> <artifactId>dynamic-datasource-spring-boot-starter</artifactId> <version>3.2.0</version> </dependency>
application.yml
server: port: 8000 servlet: context-path: / spring: datasource: dynamic: primary: user_master strict: false datasource: user_master: url: jdbc:mysql://localhost:3306/user_master username: root password: 123456 driver-class-name: com.mysql.jdbc.Driver user_slave: url: jdbc:mysql://localhost:3306/user_slave username: root password: 123456 driver-class-name: com.mysql.jdbc.Driver goods: url: jdbc:mysql://localhost:3306/goods username: root password: 123456 driver-class-name: com.mysql.jdbc.Driver
代碼目錄結(jié)構(gòu)
三、案例
3.1 查詢用戶庫主庫用戶表記錄
SysUserController
package com.yss.ds.demo.controller; import com.baomidou.dynamic.datasource.annotation.DS; import com.yss.ds.demo.entity.SysUser; import com.yss.ds.demo.service.ISysUserService; import com.yss.ds.demo.vo.ResultVO; 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; /** * <p> * 用戶表 前端控制器 * </p> * * @author qjwyss * @since 2020-09-02 */ @RestController @RequestMapping("/sysUser") public class SysUserController { @Resource private ISysUserService iSysUserService; // http://localhost:8000/sysUser/selectUser?uid=5 @GetMapping("/selectUser") public ResultVO selectUser(Integer uid) { SysUser sysUser = this.iSysUserService.selectUser(uid); return ResultVO.getSuccess("", sysUser); } }
ISysUserService
package com.yss.ds.demo.service; import com.baomidou.mybatisplus.extension.service.IService; import com.yss.ds.demo.entity.SysUser; /** * <p> * 用戶表 服務(wù)類 * </p> * * @author qjwyss * @since 2020-09-02 */ public interface ISysUserService extends IService<SysUser> { SysUser selectUser(Integer uid); }
SysUserServiceImpl: 只需要在service方法上用@DS("user_master")注解標(biāo)明該方法的數(shù)據(jù)源即可
package com.yss.ds.demo.service.impl; import com.alibaba.fastjson.JSONObject; import com.baomidou.dynamic.datasource.annotation.DS; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.yss.ds.demo.entity.Goods; import com.yss.ds.demo.entity.SysUser; import com.yss.ds.demo.mapper.SysUserMapper; import com.yss.ds.demo.service.IGoodsService; import com.yss.ds.demo.service.ISysUserService; import lombok.extern.slf4j.Slf4j; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import javax.annotation.Resource; import java.util.Date; /** * <p> * 用戶表 服務(wù)實(shí)現(xiàn)類 * </p> * * @author qjwyss * @since 2020-09-02 */ @Service @Slf4j public class SysUserServiceImpl extends ServiceImpl<SysUserMapper, SysUser> implements ISysUserService { @Resource private SysUserMapper sysUserMapper; @DS("user_master") @Override public SysUser selectUser(Integer uid) { return this.getById(uid); } }
輸出: 可以看到的查詢到的是主庫的記錄
{"code":1,"msg":"","data":{"id":5,"username":"yss003","password":"E10ADC3949BA59ABBE56E057F20F883E","nickname":"猿叔叔003-主庫","email":"yss@5566.com","headImgUrl":"qwerwqe","state":1,"registerSource":1,"createInfo":null,"createTime":"2020-01-16T14:46:50.000+0000","updateInfo":null,"updateTime":"2020-04-29T13:48:00.000+0000"}}
3.2 查詢用戶庫從庫用戶表記錄
SysUserController
@RestController @RequestMapping("/sysUser") public class SysUserController { @Resource private ISysUserService iSysUserService; // http://localhost:8000/sysUser/selectUserSlave?uid=5 @GetMapping("/selectUserSlave") public ResultVO selectUserSlave(Integer uid) { SysUser sysUser = this.iSysUserService.selectUserSlave(uid); return ResultVO.getSuccess("", sysUser); } }
ISysUserService
public interface ISysUserService extends IService<SysUser> { SysUser selectUserSlave(Integer uid); }
SysUserServiceImpl: 只需要在service方法上用@DS("user_slave")注解標(biāo)明該方法的數(shù)據(jù)源即可
@Service @Slf4j public class SysUserServiceImpl extends ServiceImpl<SysUserMapper, SysUser> implements ISysUserService { @Resource private SysUserMapper sysUserMapper; @DS("user_slave") @Override public SysUser selectUserSlave(Integer uid) { return this.getById(uid); } }
結(jié)果: 可以看到的查詢到的是從庫的記錄
{"code":1,"msg":"","data":{"id":5,"username":"yss003","password":"E10ADC3949BA59ABBE56E057F20F883E","nickname":"猿叔叔003-從庫","email":"yss@5566.com","headImgUrl":"qwerwqe","state":1,"registerSource":1,"createInfo":null,"createTime":"2020-01-16T14:46:50.000+0000","updateInfo":null,"updateTime":"2020-04-29T13:48:00.000+0000"}}
3.3 新增用戶庫主庫用戶記錄
SysUserController
@RestController @RequestMapping("/sysUser") public class SysUserController { @Resource private ISysUserService iSysUserService; // http://localhost:8000/sysUser/save @GetMapping("/save") public ResultVO saveSysUser() { this.iSysUserService.saveSysUser(); return ResultVO.getSuccess(""); } }
ISysUserService
public interface ISysUserService extends IService<SysUser> { void saveSysUser(); }
SysUserServiceImpl
@Service @Slf4j public class SysUserServiceImpl extends ServiceImpl<SysUserMapper, SysUser> implements ISysUserService { @Resource private SysUserMapper sysUserMapper; /** * 支持主數(shù)據(jù)源的事務(wù) */ @DS("user_master") @Transactional(rollbackFor = Exception.class) @Override public void saveSysUser() { SysUser sysUser = new SysUser().setUsername("yss013").setPassword("123456").setEmail("yss@013.com").setState(1).setRegisterSource(1).setCreateTime(new Date()); save(sysUser); System.out.println(1 / 0); save(sysUser); } }
結(jié)果: 支持主數(shù)據(jù)源的事務(wù),如果把1/0去掉可以看到保存了倆條記錄,不去掉則回滾都不保存;
3.4 商品庫查詢商品記錄
GoodsController
@RestController @RequestMapping("/goods") public class GoodsController { @Resource private IGoodsService iGoodsService; // http://localhost:8000/goods/selectGoods?gid=1 @GetMapping("/selectGoods") public ResultVO selectGoods(Integer gid) { Goods goods = this.iGoodsService.selectGoods(gid); return ResultVO.getSuccess(null, goods); } }
IGoodsService
package com.yss.ds.demo.service; import com.baomidou.mybatisplus.extension.service.IService; import com.yss.ds.demo.entity.Goods; /** * <p> * 商品表 服務(wù)類 * </p> * * @author qjwyss * @since 2020-09-02 */ public interface IGoodsService extends IService<Goods> { Goods selectGoods(int id); }
GoodsServiceImpl
package com.yss.ds.demo.service.impl; import com.baomidou.dynamic.datasource.annotation.DS; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.yss.ds.demo.entity.Goods; import com.yss.ds.demo.mapper.GoodsMapper; import com.yss.ds.demo.service.IGoodsService; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import java.util.Date; /** * <p> * 商品表 服務(wù)實(shí)現(xiàn)類 * </p> * * @author qjwyss * @since 2020-09-02 */ @Service public class GoodsServiceImpl extends ServiceImpl<GoodsMapper, Goods> implements IGoodsService { @DS("goods") @Override public Goods selectGoods(int id) { return this.getById(id); } }
結(jié)果
{"code":1,"data":{"id":1,"goodsName":"手機(jī)","goodsRemark":"小米手機(jī)","status":1,"createUser":"system","createTime":"2019-12-16T20:31:02.000+0000","updateUser":"system","updateTime":"2019-12-16T20:31:07.000+0000"}}
3.5 商品庫新增商品記錄
GoodsController
@RestController @RequestMapping("/goods") public class GoodsController { @Resource private IGoodsService iGoodsService; // http://localhost:8000/goods/save @GetMapping("/save") public ResultVO saveGoods() { this.iGoodsService.saveGoods(); return ResultVO.getSuccess(""); } }
IGoodsService
public interface IGoodsService extends IService<Goods> { void saveGoods(); }
GoodsServiceImpl: 只需要在service方法上用@DS("goods")注解標(biāo)明該方法的數(shù)據(jù)源即可; 單褲數(shù)據(jù)源均支持事務(wù);
@Service public class GoodsServiceImpl extends ServiceImpl<GoodsMapper, Goods> implements IGoodsService { /** * 商品庫數(shù)據(jù)源也支持事務(wù) */ @DS("goods") @Transactional(rollbackFor = Exception.class) @Override public void saveGoods() { Goods goods = new Goods().setGoodsName("商品名稱A").setStatus(1).setCreateTime(new Date()); this.save(goods); System.out.println(1/0); this.save(goods); } }
結(jié)果: 可以看到:如果去掉1/0,則保存?zhèn)z條記錄,如果加上,則倆條都不保存;
3.6 用戶庫商品庫多數(shù)據(jù)源嵌套
SysUserController
@RestController @RequestMapping("/sysUser") public class SysUserController { @Resource private ISysUserService iSysUserService; // http://localhost:8000/sysUser/saveUserAndQueryGoods @GetMapping("/saveUserAndQueryGoods") public ResultVO saveUserAndQueryGoods() { this.iSysUserService.saveUserAndQueryGoods(); return ResultVO.getSuccess(""); } }
ISysUserService
public interface ISysUserService extends IService<SysUser> { void saveUserAndQueryGoods(); void saveSingleUser(); }
SysUserServiceImpl: 嵌套數(shù)據(jù)源必須有額外的外層方法,外層方法不要標(biāo)明數(shù)據(jù)源,內(nèi)層全部在service上標(biāo)明各自的數(shù)據(jù)源;
@Service @Slf4j public class SysUserServiceImpl extends ServiceImpl<SysUserMapper, SysUser> implements ISysUserService { @Resource private SysUserMapper sysUserMapper; @Resource private IGoodsService iGoodsService; /** * 嵌套數(shù)據(jù)源的話最外層不要加數(shù)據(jù)源 * 內(nèi)層方法加各自的數(shù)據(jù)源 保證一個(gè)service只有一個(gè)數(shù)據(jù)源 */ @Override public void saveUserAndQueryGoods() { this.saveSingleUser(); Goods goods = this.iGoodsService.selectGoods(1); log.info("商品信息為:{}", JSONObject.toJSONString(goods)); } @DS("user_master") @Override public void saveSingleUser() { SysUser sysUser = new SysUser().setUsername("yss013").setPassword("123456").setEmail("yss@013.com").setState(1).setRegisterSource(1) .setCreateTime(new Date()); this.save(sysUser); } }
結(jié)果: 可以發(fā)現(xiàn)用戶庫先是添加了用戶記錄,并且查詢到了商品庫的商品信息;
四、總結(jié)
到此這篇關(guān)于Mybatis Plus整合多數(shù)據(jù)源和讀寫分離的文章就介紹到這了,更多相關(guān)Mybatis Plus多數(shù)據(jù)源讀寫分離內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
- MybatisPlus多數(shù)據(jù)源及事務(wù)解決思路
- springboot集成mybatisPlus+多數(shù)據(jù)源的實(shí)現(xiàn)示例
- mybatis plus動(dòng)態(tài)數(shù)據(jù)源切換及查詢過程淺析
- Spring Boot + Mybatis-Plus實(shí)現(xiàn)多數(shù)據(jù)源的方法
- Springboot mybatis plus druid多數(shù)據(jù)源解決方案 dynamic-datasource的使用詳解
- MyBatis-Plus實(shí)現(xiàn)多數(shù)據(jù)源的示例代碼
- Mybatis plus 配置多數(shù)據(jù)源的實(shí)現(xiàn)示例
- SpringBoot+Mybatis-Plus實(shí)現(xiàn)mysql讀寫分離方案的示例代碼
相關(guān)文章
spring cloud 之 Feign 使用HTTP請求遠(yuǎn)程服務(wù)的實(shí)現(xiàn)方法
下面小編就為大家?guī)硪黄猻pring cloud 之 Feign 使用HTTP請求遠(yuǎn)程服務(wù)的實(shí)現(xiàn)方法。小編覺得挺不錯(cuò)的,現(xiàn)在就分享給大家,也給大家做個(gè)參考。一起跟隨小編過來看看吧2017-06-06Java BufferedImage轉(zhuǎn)換為MultipartFile方式
這篇文章主要介紹了Java BufferedImage轉(zhuǎn)換為MultipartFile方式,具有很好的參考價(jià)值,希望對大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2021-09-09Spring Boot2.0使用Spring Security的示例代碼
這篇文章主要介紹了Spring Boot2.0使用Spring Security的示例代碼,小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,也給大家做個(gè)參考。一起跟隨小編過來看看吧2018-08-08利用棧使用簡易計(jì)算器(Java實(shí)現(xiàn))
這篇文章主要為大家詳細(xì)介紹了Java利用棧實(shí)現(xiàn)簡易計(jì)算器,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下2019-09-09Javassist之一秒理解java動(dòng)態(tài)編程
概述Javassist是一款字節(jié)碼編輯工具,可以直接編輯和生成Java生成的字節(jié)碼,以達(dá)到對.class文件進(jìn)行動(dòng)態(tài)修改的效果。2019-06-06JAVA序列化和反序列化的底層實(shí)現(xiàn)原理解析
這篇文章主要介紹了JAVA序列化和反序列化的底層實(shí)現(xiàn)原理解析,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2019-11-11@JsonFormat?和?@DateTimeFormat?時(shí)間格式化注解(場景示例代碼)
這篇文章主要介紹了@JsonFormat和@DateTimeFormat時(shí)間格式化注解,本文通過場景示例代碼詳解給大家介紹的非常詳細(xì),對大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2023-05-05