亲宝软件园·资讯

展开

Mybatis Plus多数据源读写分离

请叫我猿叔叔 人气:0

一、简介

二、准备

2.1 数据库

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依赖

        <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

代码目录结构

三、案例

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>
 * 用户表 服务类
 * </p>
 *
 * @author qjwyss
 * @since 2020-09-02
 */
public interface ISysUserService extends IService<SysUser> {
 
    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;
 
/**
 * <p>
 * 用户表 服务实现类
 * </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")注解标明该方法的数据源即可

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

结果: 可以看到的查询到的是从库的记录

{"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;
 
 
    /**
     * 支持主数据源的事务
     */
    @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;
 
/**
 * <p>
 * 商品表 服务类
 * </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>
 * 商品表 服务实现类
 * </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);
    }
 
}

结果

{"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<Goods> {
 
    void saveGoods();
 
}

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

@Service
public class GoodsServiceImpl extends ServiceImpl<GoodsMapper, Goods> 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<SysUser> {
 
    void saveUserAndQueryGoods();
 
    void saveSingleUser();
}

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

@Service
@Slf4j
public class SysUserServiceImpl extends ServiceImpl<SysUserMapper, SysUser> 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);
    }
}

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

四、总结

加载全部内容

相关教程
猜你喜欢
用户评论