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

springboot+mybatisplus+mysql双数据源动态切换（详细版）_mybatis 双数据源切换

作者：alg789 | 2024-01-30 12:49:41

赞

踩

mybatis 双数据源切换

此项目也是借助网上各种双数据源动态切换改编的（参考人人代码开源），暂没考虑数据库事务。

1、首先引入各种jar，springboot、mybatisplus等，配置pom文件如下：

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <project xmlns="http://maven.apache.org/POM/4.0.0"
  3.          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  4.          xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  5.     <modelVersion>4.0.0</modelVersion>
  6.     <groupId>com.hualife</groupId>
  7.     <artifactId>springboot</artifactId>
  8.     <version>1.0-SNAPSHOT</version>
  9.     <parent>
  10.         <groupId>org.springframework.boot</groupId>
  11.         <artifactId>spring-boot-starter-parent</artifactId>
  12.         <version>2.7.5</version>
  13.     </parent>
  14.     <properties>
  15.         <java.version>1.8</java.version>
  16.         <mybatis.spring.boot.version>2.2.2</mybatis.spring.boot.version>
  17.         <pagehelper.spring.boot.version>1.4.5</pagehelper.spring.boot.version>
  18.         <druid.version>1.2.22</druid.version>
  19.         <commons.io.version>2.11.0</commons.io.version>
  20.         <commons.configuration.version>1.10</commons.configuration.version>
  21.         <mysql.version>8.0.30</mysql.version>
  22.         <druid.version>1.2.13</druid.version>
  23.         <mybatisplus.version>3.5.2</mybatisplus.version>
  24.     </properties>
  25.     <dependencies>
  26.         <dependency>
  27.             <groupId>org.springframework.boot</groupId>
  28.             <artifactId>spring-boot-starter-test</artifactId>
  29.             <scope>test</scope>
  30.         </dependency>
  31.         <dependency>
  32.             <groupId>org.springframework.boot</groupId>
  33.             <artifactId>spring-boot-starter-web</artifactId>
  34.         </dependency>
  35.         <dependency>
  36.             <groupId>com.github.pagehelper</groupId>
  37.             <artifactId>pagehelper-spring-boot-starter</artifactId>
  38.             <version>${pagehelper.spring.boot.version}</version>
  39.         </dependency>
  40.         <dependency>
  41.             <groupId>com.alibaba</groupId>
  42.             <artifactId>druid</artifactId>
  43.             <version>${druid.version}</version>
  44.         </dependency>
  45.         <!-- mysql驱动 -->
  46.         <dependency>
  47.             <groupId>mysql</groupId>
  48.             <artifactId>mysql-connector-java</artifactId>
  49.             <version>${mysql.version}</version>
  50.         </dependency>
  51.         <dependency>
  52.             <groupId>com.alibaba</groupId>
  53.             <artifactId>druid-spring-boot-starter</artifactId>
  54.             <version>1.2.4</version>
  55.             <exclusions>
  56.                 <exclusion>
  57.                     <artifactId>druid</artifactId>
  58.                     <groupId>com.alibaba</groupId>
  59.                 </exclusion>
  60.             </exclusions>
  61.         </dependency>
  62.         <dependency>
  63.             <groupId>com.baomidou</groupId>
  64.             <artifactId>mybatis-plus-boot-starter</artifactId>
  65.             <version>${mybatisplus.version}</version>
  66.             <exclusions>
  67.                 <exclusion>
  68.                     <groupId>com.baomidou</groupId>
  69.                     <artifactId>mybatis-plus-generator</artifactId>
  70.                 </exclusion>
  71.                 <exclusion>
  72.                     <artifactId>jsqlparser</artifactId>
  73.                     <groupId>com.github.jsqlparser</groupId>
  74.                 </exclusion>
  75.                 <exclusion>
  76.                     <artifactId>mybatis</artifactId>
  77.                     <groupId>org.mybatis</groupId>
  78.                 </exclusion>
  79.             </exclusions>
  80.         </dependency>
  81.         <dependency>
  82.             <groupId>org.projectlombok</groupId>
  83.             <artifactId>lombok</artifactId>
  84.             <version>1.16.22</version>
  85.         </dependency>
  86.         <dependency>
  87.             <groupId>org.apache.commons</groupId>
  88.             <artifactId>commons-lang3</artifactId>
  89.         </dependency>
  90.         <dependency>
  91.             <groupId>junit</groupId>
  92.             <artifactId>junit</artifactId>
  93.             <version>4.12</version>
  94.             <scope>test</scope>
  95.         </dependency>
  96.     </dependencies>
  97.     <build>
  98.         <plugins>
  99.             <plugin>
  100.                 <groupId>org.springframework.boot</groupId>
  101.                 <artifactId>spring-boot-maven-plugin</artifactId>
  102.                 <version>2.7.5</version>
  103.             </plugin>
  104.         </plugins>
  105.     </build>
  106. </project>

