Mybatis动态数据源

1. 业务场景

现有股票与基金业务，不同的业务分在不同的库中，但有些业务类似可以基于同一套代码，例如组织架构、权限控制与客户管理，但是为区分业务线，要将数据拆分在不同的数据库中

2. 达成效果

不同的业务传对应的业务参数，保存到相应的库中

2.1 基金业务

2.1.1新增用户

2.1.2查询用户

2.2 股票业务

2.2.1 新增用户

2.2.2 查询用户

3. 具体实现

3.1 项目结构

3.2 数据库

3.3 表结构

CREATE TABLE `rbac_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

3.4 代码实现

3.4.1 POM依赖

<!--?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">
	<modelversion>4.0.0</modelversion>
	<parent>
		<groupid>org.springframework.boot</groupid>
		<artifactid>spring-boot-starter-parent</artifactid>
		<version>2.4.0</version>
		<relativepath> <!-- lookup parent from repository -->
	</relativepath></parent>
	<groupid>com.sure</groupid>
	<artifactid>learn-mybatis-dynamic-datasource</artifactid>
	<version>0.0.1-SNAPSHOT</version>
	<name>learn-mybatis-dynamic-datasource</name>
	<description>Demo project for Spring Boot</description>

	<properties>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		
		<dependency>
			<groupid>org.springframework.boot</groupid>
			<artifactid>spring-boot-starter-web</artifactid>
		</dependency>

		<dependency>
			<groupid>org.mybatis.spring.boot</groupid>
			<artifactid>mybatis-spring-boot-starter</artifactid>
			<version>2.1.4</version>
		</dependency>

		<dependency>
			<groupid>mysql</groupid>
			<artifactid>mysql-connector-java</artifactid>
			<scope>runtime</scope>
		</dependency>

		<dependency>
			<groupid>org.projectlombok</groupid>
			<artifactid>lombok</artifactid>
			<optional>true</optional>
		</dependency>

		<!--AOP 依赖-->
		<dependency>
			<groupid>org.springframework.boot</groupid>
			<artifactid>spring-boot-starter-aop</artifactid>
		</dependency>

		<dependency>
			<groupid>org.springframework.boot</groupid>
			<artifactid>spring-boot-starter-test</artifactid>
			<scope>test</scope>
		</dependency>

	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupid>org.springframework.boot</groupid>
				<artifactid>spring-boot-maven-plugin</artifactid>
			</plugin>
		</plugins>
	</build>

</project>

3.4.2 配置文件

不同的业务线配置不同的数据库

spring:
  datasource:
    fund:
      driverClassName: com.mysql.cj.jdbc.Driver
      username: root
      password: root
      jdbcUrl: jdbc:mysql://localhost:3306/fund?useUnicode=true&characterEncoding=utf8
    stock:
      driverClassName: com.mysql.cj.jdbc.Driver
      username: root
      password: root
      jdbcUrl: jdbc:mysql://localhost:3306/stock?useUnicode=true&characterEncoding=utf8

logging:
  level:
    com.sure: debug

3.4.3 创建业务平台枚举类型

/**
 * 业务平台枚举
 *
 * @author sure
 */
public enum PlatformEnum {

    FUND("fund"),
    STOCK("stock");

    public String value;

    PlatformEnum(String value){
        this.value = value;
    }
}

3.4.4 动态数据源上下文管理

/**
 * 动态数据源上下文管理
 * 
 * @author sure 
 */
public class DataSourceContextHolder {

    private static final ThreadLocal<string> contextHolder = new ThreadLocal<>();

    /**
     * 设置数据源
     * 
     * @param dataSourceKey
     */
    public static void setDataSource(String dataSourceKey){
        contextHolder.set(dataSourceKey);
    }
    /**
     * 获取数据源
     * @return
     */
    public static String getDataSource(){
        return contextHolder.get();
    }
    /**
     * 清空数据源设置
     */
    public static void clearDataSource(){
        contextHolder.remove();
    }
}

3.4.5 动态数据源

/**
 * 动态数据源
 */
public class DynamicDataSource extends AbstractRoutingDataSource {
    
    @Override
    protected Object determineCurrentLookupKey() {
        return DataSourceContextHolder.getDataSource();
    }
}

3.4.6 Mybatis多数据源配置

/**
 * Mybatais 动态数据源配置
 * 
 * @author sure 
 */
@Configuration
@MapperScan(basePackages = "com.sure.learnmybatisdynamicdatasource.mapper")
public class MybaticDynamicDataSourceConfig {

    @Bean
    @ConfigurationProperties("spring.datasource.fund")
    public DataSource fundDataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties("spring.datasource.stock")
    public DataSource stockDataSource(){
        return DataSourceBuilder.create().build();
    }

    @Bean
    public DynamicDataSource dataSource(@Qualifier("fundDataSource") DataSource fundDataSource,
                                        @Qualifier("stockDataSource") DataSource stockDataSource){

        Map<object,object> dataSources = new HashMap<>();

        dataSources.put(PlatformEnum.FUND.value,fundDataSource);
        dataSources.put(PlatformEnum.STOCK.value,stockDataSource);

        DynamicDataSource dynamicDataSource = new DynamicDataSource();

        dynamicDataSource.setTargetDataSources(dataSources);
        dynamicDataSource.setDefaultTargetDataSource(fundDataSource);

        return dynamicDataSource;
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory(DynamicDataSource dynamicDataSource) throws Exception {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();

        sqlSessionFactoryBean.setDataSource(dynamicDataSource);

        // 扫描mapper路径
        Resource[] resources = new PathMatchingResourcePatternResolver().getResources("classpath:mapper/*.xml");
        sqlSessionFactoryBean.setMapperLocations(resources);
        return sqlSessionFactoryBean.getObject();
    }


    @Bean
    public PlatformTransactionManager transactionManager(DynamicDataSource dynamicDataSource){
        return new DataSourceTransactionManager(dynamicDataSource);
    }
}

3.4.7 动态数据源切面

/**
 * 动态数据源切面
 * 
 * @author sure 
 */
@Aspect
@Component
public class DataSourceAspect {

    @Pointcut("execution(public * com.sure.learnmybatisdynamicdatasource.mapper..*.*(..))")
    public void aop(){}


    @Around("aop()")
    public Object around(ProceedingJoinPoint joinPoint) throws Throwable {
        Object[] args = joinPoint.getArgs();
        if (Objects.nonNull(args) && args.length>0){
            DataSourceContextHolder.setDataSource(args[0].toString());
        }
        try {
            return joinPoint.proceed();
        }finally {
            DataSourceContextHolder.clearDataSource();
        }
    }
}

3.4.8 编写Mapper

注意mapper中的第一个参数预留来指定数据源

@Mapper
public interface RbacUserMapper {

    Integer save(String dataSource, @Param("user") RbacUserDO rbacUserDO);

    RbacUserDO get(String dataSource, @Param("id") Integer id);

}

<mapper namespace="com.sure.learnmybatisdynamicdatasource.mapper.RbacUserMapper">

    <insert id="save" keyproperty="id">
        insert into rbac_user(name) values (#{user.name})
    </insert>

    <select id="get" resulttype="com.sure.learnmybatisdynamicdatasource.domain.RbacUserDO">
        select id,name from rbac_user where id = #{id}
    </select>

</mapper>

3.4.9 编写service

同样注意方法参数中的数据源参数

@Service
public class RbacUserService {

    @Autowired
    private RbacUserMapper rbacUserMapper;


    public Integer save(String dataSource, RbacUserDO rbacUserDO){
        return rbacUserMapper.save(dataSource, rbacUserDO);
    }

    public RbacUserDO get(String dataSource, Integer id){
        RbacUserDO rbacUserDO = rbacUserMapper.get(dataSource, id);
        return rbacUserDO;
    }

}

3.4.10 编写Controller层

通用返回参数

@Data
public class Result<T> implements Serializable {

    private Integer code;

    private String msg;

    private T data;

    private Result(Integer code , String msg){
        this.code = code;
        this.msg = msg;
    }

    private Result(Integer code , String msg, T data){
        this.code = code;
        this.msg = msg;
        this.data = data;
    }

    public static  Result ok(){
        return new Result(ResultEnum.SUCCESS.code,ResultEnum.SUCCESS.msg);
    }

    public static <T> Result ok(T data){
        return new Result(ResultEnum.SUCCESS.code,ResultEnum.SUCCESS.msg,data);
    }

    public static  Result error(){
        return new Result(ResultEnum.ERROR.code,ResultEnum.ERROR.msg);
    }

    public static  Result error(Integer code,String msg){
        return new Result(code,msg);
    }

}

状态码枚举

public enum ResultEnum {
    SUCCESS(1,"success"),
    ERROR(-1,"system error");

    public Integer code;

    public String msg;

    ResultEnum (Integer code,String msg){
        this.code = code;
        this.msg = msg;
    }
}

使用path参数来指定业务平台

@RestController
@RequestMapping("api/1")
public class RbacUserController {

    @Autowired
    public RbacUserService rbacUserService;

    @PostMapping("{platform}/user")
    public Result addUser(@PathVariable String platform, RbacUserDO rbacUserDO){

        return Result.ok(rbacUserService.save(platform, rbacUserDO));
    }


    @GetMapping("{platform}/user/{id}")
    public Result getUser(@PathVariable("platform") String platform,@PathVariable("id") Integer id){
        RbacUserDO rbacUserDO = rbacUserService.get(platform, id);
        return Result.ok(rbacUserDO);
    }

}

4. 踩到的坑

注意切面类返回值！！！