首页 > 编程语言 > 带你了解mybatis如何实现读写分离
2022
01-06

带你了解mybatis如何实现读写分离

1、spring aop实现

首先application-test.yml增加如下数据源的配置

spring:
  datasource:
    master:
      jdbc-url: jdbc:mysql://master域名:3306/test
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver
    slave1:
      jdbc-url: jdbc:mysql://slave域名:3306/test
      username: root   # 只读账户
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver
    slave2:
      jdbc-url: jdbc:mysql://slave域名:3306/test
      username: root   # 只读账户
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver

package com.cjs.example.enums;
public enum DBTypeEnum {
    MASTER, SLAVE1, SLAVE2;
}

定义ThreadLocal上下文，将当前线程的数据源进行动态修改

public class DBContextHolder {
    private static  volatile ThreadLocal<DBTypeEnum> contextHolder = new ThreadLocal<>();
    public static synchronized void set(DBTypeEnum dbType) {
        contextHolder.set(dbType);
    }
    public static synchronized DBTypeEnum get() {
        return contextHolder.get();
    }
    public static void master() {
        set(DBTypeEnum.MASTER);
    }
    public static void slave() {
        set(DBTypeEnum.SLAVE1);
    }
    public static void slave2(){ set(DBTypeEnum.SLAVE2); }
    // 清除数据源名
    public static void clearDB() {
        contextHolder.remove();
    }
}

重写mybatis数据源路由接口，在此修改数据源为我们上一块代码设置的上下文的数据源

public class MyRoutingDataSource extends AbstractRoutingDataSource {
    @Nullable
    @Override
    protected Object determineCurrentLookupKey() {
        DBTypeEnum dbTypeEnum=DBContextHolder.get();
        return dbTypeEnum;
    }
}

将yml配置的多数据源手动指定注入

@Configuration
public class DataSourceConfig {
    @Bean
    @ConfigurationProperties("spring.datasource.master")
    public DataSource masterDataSource() {
        return DataSourceBuilder.create().build();
    }
    @Bean
    @ConfigurationProperties("spring.datasource.slave1")
    public DataSource slave1DataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
                                          @Qualifier("slave1DataSource") DataSource slave1DataSource) {
        Map<Object, Object> targetDataSources = new HashMap<>();
        targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);
        targetDataSources.put(DBTypeEnum.SLAVE1, slave1DataSource);
        MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();
        myRoutingDataSource.setDefaultTargetDataSource(masterDataSource);
        myRoutingDataSource.setTargetDataSources(targetDataSources);
        return myRoutingDataSource;
    }
}

sqlsession注入以上我们配置的datasource路由

@EnableTransactionManagement
@Configuration
@Import({TableSegInterceptor.class})
public class MyBatisConfig {
    @Resource(name = "myRoutingDataSource")
    private DataSource myRoutingDataSource;
    @Autowired
    private MybatisConfigProperty mybatisConfigProperty;
    @Autowired
    private TableSegInterceptor tableSegInterceptor;
    @Bean
    public SqlSessionFactory sqlSessionFactory() throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(myRoutingDataSource);
        // SpringBoot项目集成mybatis打包为jar运行时setTypeAliasesPackage无效解决
        VFS.addImplClass(SpringBootVFS.class);
        sqlSessionFactoryBean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources(mybatisConfigProperty.getMapperLocations()));
        sqlSessionFactoryBean.setTypeAliasesPackage(mybatisConfigProperty.getTypeAliasesPackage());
        sqlSessionFactoryBean.setConfigLocation(
                new PathMatchingResourcePatternResolver().getResource(mybatisConfigProperty.getConfigLocation()));
        sqlSessionFactoryBean.setPlugins(new Interceptor[]{tableSegInterceptor});
        return sqlSessionFactoryBean.getObject();
    }
    @Bean
    public PlatformTransactionManager platformTransactionManager() {
        return new DataSourceTransactionManager(myRoutingDataSource);
    }
}

spring aop拦截指定前缀的service方法，并设置对应所属的上下文

