Spring Boot 多数据源配置

第一种方式： AbstractRoutingDataSource
1.1. 手动切换数据源

application.properties

1. # Order
2. # 如果用Druid作为数据源，应该用url属性，而不是jdbc-url
3. spring.datasource.order.jdbc-url=jdbc:mysql://localhost:3306/order?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
4. spring.datasource.order.username=root
5. spring.datasource.order.password=123456
6. spring.datasource.order.driver-class-name=com.mysql.cj.jdbc.Driver
7. # Stock
8. spring.datasource.stock.jdbc-url=jdbc:mysql://localhost:3306/stock?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
9. spring.datasource.stock.username=root
10. spring.datasource.stock.password=123456
11. spring.datasource.stock.driver-class-name=com.mysql.cj.jdbc.Driver
12. # Account
13. spring.datasource.account.jdbc-url=jdbc:mysql://localhost:3306/account?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
14. spring.datasource.account.username=root
15. spring.datasource.account.password=123456
16. spring.datasource.account.driver-class-name=com.mysql.cj.jdbc.Driver

复制代码

配置数据源
DataSourceConfig.java

1. package com.cjs.example.config;
3. import com.alibaba.druid.pool.DruidDataSource;
4. import com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean;
5. import com.zaxxer.hikari.HikariDataSource;
6. import org.mybatis.spring.SqlSessionFactoryBean;
7. import org.springframework.beans.factory.annotation.Qualifier;
8. import org.springframework.boot.context.properties.ConfigurationProperties;
9. import org.springframework.boot.jdbc.DataSourceBuilder;
10. import org.springframework.context.annotation.Bean;
11. import org.springframework.context.annotation.Configuration;
12. import org.springframework.context.annotation.Primary;
13. import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
15. import javax.sql.DataSource;
16. import java.util.HashMap;
17. import java.util.Map;
19. @Configuration
20. public class DataSourceConfig {
22.     @Bean("orderDataSource")
23.     @ConfigurationProperties(prefix = "spring.datasource.order")
24.     public DataSource orderDataSource() {
25. //        return new HikariDataSource();
26. //        return new DruidDataSource();
27.         return DataSourceBuilder.create().build();
28.     }
30.     @Bean("accountDataSource")
31.     @ConfigurationProperties(prefix = "spring.datasource.account")
32.     public DataSource accountDataSource() {
33. //        return new HikariDataSource();
34. //        return new DruidDataSource();
35.         return DataSourceBuilder.create().build();
36.     }
38.     @Bean("stockDataSource")
39.     @ConfigurationProperties(prefix = "spring.datasource.stock")
40.     public DataSource stockDataSource() {
41. //        return new HikariDataSource();
42. //        return new DruidDataSource();
43.         return DataSourceBuilder.create().build();
44.     }
46.     @Primary
47.     @Bean("dynamicDataSource")
48.     public DataSource dynamicDataSource(@Qualifier("orderDataSource") DataSource orderDataSource,
49.                                         @Qualifier("accountDataSource") DataSource accountDataSource,
50.                                         @Qualifier("stockDataSource") DataSource stockDataSource) {
52.         Map<Object, Object> dataSourceMap = new HashMap<>(3);
53.         dataSourceMap.put(DataSourceKey.ORDER.name(), orderDataSource);
54.         dataSourceMap.put(DataSourceKey.STOCK.name(), stockDataSource);
55.         dataSourceMap.put(DataSourceKey.ACCOUNT.name(), accountDataSource);
57.         DynamicRoutingDataSource dynamicRoutingDataSource = new DynamicRoutingDataSource();
58.         dynamicRoutingDataSource.setDefaultTargetDataSource(orderDataSource);
59.         dynamicRoutingDataSource.setTargetDataSources(dataSourceMap);
61.         return dynamicRoutingDataSource;
62.     }
64.     /* https://baomidou.com/pages/3b5af0/ */
65.     @Bean
66.     public MybatisSqlSessionFactoryBean sqlSessionFactoryBean(@Qualifier("dynamicDataSource") DataSource dataSource) {
67.         MybatisSqlSessionFactoryBean sqlSessionFactoryBean = new MybatisSqlSessionFactoryBean();
68.         sqlSessionFactoryBean.setDataSource(dataSource);
69. //        sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mappers/*.xml"));
70.         return sqlSessionFactoryBean;
71.     }
72. }

复制代码

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

DataSourceKey.java

1. package com.cjs.example.config;
3. public enum DataSourceKey {
4.     /**
5.      * Order data source key.
6.      */
7.     ORDER,
8.     /**
9.      * Stock data source key.
10.      */
11.     STOCK,
12.     /**
13.      * Account data source key.
14.      */
15.     ACCOUNT
16. }

复制代码

DynamicDataSourceContextHolder.java

