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

心已赠人 2024-03-31 10:17 30阅读 0赞

第一种方式： AbstractRoutingDataSource

1.1. 手动切换数据源

![ad004f381d5b6c75f3cab3bf510820b2.png][]

application.properties

# Order
    # 如果用Druid作为数据源，应该用url属性，而不是jdbc-url
    spring.datasource.order.jdbc-url=jdbc:mysql://localhost:3306/order?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
    spring.datasource.order.username=root
    spring.datasource.order.password=123456
    spring.datasource.order.driver-class-name=com.mysql.cj.jdbc.Driver
    # Stock
    spring.datasource.stock.jdbc-url=jdbc:mysql://localhost:3306/stock?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
    spring.datasource.stock.username=root
    spring.datasource.stock.password=123456
    spring.datasource.stock.driver-class-name=com.mysql.cj.jdbc.Driver
    # Account
    spring.datasource.account.jdbc-url=jdbc:mysql://localhost:3306/account?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
    spring.datasource.account.username=root
    spring.datasource.account.password=123456
    spring.datasource.account.driver-class-name=com.mysql.cj.jdbc.Driver

配置数据源

DataSourceConfig.java

package com.cjs.example.config;
    
    import com.alibaba.druid.pool.DruidDataSource;
    import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
    import com.zaxxer.hikari.HikariDataSource;
    import org.mybatis.spring.SqlSessionFactoryBean;
    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 javax.sql.DataSource;
    import java.util.HashMap;
    import java.util.Map;
    
    @Configuration
    public class DataSourceConfig {
    
        @Bean("orderDataSource")
        @ConfigurationProperties(prefix = "spring.datasource.order")
        public DataSource orderDataSource() {
    //        return new HikariDataSource();
    //        return new DruidDataSource();
            return DataSourceBuilder.create().build();
        }
    
        @Bean("accountDataSource")
        @ConfigurationProperties(prefix = "spring.datasource.account")
        public DataSource accountDataSource() {
    //        return new HikariDataSource();
    //        return new DruidDataSource();
            return DataSourceBuilder.create().build();
        }
    
        @Bean("stockDataSource")
        @ConfigurationProperties(prefix = "spring.datasource.stock")
        public DataSource stockDataSource() {
    //        return new HikariDataSource();
    //        return new DruidDataSource();
            return DataSourceBuilder.create().build();
        }
    
        @Primary
        @Bean("dynamicDataSource")
        public DataSource dynamicDataSource(@Qualifier("orderDataSource") DataSource orderDataSource,
                                            @Qualifier("accountDataSource") DataSource accountDataSource,
                                            @Qualifier("stockDataSource") DataSource stockDataSource) {
    
            Map<Object, Object> dataSourceMap = new HashMap<>(3);
            dataSourceMap.put(DataSourceKey.ORDER.name(), orderDataSource);
            dataSourceMap.put(DataSourceKey.STOCK.name(), stockDataSource);
            dataSourceMap.put(DataSourceKey.ACCOUNT.name(), accountDataSource);
    
            DynamicRoutingDataSource dynamicRoutingDataSource = new DynamicRoutingDataSource();
            dynamicRoutingDataSource.setDefaultTargetDataSource(orderDataSource);
            dynamicRoutingDataSource.setTargetDataSources(dataSourceMap);
    
            return dynamicRoutingDataSource;
        }
    
        /* https://baomidou.com/pages/3b5af0/ */
        @Bean
        public MybatisSqlSessionFactoryBean sqlSessionFactoryBean(@Qualifier("dynamicDataSource") DataSource dataSource) {
            MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
            sqlSessionFactoryBean.setDataSource(dataSource);
    //        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mappers/*.xml"));
            return sqlSessionFactoryBean;
        }
    }

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

DataSourceKey.java

package com.cjs.example.config;
    
    public enum DataSourceKey {
        /**
         * Order data source key.
         */
        ORDER,
        /**
         * Stock data source key.
         */
        STOCK,
        /**
         * Account data source key.
         */
        ACCOUNT
    }

DynamicDataSourceContextHolder.java

package com.cjs.example.config;
    
    public class DynamicDataSourceContextHolder {
    
        private static final ThreadLocal<String> CONTEXT_HOLDER = ThreadLocal.withInitial(DataSourceKey.ORDER::name);
    
        public static void setDataSourceKey(DataSourceKey key) {
            CONTEXT_HOLDER.set(key.name());
        }
    
        public static String getDataSourceKey() {
            return CONTEXT_HOLDER.get();
        }
    
        public static void clearDataSourceKey() {
            CONTEXT_HOLDER.remove();
        }
    
    }