2、在yml中配置数据源信息如下（可多配置一些数据库连接池信息，自行百度吧有很多可以借鉴）：

  1. server:
  2.   port: 8080
  3. spring:
  4.   datasource:
  5.     dynamic:
  6.       datasource:
  7.         master:
  8.           #MySQL配置
  9.           driver-class-name: com.mysql.cj.jdbc.Driver
  10.           url: jdbc:mysql://localhost:3306/DB1?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true
  11.           username: root
  12.           password:
  13.           initial-size: 2
  14.           max-active: 20
  15.           min-idle: 1
  16.         slave:
  17.           #MySQL配置
  18.           driver-class-name: com.mysql.cj.jdbc.Driver
  19.           url: jdbc:mysql://localhost:3306/DB2?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true
  20.           username: root
  21.           password:
  22.           initial-size: 2
  23.           max-active: 20
  24.           min-idle: 1
  25. mybatis-plus:
  26.   configuration:
  27.     log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  28.   mapper-locations: classpath:mapper/**/*.xml

3、建数据库，建表（不会的百度吧），并且生成对应的Java实体及mapper及xml等（可使用开源版人人代码生成器）。

4、搭建springboot项目，项目结构如下图（除了config以外，其他的包应该都知道是啥）：

 

5、在config目录下创建Java类，CurrDataSource.java（注解类，可用于项目中区分数据源），DataSourceAspect.java(切面类，根据切面选择不同的数据源)，DataSourceFactory.java（数据源工厂，包含数据库连接池信息），DataSourceProperties.java（数据源信息封装的实体类），DBTypeName.java（数据源名称常量），DynamicContextHolder.java（数据源上下文切换用的，实际是队列），
DynamicDataSourceProperties.java(双数据源信息对应的map，具体看下文中的代码吧)，MyDynamicDataSourceConfig.java（动态数据源配置类），MyRoutingDataSource.java(数据源路由类)

6、数据源切换的注解：

  1. import java.lang.annotation.*;
  2. /**
  3.  * 多数据源注解
  4.  * 自己项目中添加
  5.  */
  6. @Target({ElementType.METHOD, ElementType.TYPE})
  7. @Retention(RetentionPolicy.RUNTIME)
  8. @Documented
  9. @Inherited
  10. public @interface CurrDataSource {
  11.     String value() default "master";
  12. }

