摘要：本文介绍了基于springboot+mybatis-plus的动态数据源方案实现原理和使用案例。

原理分析

Springboot官方提供了数据源路由的类AbstractRoutingDataSource，我们只需要继承该类注入数据源，和动态获取当前SQL需要的DataSource即可。

基础配置

DynamicDataSourceProperties配置类

@Data
@ConfigurationProperties(prefix = "spring.datasource.dynamic")
public class DynamicDataSourceProperties {

    /** 主数据源 */
    private String primary = "master";
    /** 配置的数据库 */
    Map<String,DataSourceProperties> datasource;

    /**
     * 动态的数据源
     */
    public static final class DyDataSourceProperties extends DataSourceProperties {

    }
}

DynamicDataSourceContextHolder当前上下文数据库名

采用了Deque的实现方式，用了里面栈的实现方式，配合非事务型的嵌套使用。

public class DynamicDataSourceContextHolder {

    private static final ThreadLocal<Deque<String>> CURRENT_DATA_SOURCE = new ThreadLocal<Deque<String>>(){
        @Override
        protected Deque<String> initialValue() {
            return new ArrayDeque<>();
        }
    };

    public static void push(String name){
        CURRENT_DATA_SOURCE.get().push(name);
    }

    public static String poll(){
        return CURRENT_DATA_SOURCE.get().poll();
    }

    public static void clear(){
        CURRENT_DATA_SOURCE.remove();
    }

}

DynamicDataSource动态数据源核心类

获取当前线程需要的数据库实例

public class DynamicDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return DynamicDataSourceContextHolder.poll();
    }

}

DynamicDataSourceConfig动态数据源配置类

@Import(DynamicDataSourceProperties.class)
@Configuration
public class DynamicDataSourceConfig {

    private static final Logger log = LoggerFactory.getLogger(DynamicDataSourceConfig.class);

    private final Map<Object, Object> TARGET_DATA_SOURCES = new ConcurrentHashMap<>();

    @Autowired
    private DynamicDataSourceProperties dynamicDataSourceProperties;

    @Bean(name = "dynamicDataSource")
    @Primary
    public DynamicDataSource dataSource() throws Exception{
        if(CollectionUtils.isEmpty(dynamicDataSourceProperties.getDatasource())){
            throw new Exception("注意！！！！未配置多数据源！！！！");
        }
        dynamicDataSourceProperties.getDatasource().forEach((name,property) -> {
            DataSource dataSource = DataSourceBuilder.create()
                    .url(property.getUrl())
                    .username(property.getUsername())
                    .password(property.getPassword())
                    .build();
            TARGET_DATA_SOURCES.put(property.getName(), dataSource);
        });

        String masterName = dynamicDataSourceProperties.getPrimary();
        if(ObjectUtils.isEmpty(masterName)){
            throw new Exception("未指定主数据源");
        }
        DataSource defaultDataSource = (DataSource) TARGET_DATA_SOURCES.get(masterName);
        if(null == defaultDataSource){
            throw new Exception("未找到主数据源："+masterName);
        }
        DynamicDataSource dynamicDataSource = new DynamicDataSource();
        dynamicDataSource.setTargetDataSources(TARGET_DATA_SOURCES);
        dynamicDataSource.setDefaultTargetDataSource(defaultDataSource);
        return dynamicDataSource ;
    }

    /**
     * 获取当前配置的数据源
     * @return
     */
    public Map<Object, Object> getDataSourceMap(){
        return TARGET_DATA_SOURCES;
    }

}

DynamicDataSourceManager动态数据源管理类

该类可以动态新增和删除数据源实现多租户数据库隔离实现方案

@Component
public class DynamicDataSourceManager {

    private static final Logger log = LoggerFactory.getLogger(DynamicDataSourceManager.class);

    @Autowired
    private DynamicDataSourceConfig dynamicDataSourceConfig;
    @Autowired
    private DynamicDataSource dynamicDataSource;