DynamicRoutingDataSource.java

package com.cjs.example.config;
    
    import lombok.extern.slf4j.Slf4j;
    import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
    
    @Slf4j
    public class DynamicRoutingDataSource extends AbstractRoutingDataSource {
        @Override
        protected Object determineCurrentLookupKey() {
            log.info("当前数据源 [{}]", DynamicDataSourceContextHolder.getDataSourceKey());
            return DynamicDataSourceContextHolder.getDataSourceKey();
        }
    }

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

DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.ACCOUNT);

举个例子：

package com.cjs.example;
    
    import com.cjs.example.account.entity.Account;
    import com.cjs.example.account.service.IAccountService;
    import com.cjs.example.config.DataSourceKey;
    import com.cjs.example.config.DynamicDataSourceContextHolder;
    import com.cjs.example.order.entity.Order;
    import com.cjs.example.order.service.IOrderService;
    import com.cjs.example.stock.entity.Stock;
    import com.cjs.example.stock.service.IStockService;
    import org.junit.jupiter.api.Test;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.test.context.SpringBootTest;
    
    import java.math.BigDecimal;
    
    @SpringBootTest
    public class Demo1122ApplicationTests {
    
    	@Autowired
    	private IOrderService orderService;
    	@Autowired
    	private IAccountService accountService;
    	@Autowired
    	private IStockService stockService;
    
    	@Test
    	public void doBusiness() {
    		DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.ORDER);
    		Order order = new Order();
    		order.setOrderNo("123");
    		order.setUserId("1");
    		order.setCommodityCode("abc");
    		order.setCount(1);
    		order.setAmount(new BigDecimal("9.9"));
    		orderService.save(order);
    
    		DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.STOCK);
    		Stock stock = new Stock();
    		stock.setId(1);
    		stock.setCommodityCode("abc");
    		stock.setName("huawei");
    		stock.setCount(1);
    		stockService.updateById(stock);
    
    		DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.ACCOUNT);
    		Account account = new Account();
    		account.setId(1);
    		account.setUserId("1");
    		account.setAmount(new BigDecimal(100));
    		accountService.updateById(account);
    	}
    
    }

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

1.2. 自动切换数据源

[Core Technologies][]

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

1、添加@EnableAspectJAutoProxy注解

package com.cjs.example;
    
    import org.mybatis.spring.annotation.MapperScan;
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    import org.springframework.context.annotation.EnableAspectJAutoProxy;
    
    @EnableAspectJAutoProxy
    @MapperScan("com.cjs.example.*.mapper")
    @SpringBootApplication
    public class Demo1122Application {
    	public static void main(String[] args) {
    		SpringApplication.run(Demo1122Application.class, args);
    	}
    }

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

package com.cjs.example.aop;
    
    import com.cjs.example.config.DataSourceKey;
    import com.cjs.example.config.DynamicDataSourceContextHolder;
    import org.aspectj.lang.ProceedingJoinPoint;
    import org.aspectj.lang.annotation.Around;
    import org.aspectj.lang.annotation.Aspect;
    import org.aspectj.lang.annotation.Pointcut;
    import org.springframework.stereotype.Component;
    
    @Aspect
    @Component
    public class DataSourceAdvice {
    
    //    @Pointcut("within(com.cjs.example.order..*)")
        @Pointcut("execution(* com.cjs.example.order..*.*(..))")
        public void orderPointcut() {}
    
    //    @Pointcut("within(com.cjs.example.account..*)")
        @Pointcut("execution(* com.cjs.example.account..*.*(..))")
        public void accountPointcut() {}
    
    //    @Pointcut("within(com.cjs.example.stock..*)")
        @Pointcut("execution(* com.cjs.example.stock..*.*(..))")
        public void stockPointcut() {}
    
    
        @Around("orderPointcut()")
        public Object order(ProceedingJoinPoint pjp) throws Throwable {
            DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.ORDER);
            Object retVal = pjp.proceed();
            DynamicDataSourceContextHolder.clearDataSourceKey();
            return retVal;
        }
        @Around("accountPointcut()")
        public Object account(ProceedingJoinPoint pjp) throws Throwable {
            DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.ACCOUNT);
            Object retVal = pjp.proceed();
            DynamicDataSourceContextHolder.clearDataSourceKey();
            return retVal;
        }
        @Around("stockPointcut()")
        public Object stock(ProceedingJoinPoint pjp) throws Throwable {
            DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.STOCK);
            Object retVal = pjp.proceed();
            DynamicDataSourceContextHolder.clearDataSourceKey();
            return retVal;
        }
    }

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