@Aspect
@Component
public class DataSourceAop {
    @Pointcut("!@annotation(com.ask.student.interceptor.annotation.Master) " +
            "&& (execution(* com.ask.student.service..*.select*(..)) " +
            "|| execution(* com.ask.student.service..*.get*(..))" +
            "|| execution(* com.ask.student.service..*.find*(..))" +
            ")")
    public void readPointcut() {
    }
    @Pointcut("@annotation(com.ask.student.interceptor.annotation.Master) " +
            "|| execution(* com.ask.student.service..*.insert*(..)) " +
            "|| execution(* com.ask.student.service..*.clean*(..)) " +
            "|| execution(* com.ask.student.service..*.reset*(..)) " +
            "|| execution(* com.ask.student.service..*.add*(..)) " +
            "|| execution(* com.ask.student.service..*.update*(..)) " +
            "|| execution(* com.ask.student.service..*.edit*(..)) " +
            "|| execution(* com.ask.student.service..*.delete*(..)) " +
            "|| execution(* com.ask.student.service..*.remove*(..))")
    public void writePointcut() {
    }
    @Before("readPointcut()")
    public void read() {
        DBContextHolder.slave();
    }
    @Before("writePointcut()")
    public void write() {
        DBContextHolder.master();
    }
    @After("readPointcut()||writePointcut()")
    public void afterSwitchDS(){
        DBContextHolder.clearDB();
    }
}

以上最后一个方法的作用，在拦截器中获取后及时清除避免导致来回切换当前线程变量延迟问题导致某些操作的数据源错误

DBContextHolder.clearDB();

@After("readPointcut()||writePointcut()")

public void afterSwitchDS(){

DBContextHolder.clearDB();

}

2、mybatis-plus的实现方式

这个方式配置简单，代码少，很多事情mybatis-plus都已经做好了，推荐使用

yml配置如下

  datasource:
    dynamic:
      primary: master  #设置默认的数据源或者数据源组,默认值即为master
      strict: false #设置严格模式,默认false不启动. 启动后在未匹配到指定数据源时候会抛出异常,不启动则使用默认数据源.
      datasource:
        master:
          url: jdbc:mysql://xxx:3306/db0?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
          username: admin
          password: 123456
          driver-class-name: com.mysql.cj.jdbc.Driver
          type: com.zaxxer.hikari.HikariDataSource
          hikari:
            minimum-idle: 5
            maximum-pool-size: 15
            auto-commit: true
            idle-timeout: 30000
            pool-name: springHikariCP
            max-lifetime: 1800000
            connection-timeout: 30000
            connection-test-query: SELECT 1
        slave1:
          url: jdbc:mysql://xxx:3306/db2?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
          username: admin
          password: 123456
          driver-class-name: com.mysql.cj.jdbc.Driver
          type: com.zaxxer.hikari.HikariDataSource
          hikari:
            minimum-idle: 5
            maximum-pool-size: 15
            auto-commit: true
            idle-timeout: 30000
            pool-name: springHikariCP
            max-lifetime: 1800000
            connection-timeout: 30000
            connection-test-query: SELECT 1
        slave2:
          url: jdbc:mysql://xxx:3306/db3?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull&serverTimezone=Asia/Shanghai
          username: admin
          password: 123456
          driver-class-name: com.mysql.cj.jdbc.Driver
          type: com.zaxxer.hikari.HikariDataSource
          hikari:
            minimum-idle: 5
            maximum-pool-size: 15
            auto-commit: true
            idle-timeout: 30000
            pool-name: springHikariCP
            max-lifetime: 1800000
            connection-timeout: 30000
            connection-test-query: SELECT 1

使用起来非常简单，只需要加上这个master的注解即可

    @Override
    @DS("master")
    public DestMedia getOneByCodeFromEpg(String code) {
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.eq("code", code);
        return super.getOne(queryWrapper);
    }

总结

本篇文章就到这里了，希望能给你带来帮助，也希望您能够多多关注自学编程网的更多内容！

编程技巧