devbox
代码创新者
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

druid+mybatis 多数据源及读写分离的处理_druiddatasource 读写分离

作者：代码创新者 | 2024-01-30 12:16:32

赞

踩

druiddatasource 读写分离

现如今复杂的业务系统，一般会将数据库按业务拆开，比如产品系统的数据库放在product db中，订单系统的数据库放在order db中...，然后，如果量大了，可能每个库还要考虑做读、写分离，以进一步提高系统性能，下面就来看看如何处理：

核心思路：配置多个数据源，然后利用RoutingDataSource结合AOP来动态切不同的库。

 存在一下几个问题：

  1、配置文件中，多数据源的配置节点如何设计？

  1. #yml文件中进行如下配置
  2. druid:
  3.     type: com.alibaba.druid.pool.DruidDataSource
  4.     study:
  5.       master: #study库的主库名
  6.         url: jdbc:mysql://localhost:3306/study?useSSL=false&characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
  7.         driver-class-name: com.mysql.cj.jdbc.Driver
  8.         username: root
  9.         password: 1234 #加密很好
  10.         initial-size: 5
  11.         min-idle: 1
  12.         max-active: 20
  13.         test-on-borrow: true
  14.       slave: #study库的从库名
  15.         url: jdbc:mysql://localhost:3306/study_slave?useSSL=false&characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
  16.         driver-class-name: com.mysql.cj.jdbc.Driver
  17.         username: root
  18.         password: A1b2c3@def.com
  19.         initial-size: 5
  20.         min-idle: 1
  21.         max-active: 20
  22.         test-on-borrow: true
  23.     product:
  24.       master: #product库的主库名
  25.         url: jdbc:mysql://localhost:3306/product?useSSL=false&characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
  26.         driver-class-name: com.mysql.cj.jdbc.Driver
  27.         username: root
  28.         password: 1234 #加密
  29.         initial-size: 5
  30.         min-idle: 1
  31.         max-active: 20
  32.         test-on-borrow: true
  33.       slave: #product库的从库
  34.         url: jdbc:mysql://localhost:3306/product_slave?useSSL=false&characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&useUnicode=true
  35.         driver-class-name: com.mysql.cj.jdbc.Driver
  36.         username: root
  37.         password: 1234
  38.         initial-size: 5
  39.         min-idle: 1
  40.         max-active: 20
  41.         test-on-borrow: true

上面的配置写法供参数，如果slave节点数要扩展，按这个格式，改造成slave1,slave2... 自行扩展。

  1. package com.aspire.sh.db.config;
  2.  
  3. import com.aspire.sh.db.datasource.DbContextHolder;
  4. import com.aspire.sh.db.datasource.MasterSlaveRoutingDataSource;
  5. import org.springframework.beans.factory.annotation.Value;
  6. import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
  7. import org.springframework.boot.context.properties.ConfigurationProperties;
  8. import org.springframework.context.annotation.Bean;
  9. import org.springframework.context.annotation.Configuration;
  10. import org.springframework.context.annotation.Primary;
  11. import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
  12. import org.springframework.transaction.annotation.EnableTransactionManagement;
  13.  
  14. import javax.sql.DataSource;
  15. import java.util.HashMap;
  16. import java.util.Map;
  17.  
  18.  
  19. @Configuration
  20. @EnableTransactionManagement
  21. public class DataSourceConfiguration {
  22.  
  23.     @Value("${druid.type}")
  24.     private Class<? extends DataSource> dataSourceType;
  25.  
  26.     @Bean(name = "studyMasterDataSource")
  27.     @ConfigurationProperties(prefix = "druid.study.master")
  28.     public DataSource studyMasterDataSource() {
  29.         return DataSourceBuilder.create().type(dataSourceType).build();
  30.     }
  31.  
  32.     @Bean(name = "studySlaveDataSource")
  33.     @ConfigurationProperties(prefix = "druid.study.slave")
  34.     public DataSource studySlaveDataSource1() {
  35.         return DataSourceBuilder.create().type(dataSourceType).build();
  36.     }
  37.  
  38.     @Bean(name = "productMasterDataSource")
  39.     @ConfigurationProperties(prefix = "druid.product.master")
  40.     public DataSource productMasterDataSource() {
  41.         return DataSourceBuilder.create().type(dataSourceType).build();
  42.     }
  43.  
  44.     @Bean(name = "productSlaveDataSource")
  45.     @ConfigurationProperties(prefix = "druid.product.slave")
  46.     public DataSource productSlaveDataSource1() {
  47.         return DataSourceBuilder.create().type(dataSourceType).build();
  48.     }
  49.  
  50.     @Bean(name = "dataSource")
  51.     @Primary
  52.     public AbstractRoutingDataSource dataSource() {
  53.         MasterSlaveRoutingDataSource proxy = new MasterSlaveRoutingDataSource();
  54.         Map<Object, Object> targetDataResources = new HashMap<>();
  55.         targetDataResources.put(DbContextHolder.DbType.PRODUCT_MASTER, productMasterDataSource());
  56.         targetDataResources.put(DbContextHolder.DbType.PRODUCT_SLAVE, productSlaveDataSource1());
  57.         targetDataResources.put(DbContextHolder.DbType.STUDY_MASTER, studyMasterDataSource());
  58.         targetDataResources.put(DbContextHolder.DbType.STUDY_SLAVE, studySlaveDataSource1());
  59.         proxy.setDefaultTargetDataSource(productMasterDataSource());
  60.         proxy.setTargetDataSources(targetDataResources);
  61.         proxy.afterPropertiesSet();
  62.         return proxy;
  63.     }
  64.  
  65. }

