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

一篇搞懂springboot多数据源

news 2025/7/29 3:42:54

好文推荐

  • https://zhuanlan.zhihu.com/p/563949762

mybatis 配置多数据源

参考文章

  • https://blog.csdn.net/qq_38353700/article/details/118583828

使用mybatis配置多数据源我接触过的有两种方式，一种是通过java config的方式手动配置两个数据源，另一种方式便是使用mybatis-plus-dynamic。*

总体来说，配置主要包括，产生DataSource，然后是mybatis所需要的SqlSessionFactory，以及配置相应的事务管理器

示例代码

  • pom

            <!--MyBatis整合SpringBoot框架的起步依赖--><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.1.4</version></dependency><!-- Mysql驱动包 --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.27</version></dependency><!-- jdbc --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-jdbc</artifactId><version>2.7.8</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId></dependency><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><optional>true</optional></dependency>

  • 配置文件 yml

    server:port: 8080spring:datasource:master:jdbc-url: jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT%2B8username: rootpassword: 123456driver-class-name: com.mysql.cj.jdbc.Driverslave:jdbc-url: jdbc:mysql://43.143.217.124:3306/hongbei?characterEncoding=utf8&useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT%2B8username: rootpassword: 123456driver-class-name: com.mysql.cj.jdbc.Driver

  • java config 配置数据源一

    
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
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.boot.jdbc.DataSourceBuilder;
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;/*** @Classname DB1DataSourceConfig* @Description DB1DataSourceConfig* @Date 2023-02-24 15:14* @Created by lihw*/
@Configuration
@MapperScan(basePackages = "com.example.demo.mapper.master",sqlSessionFactoryRef = "masterSqlSessionFactory")
public class DB1DataSourceConfig {String MAPPER_LOCATION = "classpath*:abc/*.xml";@Primary@Bean("masterDataSource")@ConfigurationProperties(prefix = "spring.datasource.master")public DataSource getMasterDataSource() {return DataSourceBuilder.create().build();}@Primary@Bean("masterSqlSessionFactory")public SqlSessionFactory sqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {// 使用 mybatis plus  配置//MybatisSqlSessionFactoryBean mybatisSqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();//mybatisSqlSessionFactoryBean.setDataSource(dataSource);//mybatisSqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()//        .getResources(MAPPER_LOCATION)); // "classpath:mapping/*Mapper.xml"//mybatisSqlSessionFactoryBean.setTypeAliasesPackage("com.example.demo.entity");////return mybatisSqlSessionFactoryBean.getObject();// mybatis 配置SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();sqlSessionFactoryBean.setDataSource(dataSource);sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));sqlSessionFactoryBean.setTypeAliasesPackage("com.example.demo.entity.master");org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();configuration.setMapUnderscoreToCamelCase(true);sqlSessionFactoryBean.setConfiguration(configuration);return sqlSessionFactoryBean.getObject();}@Primary@Bean("masterSqlSessionTemplate")public SqlSessionTemplate  sqlSessionTemplate(@Qualifier("masterSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {SqlSessionTemplate sqlSessionTemplate = new SqlSessionTemplate(sqlSessionFactory);return sqlSessionTemplate;}@Bean("masterTransactionManager")public DataSourceTransactionManager transactionManager(@Qualifier("masterDataSource") DataSource dataSource) {DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager(dataSource);return dataSourceTransactionManager;}}

