SpringBoot采用Dynamic-Datasource方式实现多JDBC数据源

1. Dynamic-Datasource实现多JDBC数据源配置

1.1 特性

• 提供本地多数据源事务方案。由AOP实现
• 支持数据源分组，适用于多种场景，如多库读写分离、一主多从(实现了数据库负载均衡算法)、混合模式
• 提供自定义数据源来源方案(如全从数据库加载)
• 提供项目启动后动态增加移除数据源方案
• 支持数据库敏感配置信息加密(可自定义)ENC()
• 支持每个数据库独立初始化表结构schema和数据库database
• 支持无数据源启动，支持懒加载数据源(需要的时候再创建连接)
• 支持自定义注解，需继承DS(3.2.0+)
• 提供并简化对Druid、HikariCp、BeeCp、Dbcp2的快速集成
• 提供对Mybatis-Plus、Quartz、ShardingJdbc、P6sy、Jndi等组件的集成方案
• 提供Mybatis环境下的纯读写分离方案
• 提供使用spel动态参数解析数据源方案。内置spel、session、header，支持自定义
• 支持多层数据源嵌套切换。(ServiceA >>> ServiceB >>> ServiceC)
• 支持Seata分布式事务

1.2 Mysql数据准备

分别创建read_db.user和write_db.user，并向read_db.user写入数据

mysql> create database read_db;
Query OK, 1 row affected (0.14 sec)

mysql> create database write_db;
Query OK, 1 row affected (0.01 sec)

mysql> create table read_db.user (
    -> id bigint(20) auto_increment not null comment '主键ID',
    -> name varchar(30) null default null comment '姓名',
    -> primary key (id)
    -> );
Query OK, 0 rows affected, 1 warning (0.29 sec)

mysql> 
mysql> insert into read_db.user (id, name) values
    -> (1, 'read_name1'),
    -> (2, 'read_name2'),
    -> (3, 'read_name3'),
    -> (4, 'read_name4'),
    -> (5, 'read_name5');