注：@Primary一定要在动态数据源上，否则事务回滚无效！

3、根据什么来切换db?

a、用约定的方法前缀，比如：get/query/list开头的约定为读从库，其它为主库，但是这样还要考虑不同业务库的切换（即：何时切换到product库，何时切换到order库，可以再用不同的Scanner来处理，略复杂）

b、用自定义注解来处理，比如 @ProductMaster注解，表示切换到product的master库，这样同时把业务库，以及主还是从，一次性解决了，推荐这种。

这里，我定义了4个注解，代表product，study二个库的主及从。

4、aop在哪里拦截,如何拦截？

service层和mapper层都可以拦截，推荐在服务层拦截，否则如果一个业务方法里，即有读又有写，还得考虑如果遇到事务，要考虑的东西更多。

当然，如果拦截特定的注解，就不用过多考虑在哪个层，只认注解就行（当然，注解还是建议打在服务层上）。

dubbo-starter的一个小坑：spring boot中，只有managed bean才能用aop拦截，而dubbo-starter中的@service注解不是spring中的注解（是阿里package下的自定义注解），生成的service provider实例，aop拦截不到，解决办法，再加一个注解让spring认识它.

Aop拦截类的参考代码如下：

  1. package com.aspire.sh.db.aspect;
  2.  
  3. import com.aspire.sh.db.annotation.ProductMaster;
  4. import com.aspire.sh.db.annotation.ProductSlave;
  5. import com.aspire.sh.db.annotation.StudyMaster;
  6. import com.aspire.sh.db.annotation.StudySlave;
  7. import com.aspire.sh.db.datasource.DbContextHolder;
  8. import org.aspectj.lang.ProceedingJoinPoint;
  9. import org.aspectj.lang.annotation.Around;
  10. import org.aspectj.lang.annotation.Aspect;
  11. import org.slf4j.Logger;
  12. import org.slf4j.LoggerFactory;
  13. import org.springframework.core.Ordered;
  14. import org.springframework.stereotype.Component;
  15.  
  16.  
  17. @Aspect
  18. @Component 
  19. public class MasterSlaveAspect implements Ordered {
  20.  
  21.     public static final Logger logger = LoggerFactory.getLogger(MasterSlaveAspect.class);
  22.  
  23.  
  24.     /**
  25.      * 切换到product主库
  26.      *
  27.      * @param proceedingJoinPoint
  28.      * @param productMaster
  29.      * @return
  30.      * @throws Throwable
  31.      */
  32.     @Around("@annotation(productMaster)")
  33.     public Object proceed(ProceedingJoinPoint proceedingJoinPoint, ProductMaster productMaster) throws Throwable {
  34.         try {
  35.             logger.info("set database connection to product-master only");
  36.             DbContextHolder.setDbType(DbContextHolder.DbType.PRODUCT_MASTER);
  37.             Object result = proceedingJoinPoint.proceed();
  38.             return result;
  39.         } finally {
  40.             DbContextHolder.clearDbType();
  41.             logger.info("restore database connection");
  42.         }
  43.     }
  44.  
  45.  
  46.     /**
  47.      * 切换到product从库
  48.      *
  49.      * @param proceedingJoinPoint
  50.      * @param productSlave
  51.      * @return
  52.      * @throws Throwable
  53.      */
  54.     @Around("@annotation(productSlave)")
  55.     public Object proceed(ProceedingJoinPoint proceedingJoinPoint, ProductSlave productSlave) throws Throwable {
  56.         try {
  57.             logger.info("set database connection to product-slave only");
  58.             DbContextHolder.setDbType(DbContextHolder.DbType.PRODUCT_SLAVE);
  59.             Object result = proceedingJoinPoint.proceed();
  60.             return result;
  61.         } finally {
  62.             DbContextHolder.clearDbType();
  63.             logger.info("restore database connection");
  64.         }
  65.     }
  66.  
  67.     /**
  68.      * 切换到study主库
  69.      *
  70.      * @param proceedingJoinPoint
  71.      * @param studyMaster
  72.      * @return
  73.      * @throws Throwable
  74.      */
  75.     @Around("@annotation(studyMaster)")
  76.     public Object proceed(ProceedingJoinPoint proceedingJoinPoint, StudyMaster studyMaster) throws Throwable {
  77.         try {
  78.             logger.info("set database connection to study-master only");
  79.             DbContextHolder.setDbType(DbContextHolder.DbType.STUDY_MASTER);
  80.             Object result = proceedingJoinPoint.proceed();
  81.             return result;
  82.         } finally {
  83.             DbContextHolder.clearDbType();
  84.             logger.info("restore database connection");
  85.         }
  86.     }
  87.  
  88.     /**
  89.      * 切换到study从库
  90.      *
  91.      * @param proceedingJoinPoint
  92.      * @param studySlave
  93.      * @return
  94.      * @throws Throwable
  95.      */
  96.     @Around("@annotation(studySlave)")
  97.     public Object proceed(ProceedingJoinPoint proceedingJoinPoint, StudySlave studySlave) throws Throwable {
  98.         try {
  99.             logger.info("set database connection to study-slave only");
  100.             DbContextHolder.setDbType(DbContextHolder.DbType.STUDY_SLAVE);
  101.             Object result = proceedingJoinPoint.proceed();
  102.             return result;
  103.         } finally {
  104.             DbContextHolder.clearDbType();
  105.             logger.info("restore database connection");
  106.         }
  107.     }
  108.  
  109.     @Override
  110.     public int getOrder() {
  111.         return 0;
  112.     }
  113. }