7、aop切面类：

  1. import lombok.extern.slf4j.Slf4j;
  2. import org.aspectj.lang.ProceedingJoinPoint;
  3. import org.aspectj.lang.annotation.Around;
  4. import org.aspectj.lang.annotation.Aspect;
  5. import org.aspectj.lang.annotation.Pointcut;
  6. import org.aspectj.lang.reflect.MethodSignature;
  7. import org.springframework.core.Ordered;
  8. import org.springframework.core.annotation.AnnotationUtils;
  9. import org.springframework.core.annotation.Order;
  10. import org.springframework.stereotype.Component;
  11. import java.lang.reflect.Method;
  12. /**
  13.  * 多数据源，切面处理类
  14.  */
  15. @Aspect
  16. @Component
  17. @Order(Ordered.HIGHEST_PRECEDENCE)
  18. @Slf4j
  19. public class DataSourceAspect {
  20.  
  21.     @Pointcut("@annotation(com.hualife.modules.config.CurrDataSource) "  //方法包含注解
  22.             +"|| @within(com.hualife.modules.config.CurrDataSource)"    //类包含注解
  23.             +"|| within(com.baomidou.mybatisplus.core.mapper.BaseMapper+)")
  24.   // 切BaseMapper及其子类，因为在mapper加注解但调用父类的方法切不到，故加了within，这样就可以切到父类了
  25.   //其实只要within表达式就行，因为不管是service的impl还是mapper最终调的都是mapper的方法
  26.   public void dSPointCut() {
  27.     }
  28.  
  29.     @Around("dSPointCut()")
  30.     public Object around(ProceedingJoinPoint point) throws Throwable {
  31.         MethodSignature signature = (MethodSignature) point.getSignature();
  32.         Class targetClass = point.getTarget().getClass();
  33.         Method method = signature.getMethod();
  34.         log.info("执行数据库操作的类是：{},函数是：{}", targetClass.getName(), method.getName());
  35. //        CurrDataSource targetDataSource = (CurrDataSource) targetClass.getAnnotation(CurrDataSource.class);
  36.         CurrDataSource targetDataSource = AnnotationUtils.findAnnotation(targetClass, CurrDataSource.class);//获取目标类上注解
  37.         CurrDataSource methodDataSource = method.getAnnotation(CurrDataSource.class);//获取目标函数上注解
  38.         if (targetDataSource != null || methodDataSource != null) {
  39.             log.info(targetDataSource + "===========" + methodDataSource);
  40.             String value;
  41.             if (methodDataSource != null) {
  42.                 value = methodDataSource.value();
  43.             } else {
  44.                 value = targetDataSource.value();
  45.             }
  46.             DynamicContextHolder.push(value);
  47.         } else {
  48.             log.info("执行数据库操作的类及其函数上没有@CurrDataSource，而这个执行数据库操作的函数属于BaseMapper及其子类下的函数，故调用master");
  49.             DynamicContextHolder.push(DBTypeName.MASTER);
  50.         }
  51.         try {
  52.             return point.proceed();
  53.         } finally {
  54.             DynamicContextHolder.poll();
  55.             log.info("clean datasource");
  56.         }
  57.     }
  58. }

8、数据源工厂：

  1. import com.alibaba.druid.pool.DruidDataSource;
  2. import java.sql.SQLException;
  3. /**
  4.  * DruidDataSource
  5.  */
  6. public class DataSourceFactory {
  7.     public static DruidDataSource buildDruidDataSource(DataSourceProperties properties) {
  8.         DruidDataSource druidDataSource = new DruidDataSource();
  9.         druidDataSource.setDriverClassName(properties.getDriverClassName());
  10.         druidDataSource.setUrl(properties.getUrl());
  11.         druidDataSource.setUsername(properties.getUsername());
  12.         druidDataSource.setPassword(properties.getPassword());
  13.  
  14.         druidDataSource.setInitialSize(properties.getInitialSize());
  15.         druidDataSource.setMaxActive(properties.getMaxActive());
  16.         druidDataSource.setMinIdle(properties.getMinIdle());
  17.         druidDataSource.setMaxWait(properties.getMaxWait());
  18.         druidDataSource.setTimeBetweenEvictionRunsMillis(properties.getTimeBetweenEvictionRunsMillis());
  19.         druidDataSource.setMinEvictableIdleTimeMillis(properties.getMinEvictableIdleTimeMillis());
  20.         druidDataSource.setMaxEvictableIdleTimeMillis(properties.getMaxEvictableIdleTimeMillis());
  21.         druidDataSource.setValidationQuery(properties.getValidationQuery());
  22.         druidDataSource.setValidationQueryTimeout(properties.getValidationQueryTimeout());
  23.         druidDataSource.setTestOnBorrow(properties.isTestOnBorrow());
  24.         druidDataSource.setTestOnReturn(properties.isTestOnReturn());
  25.         druidDataSource.setPoolPreparedStatements(properties.isPoolPreparedStatements());
  26.         druidDataSource.setMaxOpenPreparedStatements(properties.getMaxOpenPreparedStatements());
  27.         druidDataSource.setSharePreparedStatements(properties.isSharePreparedStatements());
  28.         try {
  29.             // druidDataSource.setFilters(properties.getFilters());
  30.             druidDataSource.init();
  31.         } catch (SQLException e) {
  32.             e.printStackTrace();
  33.         }
  34.         return druidDataSource;
  35.     }
  36. }