Query OK, 5 rows affected (0.16 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> 
mysql> create table write_db.user (
    -> id bigint(20) auto_increment not null comment '主键ID',
    -> name varchar(30) null default null comment '姓名',
    -> primary key (id)
    -> );
Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

2.2 通过Dynamic-Datasource实现多JDBC数据源

2.2.1 pom.xml依赖

        
            mysql
            mysql-connector-java
            8.0.31
        

        
            com.alibaba
            druid
            1.2.15
        

        
            com.baomidou
            dynamic-datasource-spring-boot-starter
            3.5.2
        
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

可以看到自动添加了spring-boot-starter-jdbc、spring-boot-starter-aop依赖

2.2.2 application.properties配置

通过dynamic方式，指定了datasource1和datasource2两个DataSource

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
# 设置默认的数据源。默认是master
spring.datasource.dynamic.primary=datasource1
# 严格匹配数据源, 默认false。true表示未匹配到指定数据源时抛异常, false表示使用默认数据源
spring.datasource.dynamic.strict=true

# 用于读的数据库
spring.datasource.dynamic.datasource.datasource1.url=jdbc:mysql://192.168.28.12:3306/read_db
spring.datasource.dynamic.datasource.datasource1.username=root
spring.datasource.dynamic.datasource.datasource1.password=Root_123
spring.datasource.dynamic.datasource.datasource1.driver-class-name=com.mysql.cj.jdbc.Driver

# 用于写的数据库
spring.datasource.dynamic.datasource.datasource2.url=jdbc:mysql://192.168.28.12:3306/write_db
spring.datasource.dynamic.datasource.datasource2.username=root
spring.datasource.dynamic.datasource.datasource2.password=Root_123
spring.datasource.dynamic.datasource.datasource2.driver-class-name=com.mysql.cj.jdbc.Driver
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

2.2.3 使用@DS注解选择DataSource

可以在Service的类或方法上(优先级更高)使用@DS注解选择DataSource

2.2.4 使用@Transactional + @DSTransactional实现事务

可以在Service的类或方法上(优先级更高)使用@Transactional实现事务。对异常的捕获后，通过TransactionAspectSupport.currentTransactionStatus().setRollbackOnly()进行手动回滚

@Transactional注解会让@DS的多数据源切换失效，虽然可以通过@Transactional(propagation = Propagation.REQUIRES_NEW)开启一个内部子事务来使@DS切换数据源，但是大部分场景不适用

@DSTransactional能和@DS多数据源切换配合使用，@DSTransactional注解最好使用在方法上(类似好像不生效)，@DSTransactional注解不能对异常进行捕获手动回滚，只能自动回滚然后抛出异常

2.3 动态数据源测试

2.3.1 创建User类

package com.hh.springboottest.myController;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@NoArgsConstructor
@AllArgsConstructor
@Data
@ToString
public class User {

    private Long id;
    private String name;

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

2.3.2 Mapper接口实现

package com.hh.springboottest.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hh.springboottest.myController.User;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface UserMapper extends BaseMapper {

}
1
2
3
4
5
6
7
8
9
10

2.3.3 Service实现

Service接口实现

package com.hh.springboottest.service;

import com.hh.springboottest.myController.User;

public interface UserService {

    public User getUser(Long id);

    public void saveMultiUser();
}
1
2
3
4
5
6
7
8
9
10

ServiceImpl实现类。说明如下：

• @DS(“datasource1”)注解，让userMapper.selectById使用了datasource1数据源
• @DS(“datasource2”)注解，让userMapper.insert使用了datasource2数据源
• @Transactional注解，当userMapper.insert(user1)执行成功，userMapper.insert(user2)执行失败，会回滚userMapper.insert(user1)的操作

package com.hh.springboottest.service.impl;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.hh.springboottest.mapper.UserMapper;
import com.hh.springboottest.myController.User;
import com.hh.springboottest.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    UserMapper userMapper;

    @DS("datasource1")
    @Override
    public User getUser(Long id) {
        return userMapper.selectById(id);
    }

    @Transactional
    @DS("datasource2")
    @Override
    public void saveMultiUser() {
        User user2 = new User(2L, "write_name2");
        userMapper.insert(user2);


        User user3 = new User(3L, "write_name3");
        userMapper.insert(user3);
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

2.3.4 测试

package com.hh.springboottest;

import com.hh.springboottest.myController.User;
import com.hh.springboottest.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

@Slf4j
@SpringBootTest
public class MyApplicationTest {

    @Autowired
    UserService userService;

    @Test
    public void dynamicDataSourceTest() {

        User user = userService.getUser(1L);
        log.info("获取到的用户为：{}", user);

        userService.saveMultiUser();
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

运行程序，结果如下：

2022-11-24 09:16:15.733  INFO 33760 --- [           main] com.hh.springboottest.MyApplicationTest  : 获取到的用户为：User(id=1, name=read_name1)
2022-11-24 09:16:15.857  INFO 33760 --- [ionShutdownHook] c.b.d.d.DynamicRoutingDataSource         : dynamic-datasource start closing ....
2022-11-24 09:16:15.861  INFO 33760 --- [ionShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} closing ...
2022-11-24 09:16:15.866  INFO 33760 --- [ionShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} closed
2022-11-24 09:16:15.866  INFO 33760 --- [ionShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-2} closing ...
2022-11-24 09:16:15.867  INFO 33760 --- [ionShutdownHook] com.alibaba.druid.pool.DruidDataSource   : {dataSource-2} closed
2022-11-24 09:16:15.867  INFO 33760 --- [ionShutdownHook] c.b.d.d.DynamicRoutingDataSource         : dynamic-datasource all closed success,bye
1
2
3
4
5
6
7

同时查看write_db.user表，数据如下：

mysql> select * from write_db.user;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | write_name1  |
|  2 | write_name2  |
|  3 | write_name3  |
+----+--------------+
3 rows in set (0.11 sec)

mysql> 
1
2
3
4
5
6
7
8
9
10
11

2.4 removeDataSource删除DataSource

通过DynamicRoutingDataSource的removeDataSource方法删除DataSource

package com.hh.springboottest.service.impl;

import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;

@Service
public class DataSourceServiceImpl implements DataSourceService {

    @Autowired
    DataSource dataSource;

    public void removeDataSource(String dataSourceName) {
        DynamicRoutingDataSource dynamicRoutingDataSource =
                (DynamicRoutingDataSource) dataSource;
        dynamicRoutingDataSource.removeDataSource(dataSourceName);
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21