Springboot多数据源

Mybatis 动态切换数据源 示例

项目说明

本项目演示如何使用 AbstractRoutingDataSource 类 快速与 Mybatis 集成多数据源。

AbstractRoutingDataSource 是spring对外提供的数据源切换的父类，基于此类 + Aop 可快速实现动态切换以及增加删除数据源：

本项目专注于AbstractRoutingDataSource 、Mybatis、Durid 的整合，且只提供基础范例，更多示例特性例如支持spel表达式切换数据源可自行学习探索。

优&缺

1、优点

• 可以从偏底层了解切换数据的原理，可扩展性强

​

2、缺点

• 配置较多，需要一定的时间了解

示例

本示例默认已搭建完成 Springboot + Mybatis + Mysql + Durid 的Maven项目框架，如有疑问，可参照本示例代码构建

配置数据源（格式自定义，但确保能读取到）

spring:
  datasource:
    master:
      pollName: master
      url: jdbc:mysql://localhost:3306/master?useUnicode=true&characterEncoding=utf-8&&useOldAliasMetadataBehavior=true
      username: root
      password: 123456
      driverClassName: com.mysql.cj.jdbc.Driver
    slave1:
      pollName: slave1
      url: jdbc:mysql://localhost:3306/slave1?useUnicode=true&characterEncoding=utf-8&&useOldAliasMetadataBehavior=true
      username: root
      password: 123456
      driverClassName: com.mysql.cj.jdbc.Driver
    druid:
      filters: stat
      maxActive: 30
      initialSize: 5
      maxWait: 60000
      minIdle: 1
      testWhileIdle: true
      testOnBorrow: true
      testOnReturn: false
      poolPreparedStatements: true
      maxOpenPreparedStatements: 20
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: select 1
      #更多druid参数配置可参考 https://github.com/alibaba/druid/wiki/DruidDataSource配置属性列表

如选择Durid为数据源，请如下设置 ，其他则可跳到下一步

// 排除Durid默认配置自动加载
         @SpringBootApplication(exclude = DruidDataSourceAutoConfigure.class)
         @MapperScan("com.southsmart.springboot.datasource.dynamicsample1.mapper")
         public class DynamicSample1Application {
         
             public static void main(String[] args) {
                 SpringApplication.run(DynamicSample1Application.class, args);
             }
            
         }

或

spring:
     autoconfigure:
       exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure

配置项

• 获取yml文件中数据源配置，此处采用截取方式，更多方式自行选择

@ConfigurationProperties(prefix = "spring")
@Component
@Data
public class DataSourceProperty {
           
 private Map<String, MultiDataSource> datasource;
       
  }

@ConfigurationProperties(prefix = "spring.datasource")
@Component
@Data
public class DruidProperty {
           
    private Map<String, Object> druid;
           
    public Map<String, Object> getDruid() {
           Map<String, Object> map = new HashMap<>();
               for (Map.Entry<String, Object> entry : druid.entrySet()) {
                   String key = entry.getKey();
                   map.put("druid." + key, entry.getValue());
               }
       
               return map;
           }
       }