![36be3f49224fe3378cdaec8214228985.png][]

工程结构

![953f853f23c0f9f8ae5398ee442ea427.png][]

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

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

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

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

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

2、配置数据源

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

主从配置，读写分离

# 多主多从                      纯粹多库（记得设置primary）                   混合配置
    spring:                               spring:                               spring:
      datasource:                           datasource:                           datasource:
        dynamic:                              dynamic:                              dynamic:
          datasource:                           datasource:                           datasource:
            master_1:                             mysql:                                master:
            master_2:                             oracle:                               slave_1:
            slave_1:                              sqlserver:                            slave_2:
            slave_2:                              postgresql:                           oracle_1:
            slave_3:                              h2:                                   oracle_2:

改造一下前面的例子

spring.datasource.dynamic.primary=order
    # Order
    spring.datasource.dynamic.datasource.order.url=jdbc:mysql://localhost:3306/order?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
    spring.datasource.dynamic.datasource.order.username=root
    spring.datasource.dynamic.datasource.order.password=123456
    spring.datasource.dynamic.datasource.order.driver-class-name=com.mysql.cj.jdbc.Driver
    # Stock
    spring.datasource.dynamic.datasource.stock.url=jdbc:mysql://localhost:3306/stock?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
    spring.datasource.dynamic.datasource.stock.username=root
    spring.datasource.dynamic.datasource.stock.password=123456
    spring.datasource.dynamic.datasource.stock.driver-class-name=com.mysql.cj.jdbc.Driver
    # Account
    spring.datasource.dynamic.datasource.account.url=jdbc:mysql://localhost:3306/account?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
    spring.datasource.dynamic.datasource.account.username=root
    spring.datasource.dynamic.datasource.account.password=123456
    spring.datasource.dynamic.datasource.account.driver-class-name=com.mysql.cj.jdbc.Driver

3、使用 **@DS** 切换数据源

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

<table> 
 <tbody> 
  <tr> 
   <td><strong>注解</strong></td> 
   <td><strong>结果</strong></td> 
  </tr> 
  <tr> 
   <td>没有@DS</td> 
   <td>默认数据源</td> 
  </tr> 
  <tr> 
   <td>@DS("dsName")  </td> 
   <td>dsName可以为组名也可以为具体某个库的名称</td> 
  </tr> 
 </tbody> 
</table>

package com.cjs.example.order.service.impl;
    
    import com.baomidou.dynamic.datasource.annotation.DS;
    import com.cjs.example.order.entity.Order;
    import com.cjs.example.order.mapper.OrderMapper;
    import com.cjs.example.order.service.IOrderService;
    import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    import org.springframework.stereotype.Service;
    
    @DS("order")
    @Service
    public class OrderServiceImpl extends ServiceImpl<OrderMapper, Order> implements IOrderService {
    
    }

package com.cjs.example.stock.service.impl;
    
    import com.baomidou.dynamic.datasource.annotation.DS;
    import com.cjs.example.stock.entity.Stock;
    import com.cjs.example.stock.mapper.StockMapper;
    import com.cjs.example.stock.service.IStockService;
    import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    import org.springframework.stereotype.Service;
    
    @DS("stock")
    @Service
    public class StockServiceImpl extends ServiceImpl<StockMapper, Stock> implements IStockService {
    
    }

package com.cjs.example.account.service.impl;
    
    import com.baomidou.dynamic.datasource.annotation.DS;
    import com.cjs.example.account.entity.Account;
    import com.cjs.example.account.mapper.AccountMapper;
    import com.cjs.example.account.service.IAccountService;
    import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
    import org.springframework.stereotype.Service;
    
    @DS("account")
    @Service
    public class AccountServiceImpl extends ServiceImpl<AccountMapper, Account> implements IAccountService {
    
    }

[ad004f381d5b6c75f3cab3bf510820b2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/12e7c9b140f844838defef06dad46903.png
[Core Technologies]: https://docs.spring.io/spring-framework/docs/current/reference/html/core.html#aop-ataspectj
[36be3f49224fe3378cdaec8214228985.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/a8e61bde90444dfeb04dbe753bcf186e.png
[953f853f23c0f9f8ae5398ee442ea427.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/bbcfd1dc13794b3984a2c819ff5c6093.png
[GitHub - baomidou_dynamic-datasource-spring-boot-starter_ dynamic datasource for springboot _ _ _ _]: https://github.com/baomidou/dynamic-datasource-spring-boot-starter