1. package com.cjs.example.config;
3. public class DynamicDataSourceContextHolder {
5.     private static final ThreadLocal<String> CONTEXT_HOLDER = ThreadLocal.withInitial(DataSourceKey.ORDER::name);
7.     public static void setDataSourceKey(DataSourceKey key) {
8.         CONTEXT_HOLDER.set(key.name());
9.     }
11.     public static String getDataSourceKey() {
12.         return CONTEXT_HOLDER.get();
13.     }
15.     public static void clearDataSourceKey() {
16.         CONTEXT_HOLDER.remove();
17.     }
19. }

复制代码

DynamicRoutingDataSource.java

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

复制代码

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

1. DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.ACCOUNT);

复制代码

举个例子：

1. package com.cjs.example;
3. import com.cjs.example.account.entity.Account;
4. import com.cjs.example.account.service.IAccountService;
5. import com.cjs.example.config.DataSourceKey;
6. import com.cjs.example.config.DynamicDataSourceContextHolder;
7. import com.cjs.example.order.entity.Order;
8. import com.cjs.example.order.service.IOrderService;
9. import com.cjs.example.stock.entity.Stock;
10. import com.cjs.example.stock.service.IStockService;
11. import org.junit.jupiter.api.Test;
12. import org.springframework.beans.factory.annotation.Autowired;
13. import org.springframework.boot.test.context.SpringBootTest;
15. import java.math.BigDecimal;
17. @SpringBootTest
18. public class Demo1122ApplicationTests {
20.         @Autowired
21.         private IOrderService orderService;
22.         @Autowired
23.         private IAccountService accountService;
24.         @Autowired
25.         private IStockService stockService;
27.         @Test
28.         public void doBusiness() {
29.                 DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.ORDER);
30.                 Order order = new Order();
31.                 order.setOrderNo("123");
32.                 order.setUserId("1");
33.                 order.setCommodityCode("abc");
34.                 order.setCount(1);
35.                 order.setAmount(new BigDecimal("9.9"));
36.                 orderService.save(order);
38.                 DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.STOCK);
39.                 Stock stock = new Stock();
40.                 stock.setId(1);
41.                 stock.setCommodityCode("abc");
42.                 stock.setName("huawei");
43.                 stock.setCount(1);
44.                 stockService.updateById(stock);
46.                 DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.ACCOUNT);
47.                 Account account = new Account();
48.                 account.setId(1);
49.                 account.setUserId("1");
50.                 account.setAmount(new BigDecimal(100));
51.                 accountService.updateById(account);
52.         }
54. }

复制代码

这样写看起来确实有些麻烦，通常可能不会像这样在一个方法里操作多个数据库，就比如说假设这是一个管理后台，为了图省事把所有业务都写在这一个项目里，这个时候就需要配置多个数据源，各个数据库的业务互相没有关联，只是写在同一个项目中而已，这样的话如果每次都手动设置数据源太麻烦，可以定义一个AOP切面来自动切换数据源。
1.2. 自动切换数据源
https://docs.spring.io/spring-framework/docs/current/reference/html/core.html#aop-ataspectj
給刚才的代码升个级，利用AOP来拦截目标方法自动切换数据源
1、添加@EnableAspectJAutoProxy注解

1. package com.cjs.example;
3. import org.mybatis.spring.annotation.MapperScan;
4. import org.springframework.boot.SpringApplication;
5. import org.springframework.boot.autoconfigure.SpringBootApplication;
6. import org.springframework.context.annotation.EnableAspectJAutoProxy;
8. @EnableAspectJAutoProxy
9. @MapperScan("com.cjs.example.*.mapper")
10. @SpringBootApplication
11. public class Demo1122Application {
12.         public static void main(String[] args) {
13.                 SpringApplication.run(Demo1122Application.class, args);
14.         }
15. }

复制代码

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

1. package com.cjs.example.aop;
3. import com.cjs.example.config.DataSourceKey;
4. import com.cjs.example.config.DynamicDataSourceContextHolder;
5. import org.aspectj.lang.ProceedingJoinPoint;
6. import org.aspectj.lang.annotation.Around;
7. import org.aspectj.lang.annotation.Aspect;
8. import org.aspectj.lang.annotation.Pointcut;
9. import org.springframework.stereotype.Component;
11. @Aspect
12. @Component
13. public class DataSourceAdvice {
15. //    @Pointcut("within(com.cjs.example.order..*)")
16.     @Pointcut("execution(* com.cjs.example.order..*.*(..))")
17.     public void orderPointcut() {}
19. //    @Pointcut("within(com.cjs.example.account..*)")
20.     @Pointcut("execution(* com.cjs.example.account..*.*(..))")
21.     public void accountPointcut() {}
23. //    @Pointcut("within(com.cjs.example.stock..*)")
24.     @Pointcut("execution(* com.cjs.example.stock..*.*(..))")
25.     public void stockPointcut() {}
28.     @Around("orderPointcut()")
29.     public Object order(ProceedingJoinPoint pjp) throws Throwable {
30.         DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.ORDER);
31.         Object retVal = pjp.proceed();
32.         DynamicDataSourceContextHolder.clearDataSourceKey();
33.         return retVal;
34.     }
35.     @Around("accountPointcut()")
36.     public Object account(ProceedingJoinPoint pjp) throws Throwable {
37.         DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.ACCOUNT);
38.         Object retVal = pjp.proceed();
39.         DynamicDataSourceContextHolder.clearDataSourceKey();
40.         return retVal;
41.     }
42.     @Around("stockPointcut()")
43.     public Object stock(ProceedingJoinPoint pjp) throws Throwable {
44.         DynamicDataSourceContextHolder.setDataSourceKey(DataSourceKey.STOCK);
45.         Object retVal = pjp.proceed();
46.         DynamicDataSourceContextHolder.clearDataSourceKey();
47.         return retVal;
48.     }
49. }

