微服务架构(四)springboot整合mybatis-plus实现主从分离(一主两从)

最新推荐文章于 2024-05-15 14:15:22 发布

非生物语言学家

最新推荐文章于 2024-05-15 14:15:22 发布

阅读量1.5k

点赞数

分类专栏：主从备份文章标签： spring boot mysql spring java 经验分享

本文链接：https://blog.csdn.net/qq_29653373/article/details/115433956

版权

主从备份专栏收录该内容

3 篇文章 1 订阅

订阅专栏

一、效果图

二、思路

读写分离的关键在于DataSource的抽象类AbstractRoutingDataSourc，它应许用户自定义选库规:

第一步、枚举出有多少种读写方式(ReadsAndWrite)和读写选库规则(ReadWriteSeparationRule)

第二步、重写RWSRoutingDataSource实现determineCurrentLookupKey方法

第三步、新建RWSAOPRule读写分离切面规则

第四步、新建配置MyBatisPlushConfig

第五步、新建数据源DataSourceConfig简单的user操作测试

三、实现

1.目录结构

2.添加依赖

     <dependencies>
        <!-- aop-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
        <!-- yml密码等关键信息脱敏-->
        <dependency>
            <groupId>com.github.ulisesbocchio</groupId>
            <artifactId>jasypt-spring-boot-starter</artifactId>
            <version>3.0.3</version>
        </dependency>
        <!-- mysql驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <!-- 实体辅助类-->
        <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>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>RELEASE</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>org.apache.httpcomponents</groupId>
            <artifactId>httpcore</artifactId>
            <version>4.4.12</version>
        </dependency>
        <dependency>
            <groupId>commons-lang</groupId>
            <artifactId>commons-lang</artifactId>
            <version>2.6</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.2</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

3.列出读写分离枚举(一个主写两个从读)和读写规则

public enum ReadsAndWrite {
    WRITE, READ1, READ2
}

import java.util.concurrent.atomic.AtomicInteger;
 
/**
 * 读写分离规则
 */
public class ReadWriteSeparationRule {
    private static final ThreadLocal<ReadsAndWrite> contextHolder = new ThreadLocal<>();
    private static final AtomicInteger counter = new AtomicInteger(-1);
 
    public static void set(ReadsAndWrite nodeType) {
        contextHolder.set(nodeType);
    }
 
    public static ReadsAndWrite get() {
        return contextHolder.get();
    }
 
    /**
     * 多个写节点也可以做简单的负载均衡
     */
    public static void writer() {
        set(ReadsAndWrite.WRITE);
        System.out.println("切换到写节点数据库");
    }
 
    /**
     * 读简单的1:2权重负载均衡
     */
    public static void reader() {
        int index = counter.getAndIncrement() % 3;
        if (counter.get() > 1000) {
            counter.set(-1);
        }
        if (index == 0) {
            set(ReadsAndWrite.READ1);
            System.out.println("切换到第一个读节点数据库");
        } else {
            set(ReadsAndWrite.READ2);
            System.out.println("切换到第二个读节点数据库");
        }
    }
}

4.RWSRoutingDataSource实现determineCurrentLookupKey方法

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import org.springframework.lang.Nullable;
 
public class RWSRoutingDataSource extends AbstractRoutingDataSource {
    @Nullable
    @Override
    protected Object determineCurrentLookupKey() {
        return ReadWriteSeparationRule.get();
    }
}

5.新建RWSAOPRule读写分离切面规则（Writer注解强制走主节点，具体包名以实际项目为准）

import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;
 
@Aspect
@Component
public class RWSAOPRule {
    @Pointcut("!@annotation(cn.morik.mybitslpush.anmotion.Writer) " +
            "&& (execution(* cn.morik.mybitslpush..*.select*(..)) " +
            "|| execution(* cn.morik.mybitslpush..*.find*(..)))")
    public void readPointcut() {
 
    }
 
    @Pointcut("@annotation(cn.morik.mybitslpush.anmotion.Writer) " +
            "|| execution(* cn.morik.mybitslpush..*.insert*(..)) " +
            "|| execution(* cn.morik.mybitslpush..*.save*(..)) " +
            "|| execution(* cn.morik.mybitslpush..*.add*(..)) " +
            "|| execution(* cn.morik.mybitslpush..*.update*(..)) " +
            "|| execution(* cn.morik.mybitslpush..*.edit*(..)) " +
            "|| execution(* cn.morik.mybitslpush..*.delete*(..)) " +
            "|| execution(* cn.morik.mybitslpush..*.remove*(..))")
    public void writePointcut() {
 
    }
 
