在我们开发系统中,有时候要实现对不同的数据库进行插入和查询数据,这时我们系统要根据业务需求进行动态的数据源切换,实现的方式有两种,一种是使用myabtisPlus给我们提供的dynamic-datasource,在项目中增加动态数据源依赖,编写动态数据源配置文件可以指定默认的数据源,最后在需要指定的使用的数据源增加接口或者类使用@DS("数据源")即可,另外一种也是我们本次要需要实现的方式,通过继承AbstractRoutingDataSource,自定义的规则选择当前的数据源,在配合使用注解加Aop实现动态数据源切换。
一、具体实现步骤
实现的步骤:
- 添加mybatisPlus依赖和druid到pom文件中
- 在yml配置文件分别配置多个数据库
- 新建动态数据源类继承AbstractRoutingDataSource,重写路由方法determineCurrentLookupKey
- 配置数据源类,分别读取配置文件中的多个数据库为对应的DataSource
- 配置数据源类,实列化动态数据源类,进行多数据源配置
- 配置sqlSessionFactory 多数据源(DataSource)、事务(DataSourceTransactionManager)、yml配置文件(MybatisConfiguration)、拦截器(MybatisPlusInterceptor)等
- 使用ThreadLocal存储当前上下文使用的数据源,从而进行切换对应的数据源
- 定义注解、和定义切面
- 需要更换数据的类和方法进行增加对应的注解
1、分别添加对应的依赖mybatisPlus依赖和druid依赖如图所示
2、添加对应数据库的配置,如有多个数据库则配置多个,如图所示
3、创建动态数据源类实现AbstractRoutingDataSource,重写路由方determineCurrentLookupKey如图所示
4、使用ThreadLocal存储数据源名,从而在determineCurrentLookupKey进行获取当前存储的数据源名如图所示
5、进行配置多个数据源类、实列动态数据源类配置多数据源、配置sqlSessionFactory多数据源(DataSource)、事务(DataSourceTransactionManager)、yml配置文件(MybatisConfiguration)、拦截器(MybatisPlusInterceptor),MybatisPlusDataSourceConfig配置如下代码
package com.wukangyou.annotation.config.dynamic;
import com.alibaba.druid.pool.DruidDataSource;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.MybatisConfiguration;
import com.baomidou.mybatisplus.core.config.GlobalConfig;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
@Configuration
@MapperScan(value = "com.wukangyou.mapper")
public class MybatisPlusDataSourceConfig {
//数据源一
@Bean("db01")
@ConfigurationProperties(prefix = "spring.datasource.druid.db01")
public DataSource db01(){
return new DruidDataSource();
}
//数据源二
@Bean("db02")
@ConfigurationProperties(prefix = "spring.datasource.druid.db02")
public DataSource db02(){
return new DruidDataSource();
}
@Bean("dataSource")
public DataSource dynamicDataSource(@Qualifier("db01") DataSource db01,@Qualifier("db02") DataSource db02){
//多个数据源存储为map
Map<Object,Object> dataSource=new HashMap<>();
dataSource.put("db01",db01);
dataSource.put("db02",db02);
//实例化DynamicDataSource,设置数据源Map
DynamicDataSource dynamicDataSource = new DynamicDataSource();
//赋值多个数据源
dynamicDataSource.setTargetDataSources(dataSource);
//默认数据源
dynamicDataSource.setDefaultTargetDataSource(db01);
return dynamicDataSource;
}
//mybatisplus拦截器
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor(){
MybatisPlusInterceptor mybatisPlusInterceptor=new MybatisPlusInterceptor();
//分页插件
mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
//乐观锁
mybatisPlusInterceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
return mybatisPlusInterceptor;
}
//数据源事物
@Bean
@Primary
public DataSourceTransactionManager dataSourceTransactionManager(@Qualifier("dataSource") DataSource dataSource){
return new DataSourceTransactionManager(dataSource);
}
//mybatisplus配置文件
@Bean
@ConfigurationProperties(prefix = "mybatis-plus.configuration")
public MybatisConfiguration mybatisConfiguration(){
return new MybatisConfiguration();
}
//配置SqlSessionFactory工厂
@Bean
public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSource") DataSource dataSource) throws Exception {
MybatisSqlSessionFactoryBean sqlSessionFactoryBean=new MybatisSqlSessionFactoryBean();
//设置数据源
sqlSessionFactoryBean.setDataSource(dataSource);
//加载sql映射文件
sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath*:com/wukangyou.mapper/*mapper.xml"));
//自动填充策略
sqlSessionFactoryBean.setGlobalConfig(new GlobalConfig().setBanner(true).setMetaObjectHandler(new MyMetaObjectHandler()));
//拦截器
sqlSessionFactoryBean.setPlugins(mybatisPlusInterceptor());
//mybatisplus配置文件
sqlSessionFactoryBean.setConfiguration(mybatisConfiguration());
return sqlSessionFactoryBean.getObject();
}
//SqlSessionTemplate模板
@Bean
@Primary
public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactory") SqlSessionFactory sqlSessionFactory){
return new SqlSessionTemplate(sqlSessionFactory);
}
}
6、定义注解如图所示
7、定义动态数据源切面,DynamicDatasourceAspect如下代码
package com.wukangyou.annotation.config.aspect;
import com.wukangyou.annotation.config.annotation.DataDbAName;
import com.wukangyou.annotation.config.dynamic.DynamicDataSourceContextHolder;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.JoinPoint;
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 java.lang.reflect.Method;
/**
* @author :wukangyou
* @description: 动态数据源切面
* @date :2024-04-01 9:11
*/
@Aspect
@Component
@Slf4j
public class DynamicDatasourceAspect {
/**
* @Description: pointcutMethod(定义数据源切点)
* @Author: wukangyou
* @Date: 2024/4/1 9:26
*/
@Pointcut("execution(* com.wukangyou.service.*.*(..))")
private void pointcutMethod(){
}
@Before(value = "pointcutMethod()")
public void beforeSwitchDS(JoinPoint joinPoint) throws NoSuchMethodException {
//获取访问的类
Class<?> targetClass = joinPoint.getTarget().getClass();
//获取访问的方法名称
String methodName = joinPoint.getSignature().getName();
//获取到方法参数类型
Class[] parameterTypes = ((MethodSignature) joinPoint.getSignature()).getParameterTypes();
//找到对应的方法
Method targetMethod = targetClass.getDeclaredMethod(methodName, parameterTypes);
//是含有该注解
if (targetMethod.isAnnotationPresent(DataDbAName.class)){
//获取到注解值
DataDbAName dataDbAName =(DataDbAName) targetMethod.getAnnotation(DataDbAName.class);
String dbName = dataDbAName.value();
log.info("当前访问的数据源是:"+dbName);
DynamicDataSourceContextHolder.setContextHolder(dbName);
}
}
}
8、在对应的service方法使用注解进行指定数据源,如下代码
package com.wukangyou.service.imp;
import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.wukangyou.annotation.config.annotation.DataDbAName;
import org.springframework.stereotype.Service;
import com.wukangyou.mapper.UserMapper;
import com.wukangyou.pojo.User;
import com.wukangyou.request.UserReq;
import com.wukangyou.service.IUserService;
import javax.annotation.Resource;
/**
* @author :wukangyou
* @description: 用户服务层实现类
* @date :2024-03-26 22:59
*/
@Service
public class UserServiceimpl extends ServiceImpl<UserMapper, User> implements IUserService {
@Resource
private UserMapper userMapper;
@DataDbAName(value = "db01")
@Override
public IPage<User> queryUserPage(UserReq req) {
Page<User> page = new Page<>(req.getCurrent(),req.getSize());
IPage<User> selectPage = userMapper.selectPage(page, new QueryWrapper<User>().lambda()
.eq(StrUtil.isNotEmpty(req.getGuid()),User::getGuid,req.getGuid())
.eq(StrUtil.isNotEmpty(req.getName()),User::getName,req.getName())
.eq(StrUtil.isNotEmpty(req.getAge()),User::getAge,req.getAge())
.eq(StrUtil.isNotEmpty(req.getDbType()),User::getDbType,req.getDbType())
);
return selectPage;
}
@DataDbAName(value = "db02")
@Override
public boolean insertUser(UserReq req) {
User user = BeanUtil.toBean(req, User.class);
int insert = userMapper.insert(user);
if (insert>0) return true;
return false;
}
@DataDbAName
@Override
public boolean deleteUserById(int id) {
int deleteById = userMapper.deleteById(id);
if (deleteById>0) return true;
return false;
}
@DataDbAName
@Override
public boolean updateUserById(UserReq req) {
User user = userMapper.selectById(req.getGuid());
int update = userMapper.updateById(user);
if (update>0) return true;
return false;
}
}
9、编写对应的controller控制器如下代码
package com.wukangyou.controller;
import com.baomidou.mybatisplus.core.metadata.IPage;
import org.springframework.web.bind.annotation.*;
import com.wukangyou.pojo.User;
import com.wukangyou.request.IdReq;
import com.wukangyou.request.UserReq;
import com.wukangyou.response.JsonResult;
import com.wukangyou.service.IUserService;
import javax.annotation.Resource;
/**
* @author :wukangyou
* @description: 前段用户控制器
* @date :2024-03-26 15:19
*/
@RestController
@RequestMapping(value = "/user")
public class UserController {
@Resource
private IUserService iUserService;
@PostMapping("/queryPage")
@ResponseBody
public JsonResult<IPage<User>> queryUserPage(@RequestBody UserReq req){
return new JsonResult<>(iUserService.queryUserPage(req));
}
@PostMapping("/insertUser")
@ResponseBody
public JsonResult<String> insertUser(@RequestBody UserReq req){
boolean save = iUserService.insertUser(req);
if (save){
return new JsonResult<String>(true,200,"MSG-SS-0001");
}
return new JsonResult<String>(false,200,"MSG-SS-0002");
}
@PostMapping("/deleteUserById")
@ResponseBody
public JsonResult<String> deleteUserById(@RequestBody IdReq req){
boolean deleteflage = iUserService.deleteUserById(Integer.parseInt(req.getId()));
if (deleteflage){
return new JsonResult<String>(true,200,"MSG-SS-0001");
}
return new JsonResult<String>(false,200,"MSG-SS-0001");
}
@PostMapping("/updateUser")
@ResponseBody
public JsonResult<String> updateUser(@RequestBody UserReq req){
boolean update = iUserService.updateUserById(req);
if (update){
return new JsonResult<String>(true,200,"MSG-SS-0001");
}
return new JsonResult<String>(false,200,"MSG-SS-0002");
}
}
10、项目文件夹目录如图所示
11、在启动类上排除DruidDataSourceAutoConfigure.class,因为涉及到多数据源,使用自己的配置,如图所示,不然会报错,原因是配置多个数据源,不知道使用哪个数据源
11、启动项目进行测试结果
sql日志增加成功
数据库数据新增成功
二、总结
以上的就是mybatisPlus+druid实现多数据源的步骤,步骤仅做参考,有不对的地方,欢迎大家交流指导。