• 配置数据源上下文

       public class DynamicDataSourceContextHolder {
           /**
            * 当使用ThreadLocal维护变量时，ThreadLocal为每个使用该变量的线程提供独立的变量副本，
            * 所以每一个线程都可以独立地改变自己的副本，而不会影响其它线程所对应的副本。
            */
           private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();
       
           /**
            * 使用setDataSourceType设置当前的
            *
            * @param dataSourceType dataSourceType
            */
           public static void setDataSourceType(String dataSourceType) {
               CONTEXT_HOLDER.set(dataSourceType);
           }
       
           /**
            * 获取数据源类型 默认使用主数据源
            *
            * @return 数据源 data source type
            */
           public static String getDataSourceType() {
               return CONTEXT_HOLDER.get() == null ? "master" : CONTEXT_HOLDER.get();
           }
       
           /**
            * 清除数据源
            */
           public static void clearDataSourceType() {
               CONTEXT_HOLDER.remove();
           }
       
       
           /**
            * Save data source type name.
            *
            * @param name the name
            */
           public static void saveDataSourceTypeName(String name) {
               DATA_SOURCE_LIST.add(name);
           }
       
           /**
            * Check data source type boolean.
            *
            * @param name the name
            * @return the boolean
            */
           public static boolean checkDataSourceType(String name) {
               return DATA_SOURCE_LIST.contains(name);
           }
       
           /**
            * 校验输入的数据库名称是否正确
            */
           private static final List<String> DATA_SOURCE_LIST = new ArrayList<>();

• 继承类 AbstractRoutingDataSource

       public class DynamicDataSource extends AbstractRoutingDataSource {
           @Override
           protected Object determineCurrentLookupKey() {
               return DynamicDataSourceContextHolder.getDataSourceType();
           }
           @Override
           public Connection getConnection()
               throws SQLException {
               String type = DynamicDataSourceContextHolder.getDataSourceType();
               return DataSourceConfig.DATA_SOURCE_MAP.get(type).getConnection();
               
           }
       }

• 数据源bean的配置

       @Component
       public class DataSourceConfig {
           
           /**
            * The Audit data source property.
            */
           @Resource
           private DataSourceProperty auditDataSourceProperty;
           
           @Resource
           private DruidProperty druidProperty;
       
           
           /**
            * 用map存储数据源
            */
           public static final Map<String, DataSource> DATA_SOURCE_MAP = new ConcurrentHashMap<>();
           
           /**
            * Multiple data source to choose.
            */
           @PostConstruct
           public void multipleDataSourceToChoose() {
               Map<String, MultiDataSource> datasource = auditDataSourceProperty.getDatasource();
               for (Map.Entry<String, MultiDataSource> entry : datasource.entrySet()) {
                   add(entry.getValue());
               }
               
               System.out.println(datasource.size());
           }
           
           /**
            * 对外提供新增方法
            *
            * @param dataSource the data source
            */
           public void add(MultiDataSource dataSource) {
               DruidDataSource druidDataSource = new DruidDataSource();
               druidDataSource.setUrl(dataSource.getUrl());
               druidDataSource.setUsername(dataSource.getUsername());
               druidDataSource.setPassword(dataSource.getPassword());
               druidDataSource.setDriverClassName(dataSource.getDriverClassName());
               DATA_SOURCE_MAP.put(dataSource.getPollName(), common(druidDataSource));
               DynamicDataSourceContextHolder.saveDataSourceTypeName(dataSource.getPollName());
           }
           
           /**
            * 数据源bean
            *
            * @return the data source
            */
           @Bean
           public DataSource dataSource() {
               DynamicDataSource dynamicDataSource = new DynamicDataSource();
               Map<Object, Object> map = new HashMap<>();
               for (Map.Entry<String, DataSource> entry : DATA_SOURCE_MAP.entrySet()) {
                   map.put(entry.getKey(), entry.getValue());
               }
               
               System.out.println(map.size());
               //添加所拥有数据源，可null
               dynamicDataSource.setTargetDataSources(map);
               return dynamicDataSource;
           }
           
           /**
            * 配置 druid 连接池 ，不同连接池按需修改
            *
            * @param druidDataSource the druid data source
            * @return the data source
            */
           public DataSource common(DruidDataSource druidDataSource) {
               // todo 主要获取 druid 属性 注意配置文件
               Properties properties = new Properties();
               Map<String, Object> druidProperties = druidProperty.getDruid();
               properties.putAll(druidProperties);
               druidDataSource.configFromPropety(properties);
               return druidDataSource;
           }

• 自定义注解

       @Documented
       @Retention(RetentionPolicy.RUNTIME)
       @Target({ElementType.METHOD})
       public @interface DS {
       
           /**
            * 数据源名称
            *
            * @return the string
            */
           String name() default "";
       }

• AOP切面配置

     @Aspect
     @Order(-10) //保证该AOP在@Transactional之前执行
     @Component
     @Slf4j
     public class DynamicDataSourceAspect {
         
         /**
          * 切点为自定义注解
          *
          * @param point the point
          * @param source the source
          * @throws Exception the exception
          */
         @Before(value = "@annotation(source)")
         public void changeDataSource(JoinPoint point, DS source)
             throws Exception {
             String name = source.name();
              TODO:  此处可添加spel 表达式解析
             if (!DynamicDataSourceContextHolder.checkDataSourceType(name)) {
                 throw new Exception("没有该数据源！");
             }
             DynamicDataSourceContextHolder.setDataSourceType(name);
         }
         
         /**
          * Restore data source.
          *
          * @param point the point
          * @param source the source
          */
         @AfterReturning(value = "@annotation(source)")
         public void restoreDataSource(JoinPoint point, DS source) {
             //方法执行完毕之后，销毁当前数据源信息，进行垃圾回收。
             DynamicDataSourceContextHolder.clearDataSourceType();
         }  

项目结构

   ├─src
   │  ├─main
   │  │  ├─java
   │  │  │  └─com
   │  │  │      └─maofs
   │  │  │          └─springboot
   │  │  │              └─datasource
   │  │  │                  └─dynamicsample2
   │  │  │                      ├─controller
   │  │  │                      ├─datasourceconfig 动态数据源配置类
   │  │  │                      ├─entity
   │  │  │                      ├─mapper
   │  │  │                      └─service
   │  │  │                          └─impl
   │  │  └─resources
   │  │      ├─static
   │  │      └─templates
   │  └─test
   └─target

示例代码

• entity

  @Data
  public class Userinfo  implements Serializable{
  /**
   * The constant serialVersionUID.
   */
  private static final long serialVersionUID = -2545252496999160624L;
  /**
   * The Id.
   */
  private String id;
  
  /**
   * 姓名
   */
  private String name;
  
  /**
   * 年龄
   */
  private Integer age;
  }
  

• dto

  用于动态增加数据源

  @Data
  public class MultiDataSource {
      /**
       * 连接
       */
      private String pollName;
      /**
       * 连接
       */
      private String url;
  
      /**
       * 账号
       */
      private String username;
  
      /**
       * 密码
       */
      private String password;
  
      /**
       * 驱动
       */
      private String driverClassName;
  

• mapper

  public interface UserMapper {
     
      @Select("SELECT * FROM userinfo where id = #{id}")
      Userinfo findById(String id);
  

• service

  public interface IUserinfoService {
  
      /**
       * 默认数据库
       *
       * @param id the id
       * @return the userinfo
       */
      Userinfo findById(@NonNull String id);
  
      /**
       * master数据库
       *
       * @param id the id
       * @return the userinfo
       */
      Userinfo findByIdFromMaster(@NonNull String id);
  
      /**
       * slave1数据库
       *
       * @param id the id
       * @return the userinfo
       */
      Userinfo findByIdFromSlave1(@NonNull String id);
  
      /**
       * slave2数据库
       *
       * @param id the id
       * @return the userinfo
       */
      Userinfo findByIdFromSlave2(@NonNull String id);
  

  实现层控制数据源切换主要依靠 @Ds（） 注解

  @Slf4j
  @Service
  public class UserinfoServiceImpl implements IUserinfoService {
      @Resource
      private UserMapper userMapper;
      
      @Override
      public Userinfo findById(String id) {
          Userinfo userinfo = userMapper.findById(id);
          log.info("主数据库的用户:{}", userinfo.getName());
          return Optional.of(userinfo).orElse(new Userinfo());
      }
      
      @Override
      @DS(name = "master")
      public Userinfo findByIdFromMaster(String id) {
          Userinfo userinfo = userMapper.findById(id);
          log.info("主数据库的用户:{}", userinfo.getName());
          return Optional.of(userinfo).orElse(new Userinfo());
      }
      
      @Override
      @DS(name = "slave1")
      public Userinfo findByIdFromSlave1(String id) {
          Userinfo userinfo = userMapper.findById(id);
          log.info("从数据库1的用户:{}", userinfo.getName());
          return Optional.of(userinfo).orElse(new Userinfo());
      }
  
      @Override
      @DS(name = "slave2")
      public Userinfo findByIdFromSlave2(String id) {
          Userinfo userinfo = userMapper.findById(id);
          log.info("从数据库2的用户:{}", userinfo.getName());
          return Optional.of(userinfo).orElse(new Userinfo());
      }
  

• controller

  @RestController
  @AllArgsConstructor
  @Validated
  @RequestMapping("/datasources")
  public class DataSourceController {
  /**
   * The Userinfo service.
   */
  @Resource
  private IUserinfoService userinfoService;
  
  @Resource
  private DataSourceConfig dataSourceConfig;
  
  @PostMapping("/add")
  public Object add(@Valid @RequestBody MultiDataSource source) {
      dataSourceConfig.add(source);
      return "1";
  }
  
  /**
   * 获取默认数据源结果
   *
   * @return the object
   */
  @GetMapping("/default")
  public Object defaultDataSource() {
      
      return userinfoService.findById("1");
  }
  
  /**
   * 获取主数据源结果
   *
   * @return the object
   */
  @GetMapping("/master")
  public Object masterDataSource() {
      
      return userinfoService.findByIdFromMaster("1");
  }
  
  /**
   * 获取从数据源1的结果
   *
   * @return the object
   */
  @GetMapping("/slave1")
  public Object slave1DataSource() {
      
      return userinfoService.findByIdFromSlave1("1");
  }
  
  /**
   * 获取从数据源2的结果
   *
   * @return the object
   */
  @GetMapping("/slave2")
  public Object slave2DataSource() {
  
      return userinfoService.findByIdFromSlave2("1");
   }
  }
  

• 重点 数据源的配置

  • 获取yml文件中数据源配置，此处采用截取方式，更多方式自行选择

    @ConfigurationProperties(prefix = "spring")
    @Component
    @Data
    public class DataSourceProperty {
        
        private Map<String, MultiDataSource> datasource;
    
    }
    

    @ConfigurationProperties(prefix = "spring.datasource")
    @Component
    @Data
    public class DruidProperty {
        
        private Map<String, Object> druid;
        
        public Map<String, Object> getDruid() {
            Map<String, Object> map = new HashMap<>();
            for (Map.Entry<String, Object> entry : druid.entrySet()) {
                String key = entry.getKey();
                map.put("druid." + key, entry.getValue());
            }
    
            return map;
        }
    }
    

  • 配置数据源上下文

    public class DynamicDataSourceContextHolder {
        /**
         * 当使用ThreadLocal维护变量时，ThreadLocal为每个使用该变量的线程提供独立的变量副本，
         * 所以每一个线程都可以独立地改变自己的副本，而不会影响其它线程所对应的副本。
         */
        private static final ThreadLocal<String> CONTEXT_HOLDER = new ThreadLocal<>();
    
        /**
         * 使用setDataSourceType设置当前的
         *
         * @param dataSourceType dataSourceType
         */
        public static void setDataSourceType(String dataSourceType) {
            CONTEXT_HOLDER.set(dataSourceType);
        }
    
        /**
         * 获取数据源类型 默认使用主数据源
         *
         * @return 数据源 data source type
         */
        public static String getDataSourceType() {
            return CONTEXT_HOLDER.get() == null ? "master" : CONTEXT_HOLDER.get();
        }
    
        /**
         * 清除数据源
         */
        public static void clearDataSourceType() {
            CONTEXT_HOLDER.remove();
        }
    
    
        /**
         * Save data source type name.
         *
         * @param name the name
         */
        public static void saveDataSourceTypeName(String name) {
            DATA_SOURCE_LIST.add(name);
        }
    
        /**
         * Check data source type boolean.
         *
         * @param name the name
         * @return the boolean
         */
        public static boolean checkDataSourceType(String name) {
            return DATA_SOURCE_LIST.contains(name);
        }
    
        /**
         * 校验输入的数据库名称是否正确
         */
        private static final List<String> DATA_SOURCE_LIST = new ArrayList<>();
    

  • 继承类 AbstractRoutingDataSource

    public class DynamicDataSource extends AbstractRoutingDataSource {
        @Override
        protected Object determineCurrentLookupKey() {
            return DynamicDataSourceContextHolder.getDataSourceType();
        }
        @Override
        public Connection getConnection()
            throws SQLException {
            String type = DynamicDataSourceContextHolder.getDataSourceType();
            return DataSourceConfig.DATA_SOURCE_MAP.get(type).getConnection();
            
        }
    }
    

  • 数据源bean的配置

    @Component
    public class DataSourceConfig {
        
        /**
         * The Audit data source property.
         */
        @Resource
        private DataSourceProperty auditDataSourceProperty;
        
        @Resource
        private DruidProperty druidProperty;
    
        
        /**
         * 用map存储数据源
         */
        public static final Map<String, DataSource> DATA_SOURCE_MAP = new ConcurrentHashMap<>();
        
        /**
         * Multiple data source to choose.
         */
        @PostConstruct
        public void multipleDataSourceToChoose() {
            Map<String, MultiDataSource> datasource = auditDataSourceProperty.getDatasource();
            for (Map.Entry<String, MultiDataSource> entry : datasource.entrySet()) {
                add(entry.getValue());
            }
            
            System.out.println(datasource.size());
        }
        
        /**
         * 对外提供新增方法
         *
         * @param dataSource the data source
         */
        public void add(MultiDataSource dataSource) {
            DruidDataSource druidDataSource = new DruidDataSource();
            druidDataSource.setUrl(dataSource.getUrl());
            druidDataSource.setUsername(dataSource.getUsername());
            druidDataSource.setPassword(dataSource.getPassword());
            druidDataSource.setDriverClassName(dataSource.getDriverClassName());
            DATA_SOURCE_MAP.put(dataSource.getPollName(), common(druidDataSource));
            DynamicDataSourceContextHolder.saveDataSourceTypeName(dataSource.getPollName());
        }
        
        /**
         * 数据源bean
         *
         * @return the data source
         */
        @Bean
        public DataSource dataSource() {
            DynamicDataSource dynamicDataSource = new DynamicDataSource();
            Map<Object, Object> map = new HashMap<>();
            for (Map.Entry<String, DataSource> entry : DATA_SOURCE_MAP.entrySet()) {
                map.put(entry.getKey(), entry.getValue());
            }
            
            System.out.println(map.size());
            //添加所拥有数据源，可null
            dynamicDataSource.setTargetDataSources(map);
            return dynamicDataSource;
        }
        
        /**
         * 配置 druid 连接池 ，不同连接池按需修改
         *
         * @param druidDataSource the druid data source
         * @return the data source
         */
        public DataSource common(DruidDataSource druidDataSource) {
            // todo 主要获取 druid 属性 注意配置文件
            Properties properties = new Properties();
            Map<String, Object> druidProperties = druidProperty.getDruid();
            properties.putAll(druidProperties);
            druidDataSource.configFromPropety(properties);
            return druidDataSource;
        }
    

  • 自定义注解

    @Documented
    @Retention(RetentionPolicy.RUNTIME)
    @Target({ElementType.METHOD})
    public @interface DS {
    
        /**
         * 数据源名称
         *
         * @return the string
         */
        String name() default "";
    }
    

  • AOP切面配置

    @Aspect
    @Order(-10) //保证该AOP在@Transactional之前执行
    @Component
    @Slf4j
    public class DynamicDataSourceAspect {
        
        /**
         * 切点为自定义注解
         *
         * @param point the point
         * @param source the source
         * @throws Exception the exception
         */
        @Before(value = "@annotation(source)")
        public void changeDataSource(JoinPoint point, DS source)
            throws Exception {
            String name = source.name();
             TODO:  此处可添加spel 表达式解析
            if (!DynamicDataSourceContextHolder.checkDataSourceType(name)) {
                throw new Exception("没有该数据源！");
            }
            DynamicDataSourceContextHolder.setDataSourceType(name);
        }
        
        /**
         * Restore data source.
         *
         * @param point the point
         * @param source the source
         */
        @AfterReturning(value = "@annotation(source)")
        public void restoreDataSource(JoinPoint point, DS source) {
            //方法执行完毕之后，销毁当前数据源信息，进行垃圾回收。
            DynamicDataSourceContextHolder.clearDataSourceType();
        }
       
       
    

创建master、slave1、slave2 数据库，以及userinfo表

--Master
CREATE TABLE `master`.`userinfo`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'id',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '姓名',
  `age` int(4) NULL DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO `master`.`userinfo` VALUES ('1', '我是数据源mater', 18);
--slave1
CREATE TABLE `slave1`.`userinfo`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'id',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '姓名',
  `age` int(4) NULL DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO `slave1`.`userinfo` VALUES ('1', '我是数据源slave1', 18);
--slave2
CREATE TABLE `slave2`.`userinfo`  (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'id',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '姓名',
  `age` int(4) NULL DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

INSERT INTO `slave2`.`userinfo` VALUES ('1', '我是数据源slave2', 18);

测试api接口

• 测试使用 idea 自带 httpclient

   ### 默认数据源
   GET http://localhost:8888/datasources/default
   
   ### 主数据源
   GET http://localhost:8888/datasources/master
   
   ###  从数据源1
   GET http://localhost:8888/datasources/slave1
   
   ###  从数据源2
   GET http://localhost:8888/datasources/slave2
   
   ### 添加从数据源2
   POST http://localhost:8888/datasources/add
   Content-Type: application/json
   
   {
     "pollName":"slave2",
     "driverClassName":"com.mysql.cj.jdbc.Driver",
     "url":"jdbc:mysql://localhost:3306/slave2?useUnicode=true&characterEncoding=utf-8&&useOldAliasMetadataBehavior=true",
     "username":"root",
     "password":"123456"
   }
   
   ###  从数据源2
   GET http://localhost:8888/datasources/slave2
  

• 结果如下图
  
  
  
  
  
  

​