5、其它事项

启用类上，一定要排除spring-boot自带的datasource配置，即：

  1. @SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
  2. @EnableAspectJAutoProxy
  3. @ComponentScan("com.aspire.sh")
  4. @MapperScan(basePackages = "com.aspire.sh.dao.mapper")
  5. public class ServiceProvider {
  6.     public static void main(String[] args) {
  7.         SpringApplication.run(ServiceProvider.class, args);
  8.     }
  9. }

6、日志中如何输出格式化且带参数值的sql?

是不是更友好！

方法：加一个mybtais的拦截器即可  (代码块在最前面)

这里面还用了hibernate的一个小工具，用于格式化sql(代码块在最前面)

接下来，把这个拦截器配置在mybatis-config.xml里

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  3.         "http://mybatis.org/dtd/mybatis-3-config.dtd">
  4. <configuration>
  5.     <settings>
  6.         <setting name="cacheEnabled" value="true"/>
  7.     </settings>
  8.  
  9.     <plugins>
  10.         <plugin interceptor="com.aspire.sh.db.interceptor.MybatisInterceptor">
  11.         </plugin>
  12.     </plugins>
  13.  
  14. </configuration>

最后在application.yml里指定mybatis-config.xml所在的路径：

注意：包路径可能会有些差异com.cnblogs.yjmyzz与com.aspire.sh二者取其一

