Mybatis-Plus-多数据源

官方文档地址：https://mp.baomidou.com/guide/dynamic-datasource.html#%E6%96%87%E6%A1%A3-documentation

1. 常规使用

  1. 引入dynamic-datasource-spring-boot-starter
1
2
3
4
5
<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
  <version>${version}</version>
</dependency>
  1. 配置数据源
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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
  1. 切换数据源
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@Service
@DS("slave")
public class UserServiceImpl implements UserService {

  @Autowired
  private JdbcTemplate jdbcTemplate;

  public List selectAll() {
    return  jdbcTemplate.queryForList("select * from user");
  }
  
  @Override
  @DS("slave_1")
  public List selectByCondition() {
    return  jdbcTemplate.queryForList("select * from user where age >10");
  }
}

2. 动态添加数据源并执行动态sql

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
/**
 * @author iseven.yang
 * @date 2021/11/2 16:51
 */
@Service
@Slf4j
public class SqlQueryServiceImpl implements SqlQueryService {

    @Qualifier("hikariDataSourceCreator")
    @Autowired
    private DataSourceCreator dataSourceCreator;
    @Autowired
    private DataSource dataSource;
    @Autowired
    private JdbcTemplate jdbcTemplate;

    private static final String REGEX = "#\\{(\\w*)\\}";
    private static final String REPLACE = "?";

    @Override
    public Set<String> getAllDataSource() {
        DynamicRoutingDataSource dynamicRoutingDataSource = (DynamicRoutingDataSource) dataSource;
        return dynamicRoutingDataSource.getDataSources().keySet();
    }

    @Override
    public Set<String> addDataSource(DataSourceQry dataSourceQry) {
        DataSourceProperty dataSourceProperty = new DataSourceProperty();
        dataSourceProperty.setPoolName(dataSourceQry.getDbKey());
        dataSourceProperty.setDriverClassName("com.mysql.jdbc.Driver");
        dataSourceProperty.setUrl(dataSourceQry.getUrl());
        dataSourceProperty.setUsername(dataSourceQry.getUsername());
        dataSourceProperty.setPassword(dataSourceQry.getPassword());
        DataSource newDataSource = dataSourceCreator.createDataSource(dataSourceProperty);
        DynamicRoutingDataSource dynamicRoutingDataSource = (DynamicRoutingDataSource) dataSource;
        dynamicRoutingDataSource.addDataSource(dataSourceQry.getDbKey(), newDataSource);
        return dynamicRoutingDataSource.getDataSources().keySet();
    }

    @Override
    public Map<String, Object> queryForMap(DbQueryQry dbQueryQry) {
        if(StringUtils.isAnyBlank(dbQueryQry.getDbKey(), dbQueryQry.getSql())) {
            return null;
        }
        String sql = dbQueryQry.getSql();
        Map<String, Object> param = dbQueryQry.getParam();
        //将#{xxx}转成?， 将map参数结构转成数组
        Pattern p = Pattern.compile(REGEX);
        Matcher m = p.matcher(sql);
        List<String> matchList = new ArrayList<>();
        while (m.find()) {
            matchList.add(m.group());
        }
        sql = m.replaceAll(REPLACE);
        Object[] sqlArgs = new Object[matchList.size()];
        for(int i=0; i<matchList.size(); i++) {
            sqlArgs[i] = param.get(matchList.get(i).substring(2, matchList.get(i).length()-1));
        }
        try {
            DynamicDataSourceContextHolder.push(dbQueryQry.getDbKey());
            List<Map<String, Object>> queryList = jdbcTemplate.query(sql, sqlArgs, new ColumnMapRowMapper());
            if(CollUtil.isNotEmpty(queryList)) {
                return queryList.get(0);
            }
            return null;
        } finally {
            log.info("移除当前线程数据源");
            DynamicDataSourceContextHolder.poll();
        }
    }
}

3. 多数据源测试全量代码

  • 创建springboot工程，引入maven依赖
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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
<?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>cn.aacopy.learn</groupId>
    <artifactId>mybatisplus-learn</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>mybatisplus-learn</name>
    <description>mybatisplus-learn</description>
    <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.1</version>
        </dependency>

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

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

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </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>
            </plugin>
        </plugins>
    </build>

</project>

  • 添加配置项application.yml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
spring:
  datasource:
    dynamic:
      primary: aacopy
      strict: false
      datasource:
        aacopy:
          url: jdbc:mysql://192.168.25.216:3306/aacopy?useSSL=false&autoReconnect=true&useUnicode=true&characterEncoding=utf8
          username: root
          password: aacopy.cn
        aacopy1:
          url: jdbc:mysql://192.168.25.216:3306/aacopy1?useSSL=false&autoReconnect=true&useUnicode=true&characterEncoding=utf8
          username: root
          password: aacopy.cn
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  • 启动类上添加扫描注解
1
2
3
4
5
6
7
8
9
@SpringBootApplication
@MapperScan("cn.aacopy.learn.mybatisplus.mapper")
public class MybatisplusLearnApplication {

    public static void main(String[] args) {
        SpringApplication.run(MybatisplusLearnApplication.class, args);
    }

}

  • 创建两个数据库。这里创建了aacopy和aacopy1

    • aacopy中创建表goods
    1
    2
    3
    4
    5
    6
    7
    8
    9
    -- aacopy.goods definition

    CREATE TABLE `goods` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
      `num` bigint(20) DEFAULT NULL,
      `version` int(11) DEFAULT '1',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    • aacopy1中创建表
    1
    2
    3
    4
    5
    6
    7
    -- aacopy1.`user` definition

    CREATE TABLE `user` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

  • 用代码生成工具生成对应的代码文件

