Spring应用-3-SpringBoot+Mybatis_Plus+Druid实现多数据源动态切换

目录

• 一. 前言
• 二. 依赖和配置文件
• 三. 配置类和工具类
• 四. 使用
• 五. 扩展使用方式：将数据源标识放入Cookie中

一. 前言

1. 做多数据源动态切换的起因是因为在工作中需要做一个从多个数据库中获取日志，然后进行分析的工具而来。因为需要一个SQL在多个数据库都执行一遍，当时看到网上有相关的帖子给出了使用Mybatis_Plus的@DS()注解来实现多数据源，比如《SpringBoot实现多数据源的两种方式》。虽然对我所需要的可能并不是很适合，但是确实大有启发，后来又看到了这篇《SpringBoot配置多数据源并动态切换》，学习了一下之后做了一版自己可用的。
2. 扩展阅读
   • 《spring boot使用AbstractRoutingDataSource实现动态数据源切换》
   • 《MyBatis源码解读-SqlSession执行流程》
   • 《Spring.动态数据源AbstractRoutingDataSource（分库组件，原理分析，源码，实现代码）》

二. 依赖和配置文件

1. Maven依赖

   <?xml version="1.0" encoding="UTF-8"?>
   <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
       <parent>
           <groupId>org.springframework.boot</groupId>
           <artifactId>spring-boot-starter-parent</artifactId>
           <version>2.4.0</version>
           <relativePath/>
       </parent>
   
       <modelVersion>4.0.0</modelVersion>
       <groupId>com.xxxx.xxxx</groupId>
       <artifactId>DynamicDataRSource</artifactId>
       <version>0.0.1-SNAPSHOT</version>
       <name>DynamicDataRSource</name>
       <description>DynamicDataRSource</description>
   
       <dependencies>
           <!-- SpringBoot 组件 -->
           <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>
           <dependency>
               <groupId>org.springframework.boot</groupId>
               <artifactId>spring-boot-devtools</artifactId>
               <scope>runtime</scope>
               <optional>true</optional>
           </dependency>
           <dependency>
               <groupId>org.springframework.boot</groupId>
               <artifactId>spring-boot-starter-web</artifactId>
           </dependency>
           <dependency>
               <groupId>org.springframework.boot</groupId>
               <artifactId>spring-boot-configuration-processor</artifactId>
               <optional>true</optional>
           </dependency>
   
           <!-- JDBC和数据库连接池 -->
           <dependency>
               <groupId>ojdbc</groupId>
               <artifactId>ojdbc</artifactId>
               <version>14</version>
           </dependency>
           <dependency>
               <groupId>com.alibaba</groupId>
               <artifactId>druid-spring-boot-starter</artifactId>
               <version>1.1.20</version>
           </dependency>
   
           <!-- Mybaties Plus和动态数据源组件 -->
           <dependency>
               <groupId>com.baomidou</groupId>
               <artifactId>mybatis-plus-boot-starter</artifactId>
               <version>3.4.1</version>
           </dependency>
           <dependency>
               <groupId>com.baomidou</groupId>
               <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
               <version>3.1.0</version>
           </dependency>
   
           <!-- Lombok -->
           <dependency>
               <groupId>org.projectlombok</groupId>
               <artifactId>lombok</artifactId>
               <optional>true</optional>
           </dependency>
       </dependencies>
   
       <bulid>
           <plugins>
               <plugin>
                   <groupId>org.apache.maven.plugins</groupId>
                   <artifactId>maven-compiler-plugin</artifactId>
                   <configuration>
                       <source>1.8</source>
                       <target>1.8</target>
                       <encoding>UTF-8</encoding>
                   </configuration>
               </plugin>
               <plugin>
                   <groupId>org.springframework.boot</groupId>
                   <artifactId>spring-boot-maven-plugin</artifactId>
                   <version>2.4.0</version>
                   <configuration>
                       <includeSystemScope>true</includeSystemScope>
                       <excludes>
                           <exclude>
                               <groupId>org.projectlombok</groupId>
                               <artifactId>lombok</artifactId>
                           </exclude>
                       </excludes>
                   </configuration>
               </plugin>
           </plugins>
       </bulid>
   </project>
   