    @Before("readPointcut()")
    public void read() {
        ReadWriteSeparationRule.reader();
    }
 
    @Before("writePointcut()")
    public void write() {
        ReadWriteSeparationRule.writer();
    }
}

public @interface Writer {
}

6.配置MyBatisPlusConfig

import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.annotation.Resource;
import javax.sql.DataSource;

@EnableTransactionManagement
@Configuration
public class MyBatisPlusConfig {

    @Resource(name = "rwsRoutingDataSource")
    private DataSource rwsRoutingDataSource;

    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean(); 
        sqlSessionFactoryBean.setDataSource(rwsRoutingDataSource);
        return sqlSessionFactoryBean.getObject();
    }

    @Bean
    public PlatformTransactionManager platformTransactionManager() {
        return new DataSourceTransactionManager(rwsRoutingDataSource);
    }
}

7.配置DataSourceConfig以及yml参数

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
 
 
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
 
@Configuration
public class DataSourceConfig {
    @Bean
    @ConfigurationProperties("spring.datasource.writer")
    public DataSource writeDataSource() {
        return DataSourceBuilder.create().build();
    }
 
    @Bean
    @ConfigurationProperties("spring.datasource.reader1")
    public DataSource read1DataSource() {
        return DataSourceBuilder.create().build();
    }
 
    @Bean
    @ConfigurationProperties("spring.datasource.reader2")
    public DataSource read2DataSource() {
 
        return DataSourceBuilder.create().build();
    }
 
    @Bean
    public DataSource rwsRoutingDataSource(@Qualifier("writeDataSource") DataSource writeDataSource,
                                          @Qualifier("read1DataSource") DataSource read1DataSource,
                                          @Qualifier("read2DataSource") DataSource read2DataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(ReadsAndWrite.WRITE, writeDataSource);
        targetDataSources.put(ReadsAndWrite.READ1, read1DataSource);
        targetDataSources.put(ReadsAndWrite.READ2, read2DataSource);
        RWSRoutingDataSource rwsRoutingDataSource = new RWSRoutingDataSource();
        rwsRoutingDataSource.setDefaultTargetDataSource(writeDataSource);
        rwsRoutingDataSource.setTargetDataSources(targetDataSources);
        return rwsRoutingDataSource;
    }
 
}

server:
  port: 8081
  servlet:
    context-path: /

spring:
  datasource:
    writer:
      jdbc-url: jdbc:mysql://192.168.0.8:3306/paris?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&useSSL=false&serverTimezone=Asia/Shanghai
      username: ENC(SshT4Tf3hC2r+uNcGNv0OTk2DwQ+7H67H0WH9uu+SrZFIYTXVlfN4nU61hhKC4UW)
      password: ENC(4i8/Loc9x9MuRGD9WRFkCvboENM1mZjEEPnnZq3hkRxj5lAyEmbuDCmZHWvD6Tol)
      driver-class-name: com.mysql.cj.jdbc.Driver
    reader1:
      jdbc-url: jdbc:mysql://192.168.0.15:3306/paris?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&useSSL=false&serverTimezone=Asia/Shanghai
      username: ENC(SshT4Tf3hC2r+uNcGNv0OTk2DwQ+7H67H0WH9uu+SrZFIYTXVlfN4nU61hhKC4UW) #只读权限用户
      password: ENC(4i8/Loc9x9MuRGD9WRFkCvboENM1mZjEEPnnZq3hkRxj5lAyEmbuDCmZHWvD6Tol)
      driver-class-name: com.mysql.cj.jdbc.Driver
    reader2:
      jdbc-url: jdbc:mysql://192.168.0.16:3306/paris?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&useSSL=false&serverTimezone=Asia/Shanghai
      username: ENC(SshT4Tf3hC2r+uNcGNv0OTk2DwQ+7H67H0WH9uu+SrZFIYTXVlfN4nU61hhKC4UW) #只读权限用户
      password: ENC(4i8/Loc9x9MuRGD9WRFkCvboENM1mZjEEPnnZq3hkRxj5lAyEmbuDCmZHWvD6Tol)
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss
    time-zone: GMT+8
    serialization:
      write-dates-as-timestamps: false

