好得很程序员自学网

<tfoot draggable='sEl'></tfoot>

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

一、简介

俩年前用AOP自己封装过一个多 数据源 ，连接地址： springboot + mybatis + druid + 多数据源 ， 有兴趣的可以看下； 当时没有处理多数据源嵌套的情况，现在发现 mybatis plus 比较好用，所以该篇文章写下demo; mybatis-plus的官网： MyBatis-Plus ,  请参考多数据源的篇幅； 另外mybatis-plus已经可以整合阿里的分布式事务组件seata了，demo待写； 因为mybatis-plus相对来说还是要手动处理的地方比较多，后面会考虑换成sharding-jdbc做多数据源和 读写分离 ，后者完全接管，不需要自己去手动处理；不过，有好有坏，后者用的时候需要将前面的没有处理的因为延时可能导致查不到的地方全部强制走主库，而前者就不需要，什么时候接入都可以，但是后者可能会多写两行代码，要多方面去权衡； 代码github路径： https://github测试数据/1956025812/ds-many

二、准备

2.1 数据库

 准备三个数据库，用户库一主一从[模拟读写分离]，商品库[模拟多数据源]。user_master[默认主库],  user_slave, goods 用户主库user_master的用户表sys_user

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

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

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

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

?

1

2

3

4

5

6

7

8

9

10

11

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

?

1

2

3

4

5

<dependency>

     <groupId>com.baomidou</groupId>

     <artifactId>dynamic-datasource-spring-boot-starter</artifactId>

     <version> 3.2 . 0 </version>

</dependency>

application.yml

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

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

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

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

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

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")注解标明该方法的数据源即可

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

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测试数据","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

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

@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

?

1

2

3

4

5

public interface ISysUserService extends IService<SysUser> {

 

     SysUser selectUserSlave(Integer uid);

 

}

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

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

@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测试数据","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

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

@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

?

1

2

3

4

5

public interface ISysUserService extends IService<SysUser> {

 

     void saveSysUser();

 

}

SysUserServiceImpl

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

@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测试数据" ).setState( 1 ).setRegisterSource( 1 ).setCreateTime( new Date());

         save(sysUser);

         System.out.println( 1 / 0 );

         save(sysUser);

     }

 

 

}

结果： 支持主数据源的事务，如果把1/0去掉可以看到保存了俩条记录，不去掉则回滚都不保存；

3.4 商品库查询商品记录

GoodsController

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

@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

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

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

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

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

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

@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

?

1

2

3

4

5

public interface IGoodsService extends IService<Goods> {

 

     void saveGoods();

 

}

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

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

@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

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

@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

?

1

2

3

4

5

6

public interface ISysUserService extends IService<SysUser> {

 

     void saveUserAndQueryGoods();

 

     void saveSingleUser();

}

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

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

@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测试数据" ).setState( 1 ).setRegisterSource( 1 )

                 .setCreateTime( new Date());

         this .save(sysUser);

     }

}

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

四、总结

到此这篇关于Mybatis Plus整合多数据源和读写分离的文章就介绍到这了,更多相关Mybatis Plus多数据源读写分离内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！

原文链接：https://blog.csdn.net/qq_35206261/article/details/108400111

查看更多关于使用Mybatis Plus整合多数据源和读写分离的详细过程的详细内容...

声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did211687
  阅读：16次

上一篇： Java8 日期、时间操作代码

下一篇：实战！Spring Cloud Gateway集成 Rbac 权限模型实现动态权限控制！

相关资讯