当前位置： 首页 > news >正文

spring boot 整合多数据源

news 2025/7/17 21:32:08

多数据源产生的场景

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

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

 注入数据源选择的时机

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

环境准备

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


create database stu;
create database tech;
use stu;
create table student
(id varchar(50) not null comment '主键',name varchar(50) null comment '姓名',stu_no varchar(50) null comment '学号',constraint student_pk primary key (id)
);insert into student values ('1','张同学','111');
insert into student values ('2','李同学','222');
use tech;
create table teacher
(id varchar(50) not null comment '主键',name varchar(50) null comment '姓名',teach_no varchar(50) null comment '教师号',constraint teacher_pk primary key (id)
);insert into teacher values ('1','王老师','111');
insert into teacher values ('2','高老师','222');

实现DataSource方式实现多数据源

配置多数据源

server:port: 9000
spring:datasource:type: com.alibaba.druid.pool.DruidDataSourcedatasource1:url: jdbc:mysql://shilei.tech:3306/stu?useSSL=true&serverTimezone=Asia/Shanghaiusername: rootpassword: root123456driver-class-name: com.mysql.cj.jdbc.Driverdatasource2:url: jdbc:mysql://shilei.tech:3306/tech?useSSL=true&serverTimezone=Asia/Shanghaiusername: rootpassword: root123456driver-class-name: com.mysql.cj.jdbc.Driverdruid:initial-size: 5min-idle: 1max-active: 20mybatis-plus:mapper-locations: classpath:/mapper/*.xmltype-aliases-package: com.datasource.dynamicdatasource.model

添加数据源配置

package com.datasource.dynamicdatasource.config;import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;import javax.sql.DataSource;/*** @author sl*/
@Configuration
public class DataSourceConfig {@Bean("dataSource1")@ConfigurationProperties(prefix = "spring.datasource.datasource1")public DataSource dataSource1(){DruidDataSource druidDataSource = new DruidDataSource();return druidDataSource;}@Bean("dataSource2")@ConfigurationProperties(prefix = "spring.datasource.datasource2")public DataSource dataSource2(){DruidDataSource druidDataSource = new DruidDataSource();return druidDataSource;}
}

 实现DataSource多数据源

package com.datasource.dynamicdatasource.config;import org.springframework.beans.factory.InitializingBean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;import javax.annotation.Resource;
import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.logging.Logger;/*** @author sl* @Primary主要注入的bean*/
@Configuration
@Primary
public class DynamicDataSource implements DataSource {public static ThreadLocal<String> nameFlag = new ThreadLocal<>();@Resourceprivate DataSource dataSource1;@Resourceprivate DataSource dataSource2;@Overridepublic Connection getConnection() throws SQLException {if("student".equals(nameFlag.get())){return dataSource1.getConnection();}return dataSource2.getConnection();}@Overridepublic Connection getConnection(String username, String password) throws SQLException {return null;}@Overridepublic <T> T unwrap(Class<T> iface) throws SQLException {return null;}@Overridepublic boolean isWrapperFor(Class<?> iface) throws SQLException {return false;}@Overridepublic PrintWriter getLogWriter() throws SQLException {return null;}@Overridepublic void setLogWriter(PrintWriter out) throws SQLException {}@Overridepublic void setLoginTimeout(int seconds) throws SQLException {}@Overridepublic int getLoginTimeout() throws SQLException {return 0;}@Overridepublic Logger getParentLogger() throws SQLFeatureNotSupportedException {return null;}
}

测试多数据源

package com.datasource.dynamicdatasource.controller;import com.datasource.dynamicdatasource.config.DynamicDataSource;
import com.datasource.dynamicdatasource.model.Student;
import com.datasource.dynamicdatasource.model.Teacher;
import com.datasource.dynamicdatasource.service.StudentService;
import com.datasource.dynamicdatasource.service.TeacherService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;import java.util.List;/*** @author sl*/
@RestController
public class TestDataSourceController {@Autowiredprivate StudentService studentService;@Autowiredprivate TeacherService teacherService;@GetMapping("/stu")public String getStu(){DynamicDataSource.nameFlag.set("student");List<Student> allStudent = studentService.findAllStudent();return allStudent.toString();}@GetMapping("/tech")public String getTech(){DynamicDataSource.nameFlag.set("teacher");List<Teacher> allTeacher = teacherService.findAllTeacher();return allTeacher.toString();}
}