9、多数据源属性类：

  1. import lombok.AllArgsConstructor;
  2. import lombok.Data;
  3. import lombok.NoArgsConstructor;
  4. /**
  5.  * 多数据源属性，yml
  6.  */
  7. @Data
  8. @AllArgsConstructor
  9. @NoArgsConstructor
  10. public class DataSourceProperties {
  11.     private String driverClassName;
  12.     private String url;
  13.     private String username;
  14.     private String password;
  15.     /**
  16.      * Druid默认参数
  17.      */
  18.     private int initialSize = 2;
  19.     private int maxActive = 10;
  20.     private int minIdle = -1;
  21.     private long maxWait = 60 * 1000L;
  22.     private long timeBetweenEvictionRunsMillis = 60 * 1000L;
  23.     private long minEvictableIdleTimeMillis = 1000L * 60L * 30L;
  24.     private long maxEvictableIdleTimeMillis = 1000L * 60L * 60L * 7;
  25.     private String validationQuery = "select 1";
  26.     private int validationQueryTimeout = -1;
  27.     private boolean testOnBorrow = false;
  28.     private boolean testOnReturn = false;
  29.     private boolean testWhileIdle = true;
  30.     private boolean poolPreparedStatements = false;
  31.     private int maxOpenPreparedStatements = -1;
  32.     private boolean sharePreparedStatements = false;
  33.     private String filters = "stat,wall";
  34. }

10、数据库名字常量：

  1. /**
  2.  * 数据库名字常量
  3.  */
  4. public class DBTypeName {
  5.     public static final String MASTER = "master";
  6.     public static final String SLAVE = "slave";
  7. }

11、多数据源上下文 ，用来放置数据源名字：

  1. import java.util.ArrayDeque;
  2. import java.util.Deque;
  3. /**
  4.  * 多数据源上下文
  5.  * 用来放置数据源
  6.  */
  7. public class DynamicContextHolder {
  8.     private static final ThreadLocal<Deque<String>> HOLDER = new ThreadLocal<Deque<String>>() {
  9.         @Override
  10.         protected Deque<String> initialValue() {
  11.             return new ArrayDeque<String>();
  12.         }
  13.     };
  14.     /**
  15.      * 获得当前线程数据源
  16.      * @return 数据源名称
  17.      */
  18.     public static String peek() {
  19.         return HOLDER.get().peek();
  20.     }
  21.  
  22.     /**
  23.      * 设置当前线程数据源
  24.      * @param dataSource 数据源名称
  25.      */
  26.     public static void push(String dataSource) {
  27.         HOLDER.get().push(dataSource);
  28.     }
  29.     /**
  30.      * 清空当前线程数据源
  31.      */
  32.     public static void poll() {
  33.         Deque<String> deque = HOLDER.get();
  34.         deque.poll();
  35.         if (deque.isEmpty()) {
  36.             HOLDER.remove();
  37.         }
  38.     }
  39. }

12、多数据源属性类（把yml中多数据源信息对应成map）：

  1. import org.springframework.boot.context.properties.ConfigurationProperties;
  2. import java.util.LinkedHashMap;
  3. import java.util.Map;
  4. /**
  5.  * 多数据源属性
  6.  */
  7. @ConfigurationProperties(prefix = "spring.datasource.dynamic")
  8. public class DynamicDataSourceProperties {
  9.     private Map<String, DataSourceProperties> datasource = new LinkedHashMap<>();
  10.     public Map<String, DataSourceProperties> getDatasource() {
  11.         return datasource;
  12.     }
  13.     public void setDatasource(Map<String, DataSourceProperties> datasource) {
  14.         this.datasource = datasource;
  15.     }
  16. }