    /**
     * 新增数据源
     * @param property
     * @return
     */
    public boolean addDataSource(DynamicDataSourceProperties.DyDataSourceProperties property){
        DataSource dataSource = DataSourceBuilder.create()
                .url(property.getUrl())
                .username(property.getUsername())
                .password(property.getPassword())
                .build();
        dynamicDataSourceConfig.getDataSourceMap().put(property.getName(),dataSource);
        dynamicDataSource.afterPropertiesSet();
        return Boolean.TRUE;
    }

    /**
     * 删除数据源
     * @param name
     * @return
     */
    public boolean removeDataSource(String name){
        log.info("删除了数据源："+name);
        dynamicDataSourceConfig.getDataSourceMap().remove(name);
        dynamicDataSource.afterPropertiesSet();
        return Boolean.TRUE;
    }

}

SDS注解，实现数据库动态切换

该注解主要为业务固定的几个数据库进行切换使用

@Target({ElementType.METHOD, ElementType.TYPE})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface SDS {

    String value();

}

DynamicDataSourceAspect切面类

基于注解的AOP动态切换数据源

@Aspect
@Order(1)
@Component
public class DynamicDataSourceAspect {

    @Pointcut("@annotation(com.github.huzhihui.dynamic.config.aop.SDS)")
    public void pointcut() {
    }

    @Around("pointcut()")
    public Object around(ProceedingJoinPoint point) throws Throwable {
        MethodSignature signature = (MethodSignature) point.getSignature();
        Method method = signature.getMethod();
        SDS sds = method.getAnnotation(SDS.class);
        if (sds != null) {
            DynamicDataSourceContextHolder.push(sds.value());
        }
        try {
            return point.proceed();
        } finally {
            DynamicDataSourceContextHolder.clear();
        }
    }

}

application.yml配置

server:
  port: 8080