复制代码

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

工程结构

第二种方式：dynamic-datasource-spring-boot-starter
功能很强大，支持 数据源分组 ，适用于多种场景 纯粹多库  读写分离  一主多从  混合模式
https://github.com/baomidou/dynamic-datasource-spring-boot-starter
1、引入dynamic-datasource-spring-boot-starter

1. <dependency>
2.     <groupId>com.baomidou</groupId>
3.     <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
4.     <version>3.5.2</version>
5. </dependency>

复制代码

2、配置数据源

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

复制代码

主从配置，读写分离

1. # 多主多从                      纯粹多库（记得设置primary）                   混合配置
2. spring:                               spring:                               spring:
3.   datasource:                           datasource:                           datasource:
4.     dynamic:                              dynamic:                              dynamic:
5.       datasource:                           datasource:                           datasource:
6.         master_1:                             mysql:                                master:
7.         master_2:                             oracle:                               slave_1:
8.         slave_1:                              sqlserver:                            slave_2:
9.         slave_2:                              postgresql:                           oracle_1:
10.         slave_3:                              h2:                                   oracle_2:

复制代码

改造一下前面的例子

1. spring.datasource.dynamic.primary=order
2. # Order
3. spring.datasource.dynamic.datasource.order.url=jdbc:mysql://localhost:3306/order?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
4. spring.datasource.dynamic.datasource.order.username=root
5. spring.datasource.dynamic.datasource.order.password=123456
6. spring.datasource.dynamic.datasource.order.driver-class-name=com.mysql.cj.jdbc.Driver
7. # Stock
8. spring.datasource.dynamic.datasource.stock.url=jdbc:mysql://localhost:3306/stock?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
9. spring.datasource.dynamic.datasource.stock.username=root
10. spring.datasource.dynamic.datasource.stock.password=123456
11. spring.datasource.dynamic.datasource.stock.driver-class-name=com.mysql.cj.jdbc.Driver
12. # Account
13. spring.datasource.dynamic.datasource.account.url=jdbc:mysql://localhost:3306/account?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&useSSL=false
14. spring.datasource.dynamic.datasource.account.username=root
15. spring.datasource.dynamic.datasource.account.password=123456
16. spring.datasource.dynamic.datasource.account.driver-class-name=com.mysql.cj.jdbc.Driver

复制代码

3、使用 @DS 切换数据源
@DS 可以注解在方法上或类上，同时存在就近原则 方法上注解 优先于 类上注解

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

1. package com.cjs.example.order.service.impl;
3. import com.baomidou.dynamic.datasource.annotation.DS;
4. import com.cjs.example.order.entity.Order;
5. import com.cjs.example.order.mapper.OrderMapper;
6. import com.cjs.example.order.service.IOrderService;
7. import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
8. import org.springframework.stereotype.Service;
10. @DS("order")
11. @Service
12. public class OrderServiceImpl extends ServiceImpl<OrderMapper, Order> implements IOrderService {
14. }

复制代码

1. package com.cjs.example.stock.service.impl;
3. import com.baomidou.dynamic.datasource.annotation.DS;
4. import com.cjs.example.stock.entity.Stock;
5. import com.cjs.example.stock.mapper.StockMapper;
6. import com.cjs.example.stock.service.IStockService;
7. import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
8. import org.springframework.stereotype.Service;
10. @DS("stock")
11. @Service
12. public class StockServiceImpl extends ServiceImpl<StockMapper, Stock> implements IStockService {
14. }

复制代码

1. package com.cjs.example.account.service.impl;
3. import com.baomidou.dynamic.datasource.annotation.DS;
4. import com.cjs.example.account.entity.Account;
5. import com.cjs.example.account.mapper.AccountMapper;
6. import com.cjs.example.account.service.IAccountService;
7. import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
8. import org.springframework.stereotype.Service;
10. @DS("account")
11. @Service
12. public class AccountServiceImpl extends ServiceImpl<AccountMapper, Account> implements IAccountService {
14. }

复制代码

 

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！