实现方式二：

在pom.xml中增加相关依赖：

  1.         <!-- aop -->
  2.         <dependency>
  3.             <groupId>org.springframework.boot</groupId>
  4.             <artifactId>spring-boot-starter-aop</artifactId>
  5.         </dependency>
  6.         <dependency>
  7.             <groupId>org.springframework</groupId>
  8.             <artifactId>spring-context-support</artifactId>
  9.         </dependency>
  10.         <!-- mysql -->
  11.         <dependency>
  12.             <groupId>mysql</groupId>
  13.             <artifactId>mysql-connector-java</artifactId>
  14.             <scope>runtime</scope>
  15.         </dependency>
  16.         <dependency>
  17.             <groupId>org.springframework</groupId>
  18.             <artifactId>spring-jdbc</artifactId>
  19.         </dependency>
  20.         <!-- druid -->
  21.         <dependency>
  22.             <groupId>com.alibaba</groupId>
  23.             <artifactId>druid-spring-boot-starter</artifactId>
  24.             <version>1.1.10</version>
  25.         </dependency>
  26.         <!--如果不添加此依赖，自定义druid属性则会绑定失败-->
  27.         <dependency>
  28.             <groupId>log4j</groupId>
  29.             <artifactId>log4j</artifactId>
  30.             <version>1.2.17</version>
  31.         </dependency>
  32.         <!-- mybatis -->
  33.         <dependency>
  34.             <groupId>org.mybatis.spring.boot</groupId>
  35.             <artifactId>mybatis-spring-boot-starter</artifactId>
  36.             <version>1.3.2</version>
  37.         </dependency>
  38.         <!-- slf4j -->
  39.         <dependency>
  40.             <groupId>org.slf4j</groupId>
  41.             <artifactId>slf4j-api</artifactId>
  42.         </dependency>

2、配置application.yml 

  1. server:
  2.   port: 8818
  3. spring:
  4.   application:
  5.     name: read-write-separationp
  6.   aop:
  7.     proxy-target-class: true
  8.     auto: true
  9.   datasource:
  10.     type: com.alibaba.druid.pool.DruidDataSourceC3P0Adapter
  11.     druid:
  12.       master:
  13.         url: jdbc:mysql://207.148.33.32:3306/captain?useSSL=true&characterEncoding=UTF-8&serverTimezone=UTC
  14.         username: captainLii
  15.         password: Captain@1689
  16.       slave:
  17.         url: jdbc:mysql://45.32.120.84:3306/captain?useSSL=true&characterEncoding=UTF-8&serverTimezone=UTC
  18.         username: captainLii
  19.         password: Captain@1689
  20.       # 配置初始化大小（默认0）、最小、最大（默认8）
  21.       initial-size: 1
  22.       min-idle: 1
  23.       max-active: 20
  24.       # 配置获取连接等待超时的时间
  25.       max-wait: 60000
  26.       # 是否缓存preparedStatement，也就是PSCache。PSCache对支持游标的数据库性能提升巨大。 默认为false
  27.       pool-prepared-statements: true
  28.       # 要启用PSCache，必须配置大于0，当大于0时，poolPreparedStatements自动触发修改为true。
  29.       max-open-prepared-statements: 20
  30.       # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
  31.       time-between-eviction-runs-millis: 60000
  32.       # 配置一个连接在池中最小和最大生存的时间，单位是毫秒
  33.       min-evictable-idle-time-millis: 300000
  34.       max-evictable-idle-time-millis: 900000
  35.       # 用来检测连接是否有效的sql，要求是一个查询语句，常用select 'x'。
  36.       # 如果validationQuery为null，testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
  37.       validation-query: SELECT 'X'
  38.       # 申请连接时执行validationQuery检测连接是否有效 默认为true
  39.       test-on-borrow: true
  40.       # 归还连接时执行validationQuery检测连接是否有效 默认为false
  41.       test-on-return: false
  42.       # 申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
  43.       test-while-idle: true
  44.  
  45. # Mybatis
  46. mybatis:
  47.   mapper-locations: classpath:mapping/*.xml
  48.   type-aliases-package: com.captain.readwriteseparation.entity

3、 定义数据源枚举类

  1. package com.captain.readwriteseparation.dbconfig;
  2.  
  3. /**
  4.  * @author 
  5.  * @description 数据源枚举
  6.  */
  7. public enum DataSourceTypeEnum {
  8.     master("master"), slave("slave");
  9.     private String value;
  10.  
  11.     DataSourceTypeEnum(String value) {
  12.         this.value = value;
  13.     }
  14.  
  15.     public String getValue() {
  16.         return value;
  17.     }
  18. }