mybatis-plus:
  configuration:
    map-underscore-to-camel-case: true
    auto-mapping-behavior: full
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations: classpath*:mapper/**/*Mapper.xml
  global-config:
    # 逻辑删除配置
    db-config:
      # 删除前
      logic-not-delete-value: 1
      # 删除后
      logic-delete-value: 0
jasypt:
  encryptor:
    password: **********  #把所有的*替换成你自定义的mysql用户名密码解密秘钥

#密码脱敏加密：
#1.把手动加密的秘文拷贝到yml中放ENC()括号里
#2.启动类上加@EnableEncryptableProperties注解自动解密

#@Autowired
#private StringEncryptor stringEncryptor;
#String username = stringEncryptor.encrypt("root");             //手动加密
#String decUsername = stringEncryptor.decrypt(username);        //手动解密

8.最后贴上启动类、实体类、service层、dao层以及api实现层最后是数据库sql相关展示

import com.ulisesbocchio.jasyptspringboot.annotation.EnableEncryptableProperties;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("cn.morik.mybitslpush.dao")
@EnableEncryptableProperties
public class MybitslpushApplication {
    public static void main(String[] args) {
        SpringApplication.run(MybitslpushApplication.class, args);
    }

}

import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@Builder
@TableName("tb_user")
@NoArgsConstructor
@AllArgsConstructor
public class UserInfo {
    private int id;
    private String userName;
    private String userPwd;
    private String userPhone;
    private int userRole;
    private int age;
}

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

public interface UserMapper extends BaseMapper<UserInfo> {
}

import com.baomidou.mybatisplus.extension.service.IService;

import java.util.List;

public interface IUserService extends IService<UserInfo> {

    List<UserInfo> findAllUser();

    int addUser(UserInfo userInfo);
}

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, UserInfo> implements IUserService {

    @Autowired
    private UserMapper userMapper;

    @Override
    public List<UserInfo> findAllUser() {
        return userMapper.selectList(null);
    }

    @Override
    public int addUser(UserInfo userInfo) {
        return userMapper.insert(userInfo);
    }
}

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/user")
public class UserController {

    @Autowired
    private IUserService userService;

    @GetMapping("/getUser")
    public UserInfo getUser() {
        return userService.getById(1);
    }


    @GetMapping("/findAllUser")
    public List<UserInfo> findAllUser() {
        return userService.findAllUser();
    }

    @GetMapping("/addUser")
    public int addUser(String userName) {
        UserInfo userInfo = UserInfo.builder().userName(userName).userPhone("6546464").userPwd("15646JKHKJ").userRole(1).age(5).build();
        return userService.addUser(userInfo);
    }
}

/*
 Navicat Premium Data Transfer

 Source Server         : 192.168.0.8
 Source Server Type    : MySQL
 Source Server Version : 50726
 Source Host           : 192.168.0.8:3306
 Source Schema         : paris

 Target Server Type    : MySQL
 Target Server Version : 50726
 File Encoding         : 65001

 Date: 07/04/2021 20:33:52
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tb_user
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '逻辑主键',
  `user_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '姓名',
  `user_role` int(1) NULL DEFAULT NULL COMMENT '角色',
  `user_phone` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '手机号',
  `user_pwd` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '密码',
  `age` int(11) NULL DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 25 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_user
-- ----------------------------
INSERT INTO `tb_user` VALUES (1, 'zhangsan', 1, '198556555898', 'LIHJIOHLKHL31654', 1);
INSERT INTO `tb_user` VALUES (4, '李四', 1, '1925555', '1564dfsaiH', 2);
INSERT INTO `tb_user` VALUES (7, '赵七', 1, '1925555', '1564dfsaiH', 3);
INSERT INTO `tb_user` VALUES (10, '六二', 1, '1925555', '1564dfsaiH', 4);
INSERT INTO `tb_user` VALUES (13, '陈皮', 1, '1568955354', 'sdafadfa', 5);
INSERT INTO `tb_user` VALUES (16, '二月红', 1, '1568955354', 'sdafadfa', 6);
INSERT INTO `tb_user` VALUES (19, 'kds', 1, '6546464', '15646JKHKJ', 5);
INSERT INTO `tb_user` VALUES (22, 'kds', 1, '6546464', '15646JKHKJ', 5);

SET FOREIGN_KEY_CHECKS = 1;

9.mysql物理数据库一主两从环境搭建请参照上篇介绍