Spring Boot 多数据源配置

第一种方式： AbstractRoutingDataSource

1.1. 手动切换数据源

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. 自动切换数据源

https://docs.spring.io/spring-framework/docs/current/reference/html/core.html#aop-ataspectj

給刚才的代码升个级，利用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方法前手动设置数据源了

工程结构

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

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

https://github.com/baomidou/dynamic-datasource-spring-boot-starter

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 可以注解在方法上或类上，同时存在就近原则 方法上注解 优先于 类上注解

注解	结果
没有@DS	默认数据源
@DS("dsName")	dsName可以为组名也可以为具体某个库的名称

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 {  }