4、设置获取数据源

  1. package com.aspire.sh.dbconfig;
  2.  
  3. /**
  4.  * @description 设置获取数据源
  5.  */
  6. public class DataSourceHolder {
  7.     private static final ThreadLocal contextHolder = new ThreadLocal<>();
  8.  
  9.     /**
  10.      * 设置数据源
  11.      *
  12.      * @param dbTypeEnum
  13.      */
  14.     public static void setDbType(DataSourceTypeEnum dbTypeEnum) {
  15.         contextHolder.set(dbTypeEnum.getValue());
  16.     }
  17.  
  18.     /**
  19.      * 取得当前数据源
  20.      *
  21.      * @return
  22.      */
  23.     public static String getDbType() {
  24.         return (String) contextHolder.get();
  25.     }
  26.  
  27.     /**
  28.      * 清除上下文数据
  29.      */
  30.     public static void clearDbType() {
  31.         contextHolder.remove();
  32.     }
  33. }

 5、数据源切换(切入点和切面)

  1. package com.captain.readwriteseparation.dbconfig;
  2.  
  3. import org.aspectj.lang.annotation.Aspect;
  4. import org.aspectj.lang.annotation.Before;
  5. import org.slf4j.Logger;
  6. import org.slf4j.LoggerFactory;
  7. import org.springframework.stereotype.Component;
  8.  
  9. /**
  10.  * @author splendor.s
  11.  * @description 数据源切换(切入点和切面)
  12.  */
  13. @Aspect
  14. @Component
  15. public class DataSourceAop {
  16.     static Logger logger = LoggerFactory.getLogger(DataSourceAop.class);
  17.  
  18.     @Before("execution(* com.captain.readwriteseparation.mapper.*.insert*(..)) || execution(* com.captain.readwriteseparation.mapper.*.update*(..)) || execution(* com.captain.readwriteseparation.mapper.*.delete*(..))")
  19.     public void setWriteDataSourceType() {
  20.         DataSourceHolder.setDbType(DataSourceTypeEnum.master);
  21.         logger.info("change -------- write ------------");
  22.     }
  23.  
  24.     @Before("execution(* com.captain.readwriteseparation.mapper.*.select*(..)) || execution(* com.captain.readwriteseparation.mapper.*.count*(..))")
  25.     public void setReadDataSourceType() {
  26.         DataSourceHolder.setDbType(DataSourceTypeEnum.slave);
  27.         logger.info("change -------- read ------------");
  28.     }
  29.  
  30. }

6、动态数据源决策

  1. package com.captain.readwriteseparation.dbconfig;
  2.  
  3. import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
  4.  
  5. /**
  6.  * @authorsplendor.s
  7.  * @description 动态数据源决策
  8.  */
  9. public class DynamicDataSource extends AbstractRoutingDataSource {
  10.     @Override
  11.     protected Object determineCurrentLookupKey() {
  12.         return  DataSourceHolder.getDbType();
  13.     }
  14. }

