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依赖
4.0.0
org.springframework.boot
spring-boot-starter-parent
2.4.0
com.sure
learn-mybatis-dynamic-datasource
0.0.1-SNAPSHOT
learn-mybatis-dynamic-datasource
Demo project for Spring Boot
1.8
org.springframework.boot
spring-boot-starter-web
org.mybatis.spring.boot
mybatis-spring-boot-starter
2.1.4
mysql
mysql-connector-java
runtime
org.projectlombok
lombok
true
org.springframework.boot
spring-boot-starter-aop
org.springframework.boot
spring-boot-starter-test
test
org.springframework.boot
spring-boot-maven-plugin
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 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 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);
}
insert into rbac_user(name) values (#{user.name})
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 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 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. 踩到的坑
注意切面类返回值！！！