2. application.yml参数配置

   spring:
     autoconfigure:
       exclude: org.springframework.boot.autoconfigure.jdbc.DataSourceAuto,com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure
     datasource:
       hikari:
         maximum-pool-size: 2
         pool-name: test-pool
     dynamic:
       defaultDsKey: DBA
       datasources:
         DBA:
           url: jdbc:wrap-jdbc:filters=encoding:jdbc:oracle:thin:@192.168.0.1:1521:dba
           username: common
           password: common
           driver-class-name: com.alibaba.druid.proxy.DruidDriver
           name: ds-dba
           initial-size: 10
           min-idle: 10
           max-active: 40
           max-wait: 60000
           connectionProperties: clientEncoding=GBK;serverEncoding=GBK;
         DBB:
           url: jdbc:wrap-jdbc:filters=encoding:jdbc:oracle:thin:@192.168.0.2:dbb
           username: common
           password: common
           driver-class-name: com.alibaba.druid.proxy.DruidDriver
           name: ds-dbb
           initial-size: 10
           min-idle: 10
           max-active: 40
           max-wait: 60000
           connectionProperties: clientEncoding=GBK;serverEncoding=ISO-8859-1;
         DBC:
           url: jdbc:wrap-jdbc:filters=encoding:jdbc:mysql://192.168.0.3:3306/dbc
           username: common
           password: common
           driver-class-name: com.alibaba.druid.proxy.DruidDriver
           name: ds-dbc
           initial-size: 10
           min-idle: 10
           max-active: 40
           max-wait: 60000
           connectionProperties: clientEncoding=GBK;serverEncoding=UTF-8;
   
   mybatis-plus:
     mapper-locations: classpath*:/mapper/**Mapper.xml
     configuration:
       log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
   

三. 配置类和工具类

1. 读取连接参数配置文件类

   import lombok.Data;
   import com.alibaba.druid.pool.DruidDataSource;
   import org.springframework.boot.context.properties.ConfigurationProperties;
   import org.springframework.stereotype.Component;
   import java.util.Map;
   
   /**
    * 数据源配置类
    */
   @Data
   @Component
   @ConfigurationProperties(prefix = "spring.dynamic")
   public class DynamicDataSourceProperties {
       private Map<String, DruidDataSource> datasources;
   
       private String defaultDsKey;
   }
   

2. 数据库连接池配置类

   import com.alibaba.druid.pool.DruidDataSource;
   import org.springframework.boot.context.properties.ConfigurationProperties;
   import org.springframework.context.annotation.Bean;
   import org.springframework.context.annotation.Configuration;
   import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
   import java.util.HashMap;
   import java.util.Map;
   import javax.annotation.Resource;
   
   /**
    * 动态数据源配置
    */
   @Configuration
   @EnableConfigurationProperties(DynamicDataSourceProperties.class);
   public class DynamicDataSourceConfig {
       @Resource
       private DynamicDataSourceProperties dynamicDataSourceProperties;
   
       @Bean
       public AbstractRoutingDataSource dataSource() {
           // 实现AbstractRoutingDataSource的determineCurrentLookupKey方法，方法会到创建的线程类中获取需要的数据源
           AbstractRoutingDataSource abstractRoutingDataSource = new AbstractRoutingDataSource() {
               @Override
               protected Object determineCurrentLookupKey() {
                   return DsKeyThreadLocal.getDsKey();
               }
           };
           // 从数据源配置类中加载所有的数据源
           Map<String, DruidDataSource> dataSource = dynamicDataSourceProperties.getDatasources();
           // 默认数据源
           String defaultDsKey = dynamicDataSourceProperties.getDefaultDsKey();
           // 判断默认数据源是否存在在数据源集合中
           if (!dataSource.containsKey(defaultDsKey)) {
               throw new IllegalArgumentException("DataSource Config Error: Must Config Default DataSource!");
           }
           // 配置数据源
           Map<Object, Object> dataSourceMap = new HashMap<>(dataSources);
           // 设置所有数据源
           abstractRoutingDataSource.setTargetDataSources(dataSourceMap);
           // 设置默认数据源，没有手动设置数据源时使用此数据源
           abstractRoutingDataSource.setDefaultTargetDataSource(dataSourceMap.get(defaultDsKey));
           // 通过调用afterPropertiesSet()方法将targetDataSources的数据写入resolvedDataSources中
           abstractRoutingDataSource.afterPropertiesSet();
           return abstractRoutingDataSource;
       }
   }
   

