mybatis-plus读写分离

读写分离要做的事情就是对于一条SQL该选择哪个数据库去执行，要区分好主表以及从表的关系。

1. maven依赖

<properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <java.version>1.8</java.version>
    </properties>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.1.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- mybatis-plus begin -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatisplus-spring-boot-starter</artifactId>
            <version>1.0.5</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus</artifactId>
            <version>2.1.8</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <!-- mybatis-plus end -->
        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.34</version>
            <scope>runtime</scope>
        </dependency>
        <!-- 模板引擎 -->
        <dependency>
            <groupId>org.apache.velocity</groupId>
            <artifactId>velocity-engine-core</artifactId>
            <version>2.0</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.1</version>
        </dependency>
        <!--主从配置依赖-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>2.4.2</version>
        </dependency>
        <!--主从end-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

2. 数据源配置

application.yml

server:
  port: 8555
spring:
  datasource:
    dynamic:
      #默认数据源
      primary: master
      datasource:
        master:
          username: root
          password: 123456
          driver-class-name: com.mysql.jdbc.Driver
          url: jdbc:mysql://43.138.47.28:3307/strength-test?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
        slave_1:
          username: root
          password: 123456
          driver-class-name: com.mysql.jdbc.Driver
          url: jdbc:mysql://43.138.47.28:3308/strength-test?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
logging:
  config: classpath:logback-spring.xml
  level:
    root: debug
mybatis-plus:
  mapper-locations: classpath:/mapper/*.xml
  typeAliasesPackage: com.mysql.masterslave.model.entity

 

3.设置路由key / 查找数据源

目标数据源就是那前3个这个我们是知道的，但是使用的时候是如果查找数据源的呢？

首先，我们定义一个枚举来代表这三个数据源

/**
 * @desc 数据库类型
 */

public enum DBTypeEnum {
    /**
     * 主节点
     */
    MASTER,
    /**
     * 从1
     */
    SLAVE1,
    /**
     * 从2
     */
    SLAVE2;

}

4.新建DataSourceContextHolder

接下来，通过ThreadLocal将数据源设置到每个线程上下文中

/**
 *
 * @author
 */
public class DataSourceContextHolder {

    private static final ThreadLocal<DBTypeEnum> CONTEXT_HOLDER = new ThreadLocal<>();

    private static final AtomicInteger COUNTER = new AtomicInteger(-1);

    public static void set(DBTypeEnum dbType) {
        CONTEXT_HOLDER.set(dbType);
    }

    public static DBTypeEnum get() {
        return CONTEXT_HOLDER.get();
    }

    public static void clear(){
        CONTEXT_HOLDER.remove();
    }

    public static void master() {
        set(DBTypeEnum.MASTER);
        System.out.println("切换到master");
    }

    public static void slave() {
        //  轮询
        int index = COUNTER.getAndIncrement() % 2;
        if (COUNTER.get() > 9999) {
            COUNTER.set(-1);
        }
        if (index == 0) {
            set(DBTypeEnum.SLAVE1);
            System.out.println("切换到slave1");
        } else {
            set(DBTypeEnum.SLAVE2);
            System.out.println("切换到slave2");
        }
    }

}

5.设置路由key

/**
 * 声明路由数据源key
 * @author 
 *
 */
public class MyRoutingDataSource extends AbstractRoutingDataSource {
    @Nullable
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.get();
    }

}

6.多数据源配置

/**
 *
 * @author
 */

@Configuration
public class DataSourceConfig {

    /**
     * 配置主数据源
     *
     * @return 数据源
     */
    @Bean(name = "master")
    @ConfigurationProperties(prefix = "spring.datasource.druid.master" )
    public DataSource masterDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * 配置从数据源
     *
     * @return 数据源
     */
    @Bean(name = "slave1")
    @ConfigurationProperties(prefix = "spring.datasource.druid.slave1")
    public DataSource slave1DataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * 配置从数据源
     *
     * @return 数据源
     */
    @Bean(name = "slave2")
    @ConfigurationProperties(prefix = "spring.datasource.druid.slave2")
    public DataSource slave2DataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    /**
     * 配置路由数据源
     *
     * @param masterDataSource 主节点
     * @param slave1DataSource 从节点
     * @param slave2DataSource 从节点
     * @return 数据源
     */
    @Bean
    public DataSource myRoutingDataSource(@Qualifier("master") DataSource masterDataSource,
                                          @Qualifier("slave1") DataSource slave1DataSource,
                                          @Qualifier("slave2") DataSource slave2DataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>(3);
        targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);
        targetDataSources.put(DBTypeEnum.SLAVE1, slave1DataSource);
        targetDataSources.put(DBTypeEnum.SLAVE2, slave2DataSource);
        MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();
        //设置默认数据源
        myRoutingDataSource.setDefaultTargetDataSource(masterDataSource);
        myRoutingDataSource.setTargetDataSources(targetDataSources);
        return myRoutingDataSource;
    }

}

