• spring boot 整合多数据源

    多数据源产生的场景

    一般情况下，不会有多数据源这样的场景出现，但老项目或者特殊需求的项目，可能会有这样的场景

    • 同一个应用需要访问两个数据库
    • 不用数据库中间件的读写分离

     注入数据源选择的时机

    声明两个数据源实例，在getConnection的时候根据业务的不同，注入不同数据源的连接

    环境准备

    准备sql脚本，建立两个库，这里mysql为例

    1.  
    2. create database stu;
    3. create database tech;
    4. use stu;
    5. create table student
    6. (
    7.     id varchar(50) not null comment '主键',
    8.     name varchar(50) null comment '姓名',
    9.     stu_no varchar(50) null comment '学号',
    10.     constraint student_pk primary key (id)
    11. );
    12.  
    13. insert into student values ('1','张同学','111');
    14. insert into student values ('2','李同学','222');
    15. use tech;
    16. create table teacher
    17. (
    18.     id varchar(50) not null comment '主键',
    19.     name varchar(50) null comment '姓名',
    20.     teach_no varchar(50) null comment '教师号',
    21.     constraint teacher_pk primary key (id)
    22. );
    23.  
    24. insert into teacher values ('1','王老师','111');
    25. insert into teacher values ('2','高老师','222');

    实现DataSource方式实现多数据源

    配置多数据源

    1. server:
    2.   port: 9000
    3. spring:
    4.   datasource:
    5.     type: com.alibaba.druid.pool.DruidDataSource
    6.     datasource1:
    7.       url: jdbc:mysql://shilei.tech:3306/stu?useSSL=true&serverTimezone=Asia/Shanghai
    8.       username: root
    9.       password: root123456
    10.       driver-class-name: com.mysql.cj.jdbc.Driver
    11.  
    12.     datasource2:
    13.       url: jdbc:mysql://shilei.tech:3306/tech?useSSL=true&serverTimezone=Asia/Shanghai
    14.       username: root
    15.       password: root123456
    16.       driver-class-name: com.mysql.cj.jdbc.Driver
    17.     druid:
    18.       initial-size: 5
    19.       min-idle: 1
    20.       max-active: 20
    21.  
    22. mybatis-plus:
    23.   mapper-locations: classpath:/mapper/*.xml
    24.   type-aliases-package: com.datasource.dynamicdatasource.model

    添加数据源配置

    1. package com.datasource.dynamicdatasource.config;
    2.  
    3. import com.alibaba.druid.pool.DruidDataSource;
    4. import org.springframework.boot.context.properties.ConfigurationProperties;
    5. import org.springframework.context.annotation.Bean;
    6. import org.springframework.context.annotation.Configuration;
    7.  
    8. import javax.sql.DataSource;
    9.  
    10. /**
    11.  * @author sl
    12.  */
    13. @Configuration
    14. public class DataSourceConfig {
    15.  
    16.     @Bean("dataSource1")
    17.     @ConfigurationProperties(prefix = "spring.datasource.datasource1")
    18.     public DataSource dataSource1(){
    19.         DruidDataSource druidDataSource = new DruidDataSource();
    20.         return druidDataSource;
    21.     }
    22.  
    23.  
    24.     @Bean("dataSource2")
    25.     @ConfigurationProperties(prefix = "spring.datasource.datasource2")
    26.     public DataSource dataSource2(){
    27.         DruidDataSource druidDataSource = new DruidDataSource();
    28.         return druidDataSource;
    29.     }
    30. }

     实现DataSource多数据源

    1. package com.datasource.dynamicdatasource.config;
    2.  
    3. import org.springframework.beans.factory.InitializingBean;
    4. import org.springframework.context.annotation.Configuration;
    5. import org.springframework.context.annotation.Primary;
    6.  
    7. import javax.annotation.Resource;
    8. import javax.sql.DataSource;
    9. import java.io.PrintWriter;
    10. import java.sql.Connection;
    11. import java.sql.SQLException;
    12. import java.sql.SQLFeatureNotSupportedException;
    13. import java.util.logging.Logger;
    14.  
    15. /**
    16.  * @author sl
    17.  * @Primary主要注入的bean
    18.  */
    19. @Configuration
    20. @Primary
    21. public class DynamicDataSource implements DataSource {
    22.  
    23.  
    24.     public static ThreadLocal nameFlag = new ThreadLocal<>();
    25.  
    26.     @Resource
    27.     private DataSource dataSource1;
    28.  
    29.     @Resource
    30.     private DataSource dataSource2;
    31.  
    32.     @Override
    33.     public Connection getConnection() throws SQLException {
    34.         if("student".equals(nameFlag.get())){
    35.             return dataSource1.getConnection();
    36.         }
    37.         return dataSource2.getConnection();
    38.     }
    39.  
    40.     @Override
    41.     public Connection getConnection(String username, String password) throws SQLException {
    42.         return null;
    43.     }
    44.  
    45.     @Override
    46.     public  T unwrap(Class iface) throws SQLException {
    47.         return null;
    48.     }
    49.  
    50.     @Override
    51.     public boolean isWrapperFor(Class iface) throws SQLException {
    52.         return false;
    53.     }
    54.  
    55.     @Override
    56.     public PrintWriter getLogWriter() throws SQLException {
    57.         return null;
    58.     }
    59.  
    60.     @Override
    61.     public void setLogWriter(PrintWriter out) throws SQLException {
    62.  
    63.     }
    64.  
    65.     @Override
    66.     public void setLoginTimeout(int seconds) throws SQLException {
    67.  
    68.     }
    69.  
    70.     @Override
    71.     public int getLoginTimeout() throws SQLException {
    72.         return 0;
    73.     }
    74.  
    75.     @Override
    76.     public Logger getParentLogger() throws SQLFeatureNotSupportedException {
    77.         return null;
    78.     }
    79. }

    测试多数据源

    1. package com.datasource.dynamicdatasource.controller;
    2.  
    3.  
    4. import com.datasource.dynamicdatasource.config.DynamicDataSource;
    5. import com.datasource.dynamicdatasource.model.Student;
    6. import com.datasource.dynamicdatasource.model.Teacher;
    7. import com.datasource.dynamicdatasource.service.StudentService;
    8. import com.datasource.dynamicdatasource.service.TeacherService;
    9. import org.springframework.beans.factory.annotation.Autowired;
    10. import org.springframework.web.bind.annotation.GetMapping;
    11. import org.springframework.web.bind.annotation.RestController;
    12.  
    13. import java.util.List;
    14.  
    15. /**
    16.  * @author sl
    17.  */
    18. @RestController
    19. public class TestDataSourceController {
    20.  
    21.     @Autowired
    22.     private StudentService studentService;
    23.  
    24.     @Autowired
    25.     private TeacherService teacherService;
    26.  
    27.     @GetMapping("/stu")
    28.     public String getStu(){
    29.         DynamicDataSource.nameFlag.set("student");
    30.         List allStudent = studentService.findAllStudent();
    31.         return allStudent.toString();
    32.     }
    33.  
    34.     @GetMapping("/tech")
    35.     public String getTech(){
    36.         DynamicDataSource.nameFlag.set("teacher");
    37.         List allTeacher = teacherService.findAllTeacher();
    38.         return allTeacher.toString();
    39.     }
    40. }

    效果如下所示

    此实现方式的弊端

    实现DataSource接口我们本质上只使用了一个方法，就是getConnection()这个无参的方法，其他方法，当内部调用时可能会导致错误，我们不可能实现所有的方法，所以我们继承AbstractRoutingDataSource抽象类

    继承AbstractRoutingDataSource实现多数据源

    AbstractRoutingDataSource的结构

    可以看到AbstractRoutingDataSource继承自DataSource，提供了一些实现方法

    AbstractRoutingDataSource的重要属性 

    targetDataSources 所有数据源 (需指定)

    defaultTargetDataSource 默认数据源(需指定)

    resolvedDataSources= targetDataSources 负责最终切换的数据源map 等于 tagetDataSources

    继承AbstractRoutingDataSource实现多数据源

    1. package com.datasource.dynamicdatasource.config;
    2.  
    3. import org.springframework.context.annotation.Configuration;
    4. import org.springframework.context.annotation.Primary;
    5. import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
    6.  
    7. import javax.annotation.Resource;
    8. import javax.sql.DataSource;
    9. import java.util.HashMap;
    10. import java.util.Map;
    11.  
    12.  
    13. /**
    14.  * @author sl
    15.  * @Primary主要注入的bean
    16.  */
    17. @Configuration
    18. @Primary
    19. public class DynamicDataSource extends AbstractRoutingDataSource {
    20.  
    21.  
    22.     public static ThreadLocal nameFlag = new ThreadLocal<>();
    23.  
    24.     @Resource
    25.     private DataSource dataSource1;
    26.  
    27.     @Resource
    28.     private DataSource dataSource2;
    29.  
    30.  
    31.     @Override
    32.     protected Object determineCurrentLookupKey() {
    33.         // 返回当前数据源的标识
    34.         return nameFlag.get();
    35.     }
    36.  
    37.     @Override
    38.     public void afterPropertiesSet() {
    39.  
    40.         // 为targetDataSources 初始化所有数据源
    41.         Map targetDataSources=new HashMap<>();
    42.  
    43.         targetDataSources.put("student",dataSource1);
    44.         targetDataSources.put("teacher",dataSource2);
    45.  
    46.         super.setTargetDataSources(targetDataSources);
    47.         // 设置默认数据源
    48.         super.setDefaultTargetDataSource(dataSource1);
    49.  
    50.         // 循环给resolvedDataSources，也就是最终数据源map
    51.         super.afterPropertiesSet();
    52.  
    53.     }
    54. }

    determineCurrentLookupKey的作用 

    看一段源码，就是通过determineCurrentLookupKey获取数据源的key，然后去resolvedDataSources中取数据源，resolvedDataSources数据源其实就是targetDataSources

    1. 	protected DataSource determineTargetDataSource() {
    2. 		Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");
    3. 		Object lookupKey = determineCurrentLookupKey();
    4. 		DataSource dataSource = this.resolvedDataSources.get(lookupKey);
    5. 		if (dataSource == null && (this.lenientFallback || lookupKey == null)) {
    6. 			dataSource = this.resolvedDefaultDataSource;
    7. 		}
    8. 		if (dataSource == null) {
    9. 			throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");
    10. 		}
    11. 		return dataSource;
    12. 	}

     测试多数据源

    1. package com.datasource.dynamicdatasource.controller;
    2.  
    3.  
    4. import com.datasource.dynamicdatasource.config.DynamicDataSource;
    5. import com.datasource.dynamicdatasource.model.Student;
    6. import com.datasource.dynamicdatasource.model.Teacher;
    7. import com.datasource.dynamicdatasource.service.StudentService;
    8. import com.datasource.dynamicdatasource.service.TeacherService;
    9. import org.springframework.beans.factory.annotation.Autowired;
    10. import org.springframework.web.bind.annotation.GetMapping;
    11. import org.springframework.web.bind.annotation.RestController;
    12.  
    13. import java.util.List;
    14.  
    15. /**
    16.  * @author sl
    17.  */
    18. @RestController
    19. public class TestDataSourceController {
    20.  
    21.     @Autowired
    22.     private StudentService studentService;
    23.  
    24.     @Autowired
    25.     private TeacherService teacherService;
    26.  
    27.     @GetMapping("/stu")
    28.     public String getStu(){
    29.         // 默认数据源就是student
    30.         List allStudent = studentService.findAllStudent();
    31.         return allStudent.toString();
    32.     }
    33.  
    34.     @GetMapping("/tech")
    35.     public String getTech(){
    36.         DynamicDataSource.nameFlag.set("teacher");
    37.         List allTeacher = teacherService.findAllTeacher();
    38.         return allTeacher.toString();
    39.     }
    40. }

    AOP自定义注解方式+AbstractRoutingDataSource实现多数据源

    数据源的切换还是使用AbstractRoutingDataSource，只不过切换方式采用aop拦截自定义注解切换数据源，这种方式也是mybatis-plus多数据源插件所采用的方式

    自定义注解

    1. package com.datasource.dynamicdatasource.annotation;
    2.  
    3. import java.lang.annotation.ElementType;
    4. import java.lang.annotation.Retention;
    5. import java.lang.annotation.RetentionPolicy;
    6. import java.lang.annotation.Target;
    7.  
    8. /**
    9.  * @author sl
    10.  */
    11. @Target({ElementType.TYPE, ElementType.METHOD})
    12. @Retention(RetentionPolicy.RUNTIME)
    13. public @interface MyDataSource {
    14.  
    15.     String value() default "student";
    16. }

    配置切面

    1. <dependency>
    2.    <groupId>org.springframework.bootgroupId>
    3.    <artifactId>spring-boot-starter-aopartifactId>
    4. dependency>
    1. package com.datasource.dynamicdatasource.aspect;
    2.  
    3. import com.datasource.dynamicdatasource.annotation.MyDataSource;
    4. import com.datasource.dynamicdatasource.config.DynamicDataSource;
    5. import org.aspectj.lang.JoinPoint;
    6. import org.aspectj.lang.annotation.Aspect;
    7. import org.aspectj.lang.annotation.Before;
    8. import org.aspectj.lang.annotation.Pointcut;
    9. import org.aspectj.lang.reflect.MethodSignature;
    10. import org.springframework.stereotype.Component;
    11.  
    12. import java.lang.reflect.Method;
    13.  
    14. /**
    15.  * @author sl
    16.  *@Aspect 标识是一个切面
    17.  */
    18. @Aspect
    19. @Component
    20. public class DatasourceAspect {
    21.  
    22.     /**
    23.      * 切点规则
    24.      */
    25.     @Pointcut("@annotation(com.datasource.dynamicdatasource.annotation.MyDataSource)")
    26.     public void pointcut() {
    27.     }
    28.  
    29.     @Before("pointcut()")
    30.     public void dataSourceAspect(JoinPoint joinPoint){
    31.         // 获取方法
    32.         Method method = ((MethodSignature) joinPoint.getSignature()).getMethod();
    33.  
    34.         // 判断方法中是否添加了注解
    35.         if(method.isAnnotationPresent(MyDataSource.class)){
    36.             // 获取方法上的注解
    37.             MyDataSource annotation = method.getAnnotation(MyDataSource.class);
    38.  
    39.             String value = annotation.value();
    40.             //  设置数据源
    41.             DynamicDataSource.nameFlag.set(value);
    42.         }
    43.     }
    44. }

    测试自定义注解切换数据源

    1.     @GetMapping("/tech")
    2.     @MyDataSource("teacher")
    3.     public String getTech(){
    4.         List allTeacher = teacherService.findAllTeacher();
    5.         return allTeacher.toString();
    6.     }

    dynamic-datasource多数据源组件实现多数据源

    官方文档及搭建指南地址：多数据源 | MyBatis-Plus

    引入依赖

    1. <dependency>
    2.   <groupId>com.baomidougroupId>
    3.   <artifactId>dynamic-datasource-spring-boot-starterartifactId>
    4.   <version>3.6.1version>
    5. dependency>

    配置数据源

    1. server:
    2.   port: 9000
    3. spring:
    4.   datasource:
    5.     type: com.alibaba.druid.pool.DruidDataSource
    6.     dynamic:
    7.       #设置默认的数据源或者数据源组,默认值即为master
    8.       primary: master
    9.       #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
    10.       strict: false
    11.       datasource:
    12.         master:
    13.           url: jdbc:mysql://shilei.tech:3306/stu?useSSL=true&serverTimezone=Asia/Shanghai
    14.           username: root
    15.           password: root123456
    16.           driver-class-name: com.mysql.cj.jdbc.Driver
    17.         teacher:
    18.           url: jdbc:mysql://shilei.tech:3306/tech?useSSL=true&serverTimezone=Asia/Shanghai
    19.           username: root
    20.           password: root123456
    21.           driver-class-name: com.mysql.cj.jdbc.Driver
    22.       druid:
    23.         initial-size: 5
    24.         min-idle: 1
    25.         max-active: 20
    26.  
    27. mybatis-plus:
    28.   mapper-locations: classpath:/mapper/*.xml
    29.   type-aliases-package: com.datasource.dynamicdatasource.model

    测试数据源切换

    数据源切换使用@DS注解，不使用此注解，使用默认数据源，方法上使用>类上使用 

    1. package com.datasource.dynamicdatasource.controller;
    2.  
    3.  
    4. import com.baomidou.dynamic.datasource.annotation.DS;
    5. import com.datasource.dynamicdatasource.model.Student;
    6. import com.datasource.dynamicdatasource.model.Teacher;
    7. import com.datasource.dynamicdatasource.service.StudentService;
    8. import com.datasource.dynamicdatasource.service.TeacherService;
    9. import org.springframework.beans.factory.annotation.Autowired;
    10. import org.springframework.web.bind.annotation.GetMapping;
    11. import org.springframework.web.bind.annotation.RestController;
    12.  
    13. import java.util.List;
    14.  
    15. /**
    16.  * @author sl
    17.  */
    18. @RestController
    19. public class TestDataSourceController {
    20.  
    21.     @Autowired
    22.     private StudentService studentService;
    23.  
    24.     @Autowired
    25.     private TeacherService teacherService;
    26.  
    27.     @GetMapping("/stu")
    28.     public String getStu(){
    29.         List allStudent = studentService.findAllStudent();
    30.         return allStudent.toString();
    31.     }
    32.  
    33.     @GetMapping("/tech")
    34.     @DS("teacher")
    35.     public String getTech(){
    36.         List allTeacher = teacherService.findAllTeacher();
    37.         return allTeacher.toString();
    38.     }
    39. }

    项目启动日志中可以看到两个数据源的加载信息

     访问tech以及stu都能正常访问，代表动态数据源添加成功

    需要注意的问题

    使用多数据源要注意事务的控制，提交和回滚策略，可以观看spring多数据源事务解决方案

  • 相关阅读:
    “磐云杯”中职网络安全技能大赛A模块新题
    ls命令-使用频率最高的命令
    微信公众号h5写一个全局调用微信分享功能
    JSP SSH车间生产管理系统myeclipse开发mysql数据库MVC模式java编程网页设计
    【php快速上手（十一）】
    23届前端面试选择题（长期更新）
    MybatisPlus简单使用与自定义sql以及通过自定义sql实现多表联查的分页查询
    基于文本信息抽取的列控车载设备故障发现
    ViT:Vision transformer的cls token如何实现分类？
    2D物理引擎 Box2D for javascript Games 第六章 关节和马达
  • 原文地址：https://blog.csdn.net/m0_65775063/article/details/132873594