7、数据库（源）配置

  1. package com.aspire.sh.dbconfig;
  2.  
  3. import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceBuilder;
  4. import com.alibaba.druid.support.http.StatViewServlet;
  5. import com.alibaba.druid.support.http.WebStatFilter;
  6. import org.slf4j.Logger;
  7. import org.slf4j.LoggerFactory;
  8. import org.springframework.beans.factory.annotation.Qualifier;
  9. import org.springframework.beans.factory.annotation.Value;
  10. import org.springframework.boot.context.properties.ConfigurationProperties;
  11. import org.springframework.boot.web.servlet.FilterRegistrationBean;
  12. import org.springframework.boot.web.servlet.ServletRegistrationBean;
  13. import org.springframework.context.annotation.Bean;
  14. import org.springframework.context.annotation.Configuration;
  15. import org.springframework.context.annotation.Primary;
  16.  
  17. import javax.sql.DataSource;
  18. import java.util.HashMap;
  19. import java.util.Map;
  20.  
  21. /**
  22.  * @author splendor.s
  23.  * @description 数据库（源）配置
  24.  */
  25. @Configuration
  26. public class DruidDataSourceConfig {
  27.     static Logger logger = LoggerFactory.getLogger(DruidDataSourceConfig.class);
  28.  
  29.     @Value("${spring.datasource.type}")
  30.     private Class<? extends DataSource> dataSourceType;
  31.  
  32.     @Bean
  33.     public ServletRegistrationBean staViewServlet() {
  34.         ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
  35.         Map<String, String> initParams = new HashMap<>();
  36.         //设置servlet初始化参数
  37.         initParams.put("loginUsername", "admin");//登陆名
  38.         initParams.put("loginPassword", "123456");//密码
  39.         initParams.put("allow", "");//默认就是允许所有访问
  40.         initParams.put("deny", "192.168.10.17");//拒绝相对应的id访问
  41.         //加载到容器中
  42.         bean.setInitParameters(initParams);
  43.         return bean;
  44.     }
  45.  
  46.     @Bean
  47.     public FilterRegistrationBean filterRegistrationBean() {
  48.         FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
  49.         filterRegistrationBean.addUrlPatterns("/*");
  50.         filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico," + "/druid/*");
  51.         return filterRegistrationBean;
  52.     }
  53.  
  54.     @Bean(name = "master")
  55.     @ConfigurationProperties(prefix = "spring.datasource.druid.master")
  56.     public DataSource master() {
  57.         logger.info("-------------------- master init ---------------------");
  58.         return DruidDataSourceBuilder.create().build();
  59.     }
  60.  
  61.     @Bean(name = "slave")
  62.     @ConfigurationProperties(prefix = "spring.datasource.druid.slave")
  63.     public DataSource slaveOne() {
  64.         logger.info("-------------------- slave init ---------------------");
  65.         return DruidDataSourceBuilder.create().build();
  66.     }
  67.  
  68.     // slave 多个时，可进行负载（另行处理）
  69.  
  70.     @Bean
  71.     @Primary
  72.     public DataSource multipleDataSource(@Qualifier("master") DataSource master,
  73.                                          @Qualifier("slave") DataSource slave) {
  74.         DynamicDataSource dynamicDataSource = new DynamicDataSource();
  75.         Map<Object, Object> targetDataSources = new HashMap<>();
  76.         targetDataSources.put(DataSourceTypeEnum.master.getValue(), master);
  77.         targetDataSources.put(DataSourceTypeEnum.slave.getValue(), slave);
  78.         dynamicDataSource.setTargetDataSources(targetDataSources);
  79.         dynamicDataSource.setDefaultTargetDataSource(slave);
  80.         return dynamicDataSource;
  81.     }
  82.  
  83. }

8、配置事务管理

  1. package com.aspire.sh.dbconfig;
  2.  
  3. import org.slf4j.Logger;
  4. import org.slf4j.LoggerFactory;
  5. import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration;
  6. import org.springframework.context.annotation.Bean;
  7. import org.springframework.context.annotation.Configuration;
  8. import org.springframework.transaction.annotation.EnableTransactionManagement;
  9. import javax.sql.DataSource;
  10. import javax.annotation.Resource;
  11.  
  12. /**
  13.  * @author splendor.s
  14.  * @description 事务控制
  15.  */
  16. @Configuration
  17. @EnableTransactionManagement
  18. public class DataSourceTransactionManager extends DataSourceTransactionManagerAutoConfiguration {
  19.  
  20.     static Logger logger = LoggerFactory.getLogger(DataSourceTransactionManager.class);
  21.  
  22.     @Resource(name = "master")
  23.     private DataSource dataSource;
  24.  
  25.     /**
  26.      * 自定义事务
  27.      * MyBatis自动参与到spring事务管理中，无需额外配置，只要org.mybatis.spring.SqlSessionFactoryBean引用的数据源与DataSourceTransactionManager引用的数据源一致即可，否则事务管理会不起作用。
  28.      *
  29.      * @return
  30.      */
  31.     @Bean(name = "transactionManager")
  32.     public org.springframework.jdbc.datasource.DataSourceTransactionManager transactionManagers() {
  33.         logger.info("-------------------- transactionManager init ---------------------");
  34.         return new org.springframework.jdbc.datasource.DataSourceTransactionManager(dataSource);
  35.     }
  36. }

实现方式三：

这里配置了4个数据源，1个master，2两个slave，1个路由数据源。前3个数据源都是为了生成第4个数据源，而且后续我们只用这最后一个路由数据源。

  1.  
  2. /**
  3.  * 关于数据源配置，参考SpringBoot官方文档第79章《Data Access》
  4.  * 79. Data Access
  5.  * 79.1 Configure a Custom DataSource
  6.  * 79.2 Configure Two DataSources
  7.  * 这里配置了4个数据源，1个master，2两个slave，1个路由数据源。前3个数据源都是为了生成第4个数据源，而且后续我们只用这最后一个路由数据源。
  8.  */
  9.  
  10. @Configuration
  11. public class DataSourceConfig {
  12.  
  13.     @Bean
  14.     @ConfigurationProperties("spring.datasource.master")
  15.     public DataSource masterDataSource() {
  16.         return DataSourceBuilder.create().build();
  17.     }
  18.  
  19.     @Bean
  20.     @ConfigurationProperties("spring.datasource.slave1")
  21.     public DataSource slave1DataSource() {
  22.         return DataSourceBuilder.create().build();
  23.     }
  24.  
  25.     @Bean
  26.     @ConfigurationProperties("spring.datasource.slave2")
  27.     public DataSource slave2DataSource() {
  28.         return DataSourceBuilder.create().build();
  29.     }
  30.  
  31.     @Bean
  32.     public DataSource myRoutingDataSource(@Qualifier("masterDataSource") DataSource masterDataSource,
  33.                                           @Qualifier("slave1DataSource") DataSource slave1DataSource,
  34.                                           @Qualifier("slave2DataSource") DataSource slave2DataSource) {
  35.         Map<Object, Object> targetDataSources = new HashMap<>();
  36.         targetDataSources.put(DBTypeEnum.MASTER, masterDataSource);
  37.         targetDataSources.put(DBTypeEnum.SLAVE1, slave1DataSource);
  38.         targetDataSources.put(DBTypeEnum.SLAVE2, slave2DataSource);
  39.         MyRoutingDataSource myRoutingDataSource = new MyRoutingDataSource();
  40.         myRoutingDataSource.setDefaultTargetDataSource(masterDataSource);
  41.         myRoutingDataSource.setTargetDataSources(targetDataSources);
  42.         return myRoutingDataSource;
  43.     }
  44.  
  45. }

配置Mybatis指定数据源

  1. /**
  2.  * 由于Spring容器中现在有4个数据源，所以我们需要为事务管理器和MyBatis手动指定一个明确的数据源。
  3.  */
  4. @EnableTransactionManagement
  5. @Configuration
  6. public class MyBatisConfig {
  7.  
  8.     @Resource(name = "myRoutingDataSource")
  9.     private DataSource myRoutingDataSource;
  10.  
  11.     @Bean
  12.     public SqlSessionFactory sqlSessionFactory() throws Exception {
  13.         SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
  14.         sqlSessionFactoryBean.setDataSource(myRoutingDataSource);
  15.         sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml"));
  16.         return sqlSessionFactoryBean.getObject();
  17.     }
  18.  
  19.     @Bean
  20.     public PlatformTransactionManager platformTransactionManager() {
  21.         return new DataSourceTransactionManager(myRoutingDataSource);
  22.     }
  23. }

定义一个枚举类来代表这三个数据源

  1.  
  2.  
  3. /**
  4.  * 定义一个枚举来代表这三个数据源
  5.  */
  6. public enum DBTypeEnum {
  7.     MASTER, SLAVE1, SLAVE2;
  8. }

通过ThreadLocal将数据源绑定到每个线程上下文中

  1. /**
  2.  * 通过ThreadLocal将数据源设置到每个线程上下文中
  3.  */
  4. public class DBContextHolder {
  5.  
  6.     private static final ThreadLocal<DBTypeEnum> contextHolder = new ThreadLocal<>();
  7.  
  8.     private static final AtomicInteger counter = new AtomicInteger(-1);
  9.  
  10.     public static void set(DBTypeEnum dbType) {
  11.         contextHolder.set(dbType);
  12.     }
  13.  
  14.     public static DBTypeEnum get() {
  15.         return contextHolder.get();
  16.     }
  17.  
  18.     public static void master() {
  19.         set(DBTypeEnum.MASTER);
  20.         System.out.println("切换到master");
  21.     }
  22.  
  23.     public static void slave() {
  24.         //  轮询
  25.         int index = counter.getAndIncrement() % 2;
  26.         if (counter.get() > 9999) {
  27.             counter.set(-1);
  28.         }
  29.         if (index == 0) {
  30.             set(DBTypeEnum.SLAVE1);
  31.             System.out.println("切换到slave1");
  32.         }else {
  33.             set(DBTypeEnum.SLAVE2);
  34.             System.out.println("切换到slave2");
  35.         }
  36.     }
  37.  
  38. }

通过Aop的前置通知来设置要使用的路由key（数据源）

  1. /**
  2.  * 默认情况下，所有的查询都走从库，插入/修改/删除走主库。我们通过方法名来区分操作类型（CRUD）
  3.  *
  4.  */
  5. @Aspect
  6. @Component
  7. public class DataSourceAop {
  8.  
  9.     @Pointcut("!@annotation(huaxin.annotation.Master) " +
  10.             "&& (execution(* huaxin.service.*.select*(..)) " +
  11.             "|| execution(* huaxin.service..*.find*(..)))")
  12.     public void readPointcut() {
  13.  
  14.     }
  15.  
  16.     @Pointcut("@annotation(huaxin.annotation.Master) " +
  17.             "|| execution(* huaxin.service..*.save*(..)) " +
  18.             "|| execution(* huaxin.service..*.add*(..)) " +
  19.             "|| execution(* huaxin.service..*.update*(..)) " +
  20.             "|| execution(* huaxin.service..*.edit*(..)) " +
  21.             "|| execution(* huaxin..*.delete*(..)) " +
  22.             "|| execution(* huaxin..*.remove*(..))")
  23.     public void writePointcut() {
  24.  
  25.     }
  26.  
  27.     @Before("readPointcut()")
  28.     public void read() {
  29.         DBContextHolder.slave();
  30.     }
  31.  
  32.     @Before("writePointcut()")
  33.     public void write() {
  34.         DBContextHolder.master();
  35.     }
  36.  
  37.  
  38.     /**
  39.      * 另一种写法：if...else...  判断哪些需要读从数据库，其余的走主数据库
  40.      */
  41. //    @Before("execution(* com.cjs.example.service.impl.*.*(..))")
  42. //    public void before(JoinPoint jp) {
  43. //        String methodName = jp.getSignature().getName();
  44. //
  45. //        if (StringUtils.startsWithAny(methodName, "get", "select", "find")) {
  46. //            DBContextHolder.slave();
  47. //        }else {
  48. //            DBContextHolder.master();
  49. //        }
  50. //    }
  51. }

获取当前线程上绑定的路由key

  1. /**
  2.  * 获取路由key
  3.  */
  4. public class MyRoutingDataSource extends AbstractRoutingDataSource {
  5.     @Nullable
  6.     @Override
  7.     protected Object determineCurrentLookupKey() {
  8.         return DBContextHolder.get();
  9.     }
  10. }

特殊情况下我们需要强制读主库，针对这种情况，我们定义一个注解，用该注解标注的就读主库

public @interface Master {}

给查询所有添加@Master注解

启动入口加上@MapperScan("xx.xx")

本文内容由网友自发贡献，转载请注明出处：【wpsshop博客】
推荐阅读