多租户数据源隔离

多租户数据源隔离

1.前言

多租户数据隔离的实现主要有两种方式，一是在表中添加租户字段，根据租户字段实现数据的隔离；二是每个租户创建一个数据源，实现数据的隔离。本文主要介绍第二种方式。

2.快速入门

2.1准备工作

根据sql脚本创建表

-- 用于管理租户数据源
CREATE TABLE `datasource` (
  `ds_id` bigint(20) NOT NULL COMMENT '主键id',
  `tenant_id` bigint(20) DEFAULT NULL COMMENT '租户id',
  `ds_code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '数据源标识',
  `jdbc_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '数据库url',
  `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '数据库名称',
  `password` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '数据库密码',
  PRIMARY KEY (`ds_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 测试动态切换数据源使用
CREATE TABLE `t_user` (
  `id` bigint(20) NOT NULL COMMENT '主键ID',
  `name` varchar(16) DEFAULT '' COMMENT '名字',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='t_user';

2.2代码实现

• 引入依赖

<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
			<version>2.7.18</version>
		</dependency>
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-boot-starter</artifactId>
			<version>3.5.10.1</version>
		</dependency>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.33</version>
		</dependency>

		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
			<version>4.3.1</version>
		</dependency>
		<dependency>
			<groupId>javax.validation</groupId>
			<artifactId>validation-api</artifactId>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<version>1.18.34</version>
		</dependency>
	</dependencies>

• yaml配置

这里配置了用于管理多数据源的数据库连接信息

spring:
  datasource:
    dynamic:
      #设置默认的数据源或者数据源组,默认值即为master
      primary: master
      #严格匹配数据源,默认false. true未匹配到指定数据源时抛异常,false使用默认数据源
      strict: false
      datasource:
        master:
          driver-class-name: com.mysql.cj.jdbc.Driver
          url: jdbc:mysql://127.0.0.1:3306/tenant_datasource?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF8&useSSL=false
          username: root
          password: root

• 创建实体类

@Data
@Accessors(chain = true)
@TableName("datasource")
public class DataSourceEntity{

    @TableId(type = IdType.ASSIGN_ID)
    private Long dsId;

    private Long tenantId;

    private String dsCode;

    private String jdbcUrl;

    private String username;

    private String password;

}

@Data
@Accessors(chain = true)
@TableName("t_user")
public class TUser {

    @TableId(type = IdType.ASSIGN_ID)
    private Long id;

    private String name;
}

• mapper层

@Mapper
public interface TenantMapper extends BaseMapper<DataSourceEntity> {

    List<DataSourceEntity> findAll();
}

@Mapper
public interface UserMapper extends BaseMapper<TUser> {

}

• controller层

@Slf4j
@RestController
public class TenantController {
    @Autowired
    private DataSource dataSource;
    @Autowired
    private DefaultDataSourceCreator dataSourceCreator;
    @Autowired
    private TenantService tenantService;
    @Autowired
    private UserService userService;

    //通用数据源会根据maven中配置的连接池根据顺序依次选择。
    //默认的顺序为druid>hikaricp>beecp>dbcp>spring basic
    //通用添加数据源（推荐）
    @PostMapping("/add")
    public Set<String> add(@Valid @RequestBody DataSourceDTO dto) {
        DataSourceProperty dataSourceProperty = new DataSourceProperty();
        BeanUtils.copyProperties(dto, dataSourceProperty);
        DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
        DataSource dataSource = dataSourceCreator.createDataSource(dataSourceProperty);
        ds.addDataSource(dto.getPoolName(), dataSource);
        //添加数据源到数据库中
        tenantService.add(dto);
        return ds.getDataSources().keySet();
    }

    //删除数据源,name:数据源标识
    @DeleteMapping
    public String remove(String poolName) {
        DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
        ds.removeDataSource(poolName);
        //租户表中移除
        tenantService.remove(poolName);

        return "删除成功";
    }

    @GetMapping("/{ds_code}/user")
    // Get http://localhost:8080/llp/user
    // Get http://localhost:8080/master/user
    public List<TUser> getUsers(@PathVariable("ds_code") String dsCode) {
        try {
            DynamicDataSourceContextHolder.push(dsCode);
            return userService.findAll();
        } finally {
            DynamicDataSourceContextHolder.clear();
        }
    }

}

• service

@Service
public class TenantServiceImpl extends ServiceImpl<TenantMapper, DataSourceEntity> implements TenantService {
	
    //添加数据源到表中
    @Override
    @Transactional(rollbackFor = Exception.class)
    public void add(DataSourceDTO dto) {
        DataSourceEntity dataSource = new DataSourceEntity();
        dataSource.setTenantId(dto.getTenantId());
        dataSource.setDsCode(dto.getPoolName());
        dataSource.setJdbcUrl(dto.getUrl());
        dataSource.setUsername(dto.getUsername());
        dataSource.setPassword(dto.getPassword());
        baseMapper.insert(dataSource);
    }
	
    //删除数据源，同步删除租户数据源配置
    @Override
    public void remove(String poolName) {
        DataSourceEntity dataSource = baseMapper.selectOne(new LambdaQueryWrapper<DataSourceEntity>().eq(DataSourceEntity::getDsCode, poolName));
        baseMapper.deleteById(dataSource);
    }
}

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, TUser> implements UserService {
    @Override
    public List<TUser> findAll() {
        return baseMapper.selectList(null);
    }

    /**
     * 扩展： 从header头中提取数据源标识，动态切换数据源的方式
     * 默认有三个职责链来处理动态参数解析器 header->session->spel
     * @DS("#session.tenantName")//从session获取
     * @DS("#header.tenantName")//从header获取
     * @DS("#tenantName")//使用spel从参数获取
     * @DS("#user.tenantName")//使用spel从复杂参数获取
     * @return
     */
    @DS("#header.tenantName")//从header获取
    public List findAllByHeader() {
        return baseMapper.selectList(null);
    }

}

• 数据源初始化配置

在项目启动时将数据库中配置的数据源加载到DynamicRoutingDataSource中便于后续进行数据源的操作：切换、删除等

import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.creator.DataSourceProperty;
import com.baomidou.dynamic.datasource.creator.DefaultDataSourceCreator;
import com.llp.dynamicDataSource.entity.DataSourceEntity;
import com.llp.dynamicDataSource.mapper.TenantMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.util.List;

@Slf4j
@Configuration
public class DynamicDataSourceInit {

    @Autowired
    private DefaultDataSourceCreator dataSourceCreator;

    @Autowired
    private TenantMapper tenantMapper;

    @Autowired
    private DataSource dataSource;

    @Bean
    public void initDataSource() {
        DynamicRoutingDataSource ds = (DynamicRoutingDataSource) dataSource;
        List<DataSourceEntity> datasources = tenantMapper.selectList(null);
        for (DataSourceEntity entity : datasources) {
            DataSourceProperty dsProperties = new DataSourceProperty();
            dsProperties.setPoolName(entity.getDsCode());
            dsProperties.setUrl(entity.getJdbcUrl());
            dsProperties.setUsername(entity.getUsername());
            dsProperties.setPassword(entity.getPassword());
            DataSource dataSource = dataSourceCreator.createDataSource(dsProperties);
            ds.addDataSource(dsProperties.getPoolName(), dataSource);
        }
        log.info("{}", ds.getDataSources().keySet());
    }
}

• 启动类

@SpringBootApplication
//扫描mapper接口所在的包，用于创建实现类并注册到spring容器中
@MapperScan("com.llp.dynamicDataSource.mapper")
public class DynamicDataSourceApplication {
    public static void main(String[] args) {
        SpringApplication.run(DynamicDataSourceApplication.class);
    }
}

3.扩展

前面只是做了简单的时间，我们可以扩展datasource表,比如添加租户有效期，用于控制租户的使用时限，或者和一些资费进行绑定

CREATE TABLE `datasource` (
  `ds_id` bigint(20) NOT NULL COMMENT '主键id',
  `tenant_id` bigint(20) DEFAULT NULL COMMENT '租户id',
  `ds_code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '数据源标识',
  `jdbc_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '数据库url',
  `username` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '数据库名称',
  `password` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '数据库密码',
  PRIMARY KEY (`ds_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

另外创建租户数据源之后，可能还会超级管理员、租户管理员以及一些菜单权限的问题（可以在创建数据库时就准备好一个包含了这些角色菜单的数据库初始脚本） 或者做定制化开发

4.测试验证

• 添加租户数据源

请求

POST

http://127.0.0.1:8080/add

{
  "poolName": "llp",
  "tenantId": 3,
  "driverClassName": "com.mysql.cj.jdbc.Driver",
  "url": "jdbc:mysql://127.0.0.1:3306/llp?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF8&useSSL=false",
  "username": "root",
  "password": "root"
}

响应

[
  "llp",
  "master"
]

• 查询测试

请求

GET

http://127.0.0.1:8080/llp/user

响应

[
  {
    "id": 1,
    "name": "孙悟空"
  }
]