效果如下所示

此实现方式的弊端

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

继承AbstractRoutingDataSource实现多数据源

AbstractRoutingDataSource的结构

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

AbstractRoutingDataSource的重要属性 

targetDataSources 所有数据源 (需指定)

defaultTargetDataSource 默认数据源(需指定)

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

继承AbstractRoutingDataSource实现多数据源

package com.datasource.dynamicdatasource.config;import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;/*** @author sl* @Primary主要注入的bean*/
@Configuration
@Primary
public class DynamicDataSource extends AbstractRoutingDataSource {public static ThreadLocal<String> nameFlag = new ThreadLocal<>();@Resourceprivate DataSource dataSource1;@Resourceprivate DataSource dataSource2;@Overrideprotected Object determineCurrentLookupKey() {// 返回当前数据源的标识return nameFlag.get();}@Overridepublic void afterPropertiesSet() {// 为targetDataSources 初始化所有数据源Map<Object,Object> targetDataSources=new HashMap<>();targetDataSources.put("student",dataSource1);targetDataSources.put("teacher",dataSource2);super.setTargetDataSources(targetDataSources);// 设置默认数据源super.setDefaultTargetDataSource(dataSource1);// 循环给resolvedDataSources，也就是最终数据源mapsuper.afterPropertiesSet();}
}

determineCurrentLookupKey的作用 

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

	protected DataSource determineTargetDataSource() {Assert.notNull(this.resolvedDataSources, "DataSource router not initialized");Object lookupKey = determineCurrentLookupKey();DataSource dataSource = this.resolvedDataSources.get(lookupKey);if (dataSource == null && (this.lenientFallback || lookupKey == null)) {dataSource = this.resolvedDefaultDataSource;}if (dataSource == null) {throw new IllegalStateException("Cannot determine target DataSource for lookup key [" + lookupKey + "]");}return dataSource;}

 测试多数据源

package com.datasource.dynamicdatasource.controller;import com.datasource.dynamicdatasource.config.DynamicDataSource;
import com.datasource.dynamicdatasource.model.Student;
import com.datasource.dynamicdatasource.model.Teacher;
import com.datasource.dynamicdatasource.service.StudentService;
import com.datasource.dynamicdatasource.service.TeacherService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;import java.util.List;/*** @author sl*/
@RestController
public class TestDataSourceController {@Autowiredprivate StudentService studentService;@Autowiredprivate TeacherService teacherService;@GetMapping("/stu")public String getStu(){// 默认数据源就是studentList<Student> allStudent = studentService.findAllStudent();return allStudent.toString();}@GetMapping("/tech")public String getTech(){DynamicDataSource.nameFlag.set("teacher");List<Teacher> allTeacher = teacherService.findAllTeacher();return allTeacher.toString();}
}

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

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

自定义注解

package com.datasource.dynamicdatasource.annotation;import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;/*** @author sl*/
@Target({ElementType.TYPE, ElementType.METHOD})
@Retention(RetentionPolicy.RUNTIME)
public @interface MyDataSource {String value() default "student";
}

配置切面

<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-aop</artifactId>
</dependency>
package com.datasource.dynamicdatasource.aspect;import com.datasource.dynamicdatasource.annotation.MyDataSource;
import com.datasource.dynamicdatasource.config.DynamicDataSource;
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 sl*@Aspect 标识是一个切面*/
@Aspect
@Component
public class DatasourceAspect {/*** 切点规则*/@Pointcut("@annotation(com.datasource.dynamicdatasource.annotation.MyDataSource)")public void pointcut() {}@Before("pointcut()")public void dataSourceAspect(JoinPoint joinPoint){// 获取方法Method method = ((MethodSignature) joinPoint.getSignature()).getMethod();// 判断方法中是否添加了注解if(method.isAnnotationPresent(MyDataSource.class)){// 获取方法上的注解MyDataSource annotation = method.getAnnotation(MyDataSource.class);String value = annotation.value();//  设置数据源DynamicDataSource.nameFlag.set(value);}}
}

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