这里，我们配置了4个数据源，1个master，2两个slave，1个路由数据源。

7.MyBatis配置

/**
 * mybatis 配置
 * @author 
 */
@EnableTransactionManagement
@Configuration
public class MyBatisConfig {

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

    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
        sqlSessionFactory.setDataSource(myRoutingDataSource);
        MybatisConfiguration configuration = new MybatisConfiguration();
        configuration.setJdbcTypeForNull(JdbcType.NULL);
        configuration.setMapUnderscoreToCamelCase(true);
        configuration.setCacheEnabled(false);
        sqlSessionFactory.setConfiguration(configuration);
        return sqlSessionFactory.getObject();
    }

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

由于Spring容器目前有多个数据源，所以我们需要为事务管理器和MyBatis手动指定一个明确的数据源。

8.使用aop实现数据源切换

默认情况下，插入/修改/删除走主库，取查询数据都是走从库。

package com.wenlinshan.masterslavedemo.aop;

import com.wenlinshan.masterslavedemo.config.DataSourceContextHolder;
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.aspectj.lang.annotation.Pointcut;
import org.springframework.stereotype.Component;

/**
 * 设置切面 执行具体方法选择的数据源
 * @author wls
 */
@Aspect
@Component
public class DataSourceAop {

    /**
     * 需要读的方法,切面
     */
    @Pointcut("!@annotation(com.wenlinshan.masterslavedemo.annotation.Master)" +
            "&& (execution(* com.wenlinshan.masterslavedemo.service..*.select*(..)) " +
            "|| execution(* com.wenlinshan.masterslavedemo.service..*.get*(..)))")
    public void readPointcut() {

    }

    /**
     * 写切面
     */
    @Pointcut("@annotation(com.wenlinshan.masterslavedemo.annotation.Master) " +
            "|| execution(* com.wenlinshan.masterslavedemo.service..*.insert*(..))" +
            "|| execution(* com.wenlinshan.masterslavedemo.service..*.save*(..))" +
            "|| execution(* com.wenlinshan.masterslavedemo.service..*.add*(..))" +
            "|| execution(* com.wenlinshan.masterslavedemo.service..*.update*(..))" +
            "|| execution(* com.wenlinshan.masterslavedemo.service..*.edit*(..))" +
            "|| execution(* com.wenlinshan.masterslavedemo.service..*.delete*(..))" +
            "|| execution(* com.wenlinshan.masterslavedemo.service..*.remove*(..))")
    public void writePointcut() {

    }

    @Before("readPointcut()")
    public void read() {
        DataSourceContextHolder.slave();
    }

    @Before("writePointcut()")
    public void write() {
        DataSourceContextHolder.master();
    }

    @After("readPointcut()")
    public void readAfter() {
        DataSourceContextHolder.clear();
    }

    @After("writePointcut()")
    public void writeAfter() {
        DataSourceContextHolder.clear();
    }
}

9、测试

service

@Service
public class TUserServiceImpl extends ServiceImpl<TUserMapper, TUser> implements TUserService {

    @Override
    @DS("")
    public Result add(UserDto user) {
        TUser tUser = new TUser();
        tUser.setName(user.getName());
        tUser.setCreateTime(new Date());
        baseMapper.insert(tUser);
        return new Result();
    }

    @DS("slave_1")
    @Override
    public Result getList() {
        return new Result(baseMapper.selectList(new EntityWrapper<TUser>().orderBy("id")));
    }
}

10.controller

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

    @Autowired
    private TUserService userService;

    @PostMapping("add")
    public Result test(@RequestBody UserDto user){
        System.err.println("user===============>"+user);
        return userService.add(user);
    }

    @PostMapping("list")
    public Result getList(){
        return userService.getList();
    }
}

项目地址https://gitee.com/chenchensky/mybatis-plus-master.git