AI专业者开发社区 1 号成员
  •  0 回帖  •  3 浏览  •  4 个月前
 

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 {


<span class="hljs-meta">@Bean("orderDataSource")</span>
<span class="hljs-meta">@ConfigurationProperties(prefix = "spring.datasource.order")</span>
<span class="hljs-keyword">public</span> DataSource <span class="hljs-title function_">orderDataSource</span><span class="hljs-params">()</span> {



//        return new HikariDataSource();
//        return new DruidDataSource();
return DataSourceBuilder.create().build();
}


<span class="hljs-meta">@Bean("accountDataSource")</span>
<span class="hljs-meta">@ConfigurationProperties(prefix = "spring.datasource.account")</span>
<span class="hljs-keyword">public</span> DataSource <span class="hljs-title function_">accountDataSource</span><span class="hljs-params">()</span> {



//        return new HikariDataSource();
//        return new DruidDataSource();
return DataSourceBuilder.create().build();
}


<span class="hljs-meta">@Bean("stockDataSource")</span>
<span class="hljs-meta">@ConfigurationProperties(prefix = "spring.datasource.stock")</span>
<span class="hljs-keyword">public</span> DataSource <span class="hljs-title function_">stockDataSource</span><span class="hljs-params">()</span> {



//        return new HikariDataSource();
//        return new DruidDataSource();
return DataSourceBuilder.create().build();
}


<span class="hljs-meta">@Primary</span>
<span class="hljs-meta">@Bean("dynamicDataSource")</span>
<span class="hljs-keyword">public</span> DataSource <span class="hljs-title function_">dynamicDataSource</span><span class="hljs-params">(<span class="hljs-meta">@Qualifier("orderDataSource")</span> DataSource orderDataSource,
                                    <span class="hljs-meta">@Qualifier("accountDataSource")</span> DataSource accountDataSource,
                                    <span class="hljs-meta">@Qualifier("stockDataSource")</span> DataSource stockDataSource)</span> {

    Map&lt;Object, Object&gt; dataSourceMap = <span class="hljs-keyword">new</span> <span class="hljs-title class_">HashMap</span>&lt;&gt;(<span class="hljs-number">3</span>);
    dataSourceMap.put(DataSourceKey.ORDER.name(), orderDataSource);
    dataSourceMap.put(DataSourceKey.STOCK.name(), stockDataSource);
    dataSourceMap.put(DataSourceKey.ACCOUNT.name(), accountDataSource);

    <span class="hljs-type">DynamicRoutingDataSource</span> <span class="hljs-variable">dynamicRoutingDataSource</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">DynamicRoutingDataSource</span>();
    dynamicRoutingDataSource.setDefaultTargetDataSource(orderDataSource);
    dynamicRoutingDataSource.setTargetDataSources(dataSourceMap);

    <span class="hljs-keyword">return</span> dynamicRoutingDataSource;
}

<span class="hljs-comment">/* https://baomidou.com/pages/3b5af0/ */</span>
<span class="hljs-meta">@Bean</span>
<span class="hljs-keyword">public</span> MybatisSqlSessionFactoryBean <span class="hljs-title function_">sqlSessionFactoryBean</span><span class="hljs-params">(<span class="hljs-meta">@Qualifier("dynamicDataSource")</span> DataSource dataSource)</span> {
    <span class="hljs-type">MybatisSqlSessionFactoryBean</span> <span class="hljs-variable">sqlSessionFactoryBean</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">MybatisSqlSessionFactoryBean</span>();
    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 {


<span class="hljs-keyword">private</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">final</span> ThreadLocal&lt;String&gt; CONTEXT_HOLDER = ThreadLocal.withInitial(DataSourceKey.ORDER::name);

<span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">setDataSourceKey</span><span class="hljs-params">(DataSourceKey key)</span> {
    CONTEXT_HOLDER.set(key.name());
}

<span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> String <span class="hljs-title function_">getDataSourceKey</span><span class="hljs-params">()</span> {
    <span class="hljs-keyword">return</span> CONTEXT_HOLDER.get();
}

<span class="hljs-keyword">public</span> <span class="hljs-keyword">static</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">clearDataSourceKey</span><span class="hljs-params">()</span> {
    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 {


<span class="hljs-meta">@Autowired</span>
<span class="hljs-keyword">private</span> IOrderService orderService;
<span class="hljs-meta">@Autowired</span>
<span class="hljs-keyword">private</span> IAccountService accountService;
<span class="hljs-meta">@Autowired</span>
<span class="hljs-keyword">private</span> IStockService stockService;

<span class="hljs-meta">@Test</span>
<span class="hljs-keyword">public</span> <span class="hljs-keyword">void</span> <span class="hljs-title function_">doBusiness</span><span class="hljs-params">()</span> {
	DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.ORDER);
	<span class="hljs-type">Order</span> <span class="hljs-variable">order</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">Order</span>();
	order.setOrderNo(<span class="hljs-string">"123"</span>);
	order.setUserId(<span class="hljs-string">"1"</span>);
	order.setCommodityCode(<span class="hljs-string">"abc"</span>);
	order.setCount(<span class="hljs-number">1</span>);
	order.setAmount(<span class="hljs-keyword">new</span> <span class="hljs-title class_">BigDecimal</span>(<span class="hljs-string">"9.9"</span>));
	orderService.save(order);

	DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.STOCK);
	<span class="hljs-type">Stock</span> <span class="hljs-variable">stock</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">Stock</span>();
	stock.setId(<span class="hljs-number">1</span>);
	stock.setCommodityCode(<span class="hljs-string">"abc"</span>);
	stock.setName(<span class="hljs-string">"huawei"</span>);
	stock.setCount(<span class="hljs-number">1</span>);
	stockService.updateById(stock);

	DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.ACCOUNT);
	<span class="hljs-type">Account</span> <span class="hljs-variable">account</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">Account</span>();
	account.setId(<span class="hljs-number">1</span>);
	account.setUserId(<span class="hljs-string">"1"</span>);
	account.setAmount(<span class="hljs-keyword">new</span> <span class="hljs-title class_">BigDecimal</span>(<span class="hljs-number">100</span>));
	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() {}


<span class="hljs-meta">@Around("orderPointcut()")</span>
<span class="hljs-keyword">public</span> Object <span class="hljs-title function_">order</span><span class="hljs-params">(ProceedingJoinPoint pjp)</span> <span class="hljs-keyword">throws</span> Throwable {
    DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.ORDER);
    <span class="hljs-type">Object</span> <span class="hljs-variable">retVal</span> <span class="hljs-operator">=</span> pjp.proceed();
    DynamicDataSourceContextHolder.clearDataSourceKey();
    <span class="hljs-keyword">return</span> retVal;
}
<span class="hljs-meta">@Around("accountPointcut()")</span>
<span class="hljs-keyword">public</span> Object <span class="hljs-title function_">account</span><span class="hljs-params">(ProceedingJoinPoint pjp)</span> <span class="hljs-keyword">throws</span> Throwable {
    DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.ACCOUNT);
    <span class="hljs-type">Object</span> <span class="hljs-variable">retVal</span> <span class="hljs-operator">=</span> pjp.proceed();
    DynamicDataSourceContextHolder.clearDataSourceKey();
    <span class="hljs-keyword">return</span> retVal;
}
<span class="hljs-meta">@Around("stockPointcut()")</span>
<span class="hljs-keyword">public</span> Object <span class="hljs-title function_">stock</span><span class="hljs-params">(ProceedingJoinPoint pjp)</span> <span class="hljs-keyword">throws</span> Throwable {
    DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.STOCK);
    <span class="hljs-type">Object</span> <span class="hljs-variable">retVal</span> <span class="hljs-operator">=</span> pjp.proceed();
    DynamicDataSourceContextHolder.clearDataSourceKey();
    <span class="hljs-keyword">return</span> 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 {

}

 


感谢您的阅读，如果您觉得阅读本文对您有帮助，请点一下“推荐”按钮，您的“推荐”将是我最大的写作动力！
欢迎各位转载，但必须在文章页面中给出作者和原文链接！