    @GetMapping("/tech")@MyDataSource("teacher")public String getTech(){List<Teacher> allTeacher = teacherService.findAllTeacher();return allTeacher.toString();}

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

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

引入依赖

<dependency><groupId>com.baomidou</groupId><artifactId>dynamic-datasource-spring-boot-starter</artifactId><version>3.6.1</version>
</dependency>

配置数据源

server:port: 9000
spring:datasource:type: com.alibaba.druid.pool.DruidDataSourcedynamic:#设置默认的数据源或者数据源组,默认值即为masterprimary: master#严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源strict: falsedatasource:master:url: jdbc:mysql://shilei.tech:3306/stu?useSSL=true&serverTimezone=Asia/Shanghaiusername: rootpassword: root123456driver-class-name: com.mysql.cj.jdbc.Driverteacher:url: jdbc:mysql://shilei.tech:3306/tech?useSSL=true&serverTimezone=Asia/Shanghaiusername: rootpassword: root123456driver-class-name: com.mysql.cj.jdbc.Driverdruid:initial-size: 5min-idle: 1max-active: 20mybatis-plus:mapper-locations: classpath:/mapper/*.xmltype-aliases-package: com.datasource.dynamicdatasource.model

测试数据源切换

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

package com.datasource.dynamicdatasource.controller;import com.baomidou.dynamic.datasource.annotation.DS;
import com.datasource.dynamicdatasource.model.Student;
import com.datasource.dynamicdatasource.model.Teacher;
import com.datasource.dynamicdatasource.service.StudentService;
import com.datasource.dynamicdatasource.service.TeacherService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;import java.util.List;/*** @author sl*/
@RestController
public class TestDataSourceController {@Autowiredprivate StudentService studentService;@Autowiredprivate TeacherService teacherService;@GetMapping("/stu")public String getStu(){List<Student> allStudent = studentService.findAllStudent();return allStudent.toString();}@GetMapping("/tech")@DS("teacher")public String getTech(){List<Teacher> allTeacher = teacherService.findAllTeacher();return allTeacher.toString();}
}

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

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

需要注意的问题

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

http://www.lryc.cn/news/166896.html

相关文章：

  • 数据集成：数据挖掘的准备工作之一
  • xml配置文件密码特殊字符处理
  • 遥感数据与作物模型同化
  • UI库DHTMLX Suite v8.2发布全新表单组件，让Web表单实现高度可定制！
  • 河北省图书馆典藏《乡村振兴振兴战略下传统村落文化旅游设计》许少辉八一新著
  • 什么是卷积002
  • 黑马JVM总结（八）
  • 开源网安入选广东省网络空间安全标准化技术委员会新技术及应用安全技术工作组成员单位
  • Nginx配置指南：如何定位、解读与优化Linux上的Nginx设置
  • 辉瑞与吉利德科学：制药巨头的新冠病毒之战
  • x86架构基础汇编知识
  • ThreadLocal的原理
  • Chrome 108版（64-bit 108.0.5359.125）网盘下载
  • Mars3d插件参考开发教程并在相关页面引入
  • Windows 性能突然打鸡血，靠 Bug 修复了多年顽疾
  • 亚马逊封买家账号的原因有哪些
  • 1.0零基础尝试DCM通讯（c-store）
  • vue之封装tab类组件
  • 固定资产管理中净值怎么算
  • SQlite操作后如何正确退出
  • phpcmsV9.6.0sql注入漏洞分析
  • 深入理解正则表达式：高效处理文本数据的利器
  • 张雪峰说网络空间安全专业
  • day11-ArrayList学生管理系统
  • java springboot 如何实现小程序支付
  • 题目：2839.判断通过操作能否让字符串相等 I
  • 【Prometheus】Prometheus+Grafana部署
  • 无CDN场景下的传统架构接入阿里云WAF防火墙的配置实践
  • 和鲸技术！国家气象信息中心人工智能气象应用基础技术平台上线
  • GIT高级使用技巧