本文中使用的数据源为HikariCP,实现数据源之间的切换用@DataSource自定义注解,配置AOP进行切换。需要引用的包此处不再说明,yml中mysql相关配置如下:
datasource:
# 动态数据源配置
dynamic:
hikari:
# 池中维护的最小空闲连接数
min-idle: 5
# 池中最大连接数,包括闲置和使用中的连接
max-pool-size: 20
# 池中连接最长生命周期
max-lifetime: 1800000
# 自动提交从池中返回的连接
is-auto-commit: true
# 连接允许在池中闲置的最长时间
idle-timeout: 30000
# 等待来自池的连接的最大毫秒数
connection-timeout: 30000
# 连接池名称
pool-name: HikariCP_1
primary: master #设置默认的数据源或者数据源组,默认值即为master
# 开启seata
seata: false
datasource:
# 主数据库
master:
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://${MYQSL_HOST:localhost}:${MYSQL_PORT:3306}/table?useUnicode=true&useSSL=false&characterEncoding=utf8&allowMultiQueries=true&rewriteBatchedStatements=true&serverTimezone=Asia/Shanghai
type: com.zaxxer.hikari.HikariDataSource
# 用户数据库
slave:
username: root
password: root
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://${MYQSL_HOST:localhost}:${MYSQL_PORT:3306}/table_1?useUnicode=true&useSSL=false&characterEncoding=utf8&allowMultiQueries=true&rewriteBatchedStatements=true&serverTimezone=Asia/Shanghai
type: com.zaxxer.hikari.HikariDataSource
1、先创建一个动态数据源切换类
package com.flying.demo.config;
import lombok.NoArgsConstructor;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
import javax.sql.DataSource;
import java.util.Map;
@NoArgsConstructor
public class DynamicDataSource extends AbstractRoutingDataSource {
private static final ThreadLocal DB_CONTEXT_HOLDER = new ThreadLocal<>();
@Override
protected Object determineCurrentLookupKey() {
return getDataSource();
}
public static void setDataSource(String dataSource) {
DB_CONTEXT_HOLDER.set(dataSource);
}
public static String getDataSource() {
return DB_CONTEXT_HOLDER.get();
}
public static void clearDataSource() {
DB_CONTEXT_HOLDER.remove();
}
public DynamicDataSource(DataSource defaultTargetDataSource, Map
2、多数据源配置类
package com.flying.demo.config;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;
@Component
@Configuration
public class DataSourceConfig {
@Bean(name = "master")
@ConfigurationProperties(prefix = "spring.datasource.dynamic.datasource.master")
public DataSource masterDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "slave")
@ConfigurationProperties(prefix = "spring.datasource.dynamic.datasource.slave")
public DataSource slaveDataSource() {
return DataSourceBuilder.create().build();
}
@Bean
@Primary
public DataSource dataSource(@Qualifier("master") DataSource master,
@Qualifier("slave") DataSource slave) {
Map
需要在启动类上引入此配置,并且排除springboot数据源的自动配置,代码如下:
//@SpringBootApplication
@SpringBootApplication(exclude = {DataSourceAutoConfiguration.class})
@Import({DataSourceConfig.class})
public class DemoApplication {
public static void main(String[] args) {
SpringApplication.run(DemoApplication.class, args);
}
}
3、创建自定义注解,用来指定使用哪个数据源
package com.flying.demo.common.annotation;
import java.lang.annotation.*;
@Target({ ElementType.METHOD, ElementType.TYPE })
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface DataSource {
String name() default "";
}
4、创建AOP,根据注解的参数切换数据源
package com.flying.demo.common.aop;
import com.flying.demo.common.annotation.DataSource;
import com.flying.demo.config.DynamicDataSource;
import lombok.extern.slf4j.Slf4j;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Pointcut;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.core.annotation.Order;
import org.springframework.stereotype.Component;
import java.lang.reflect.Method;
import java.util.Objects;
@Aspect
@Component
@Order(-100)
@Slf4j
public class DataSourceAspect {
@Pointcut("execution(* com.flying.demo.service..*.*(..))")
private void dsPointCut() {
}
@Around("dsPointCut()")
public Object around(ProceedingJoinPoint joinPoint) throws Throwable {
// 获取当前指定的数据源
MethodSignature ms = (MethodSignature) joinPoint.getSignature();
Method method = ms.getMethod();
DataSource dataSource = method.getAnnotation(DataSource.class);
if (Objects.isNull(dataSource)) {
// 使用默认数据源
DynamicDataSource.setDataSource("master");
log.info("未匹配到数据源,使用默认数据源");
} else {
// 匹配到的话,设置到动态数据源上下文中
DynamicDataSource.setDataSource(dataSource.name());
log.info("匹配到数据源:{}", dataSource.name());
}
try {
// 执行目标方法,返回值即为当前方法的返回值
return joinPoint.proceed();
} finally {
// 方法执行完毕之后,销毁当前数据源信息,进行垃圾回收
DynamicDataSource.clearDataSource();
log.info("当前数据源已清空");
}
}
}
5、测试
新建数据库 table 和 table_1,两个数据库中分别创建教师表teacher如下:
CREATE TABLE `teacher` ( `guid` bigint(20) NOT NULL COMMENT '主键ID', `cn_name` varchar(32) DEFAULT NULL COMMENT '中文名', `en_name` varchar(32) DEFAULT NULL COMMENT '英文名', `gender` varchar(8) DEFAULT NULL COMMENT '性别', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`guid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
两个数据库中的teacher表中各新增一条数据如图:
table.teacher
table_1.teacher
写两个查询方法,getOneById()查询主数据库master(即table),getAll()查询从数据库slave(即table_1)
@Service public class TeacherServiceImpl extends ServiceImplimplements TeacherService { @Override @DataSource(name = "master") public Teacher getOneById(Long teacherId) { return this.getById(teacherId); } @Override @DataSource(name = "slave") public List getAll() { return this.list(); } }
@Slf4j
@RestController
@RequestMapping("/teacher")
public class TeacherController {
@Autowired
TeacherService teacherService;
@ApiOperation(value = "查询")
@GetMapping("/query")
public void query(@RequestParam("teacherId") Long teacherId) {
Teacher teacher = teacherService.getOneById(teacherId);
System.out.println("DB:ffsong Teacher.name = " + teacher.getCnName());
List teachers = teacherService.getAll();
System.out.println("DB:ffsong_1 Teacher.name = " + teachers.get(0).getCnName());
}
}
同时调用以上两个方法,结果如下:
mybatis-plus 官方文档中提出了另一种更加便捷的方案,使用 @DS切换数据源。详细可到官网中查看。
https://baomidou.com/pages/a61e1b/#%E6%96%87%E6%A1%A3-documentation
2、配置数据源com.baomidou dynamic-datasource-spring-boot-starter ${version}
在文章最开始的部分已经配置过了。
3、通过@DS切换数据源@DS 可以注解在方法上或类上,同时存在就近原则 方法上注解 优先于 类上注解。
新建数据库 table 和 table_1,两个数据库中分别创建教师表student如下:
CREATE TABLE `student` ( `guid` bigint(20) NOT NULL COMMENT '主键ID', `cn_name` varchar(32) DEFAULT NULL COMMENT '中文名', `en_name` varchar(32) DEFAULT NULL COMMENT '英文名', `gender` varchar(8) DEFAULT NULL COMMENT '性别', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`guid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
两个数据库中的student表中各新增一条数据如图:
table.student
table_1.student
写两个查询方法,分别查询两个库里的数据
@Service
@DS("master")
public class StudentServiceImpl extends ServiceImpl implements StudentService {
@Override
public Student getOneById(Long studentId) {
return this.getById(studentId);
}
@Override
@DS("slave")
public List getAll() {
return this.list();
}
}
@Slf4j
@RestController
@RequestMapping("/student")
public class StudentController {
@Autowired
StudentService studentService;
@ApiOperation(value = "查询", tags = "多数据源")
@GetMapping("/query")
public void query(@RequestParam("studentId") Long studentId, @RequestParam("teacherId") Long teacherId) {
Student student = studentService.getOneById(studentId);
System.out.println("DB:table Student.name = " + student.getCnName());
List students = studentService.getAll();
System.out.println("DB:table_1 Student.name = " + students.get(0).getCnName());
}
}
同时调用以上两个方法,结果如下:
【注意】
1、使用 @DS 注解切换数据源时,使用springboot数据源的自动配置,需要将DataSourceConfig配置注释掉
2、使用 @DataSource 自定义注解时,排除springboot数据源的自动配置,引入DataSourceConfig配置
3、使用 @DS 时,数据源配置为:spring.datasource.dynamic.datasource.master.url
4、使用 @DataSource 时,数据源配置为:spring.datasource.dynamic.datasource.master.jdbc-url



