• 【超详细demo】Spring Boot 多数据源配置

    第一种方式： AbstractRoutingDataSource

    1.1. 手动切换数据源

    application.properties

    1. # Order
    2. # 如果用Druid作为数据源，应该用url属性，而不是jdbc-url
    3. spring.datasource.order.jdbc-url=jdbc:mysql://localhost:3306/order?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
    4. spring.datasource.order.username=root
    5. spring.datasource.order.password=123456
    6. spring.datasource.order.driver-class-name=com.mysql.cj.jdbc.Driver
    7. # Stock
    8. spring.datasource.stock.jdbc-url=jdbc:mysql://localhost:3306/stock?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
    9. spring.datasource.stock.username=root
    10. spring.datasource.stock.password=123456
    11. spring.datasource.stock.driver-class-name=com.mysql.cj.jdbc.Driver
    12. # Account
    13. spring.datasource.account.jdbc-url=jdbc:mysql://localhost:3306/account?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
    14. spring.datasource.account.username=root
    15. spring.datasource.account.password=123456
    16. spring.datasource.account.driver-class-name=com.mysql.cj.jdbc.Driver

    配置数据源

    DataSourceConfig.java

    1. package com.cjs.example.config;
    2.  
    3. import com.alibaba.druid.pool.DruidDataSource;
    4. import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
    5. import com.zaxxer.hikari.HikariDataSource;
    6. import org.mybatis.spring.SqlSessionFactoryBean;
    7. import org.springframework.beans.factory.annotation.Qualifier;
    8. import org.springframework.boot.context.properties.ConfigurationProperties;
    9. import org.springframework.boot.jdbc.DataSourceBuilder;
    10. import org.springframework.context.annotation.Bean;
    11. import org.springframework.context.annotation.Configuration;
    12. import org.springframework.context.annotation.Primary;
    13. import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
    14.  
    15. import javax.sql.DataSource;
    16. import java.util.HashMap;
    17. import java.util.Map;
    18.  
    19. @Configuration
    20. public class DataSourceConfig {
    21.  
    22.     @Bean("orderDataSource")
    23.     @ConfigurationProperties(prefix = "spring.datasource.order")
    24.     public DataSource orderDataSource() {
    25. //        return new HikariDataSource();
    26. //        return new DruidDataSource();
    27.         return DataSourceBuilder.create().build();
    28.     }
    29.  
    30.     @Bean("accountDataSource")
    31.     @ConfigurationProperties(prefix = "spring.datasource.account")
    32.     public DataSource accountDataSource() {
    33. //        return new HikariDataSource();
    34. //        return new DruidDataSource();
    35.         return DataSourceBuilder.create().build();
    36.     }
    37.  
    38.     @Bean("stockDataSource")
    39.     @ConfigurationProperties(prefix = "spring.datasource.stock")
    40.     public DataSource stockDataSource() {
    41. //        return new HikariDataSource();
    42. //        return new DruidDataSource();
    43.         return DataSourceBuilder.create().build();
    44.     }
    45.  
    46.     @Primary
    47.     @Bean("dynamicDataSource")
    48.     public DataSource dynamicDataSource(@Qualifier("orderDataSource") DataSource orderDataSource,
    49.                                         @Qualifier("accountDataSource") DataSource accountDataSource,
    50.                                         @Qualifier("stockDataSource") DataSource stockDataSource) {
    51.  
    52.         Map dataSourceMap = new HashMap<>(3);
    53.         dataSourceMap.put(DataSourceKey.ORDER.name(), orderDataSource);
    54.         dataSourceMap.put(DataSourceKey.STOCK.name(), stockDataSource);
    55.         dataSourceMap.put(DataSourceKey.ACCOUNT.name(), accountDataSource);
    56.  
    57.         DynamicRoutingDataSource dynamicRoutingDataSource = new DynamicRoutingDataSource();
    58.         dynamicRoutingDataSource.setDefaultTargetDataSource(orderDataSource);
    59.         dynamicRoutingDataSource.setTargetDataSources(dataSourceMap);
    60.  
    61.         return dynamicRoutingDataSource;
    62.     }
    63.  
    64.     /* https://baomidou.com/pages/3b5af0/ */
    65.     @Bean
    66.     public MybatisSqlSessionFactoryBean sqlSessionFactoryBean(@Qualifier("dynamicDataSource") DataSource dataSource) {
    67.         MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
    68.         sqlSessionFactoryBean.setDataSource(dataSource);
    69. //        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mappers/*.xml"));
    70.         return sqlSessionFactoryBean;
    71.     }
    72. }

    由于是MyBatsi-Plus，所以配的是MybatisSqlSessionFactoryBean，如果是MyBatis，则应该是SqlSessionFactoryBean

    DataSourceKey.java

    1. package com.cjs.example.config;
    2.  
    3. public enum DataSourceKey {
    4.     /**
    5.      * Order data source key.
    6.      */
    7.     ORDER,
    8.     /**
    9.      * Stock data source key.
    10.      */
    11.     STOCK,
    12.     /**
    13.      * Account data source key.
    14.      */
    15.     ACCOUNT
    16. }

    DynamicDataSourceContextHolder.java

    1. package com.cjs.example.config;
    2.  
    3. public class DynamicDataSourceContextHolder {
    4.  
    5.     private static final ThreadLocal<String> CONTEXT_HOLDER = ThreadLocal.withInitial(DataSourceKey.ORDER::name);
    6.  
    7.     public static void setDataSourceKey(DataSourceKey key) {
    8.         CONTEXT_HOLDER.set(key.name());
    9.     }
    10.  
    11.     public static String getDataSourceKey() {
    12.         return CONTEXT_HOLDER.get();
    13.     }
    14.  
    15.     public static void clearDataSourceKey() {
    16.         CONTEXT_HOLDER.remove();
    17.     }
    18.  
    19. }

    DynamicRoutingDataSource.java

    1. package com.cjs.example.config;
    2.  
    3. import lombok.extern.slf4j.Slf4j;
    4. import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
    5.  
    6. @Slf4j
    7. public class DynamicRoutingDataSource extends AbstractRoutingDataSource {
    8.     @Override
    9.     protected Object determineCurrentLookupKey() {
    10.         log.info("当前数据源 [{}]", DynamicDataSourceContextHolder.getDataSourceKey());
    11.         return DynamicDataSourceContextHolder.getDataSourceKey();
    12.     }
    13. }

    好了，配置完以后，在操作数据库之前，先设置用哪个数据源即可，就像下面这样：

    DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.ACCOUNT);

    举个例子：

    1. package com.cjs.example;
    2.  
    3. import com.cjs.example.account.entity.Account;
    4. import com.cjs.example.account.service.IAccountService;
    5. import com.cjs.example.config.DataSourceKey;
    6. import com.cjs.example.config.DynamicDataSourceContextHolder;
    7. import com.cjs.example.order.entity.Order;
    8. import com.cjs.example.order.service.IOrderService;
    9. import com.cjs.example.stock.entity.Stock;
    10. import com.cjs.example.stock.service.IStockService;
    11. import org.junit.jupiter.api.Test;
    12. import org.springframework.beans.factory.annotation.Autowired;
    13. import org.springframework.boot.test.context.SpringBootTest;
    14.  
    15. import java.math.BigDecimal;
    16.  
    17. @SpringBootTest
    18. public class Demo1122ApplicationTests {
    19.  
    20. 	@Autowired
    21. 	private IOrderService orderService;
    22. 	@Autowired
    23. 	private IAccountService accountService;
    24. 	@Autowired
    25. 	private IStockService stockService;
    26.  
    27. 	@Test
    28. 	public void doBusiness() {
    29. 		DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.ORDER);
    30. 		Order order = new Order();
    31. 		order.setOrderNo("123");
    32. 		order.setUserId("1");
    33. 		order.setCommodityCode("abc");
    34. 		order.setCount(1);
    35. 		order.setAmount(new BigDecimal("9.9"));
    36. 		orderService.save(order);
    37.  
    38. 		DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.STOCK);
    39. 		Stock stock = new Stock();
    40. 		stock.setId(1);
    41. 		stock.setCommodityCode("abc");
    42. 		stock.setName("huawei");
    43. 		stock.setCount(1);
    44. 		stockService.updateById(stock);
    45.  
    46. 		DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.ACCOUNT);
    47. 		Account account = new Account();
    48. 		account.setId(1);
    49. 		account.setUserId("1");
    50. 		account.setAmount(new BigDecimal(100));
    51. 		accountService.updateById(account);
    52. 	}
    53.  
    54. }

    这样写看起来确实有些麻烦，通常可能不会像这样在一个方法里操作多个数据库，就比如说假设这是一个管理后台，为了图省事把所有业务都写在这一个项目里，这个时候就需要配置多个数据源，各个数据库的业务互相没有关联，只是写在同一个项目中而已，这样的话如果每次都手动设置数据源太麻烦，可以定义一个AOP切面来自动切换数据源。

    1.2. 自动切换数据源

    Core Technologies

    給刚才的代码升个级，利用AOP来拦截目标方法自动切换数据源

    1、添加@EnableAspectJAutoProxy注解

    1. package com.cjs.example;
    2.  
    3. import org.mybatis.spring.annotation.MapperScan;
    4. import org.springframework.boot.SpringApplication;
    5. import org.springframework.boot.autoconfigure.SpringBootApplication;
    6. import org.springframework.context.annotation.EnableAspectJAutoProxy;
    7.  
    8. @EnableAspectJAutoProxy
    9. @MapperScan("com.cjs.example.*.mapper")
    10. @SpringBootApplication
    11. public class Demo1122Application {
    12. 	public static void main(String[] args) {
    13. 		SpringApplication.run(Demo1122Application.class, args);
    14. 	}
    15. }

    2、定义切面、切点、通知

    1. package com.cjs.example.aop;
    2.  
    3. import com.cjs.example.config.DataSourceKey;
    4. import com.cjs.example.config.DynamicDataSourceContextHolder;
    5. import org.aspectj.lang.ProceedingJoinPoint;
    6. import org.aspectj.lang.annotation.Around;
    7. import org.aspectj.lang.annotation.Aspect;
    8. import org.aspectj.lang.annotation.Pointcut;
    9. import org.springframework.stereotype.Component;
    10.  
    11. @Aspect
    12. @Component
    13. public class DataSourceAdvice {
    14.  
    15. //    @Pointcut("within(com.cjs.example.order..*)")
    16.     @Pointcut("execution(* com.cjs.example.order..*.*(..))")
    17.     public void orderPointcut() {}
    18.  
    19. //    @Pointcut("within(com.cjs.example.account..*)")
    20.     @Pointcut("execution(* com.cjs.example.account..*.*(..))")
    21.     public void accountPointcut() {}
    22.  
    23. //    @Pointcut("within(com.cjs.example.stock..*)")
    24.     @Pointcut("execution(* com.cjs.example.stock..*.*(..))")
    25.     public void stockPointcut() {}
    26.  
    27.  
    28.     @Around("orderPointcut()")
    29.     public Object order(ProceedingJoinPoint pjp) throws Throwable {
    30.         DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.ORDER);
    31.         Object retVal = pjp.proceed();
    32.         DynamicDataSourceContextHolder.clearDataSourceKey();
    33.         return retVal;
    34.     }
    35.     @Around("accountPointcut()")
    36.     public Object account(ProceedingJoinPoint pjp) throws Throwable {
    37.         DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.ACCOUNT);
    38.         Object retVal = pjp.proceed();
    39.         DynamicDataSourceContextHolder.clearDataSourceKey();
    40.         return retVal;
    41.     }
    42.     @Around("stockPointcut()")
    43.     public Object stock(ProceedingJoinPoint pjp) throws Throwable {
    44.         DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.STOCK);
    45.         Object retVal = pjp.proceed();
    46.         DynamicDataSourceContextHolder.clearDataSourceKey();
    47.         return retVal;
    48.     }
    49. }

    现在就不用每次调用service方法前手动设置数据源了

    工程结构

    第二种方式：dynamic-datasource-spring-boot-starter

    功能很强大，支持 数据源分组 ，适用于多种场景 纯粹多库  读写分离  一主多从  混合模式

    GitHub - baomidou/dynamic-datasource-spring-boot-starter: dynamic datasource for springboot 多数据源 动态数据源 主从分离 读写分离 分布式事务

    1、引入dynamic-datasource-spring-boot-starter

    1. <dependency>
    2.     <groupId>com.baomidou</groupId>
    3.     <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    4.     <version>3.5.2</version>
    5. </dependency>

    2、配置数据源

    1. spring:
    2.   datasource:
    3.     dynamic:
    4.       primary: master #设置默认的数据源或者数据源组,默认值即为master
    5.       strict: false #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
    6.       datasource:
    7.         master:
    8.           url: jdbc:mysql://xx.xx.xx.xx:3306/dynamic
    9.           username: root
    10.           password: 123456
    11.           driver-class-name: com.mysql.jdbc.Driver # 3.2.0开始支持SPI可省略此配置
    12.         slave_1:
    13.           url: jdbc:mysql://xx.xx.xx.xx:3307/dynamic
    14.           username: root
    15.           password: 123456
    16.           driver-class-name: com.mysql.jdbc.Driver
    17.         slave_2:
    18.           url: ENC(xxxxx) # 内置加密,使用请查看详细文档
    19.           username: ENC(xxxxx)
    20.           password: ENC(xxxxx)
    21.           driver-class-name: com.mysql.jdbc.Driver
    22.        #......省略
    23.        #以上会配置一个默认库master，一个组slave下有两个子库slave_1,slave_2

    主从配置，读写分离

    1. # 多主多从                      纯粹多库（记得设置primary）                   混合配置
    2. spring:                               spring:                               spring:
    3.   datasource:                           datasource:                           datasource:
    4.     dynamic:                              dynamic:                              dynamic:
    5.       datasource:                           datasource:                           datasource:
    6.         master_1:                             mysql:                                master:
    7.         master_2:                             oracle:                               slave_1:
    8.         slave_1:                              sqlserver:                            slave_2:
    9.         slave_2:                              postgresql:                           oracle_1:
    10.         slave_3:                              h2:                                   oracle_2:

    改造一下前面的例子

    1. spring.datasource.dynamic.primary=order
    2. # Order
    3. spring.datasource.dynamic.datasource.order.url=jdbc:mysql://localhost:3306/order?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
    4. spring.datasource.dynamic.datasource.order.username=root
    5. spring.datasource.dynamic.datasource.order.password=123456
    6. spring.datasource.dynamic.datasource.order.driver-class-name=com.mysql.cj.jdbc.Driver
    7. # Stock
    8. spring.datasource.dynamic.datasource.stock.url=jdbc:mysql://localhost:3306/stock?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
    9. spring.datasource.dynamic.datasource.stock.username=root
    10. spring.datasource.dynamic.datasource.stock.password=123456
    11. spring.datasource.dynamic.datasource.stock.driver-class-name=com.mysql.cj.jdbc.Driver
    12. # Account
    13. spring.datasource.dynamic.datasource.account.url=jdbc:mysql://localhost:3306/account?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
    14. spring.datasource.dynamic.datasource.account.username=root
    15. spring.datasource.dynamic.datasource.account.password=123456
    16. spring.datasource.dynamic.datasource.account.driver-class-name=com.mysql.cj.jdbc.Driver

    3、使用 @DS 切换数据源

    @DS 可以注解在方法上或类上，同时存在就近原则 方法上注解 优先于 类上注解

    注解结果
    没有@DS默认数据源
    @DS("dsName")  dsName可以为组名也可以为具体某个库的名称
    1. package com.cjs.example.order.service.impl;
    2.  
    3. import com.baomidou.dynamic.datasource.annotation.DS;
    4. import com.cjs.example.order.entity.Order;
    5. import com.cjs.example.order.mapper.OrderMapper;
    6. import com.cjs.example.order.service.IOrderService;
    7. import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    8. import org.springframework.stereotype.Service;
    9.  
    10. @DS("order")
    11. @Service
    12. public class OrderServiceImpl extends ServiceImpl, Order> implements IOrderService {
    13.  
    14. }
    1. package com.cjs.example.stock.service.impl;
    2.  
    3. import com.baomidou.dynamic.datasource.annotation.DS;
    4. import com.cjs.example.stock.entity.Stock;
    5. import com.cjs.example.stock.mapper.StockMapper;
    6. import com.cjs.example.stock.service.IStockService;
    7. import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    8. import org.springframework.stereotype.Service;
    9.  
    10. @DS("stock")
    11. @Service
    12. public class StockServiceImpl extends ServiceImpl, Stock> implements IStockService {
    13.  
    14. }
    1. package com.cjs.example.account.service.impl;
    2.  
    3. import com.baomidou.dynamic.datasource.annotation.DS;
    4. import com.cjs.example.account.entity.Account;
    5. import com.cjs.example.account.mapper.AccountMapper;
    6. import com.cjs.example.account.service.IAccountService;
    7. import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    8. import org.springframework.stereotype.Service;
    9.  
    10. @DS("account")
    11. @Service
    12. public class AccountServiceImpl extends ServiceImpl, Account> implements IAccountService {
    13.  
    14. }

  • 相关阅读:
    前端项目练习（练习-002-NodeJS项目初始化）
    多重视窗管理程序 screen
    关于 某讯QQ群的群文件上传和下载出现错误-134 的解决方法
    idea如何快速找到项目中对应的类(包括源码)
    RPA是什么?怎么成为RPA高手？
    @Bean, @Component, @Configuration简析
    BATJM面试真题文档整理，斩获心仪offer，送给想跳槽的程序猿
    结构体大小的计算（结构体内存对齐）
    92 [递归实现指数型枚举](https://www.acwing.com/problem/content/94/)
    Matlab-resample
  • 原文地址：https://blog.csdn.net/wufaqidong1/article/details/127990507