一篇搞懂springboot多数据源

好文推荐

• 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)]