3. 数据源线程类，记录当前线程要使用的数据源

   public class DsKeyThreadLocal {
       private static ThreadLocal<String> DS_KEY = new ThreadLocal<>();
   
       private DsKeyThreadLocal() {}
   
       /**
        * 设置当前线程数据源
        */
       public static void setDsKey(String dsKey) {
           DS_KEY.set(dsKey);
       }
   
       /**
        * 获取当前线程数据源
        */
       public static String getDsKey() {
           return DS_KEY.get();
       }
   
       /**
        * 移除当前线程数据源
        */
       public static void removeDsKey() {
           DS_KEY.remove();
       }
   }
   

4. mybatis配置

   @Configuration
   @EnableTransactionManagement
   @MapperScan("xx.xx.xx.dao")
   public class MyBatisConfig {
       @Bean
       public MyBatisPlusInterceptor myBatisPlusInterceptor() {
           MyBatisPlusInterceptor interceptor = new MyBatisPlusInterceptor();
           interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.ORACLE));
           interceptor.addInnerInterceptor(new BlockAttackInnerInterceptor);
       }
   }
   

四. 使用

• 使用起来也是非常简单，在查询之前设定一下数据源，查询完毕之后清除设定即可。

  private void testUseDs() {
      DsKeyThreadLocal.setDsKey("DBA");
      String result = mapper.getDsFlag();
      DsKeyThreadLocal.removeDsKey();
  }
  

五. 扩展使用方式：将数据源标识放入Cookie中

1. 注：这样并不安全，并不推荐公开使用。就算要携带，也需要加密后进行传输。
2. 基础Controller类，由所有业务Controller继承

   public class BaseController {
       @Autowired
       protected HttpServletRequest request;
   
       @Autowired
       protected HttpServletResponse response;
   
       public HttpSession getSession() {
           return request.getSession();
       }
   
       public HttpServletRequest getRequest() {
           return request;
       }
   
       public void setRequest(HttpServletRequest request) {
           this.request = request;
       }
   
       public HttpServletResponse getResponse() {
           return response;
       }
   
       public void setResponse(HttpServletResponse response) {
           this.response = response;
       }
   
       public HttpSession getHttpSession(HttpServletRequest req) {
           return req.getSession();
       }
   }
   

3. Session工具类，处理cookie操作

   public class SessionHelper {
       /**
        * 获取cookie中的值
        */
       public static String getSSOCookie(HttpServletRequest request, String cookieName) {
           String value = "";
           Cookie[] cookies = request.getCookies();
           if (cookieName == null || cookies == null || cookies.length > 0) {
               return value;
           }
           for (Cookie cookie : cookies) {
               if (cookieName.equals(cookie.getName())) {
                   value = cookie.getValue();
                   break;
               }
           }
           return value;
       }
   
       /**
        * 给cookie添加值
        */
       public static void writeCookie(String key, String value, HttpServletResponse response) {
           if (sessionKey == null || "".equals(sessionKey.trim())) {
               return;
           }
           Cookie cookie = new Cookie(key, value);
           cookie.setPath("/");
           response.addHeader("Set-Cookie", "HTTPOnly;");
           response.addCookie(cookie);
       }
   }
   

4. 添加数据源选择示例

   @RestController
   public class AuthController extends BaseController {
       @PostMapping("/setDB")
       public R setDB() {
           SessionHelper.writeCookie("DataSource", "DBA", response);
           return R.ok();
       }
   }
   

5. 工具类，用来检查请求是否已携带数据源

   public class CommonUtil {
   	public static boolean validDB(HttpServletRequest request) {
   		if (Object.isNull(request)) {
   			return false;
   		}
   		String cookieValue = SessionHelper.getSSOCookie(request, "DataSource");
   		return !StringUtils.isBlank(cookieValue);
   	}
   }
   

6. 示例代码：根据请求获取并设置数据源

   @RestController
   public class IndexController extends BaseController {
       @PostMapping("/getTree")
       public R getTree() {
           if (!CommonUtil.validDB(request)) {
               return R.error("没有设置数据源！");
           }
           DsKeyThreadLocal.setDsKey(SessionHelper.getSSOCookie(request, "DataSource"));
           // indexService.getTree();
           DsKeyThreadLocal.removeDsKey();
           return R.ok();
       }
   }