使用Mybatis Plus整合多数据源和读写分离的详细过程

使用Mybatis Plus整合多数据源和读写分离的详细过程

目录一、简介二、准备2.1 数据库2.2 代码三、案例3.1 查询用户库主库用户表记录3.2 查询用户库从库用户表记录3.3 新增用户库主库用户记录3.4 商品库查询商品记录3.5 商品库新增商品记录3.6 用户库商品库多数据源嵌套四、总结

一、简介

俩年前用AOP自己封装过一个多数据源，连接地址：springboot + mybatis + druid + 多数据源 ， 有兴趣的可以看下；

当时没有处理多数据源嵌套的情况，现在发现mybatis plus比较好用，所以该篇文章写下demo;

mybatis-plus的官网：MyBatis-Plus,  请参考多数据源的篇幅； 另外mybatis-plus已经可以整合阿里的分布式事务组件seata了，demo待写；

因为mybatis-plus相对来说还是要手动处理的地方比较多，后面会考虑换成sharding-jdbc做多数据源和读写分离，后者完全接管，不需要自己去手动处理；不过，有好有坏，后者用的时候需要将前面的没有处理的因为延时可能导致查不到的地方全部强制走主库，而前者就不需要，什么时候接入都可以，但是后者可能会多写两行代码，要多方面去权衡；

代码github路径： https://github.com/1956025812/ds-many

二、准备

2.1 数据库

准备三个数据库，用户库一主一从[模拟读写分离]，商品库[模拟多数据源]。user_master[默认主库],  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 '状态：0-删除，1-启用，2-禁用',

`register_source` tinyint(4) NOT NULL COMMENT '注册来源：1-系统注册，2-用户注册，3-QQ，4-WX',

`create_info` varchar(64) DEFAULT NULL COMMENT '创建信息',

`create_time` datetime NOT NULL COMMENT '创建时间',

`update_info` varchar(64) DEFAULT NULL COMMENT '修改信息',

`update_time` datetime DEFAULT NULL COMMENT '修改时间',

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 '状态：0-删除，1-启用，2-禁用',

`register_source` tinyint(4) NOT NULL COMMENT '注册来源：1-系统注册，2-用户注册，3-QQ，4-WX',

`create_info` varchar(64) DEFAULT NULL COMMENT '创建信息',

`create_time` datetime NOT NULL COMMENT '创建时间',

`update_info` varchar(64) DEFAULT NULL COMMENT '修改信息',

`update_time` datetime DEFAULT NULL COMMENT '修改时间',

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 '状态：0-删除，1-上架，2-下架',

`create_user` varchar(64) DEFAULT NULL COMMENT '创建人信息',

`create_time` datetime NOT NULL COMMENT '创建时间',

`update_user` varchar(64) DEFAULT NULL COMMENT '修改人信息',

`update_time` datetime DEFAULT NULL COMMENT '修改时间',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 COMMENT='商品表'

2.2 代码

pom依赖

com.baomidou

dynamic-datasource-spring-boot-starter

3.2.0

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

代码目录结构

三、案例

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;

/**

*

* 用户表 前端控制器

*

*

* @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;

/**

*

* 用户表 服务类

*

*

* @author qjwyss

* @since 2020-09-02

*/

public interface ISysUserService extends IService {

SysUser selectUser(Integer uid);

}

SysUserServiceImpl： 只需要在service方法上用@DS("user_master")注解标明该方法的数据源即可

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;

/**

*

* 用户表 服务实现类

*

*

* @author qjwyss

* @since 2020-09-02

*/

@Service

@Slf4j

public class SysUserServiceImpl extends ServiceImpl 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

@RestControllkIYYTOKer

@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 selectUserSlave(Integer uid);

}

SysUserServiceImpl： 只需要在service方法上用@DS("user_slave")注解标明该方法的数据源即可

@Service

@Slf4j

public class SysUserServiceImpl extends ServiceImpl implements ISysUserService {

@Resource

private SysUserMapper sysUserMapper;

@DS("user_slave")

@Override

public SysUser selectUserSlave(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-1kIYYTOK6T14: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 {

void saveSysUser();

}

SysUserServiceImpl

@Service

@Slf4j

public class SysUserServiceImpl extends ServiceImpl implements ISysUserService {

@Resource

private SysUserMapper sysUserMapper;

/**

* 支持主数据源的事务

*/

@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);

}

}

结果： 支持主数据源的事务，如果把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;

/**

*

* 商品表 服务类

*

*

* @author qjwyss

* @since 2020-09-02

*/

public interface IGoodsService extends IService {

Goods selectGoods(int id);

}

GoodsServiceImpl

package com.yss.ds.demo.service.impl;

impkIYYTOKort 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;

/**

*

* 商品表 服务实现类

*

*

* @author qjwyss

* @since 2020-09-02

*/

@Service

public class GoodsServiceImpl extends ServiceImpl implements IGoodsService {

@DS("goods")

@Override

public Goods selectGoods(int id) {

return this.getById(id);

}

}

结果

{"code":1,"data":{"id":1,"goodsName":"手机","goodsRemark":"小米手机","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 {

void saveGoods();

}

GoodsServiceImpl： 只需要在service方法上用@DS("goods")注解标明该方法的数据源即可; 单裤数据源均支持事务；

@Service

public class GoodsServiceImpl extends ServiceImpl implements IGoodsService {

/**

* 商品库数据源也支持事务

*/

@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);

}

}

结果： 可以看到：如果去掉1/0，则保存俩条记录，如果加上，则俩条都不保存；

3.6 用户库商品库多数据源嵌套

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 {

void saveUserAndQueryGoods();

void saveSingleUser();

}

SysUserServiceImpl： 嵌套数据源必须有额外的外层方法，外层方法不要标明数据源，内层全部在service上标明各自的数据源；

@Service

@Slf4j

public class SysUserServiceImpl extends ServiceImpl implements ISysUserService {

@Resource

private SysUserMapper sysUserMapper;

@Resource

private IGoodsService iGoodsService;

/**

* 嵌套数据源的话最外层不要加数据源

* 内层方法加各自的数据源 保证一个service只有一个数据源

*/

@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);

}

}

结果： 可以发现用户库先是添加了用户记录，并且查询到了商品库的商品信息；

四、总结

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。