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

DSTransactional能和@DS多数据源切换配合使用，@DSTransactional注解最好使用在方法上(类似好像不生效)，@DSTransactional注解不能对异常进行捕获手动回滚，只能自动回滚然后抛出异常。可以在Service的类或方法上(优先级更高)使用@Transactional实现事务。分别创建read_db.user和write_db.user，并向read_db.use

Bulut0907

2786人浏览 · 2023-10-09 09:16:04

Bulut0907 · 2023-10-09 09:16:04 发布

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>

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

2.2.1 pom.xml依赖

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.31</version>
        </dependency>

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.2.15</version>
        </dependency>

        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
            <version>3.5.2</version>
        </dependency>

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

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

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;

}

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<User> {

}

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

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

}

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

}

运行程序，结果如下：

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

同时查看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>

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

}