spring:
  datasource:
    dynamic:
      primary: dynamic_member
      datasource:
        dynamic_member:
          name: dynamic_member
          url: jdbc:mysql://192.168.137.100:3306/dynamic_member?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&autoReconnect=true&useAffectedRows=true&allowMultiQueries=true
          username: root
          password: root
        dynamic_goods:
          name: dynamic_goods
          url: jdbc:mysql://192.168.137.100:3306/dynamic_goods?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&autoReconnect=true&useAffectedRows=true&allowMultiQueries=true
          username: root
          password: root
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations: classpath*:/mybatis/mapper/**.xml

测试案例

以下测试案例使用了三个数据库dynamic_member,dynamic_member,dynamic_common，yml配置了两个数据源，dynamic_common做为动态新增的库来测试

主要代码

启动类配置@MapperScan

改成你mapper接口的包路径

@MapperScan("com.github.huzhihui.dynamic.*.mapper")

dynamic_member库

• sql

CREATE TABLE `user` (
  `id` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

• UserEntity

@Data
@TableName("user")
public class UserEntity {

    @TableId
    private String id;

    private String name;

    private String address;

    private Date createTime;
}

• UserMapper

@Repository
public interface UserMapper extends BaseMapper<UserEntity> {
}

• UserService

@Service
public class UserService {

    @Autowired
    private UserMapper userMapper;

    @SDS("dynamic_member")
    @Transactional(rollbackFor = Exception.class)
    public Object add(UserEntity userEntity) throws Exception{
        userEntity.setId(IdWorker.getIdStr());
        userEntity.setCreateTime(new Date());
        userMapper.insert(userEntity);
        return userEntity;
    }
}

dynamic_goods库

• sql

CREATE TABLE `goods` (
  `id` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `desc` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

• GoodsEntity

@Data
@TableName("goods")
public class GoodsEntity {

    @TableId
    private String id;

    @TableField(value = "`name`")
    private String name;

    @TableField(value = "`desc`")
    private String desc;

    private Date createTime;
}

• GoodsMapper

@Repository
public interface GoodsMapper extends BaseMapper<GoodsEntity> {
}

• GoodsService

@Service
public class GoodsService {

    @Autowired
    private GoodsMapper goodsMapper;

    @SDS("dynamic_goods")
    @Transactional(rollbackFor = Exception.class)
    public Object add(GoodsEntity goodsEntity) throws Exception{
        goodsEntity.setId(IdWorker.getIdStr());
        goodsEntity.setCreateTime(new Date());
        goodsMapper.insert(goodsEntity);
        return goodsEntity;
    }
}

dynamic_common库

• sql

CREATE TABLE `notice` (
  `id` varchar(20) NOT NULL,
  `content` varchar(200) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

• NoticeEntity

@Data
@TableName("notice")
public class NoticeEntity {

    @TableId
    private String id;

    private String content;

    private Date createTime;

}

• NoticeMapper

@Repository
public interface NoticeMapper extends BaseMapper<NoticeEntity> {

}

测试控制器

单库读写，默认数据源dynamic_member

@RestController
@RequestMapping(value = "user")
public class UserController {

    @Autowired
    private UserMapper userMapper;
    @Autowired
    private UserService userService;

    @GetMapping("all")
    public Object all(){
        return userMapper.selectList(new QueryWrapper<>());
    }

    @Transactional(rollbackFor = Exception.class)
    @PostMapping("add")
    public Object add(@RequestBody UserEntity userEntity) throws Exception{
        return userService.add(userEntity);
    }

}

结果

切换到dynamic_goods数据源

@RestController
@RequestMapping(value = "goods")
public class GoodsController {

    @Autowired
    private GoodsMapper goodsMapper;
    @Autowired
    private GoodsService goodsService;

    @SDS("dynamic_goods")
    @GetMapping("all")
    public Object all(){
        return goodsMapper.selectList(new QueryWrapper<>());
    }

    @Transactional(rollbackFor = Exception.class)
    @SDS("dynamic_goods")
    @PostMapping(value = "add")
    public Object add(@RequestBody GoodsEntity goodsEntity) throws Exception{
        return goodsService.add(goodsEntity);
    }
}

同一个方法中调用多个数据源

注意外层的方法不能加上事务注解，因为加上了事务注解事务控制器不会再切换数据源

@RestController
@RequestMapping(value = "common")
public class CommonController {

    @Autowired
    private UserService userService;
    @Autowired
    private GoodsService goodsService;

    @GetMapping(value = "tt")
    public Object tt() throws Exception{
        UserEntity userEntity = new UserEntity();
        userEntity.setName(UUID.randomUUID().toString());
        userService.add(userEntity);
        GoodsEntity goodsEntity = new GoodsEntity();
        goodsEntity.setName(UUID.randomUUID().toString());
        goodsService.add(goodsEntity);
        return "SUCCESS";
    }
}

结果

动态新增删除数据源

之前我们创建了dynamic_common数据源，但是未加上database的配置，这里我们动态添加配置来进行测试

DynamicDataSourceController动态数据源类

@RestController
@RequestMapping(value = "dynamic_data_source")
public class DynamicDataSourceController {

    @Autowired
    private DynamicDataSourceManager dynamicDataSourceManager;

    @PostMapping(value = "add")
    public Object add(@RequestBody DynamicDataSourceProperties.DyDataSourceProperties properties){
        dynamicDataSourceManager.addDataSource(properties);
        return "SUCCESS";
    }

    @DeleteMapping(value = "remove")
    public Object remove(String name){
        dynamicDataSourceManager.removeDataSource(name);
        return "SUCCESS";
    }

}

NoticeController测试类

@RestController
@RequestMapping(value = "notice")
public class NoticeController {

    @Autowired
    private NoticeMapper noticeMapper;

    @SDS("dynamic_common")
    @GetMapping(value = "all")
    public Object all(){
        return noticeMapper.selectList(new QueryWrapper<>());
    }
}

结果

• 未配置数据源情况

• 新增dynamic_common数据源

• 再次访问

删除数据源

• 结果