  • 配置数据源 二

    
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
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.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;import javax.sql.DataSource;/*** @Classname DB1DataSourceConfig* @Description DB1DataSourceConfig* @Date 2023-02-24 15:14* @Created by lihw*/
@Configuration
@MapperScan(basePackages = "com.example.demo.mapper.slave",sqlSessionFactoryRef = "slaveSqlSessionFactory")
public class DB2DataSourceConfig {String MAPPER_LOCATION = "classpath*:slave/*.xml";//@Primary@Bean("slaveDataSource")@ConfigurationProperties(prefix = "spring.datasource.slave")public DataSource getSlaveDataSource() {return DataSourceBuilder.create().build();}//@Primary@Bean("slaveSqlSessionFactory")public SqlSessionFactory sqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {// mybatis plus配置//MybatisSqlSessionFactoryBean mybatisSqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();//mybatisSqlSessionFactoryBean.setDataSource(dataSource);//mybatisSqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()//        .getResources(MAPPER_LOCATION)); // "classpath:mapping/*Mapper.xml"//mybatisSqlSessionFactoryBean.setTypeAliasesPackage("com.example.demo.entity");////return mybatisSqlSessionFactoryBean.getObject();// mybatis 配置SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();sqlSessionFactoryBean.setDataSource(dataSource);sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));sqlSessionFactoryBean.setTypeAliasesPackage("com.example.demo.entity.slave");// 设置mybatis配置org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();// 下划线转驼峰configuration.setMapUnderscoreToCamelCase(true);sqlSessionFactoryBean.setConfiguration(configuration);return sqlSessionFactoryBean.getObject();}//@Primary@Bean("slaveSqlSessionTemplate")public SqlSessionTemplate  sqlSessionTemplate(@Qualifier("slaveSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {SqlSessionTemplate sqlSessionTemplate = new SqlSessionTemplate(sqlSessionFactory);return sqlSessionTemplate;}@Bean("slaveTransactionManager")public DataSourceTransactionManager transactionManager(@Qualifier("slaveDataSource") DataSource dataSource) {DataSourceTransactionManager dataSourceTransactionManager = new DataSourceTransactionManager(dataSource);return dataSourceTransactionManager;}}

