Spring boot 2.x 多数据源配置

￡神魔★判官ぃ 2023-02-16 00:45 54阅读 0赞

1.主从数据源配置(读写分离)

参考链接：

[https://gitee.com/comven/dynamic-datasource-demo?spm=5176.100239.blogcont188540.13.iARYDh][https_gitee.com_comven_dynamic-datasource-demo_spm_5176.100239.blogcont188540.13.iARYDh]

[https://blog.csdn.net/qq\_35981283/article/details/78846892][https_blog.csdn.net_qq_35981283_article_details_78846892]

2数据库信息需要动态加载（每一个线程根据自己的实际参数设置数据源）

参考链接：

[https://blog.csdn.net/c598976342/article/details/82893993][https_blog.csdn.net_c598976342_article_details_82893993]

（1）pom jar包引入

<dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-web-services</artifactId>
        </dependency>
    
        
        <dependency>
          <groupId>org.mybatis.spring.boot</groupId>
          <artifactId>mybatis-spring-boot-starter</artifactId>
          <version>2.0.1</version>
        </dependency>
    
         
        <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
    
        
        <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>

（2）application.yml 设置数据源

#数据库配置
    hikari:
      #默认数据库
      -master:
        username: root
        password: aj
        jdbc-url: jdbc:mysql://localhost:3306/task_manage?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
        maximum-pool-size: 10
        pool-name: master(localhost)
        connection-timeout: 30000
        idle-timeout: 600000
        max-lifetime: 1765000
        data-source-properties:
          cachePrepStmts: true
          prepStmtCacheSize: 250
          prepStmtCacheSqlLimit: 2048
          useServerPrepStmts: true
          useLocalSessionState: true
          useLocalTransactionState: true
          rewriteBatchedStatements: true
          cacheResultSetMetadata: true
          cacheServerConfiguration: true
          elideSetAutoCommits: true
          maintainTimeStats: false
      # 通用数据库
      -common:
        username: root
        password: aj
        jdbc-url: jdbc:mysql://localhost:3306/task_%s?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
        driver-class-name: com.mysql.jdbc.Driver

（3）创建线程数据源信息存储类

DynamicDataSourceHolder.java

package com.anran.task.taskcommon.db;
    
    /**
     * @author anran
     * @version 1.0.0
     * @Type DynamicDataSourceHolder.java
     * @Desc
     * @date 2020/6/6 17:18
     */
    public class DynamicDataSourceHolder {
      /**
       * 本地线程共享对象
       */
      private static final ThreadLocal<String> THREAD_LOCAL = new ThreadLocal<>();
    
      public static void putDataSource(String name) {
        THREAD_LOCAL.set(name);
      }
    
      public static String getDataSource() {
        return THREAD_LOCAL.get();
      }
    
      public static void removeDataSource() {
        THREAD_LOCAL.remove();
      }
    }

（4）创建动态数据源对象

DynamicDataSource.java

package com.anran.task.taskcommon.db;
    
    import com.anran.task.taskcommon.util.SpringUtil;
    import com.zaxxer.hikari.HikariConfig;
    import com.zaxxer.hikari.HikariDataSource;
    import com.zaxxer.hikari.HikariJNDIFactory;
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.util.HashMap;
    import java.util.Map;
    import java.util.Set;
    import javax.sql.DataSource;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
    import org.springframework.util.StringUtils;
    
    /**
     * @author anran
     * @version 1.0.0
     * @Type DynamicDataSource.java
     * @Desc
     * @date 2020/6/6 17:19
     */
    public class DynamicDataSource extends AbstractRoutingDataSource {
    
      private static Map<String,HikariDataSource> dsDataSource = new HashMap<>();
    
      //数据源路由，此方用于产生要选取的数据源逻辑名称
      @Override
      protected Object determineCurrentLookupKey() {
        //从共享线程中获取数据源名称
        String dbName = DynamicDataSourceHolder.getDataSource();
        // 如果线程数据源信息不是空，获取通用数据源
        return StringUtils.isEmpty(dbName) ? dbName : "common";
      }
    
      @Override
      protected DataSource determineTargetDataSource() {
        HikariDataSource dataSource = (HikariDataSource)super.determineTargetDataSource();
        String dbName = DynamicDataSourceHolder.getDataSource();
        if (!StringUtils.isEmpty(dbName)) {
          // 防止一个线程重复创建数据源
          HikariDataSource newDataSource = dsDataSource.get(dbName);
          if (null == newDataSource) {
            // 创建当前线程独有的数据源
            newDataSource = new HikariDataSource();
            newDataSource.setJdbcUrl(String.format(dataSource.getJdbcUrl(), dbName));
            newDataSource.setUsername(dataSource.getUsername());
            newDataSource.setPassword(dataSource.getPassword());
            dsDataSource.put(dbName, newDataSource);
          }
          return newDataSource;
        }
        return dataSource;
      }
    
    }

（5）创建自定义数据源bean

DBProperties.java

package com.anran.task.taskcommon.db;
    
    import com.zaxxer.hikari.HikariDataSource;
    import lombok.Data;
    import java.util.Map;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.stereotype.Component;
    
    /**
     * @author anran
     * @version 1.0.0
     * @Type DBProperties.java
     * @Desc
     * @date 2020/6/6 17:24
     */
    @Component
    @Data
    @ConfigurationProperties(prefix = "")
    public class DBProperties {
      private Map<String, HikariDataSource> hikari;
    }

DataSourceConfig.java

package com.anran.task.taskcommon.db;
    
    import com.zaxxer.hikari.HikariDataSource;
    import java.util.HashMap;
    import java.util.Map;
    import java.util.Set;
    import javax.sql.DataSource;
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    import org.springframework.jdbc.datasource.DataSourceTransactionManager;
    import org.springframework.scheduling.annotation.EnableScheduling;
    import org.springframework.transaction.PlatformTransactionManager;
    
    /**
     * 数据源配置
     */
    @Configuration
    @EnableScheduling
    @Slf4j
    public class DataSourceConfig {
    
      private static final String KEY_MASTER = "master";
    
      @Autowired
      private DBProperties properties;
    
      @Bean(name = "dataSource")
      public DataSource dataSource() {
        //获取配置文件中的数据源
        Map<String, HikariDataSource> hikaris = properties.getHikari();
        Set<String> keys = hikaris.keySet();
        HikariDataSource hikariDataSource = null;
        HikariDataSource masterDB = null;
        //按照目标数据源名称和目标数据源对象的映射存放在Map中
        Map<Object, Object> targetDataSources = new HashMap<>();
        for (String key : keys){
          hikariDataSource = hikaris.get(key);
          targetDataSources.put(key,hikariDataSource);
          if (key.equals(KEY_MASTER)){
            masterDB = hikariDataSource;
          }
        }
        //采用是想AbstractRoutingDataSource的对象包装多数据源
        DynamicDataSource dataSource = new DynamicDataSource();
        dataSource.setTargetDataSources(targetDataSources);
        //设置默认的数据源，当拿不到数据源时，使用此配置
        if (null != masterDB){
          dataSource.setDefaultTargetDataSource(masterDB);
        }else {
          log.error("Can't find master db, project will be exit");
          System.exit(0);
        }
        return dataSource;
      }
    
      @Bean
      public PlatformTransactionManager txManager() {
        return new DataSourceTransactionManager(dataSource());
      }
    
    }

（6）启动类中排出springboot定义的数据源（SpringBootApplication(exclude = DataSourceAutoConfiguration.class)）

@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
    @ComponentScan({"com.anran.task.dataparseservice","com.anran.task.taskcommon"})
    public class DataparseserviceApplication {
    
      public static void main(String[] args) {
        SpringApplication.run(DataparseserviceApplication.class, args);
      }
    
    }

（7）线程中切换数据源方法

DynamicDataSourceHolder.putDataSource(String.valueOf(id));

ps：

如果使用接口参数，自动切换数据源, @Before设置当前线程数据源信息  @After销毁线程数据源信息

（1）添加AOP切面类

DataSourceAspect.java

package com.anran.task.taskcommon.db;
    
    import java.lang.reflect.Method;
    import javax.servlet.http.HttpServletRequest;
    import lombok.extern.slf4j.Slf4j;
    import org.aspectj.lang.JoinPoint;
    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.aspectj.lang.reflect.MethodSignature;
    import org.springframework.stereotype.Component;
    import org.springframework.web.context.request.RequestContextHolder;
    import org.springframework.web.context.request.ServletRequestAttributes;
    
    /**
     * 数据源AOP切面定义
     */
    @Component
    @Aspect
    @Slf4j
    public class DataSourceAspect {
    //  切换放在mapper接口的方法上，所以这里要配置AOP切面的切入点
      @Pointcut("execution( * com.anran.task.*.mapper.*.*(..))")
      public void dataSourcePointCut() {
      }
    
      // 通过接口请求参数设置数据源
      @Before("dataSourcePointCut()")
      public void before() {
        ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        HttpServletRequest request = attributes.getRequest();
        String num = null;
        if (null != request.getParameter("num") && Integer.parseInt(request.getParameter("num")) > 0) {
          num = request.getParameter("num");
        }
        DynamicDataSourceHolder.putDataSource(num);
      }
    
      //执行完切面后，将线程共享中的数据源名称清空
      @After("dataSourcePointCut()")
      public void after(JoinPoint joinPoint){
        DynamicDataSourceHolder.removeDataSource();
      }
    }

如果使用注解方式定义数据源

（1）添加注解

TargetDataSource.java

package com.anran.task.taskcommon.db;
    
    import java.lang.annotation.ElementType;
    import java.lang.annotation.Retention;
    import java.lang.annotation.RetentionPolicy;
    import java.lang.annotation.Target;
    
    /**
     * 目标数据源注解，注解在方法上指定数据源的名称
     */
    @Retention(RetentionPolicy.RUNTIME)
    @Target(ElementType.METHOD)
    public @interface TargetDataSource {
      String value();//此处接收的是数据源的名称
    }

（2）使用切面获取注解信息，并设置线程数据源

package com.anran.task.taskcommon.db;
    
    import java.lang.reflect.Method;
    import javax.servlet.http.HttpServletRequest;
    import lombok.extern.slf4j.Slf4j;
    import org.aspectj.lang.JoinPoint;
    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.aspectj.lang.reflect.MethodSignature;
    import org.springframework.stereotype.Component;
    import org.springframework.web.context.request.RequestContextHolder;
    import org.springframework.web.context.request.ServletRequestAttributes;
    
    /**
     * 数据源AOP切面定义
     */
    @Component
    @Aspect
    @Slf4j
    public class DataSourceAspect {
    //  切换放在mapper接口的方法上，所以这里要配置AOP切面的切入点
      @Pointcut("execution( * com.anran.task.*.mapper.*.*(..))")
      public void dataSourcePointCut() {
      }
    
      // 通过注解识别那个数据库
      @Before("dataSourcePointCut()")
      public void before(JoinPoint joinPoint) {
        Object target = joinPoint.getTarget();
        String method = joinPoint.getSignature().getName();
        Class<?>[] clazz = target.getClass().getInterfaces();
        Class<?>[] parameterTypes = ((MethodSignature) joinPoint.getSignature()).getMethod().getParameterTypes();
        try {
          Method m = clazz[0].getMethod(method, parameterTypes);
          //如果方法上存在切换数据源的注解，则根据注解内容进行数据源切换
          if (m != null && m.isAnnotationPresent(TargetDataSource.class)) {
            TargetDataSource data = m.getAnnotation(TargetDataSource.class);
            String dataSourceName = data.value();
            DynamicDataSourceHolder.putDataSource(dataSourceName);
            log.debug("current thread " + Thread.currentThread().getName() + " add " + dataSourceName + " to ThreadLocal");
          } else {
            log.debug("switch datasource fail,use default");
          }
        } catch (Exception e) {
          log.error("current thread " + Thread.currentThread().getName() + " add data to ThreadLocal error", e);
        }
      }
    
      //执行完切面后，将线程共享中的数据源名称清空
      @After("dataSourcePointCut()")
      public void after(JoinPoint joinPoint){
        DynamicDataSourceHolder.removeDataSource();
      }
    }

（3）使用注解

package com.anran.task.dataparseservice.mapper;
    
    import com.anran.task.taskcommon.db.TargetDataSource;
    import org.apache.ibatis.annotations.Mapper;
    import org.apache.ibatis.annotations.Param;
    import org.springframework.stereotype.Repository;
    
    /**
     * @author anran
     * @Type DealTaskMapper.java
     * @Desc
     * @date 2020/6/6 9:47
     */
    @Mapper
    @Repository
    public interface DealTaskMapper {
      @TargetDataSource("common")
      void insert2(@Param("id") int id, @Param("name") String name);
    
      void createTable();
    
      void dropTable();
    
    }

[https_gitee.com_comven_dynamic-datasource-demo_spm_5176.100239.blogcont188540.13.iARYDh]: https://gitee.com/comven/dynamic-datasource-demo?spm=5176.100239.blogcont188540.13.iARYDh
[https_blog.csdn.net_qq_35981283_article_details_78846892]: https://blog.csdn.net/qq_35981283/article/details/78846892
[https_blog.csdn.net_c598976342_article_details_82893993]: https://blog.csdn.net/c598976342/article/details/82893993