13、多数据源动态切换配置类：

  1. import com.alibaba.druid.pool.DruidDataSource;
  2. import com.baomidou.mybatisplus.core.MybatisConfiguration;
  3. import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
  4. import lombok.extern.slf4j.Slf4j;
  5. import org.apache.ibatis.session.SqlSessionFactory;
  6. import org.apache.ibatis.type.JdbcType;
  7. import org.springframework.beans.factory.annotation.Autowired;
  8. import org.springframework.beans.factory.annotation.Qualifier;
  9. import org.springframework.boot.context.properties.ConfigurationProperties;
  10. import org.springframework.boot.context.properties.EnableConfigurationProperties;
  11. import org.springframework.context.annotation.Bean;
  12. import org.springframework.context.annotation.Configuration;
  13. import java.util.HashMap;
  14. import java.util.Map;
  15.  
  16. /**
  17.  * 配置多数据源
  18.  */
  19. @Slf4j
  20. @Configuration
  21. @EnableConfigurationProperties(DynamicDataSourceProperties.class)
  22. public class MyDynamicDataSourceConfig {
  23.     @Autowired
  24.     private DynamicDataSourceProperties dynamicDataSourceProperties;
  25.  
  26.     @Bean(name = "dataSourceProperties")
  27.     @ConfigurationProperties(prefix = "spring.datasource.dynamic.datasource.master")
  28.     public DataSourceProperties dataSourceProperties() {
  29.         return new DataSourceProperties();
  30.     }
  31.  
  32.     @Bean
  33.     public MyRoutingDataSource dynamicDataSource(@Qualifier("dataSourceProperties") DataSourceProperties dataSourceProperties) {
  34.         MyRoutingDataSource dynamicDataSource = new MyRoutingDataSource();
  35.         Map<String, DataSourceProperties> dataSourcePropertiesMap = dynamicDataSourceProperties.getDatasource();
  36.         Map<Object, Object> targetDataSources = new HashMap<>(dataSourcePropertiesMap.size());
  37.         dataSourcePropertiesMap.forEach((k, v) -> {
  38.             DruidDataSource druidDataSource = DataSourceFactory.buildDruidDataSource(v);
  39.             targetDataSources.put(k, druidDataSource);
  40.         });
  41.         dynamicDataSource.setTargetDataSources(targetDataSources);
  42.         //默认数据源
  43.         DruidDataSource defaultDataSource = DataSourceFactory.buildDruidDataSource(dataSourceProperties);
  44.         dynamicDataSource.setDefaultTargetDataSource(defaultDataSource);
  45.         return dynamicDataSource;
  46.     }
  47.  
  48.     @Bean("sqlSessionFactory")
  49.     public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSourceProperties") DataSourceProperties dataSourceProperties) throws Exception {
  50.         MybatisSqlSessionFactoryBean sqlSessionFactory = new MybatisSqlSessionFactoryBean();
  51.         sqlSessionFactory.setDataSource(dynamicDataSource(dataSourceProperties));
  52.         MybatisConfiguration configuration = new MybatisConfiguration();
  53.         configuration.setJdbcTypeForNull(JdbcType.NULL);
  54.         configuration.setMapUnderscoreToCamelCase(true);
  55.         configuration.setCacheEnabled(false);
  56.         sqlSessionFactory.setConfiguration(configuration);
  57.         return sqlSessionFactory.getObject();
  58.     }
  59. }

14、多数据源路由：

  1. import lombok.extern.slf4j.Slf4j;
  2. import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
  3. /**
  4.  * 多数据源
  5.  */
  6. @Slf4j
  7. public class MyRoutingDataSource extends AbstractRoutingDataSource {
  8.     @Override
  9.     protected Object determineCurrentLookupKey() {
  10.         String dataSource = DynamicContextHolder.peek();
  11.         log.info("使用数据源：{}", dataSource);
  12.         return dataSource;
  13.     }
  14. }

15、多数据源路由理解：参考下图（借用别人的图）銆怉bstractRoutingDataSource銆戝垎鏋� - zzsuje - 鍗氬鍥�

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/article/detail/46555
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号