  • 目录结构

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-K95yKTJe-1677467535741)(https://note.youdao.com/yws/res/4010/WEBRESOURCE3a7424410d156d5dc0d6bd00cd7bf638 “image.png”)]

    • 测试接口

      @RestController
@RequestMapping("/test")
public class TestController {@AutowiredStudentMapper studentMapper;@AutowiredSysUserMapper sysUserMapper;// 数据源一 查询@GetMapping("list")public List<Student> getUserList(){List<Student> userList = studentMapper.getUserList();System.out.println(userList);return userList;}// 数据源二 查询@GetMapping("msg2")public String getmsg2(){List<SysUser> user = sysUserMapper.getUserList();System.out.println(user);return "msg22";}}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZSCJmOj9-1677467535743)(https://note.youdao.com/yws/res/4016/WEBRESOURCE0cc80d06ca0c483d00bb9e34f16ef10d)]

使用 mybatis-plus-dynamic 配置多数据源

  • pom

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

  • yml

    
# mybatis-plus-dynamic 配置多数据源
spring:datasource:dynamic:datasource:master:url: jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT%2B8username: rootpassword: 123456driver-class-name: com.mysql.cj.jdbc.Driverslave:url: jdbc:mysql://43.143.217.124:3306/hongbei?characterEncoding=utf8&useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT%2B8username: rootpassword: 123456driver-class-name: com.mysql.cj.jdbc.Driver# 指定主数据库primary: master#mybatis:
#  mapper-locations: classpath*:abc/*.xml,classpath*:slave/*.xmlmybatis-plus:mapper-locations: classpath*:abc/*.xml,classpath*:slave/*.xml

  • 使用案例

    @Service
@DS("slave-1")
public class TbServiceImpl extends ServiceImpl<TbDao, TbBean> implements TbService {@Overridepublic String save1() {TbBean tbBean = new TbBean();tbBean.setName("王五");tbBean.setSubject("英语");tbBean.setScore(113);this.save(tbBean);return "success";}
}

此处是模拟的一个新增操作，注意类上面的@DS注解，该注解可以标注在类或方法上面；也可以标注在Mapper接口上面，但是不建议同时在Mapper和service上同时标注，可能会出现问题。该注解的value属性便是对应于在yaml中配置的数据源名称，如果没有给值，默认就是使用数据源名为master的数据源。

  • 踩坑日记：

如下代码：

@Service
@DS("master")
public class UserServiceImpl extends ServiceImpl<UserDao, UserBean> implements UserService {@Autowiredprivate TbService tbService;@Override@Transactional(rollbackFor = Exception.class)public void add() {UserBean userBean = new UserBean();userBean.setId(3);userBean.setLoginName("zhangsan");userBean.setName("张三");userBean.setPassword("123456");this.save(userBean);// 第二个数据源tbService.save1();}
}

意思就是我想在保存userBean时同时调用一下tbService的save1方法，注意tbService被@DS(“slave-1”)注解标注，它对应于sqlServer数据库的操作。当直接调用上面的add方法时，会报如下的错误：

反正就是死活找不到tb这张表，实际上tb这张表是确实存在于sqlServer数据库中的，之所以报错是由于加事务的原因@Transactional(rollbackFor = Exception.class)，由于spring事务默认的传播级别是：

Propagation.REQUIRED

这个事务的特性就是如果上级方法调用时已经获取了事务，则该方法内调用的其它事务方法将复用同一个事务，结果就是对userBean的操作是对应于mysql的，由于加了事务，所以tbService.save1()方法还是在该事务内，造成的结果就是会在mysql数据库中找tb这张表，肯定找不到，结果就报错了，解决方式如下：

@Service
@DS("slave-1")
public class TbServiceImpl extends ServiceImpl<TbDao, TbBean> implements TbService {@Override@Transactional(rollbackFor = Exception.class, propagation = Propagation.REQUIRES_NEW)public String save1() {TbBean tbBean = new TbBean();tbBean.setName("王五");tbBean.setSubject("英语");tbBean.setScore(113);this.save(tbBean);return "success";}
}

给save1()方法加上  @Transactional(rollbackFor = Exception.class, propagation = Propagation.REQUIRES_NEW) ， 使其在每次获取事务时都是重新产生一个，不再复用上级方法的事务。

druid + mybatis 所数据源配置

  • pom

            <!-- Druid 数据连接池依赖 --><dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>1.1.9</version></dependency><!--MyBatis整合SpringBoot框架的起步依赖--><dependency><groupId>org.mybatis.spring.boot</groupId><artifactId>mybatis-spring-boot-starter</artifactId><version>2.1.4</version></dependency><!-- jdbc --><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-jdbc</artifactId><version>2.7.8</version></dependency><!-- Mysql驱动包 --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.27</version></dependency>

  • yml

    
# druid 多数据源配置
master:datasource:url: jdbc:mysql://localhost:3306/test?characterEncoding=utf8&useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT%2B8username: rootpassword: 123456driver-class-name: com.mysql.cj.jdbc.Driverslave:datasource:url: jdbc:mysql://43.143.217.124:3306/hongbei?characterEncoding=utf8&useUnicode=true&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT%2B8username: rootpassword: 123456driver-class-name: com.mysql.cj.jdbc.Driver

  • 数据源一 配置

    
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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;/*** @Classname DruidConfig* @Description DruidConfig* @Date 2023-02-27 10:45* @Created by lihw*/
@Configuration
@MapperScan(basePackages = {DruidConfig.PACKAGE},sqlSessionFactoryRef = "masterSqlSessionFactory")
public class DruidConfig {// 精确到 master 目录，以便跟其他数据源隔离static final String PACKAGE = "com.example.demo.mapper.master";static final String MAPPER_LOCATION = "classpath:abc/**/*.xml";@Value("${master.datasource.url}")private String url;@Value("${master.datasource.username}")private String user;@Value("${master.datasource.password}")private String password;@Value("${master.datasource.driver-class-name}")private String driverClass;@Bean("masterDataSource")@Primarypublic DataSource masterDataSource(){DruidDataSource druidDataSource = new DruidDataSource();druidDataSource.setDriverClassName(driverClass);druidDataSource.setUrl(url);druidDataSource.setUsername(user);druidDataSource.setPassword(password);return druidDataSource;}@Bean("masterTransactionManager")@Primarypublic DataSourceTransactionManager masterTransactionManager(){return new DataSourceTransactionManager(masterDataSource());}@Bean("masterSqlSessionFactory")@Primarypublic SqlSessionFactory masterSqlSessionFactory(@Qualifier("masterDataSource") DataSource dataSource) throws Exception {final SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();sqlSessionFactoryBean.setDataSource(dataSource);sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));// mybatis 配置org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();// 驼峰下划线configuration.setMapUnderscoreToCamelCase(true);sqlSessionFactoryBean.setConfiguration(configuration);return sqlSessionFactoryBean.getObject();}}

  • 数据源二 配置

    
import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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;/*** @Classname DruidConfig* @Description DruidConfig* @Date 2023-02-27 10:45* @Created by lihw*/
@Configuration
@MapperScan(basePackages = {DruidConfig2.PACKAGE},sqlSessionFactoryRef = "slaveSqlSessionFactory")
public class DruidConfig2 {// 精确到 master 目录，以便跟其他数据源隔离static final String PACKAGE = "com.example.demo.mapper.slave";static final String MAPPER_LOCATION = "classpath:slave/**/*.xml";@Value("${slave.datasource.url}")private String url;@Value("${slave.datasource.username}")private String user;@Value("${slave.datasource.password}")private String password;@Value("${slave.datasource.driver-class-name}")private String driverClass;@Bean("slaveDataSource")@Primarypublic DataSource slaveDataSource(){DruidDataSource druidDataSource = new DruidDataSource();druidDataSource.setDriverClassName(driverClass);druidDataSource.setUrl(url);druidDataSource.setUsername(user);druidDataSource.setPassword(password);return druidDataSource;}@Bean("slaveTransactionManager")@Primarypublic DataSourceTransactionManager slaveTransactionManager(){return new DataSourceTransactionManager(slaveDataSource());}@Bean("slaveSqlSessionFactory")@Primarypublic SqlSessionFactory slaveSqlSessionFactory(@Qualifier("slaveDataSource") DataSource dataSource) throws Exception {final SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();sqlSessionFactoryBean.setDataSource(dataSource);sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));// mybatis 配置org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();// 驼峰下划线configuration.setMapUnderscoreToCamelCase(true);sqlSessionFactoryBean.setConfiguration(configuration);return sqlSessionFactoryBean.getObject();}}

  • 测试代码

    @RestController
@RequestMapping("/test")
public class TestController {@AutowiredStudentMapper studentMapper;@AutowiredSysUserMapper sysUserMapper;@GetMapping("list")public List<Student> getUserList(){List<Student> userList = studentMapper.getUserList();System.out.println(userList);return userList;}@GetMapping("msg2")public String getmsg2(){List<SysUser> user = sysUserMapper.getUserList();System.out.println(user);return "msg22";}}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VnOmBExy-1677467535745)(https://note.youdao.com/yws/res/4051/WEBRESOURCE1a8c5b356140fb73fc973a659243a302)]

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

相关文章：

  • Verilog 数据类型和数组简介
  • 【数据结构】时间复杂度和空间复杂度以及相关OJ题的详解分析
  • 31--Vue-前端开发-Vue语法
  • 这份IC设计必读书单，值得所有IC设计工程师一看！
  • Acwing 蓝桥杯 第一章 递归与递推
  • 模型部署笔记
  • 多线程之wait和notify
  • MVCC 当前读 快照读 RC read view RR下事务更新不会丢失
  • NCRE计算机等级考试Python真题（二）
  • 借助IBM Spectrum LSF为芯片行业大幅提升算力，预测未来
  • 力扣-换座位
  • DFT基本入门介绍
  • 做「增长」必须懂的6大关键指标
  • Linux：soft lockup 检测机制
  • 天线理论知识4——非频变天线
  • 基础架构组件选型及服务化
  • leetcode-每日一题-1247(中等，数学逻辑)
  • 前端面试题 —— 计算机网络（一）
  • 分布式-分布式缓存笔记
  • 【反序列化漏洞-01】为什么要序列化
  • 用c语言模拟实现常用字符串函数
  • 在 Flutter 中使用 webview_flutter 4.0 | 基础用法与事件处理
  • JavaWeb--Servlet
  • Linux启动过程
  • 面试资料整理——C++
  • 【ArcGIS Pro二次开发】(9)：GeoProcessing工具和自定义工具的调用
  • 皕杰报表斜线单元格、图表里或导出pdf的中文显示小方块解决方案
  • python读写hdfs文件的实用解决方案
  • RK3399+FPGA+MIPI 方案细节之subLVDS to MIPI处理
  • Vue组件是怎样挂载的