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 {

}