Spring Boot 多数据源配置

时间：2022-11-23 17:04:33

方式一：AbstractRoutingDataSource

手动切换数据源

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.5</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.example</groupId>
<artifactId>demo1122</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>demo1122</name>

<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>

<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.15</version>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>

<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<excludes>
<exclude>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</exclude>
</excludes>
</configuration>
</plugin>
</plugins>
</build>

</project>

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);
  }

}

自动切换数据源

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

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

1、添加依赖

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>

2、添加@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);
  }
}

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

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方法前手动设置数据源了

Spring Boot 多数据源配置

工程结构

Spring Boot 多数据源配置

方式二：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 {

}


标签：

相关文章