image-20221103233842920

  • 因为user表不是默认数据源中的表，所以需要指定对应的数据源，类上添加@DS(“aacopy1”)属性
1
2
3
4
5
6
@Service
@DS("aacopy1")
public class UserServiceImpl extends ServiceImpl<UserMapper, UserDO>
    implements UserService{

}
  • 编写一个测试类
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
36
37
38
39
40
41
42
43
44
45
46
47
48
package cn.aacopy.learn.mybatisplus;

import cn.aacopy.learn.mybatisplus.domain.GoodsDO;
import cn.aacopy.learn.mybatisplus.domain.UserDO;
import cn.aacopy.learn.mybatisplus.service.GoodsService;
import cn.aacopy.learn.mybatisplus.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;

import java.util.List;

/**
 * @author iseven.yang
 * @date 2022/11/3 23:22
 */
@SpringBootTest
@Slf4j
public class DynamicDsTest {

    @Autowired
    private GoodsService goodsService;
    @Autowired
    private UserService userService;

    /**
     * 测试多数据源
     */
    @Test
    void test1() {
        GoodsDO goodsDO = new GoodsDO();
        goodsDO.setName("测试物品");
        goodsDO.setNum(100L);
        goodsService.save(goodsDO);
        List<GoodsDO> goodsDOS = goodsService.list();
        goodsDOS.forEach(o -> log.info("物品：" + o));

        log.info("=================================");

        UserDO userDO = new UserDO();
        userDO.setName("测试人员");
        userService.save(userDO);
        List<UserDO> userDOS = userService.list();
        userDOS.forEach(o -> log.info("人员：" + o));
    }
}

  • 执行结果
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
36
37
38
39
40
41
42
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@25be445f] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1330143761 wrapping com.mysql.cj.jdbc.ConnectionImpl@51e3d37e] will not be managed by Spring
==>  Preparing: INSERT INTO goods ( name, num ) VALUES ( ?, ? )
==> Parameters: 测试物品(String), 100(Long)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@25be445f]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5c134052] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1112569408 wrapping com.mysql.cj.jdbc.ConnectionImpl@51e3d37e] will not be managed by Spring
==>  Preparing: SELECT id,name,num,version FROM goods
==> Parameters: 
<==    Columns: id, name, num, version
<==        Row: 11, 测试物品, 100, 1
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5c134052]
2022-11-03 23:29:12.757  INFO 17156 --- [           main] c.a.learn.mybatisplus.DynamicDsTest      : 物品：GoodsDO(id=11, name=测试物品, num=100, version=1)
2022-11-03 23:29:12.757  INFO 17156 --- [           main] c.a.learn.mybatisplus.DynamicDsTest      : =================================
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3c232051] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@366008009 wrapping com.mysql.cj.jdbc.ConnectionImpl@3ab35b9c] will not be managed by Spring
==>  Preparing: INSERT INTO user ( name ) VALUES ( ? )
==> Parameters: 测试人员(String)
<==    Updates: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3c232051]
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4130a648] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1644128841 wrapping com.mysql.cj.jdbc.ConnectionImpl@3ab35b9c] will not be managed by Spring
==>  Preparing: SELECT id,name FROM user
==> Parameters: 
<==    Columns: id, name
<==        Row: 1, 测试人员
<==      Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4130a648]
2022-11-03 23:29:12.769  INFO 17156 --- [           main] c.a.learn.mybatisplus.DynamicDsTest      : 人员：UserDO(id=1, name=测试人员)
2022-11-03 23:29:12.785  INFO 17156 --- [ionShutdownHook] c.b.d.d.DynamicRoutingDataSource         : dynamic-datasource start closing ....
2022-11-03 23:29:12.785  INFO 17156 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : aacopy1 - Shutdown initiated...
2022-11-03 23:29:12.791  INFO 17156 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : aacopy1 - Shutdown completed.
2022-11-03 23:29:12.791  INFO 17156 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : aacopy - Shutdown initiated...
2022-11-03 23:29:12.792  INFO 17156 --- [ionShutdownHook] com.zaxxer.hikari.HikariDataSource       : aacopy - Shutdown completed.
2022-11-03 23:29:12.792  INFO 17156 --- [ionShutdownHook] c.b.d.d.DynamicRoutingDataSource         : dynamic-datasource all closed success,bye

4. SQL表名的动态替换（多租户场景）

在多租户场景中，如果使用物理隔离，将不同的租户的数据放在不同的表里，在查询SQL是，需要通过动态变更表名来路由到对应的租户表中

  • 代码逻辑实现
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
@Configuration
public class MybatisPlusConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 添加乐观锁插件
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());

        //设置动态更新表名插件
        DynamicTableNameInnerInterceptor dynamicTableNameInnerInterceptor
                = new DynamicTableNameInnerInterceptor();
        //设置表名替换规则
        dynamicTableNameInnerInterceptor.setTableNameHandler((sql, tableName) -> {
            // 模拟从当前线程变量中获取当前用户的租户id
            Long tenantId = 1L;
            // 将表面后面加上 _tenantId
            return tableName + "_" + tenantId;
        });
        interceptor.addInnerInterceptor(dynamicTableNameInnerInterceptor);
        return interceptor;
    }
}

修改后的sql：INSERT INTO goods_1 ( name, num ) VALUES ( ?, ? )