程序员の奇妙冒险

返回
多租户 Java MyBatis Spring Boot 多租户
2023-06-20 09:43 阅读:100 评论:0

druid-multi-tenant-starter 多租户插件，开箱即用，支持单个租户以及多个租户

druid-multi-tenant-starter 目标多租户插件，快速集成，开箱即用，支持单个租户以及多个租户，基于alibaba druid 解析SQL，开发过程中只需要像平时一样写SQL语句即可，执行SQL最终会被拦截串改，最终把租户字段添加到SQL语句中，生成新的SQL语句，此过程你无需关...

                    <link rel="stylesheet" href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/editerView/markdown_views-a5d25dd831.css">
                    <link rel="stylesheet" href="https://csdnimg.cn/release/blogv2/dist/mobile/css/edit_views_md-250d69367f.min.css">
                    <link rel="stylesheet" href="https://csdnimg.cn/release/blogv2/dist/mdeditor/css/style-e504d6a974.css">
                    <h2><a id="druidmultitenantstarter_0"></a>druid-multi-tenant-starter</h2>

目标多租户插件，快速集成，开箱即用，支持单个租户以及多个租户，基于alibaba druid 解析SQL，开发过程中只需要像平时一样写SQL语句即可，执行SQL最终会被拦截串改，最终把租户字段添加到SQL语句中，生成新的SQL语句，此过程你无需关心是如何添加

需要注意的是，插件最终会认为每个表都会存在租户ID字段，所以，每个表都应该出现租户ID字段，否则需要在配置中明确指出需要忽略的表，忽略的表将不会拼接租户ID字段查询条件，临时表亦是如此(否则临时表结果集中需要出现租户ID字段)

完整的YML配置

mybatis:
  tenant:
    config:
      # 是否使用MyBatis拦截器方式修改sql,需要禁用druidFilterEnable
      enable: true
      # 数据库方言，默认mysql
      db-type: mysql
      # 是否忽略表按租户ID过滤,默认所有表都按租户ID字段条件查询，指定表名称(区分大小写全等判断)
      ignore-table-name:
        - user_role
      ignore-match-table-alias
      # 匹配判断指定表别名称是否忽略表按租户ID字段条件查询(区分大小写匹配判断)
        - temp # tempTableAlias
      # 数据库中租户ID的列名
      tenant-id-column: tenant_id
      # 是否使用druid过滤器方式修改sql,依赖druid数据库连接池,需要禁用enable=false
      druid-filter-enable: false

支持

  • <input type="checkbox" class="task-list-item-checkbox" checked="true" disabled="disabled"> Mybatis Plus
  • <input type="checkbox" class="task-list-item-checkbox" checked="true" disabled="disabled"> Mybatis PageHelper 分页插件

支持忽略tenantId策略

  • <input type="checkbox" class="task-list-item-checkbox" checked="true" disabled="disabled"> 支持原SQL语句中已有tenantId字段条件跳过
  • <input type="checkbox" class="task-list-item-checkbox" checked="true" disabled="disabled"> 支持忽略指定表名添加tenantId字段条件
  • <input type="checkbox" class="task-list-item-checkbox" checked="true" disabled="disabled"> 支持忽略指定Mapper接口SQL语句添加tenantId字段条件
  • <input type="checkbox" class="task-list-item-checkbox" checked="true" disabled="disabled"> 支持指定表名忽略SQL语句添加tenantId字段条件
  • <input type="checkbox" class="task-list-item-checkbox" checked="true" disabled="disabled"> 支持指定别名忽略SQL语句添加tenantId字段条件

快速开始

  • 在已经集成Mybatis项目中引入以下依赖
<dependency>
    <groupId>io.github.osinn</groupId>
    <artifactId>druid-multi-tenant-starter</artifactId>
    <version>1.4.2</version>
</dependency>

配置

mybatis:
  tenant:
    config:
      # 是否使用MyBatis拦截器方式修改sql,需要禁用druidFilterEnable
      enable: true
      # 数据库方言
      db-type: mysql
      # 数据库中租户ID的列名
      tenant-id-column: tenant_id
  • 如果SQL中表名不在 ignore-table-name 中，则去ignore-match-table-alias匹配查找
  • 执行SQL中存在临时表可以约束指定 ignore-match-table-alias 匹配临时表别名来忽略临时表添加租户ID查询条件

实现提供获取多租户值接口

  • 需要实现ITenantService接口提供获取多租户ID值
/**
 * 演示：提供多租户ID服务接口
 *
 * @author wency_cai
 */
@Service
public class TenantServiceImpl implements ITenantService<Integer>{

@Override
public List&lt;Integer&gt; getTenantIds() {
    // 查询系统多租户id,如果有多个返回多个值即可
    int tenantId = 1;
    return Lists.newArrayList(tenantId);
}



}

  • 到此整合完成

多租户忽略Mapper方法

public interface UserMapper {

/**
 * 添加@IgnoreTenantIdField注解来忽略设置多租户字段
 */
@IgnoreTenantIdField
void deleteTestIgnoreTenantIdById(Long id);



}

select语句

SELECT id, name, tenant_id
FROM role
WHERE tenant_id = 11
------------------------------------- 

SELECT *
FROM user s
WHERE s.name = '333'
AND s.tenant_id = 11
------------------------------------- 


SELECT tenant_id
FROM people
WHERE id IN (
SELECT id
FROM user s
WHERE s.tenant_id = 11
)
AND tenant_id = 11
------------------------------------- 


SELECT tenant_id
FROM people
WHERE id IN (
SELECT id
FROM user s
WHERE s.tenant_id = 11
)
AND id IN (
SELECT u.id
FROM user u
WHERE u.tenant_id = 11
)
AND tenant_id = 11
------------------------------------- 


SELECT tenant_id
FROM people
WHERE id IN (
SELECT id
FROM user s
WHERE s.tenant_id = 11
)
AND id IN (
SELECT u.id
FROM user u
WHERE u.tenant_id = 11
)
AND tenant_id IN (
SELECT u.tenant_id
FROM user u
WHERE u.tenant_id = 11
)
AND tenant_id = 11
------------------------------------- 


SELECT p.tenant_id
, (
SELECT u.name
FROM user u
WHERE u.id = p.user_id
AND u.tenant_id = 11
)
FROM people p
WHERE p.tenant_id = 11
------------------------------------- 


SELECT p.tenant_id
, (
SELECT u.name
FROM user u
WHERE u.id = p.user_id
AND u.tenant_id = 11
)
, (
SELECT s.name
FROM user s
WHERE s.id = p.user_id
AND s.tenant_id = 11
)
FROM people p
WHERE p.tenant_id = 11
------------------------------------- 


SELECT p.tenant_id
, (
SELECT u.name
, (
SELECT s.name
FROM user s
WHERE s.id = p.user_id
AND s.tenant_id = 11
)
FROM user u
WHERE u.id = p.user_id
AND u.tenant_id = 11
)
FROM people p
WHERE p.tenant_id = 11
------------------------------------- 


SELECT u.*
FROM </span>user<span class="token punctuation"> u
JOIN user_role ur
ON ur.user_id = u.id
AND ur.tenant_id = 11
WHERE u.tenant_id = 11
------------------------------------- 


SELECT u.*
FROM </span>user<span class="token punctuation"> u
JOIN user_role ur
ON ur.user_id = u.id
AND u.id = 22
AND ur.tenant_id = 11
WHERE u.tenant_id = 11
------------------------------------- 


SELECT u.*
, (
SELECT ur.name
FROM role r
WHERE r.id = ur.role_id
AND r.tenant_id = 11
) AS r_name
FROM </span>user<span class="token punctuation"> u
JOIN user_role ur
ON ur.user_id = u.id
AND u.id = 22
AND ur.tenant_id = 11
WHERE u.tenant_id = 11
------------------------------------- 


SELECT u.*
FROM </span>user<span class="token punctuation"> u
JOIN user_role ur
ON ur.user_id = u.id
AND ur.role_id IN (
SELECT r.id
FROM role r
WHERE r.tenant_id = 11
)
AND ur.tenant_id = 11
WHERE u.tenant_id = 11
------------------------------------- 


SELECT u.*
FROM </span>user<span class="token punctuation"> u
JOIN user_role ur
ON ur.user_id = u.id
AND ur.role_id IN (
SELECT r.id
FROM role r
WHERE r.tenant_id = 11
)
AND ur.test_id IN (
SELECT r.test_id
FROM role r
WHERE r.tenant_id = 11
)
AND ur.tenant_id = 11
WHERE u.tenant_id = 11
------------------------------------- 


SELECT u.*
FROM </span>user<span class="token punctuation"> u
LEFT JOIN user_role ur
ON ur.user_id = u.id
AND ur.tenant_id = 11
WHERE u.tenant_id = 11
------------------------------------- 


SELECT u.*
FROM </span>user<span class="token punctuation"> u
RIGHT JOIN user_role ur
ON ur.user_id = u.id
AND ur.tenant_id = 11
WHERE u.tenant_id = 11
------------------------------------- 


SELECT COUNT(), id
FROM </span>user<span class="token punctuation">
WHERE tenant_id = 11
GROUP BY id
HAVING COUNT() >= 1
------------------------------------- 


SELECT EXISTS (
SELECT *
FROM </span>user<span class="token punctuation">
WHERE username = ?
AND tenant_id = 11
) AS d
------------------------------------- 


SELECT EXISTS (
SELECT tenant_id
FROM people
WHERE id IN (
SELECT id
FROM user s
WHERE s.tenant_id = 11
)
AND tenant_id = 11
) AS d
------------------------------------- 


SELECT username
FROM </span>user<span class="token punctuation">
WHERE tenant_id = 11
UNION
SELECT username
FROM account
WHERE tenant_id = 11
------------------------------------- 


SELECT u.*, r.id AS r_id, r.NAME AS r_name
FROM </span>user<span class="token punctuation"> u
LEFT JOIN user_role ur
ON ur.user_id = u.id
AND ur.tenant_id = 11
LEFT JOIN role r
ON r.id = ur.role_id
AND u.id = 22
AND r.tenant_id = 11
WHERE u.tenant_id = 11
------------------------------------- 


SELECT u.*, r.id AS r_id, r.NAME AS r_name
FROM </span>user<span class="token punctuation"> u
LEFT JOIN user_role ur
ON ur.user_id = u.id
AND ur.tenant_id = 11
LEFT JOIN role r
ON r.id = ur.role_id
AND u.id = 22
AND r.tenant_id = 11
LEFT JOIN menu m
ON m.role_id = r.id
AND m.tenant_id = 11
WHERE u.tenant_id = 11
------------------------------------- 


SELECT a, b
FROM (
SELECT *
FROM table_a
WHERE tenant_id = 11
) temp
WHERE temp.a = 'a';
------------------------------------- 


SELECT temp.
FROM (
SELECT 
FROM </span>user<span class="token punctuation">
WHERE tenant_id = 11
) temp
JOIN user_role ur
ON ur.user_id = temp.id
AND ur.tenant_id = 11
WHERE temp.a = 'a'
AND temp.tenant_id = 11;
------------------------------------- 


SELECT id, name, tenant_id
FROM role
WHERE tenant_id = 1
AND tenant_id = 11

update语句

UPDATE user u
SET ds = ?, u.name = ?, id = 'fdf', ddd = ?
WHERE id = ?
	AND u.tenant_id = 11
------------------------------------- 

UPDATE user u
SET ds = ?, u.name = ?, id = 'fdf', ddd = ?
WHERE id IN (1, 2, 3)
AND u.tenant_id = 11
------------------------------------- 


UPDATE user u
SET ds = ?, u.name = ?, id = 'fdf', ddd = ?
WHERE id IN (
SELECT ur.user_id
FROM user_role ur
WHERE ur.tenant_id = 11
)
AND u.tenant_id = 11
------------------------------------- 


UPDATE user u
SET ds = ?, u.name = ?, id = 'fdf', ddd = ?
WHERE id IN (
SELECT ur.user_id
FROM user_role ur
WHERE ur.id = 1
AND ur.tenant_id = 11
)
AND u.tenant_id = 11
------------------------------------- 


UPDATE </span>user<span class="token punctuation"> u
JOIN user_role ur
ON ur.user_id = u.id
AND u.id = 111
AND ur.tenant_id = 11
SET u.qr_code = '1212'
WHERE u.tenant_id = 11
------------------------------------- 


UPDATE </span>user<span class="token punctuation"> u
JOIN user_role ur
ON ur.user_id = u.id
AND u.id IN (
SELECT urr.user_id
FROM user_role urr
WHERE urr.tenant_id = 11
)
AND ur.tenant_id = 11
SET u.qr_code = '1212'
WHERE u.tenant_id = 11
------------------------------------- 


UPDATE user
SET name = CASE
WHEN id = 1 THEN 'name1'
WHEN id = 2 THEN 'name2'
WHEN id = 3 THEN 'name3'
END
WHERE id IN (11, 22, 33, 3)
AND tenant_id = 11;

insert语句

INSERT INTO `user` (`id`, `username`, `password`, tenant_id)
VALUES (?, ?, ?, 11),
	(?, ?, ?, 11),
	(?, ?, ?, 11)

delete语句

DELETE FROM user
WHERE id = 1
	AND tenant_id = 11
------------------------------------- 

DELETE FROM user
WHERE id IN (
SELECT id
FROM user s
WHERE s.tenant_id = 11
)
AND tenant_id = 11
------------------------------------- 


DELETE FROM system_permission_data sp
LEFT JOIN system_role_data_relation re
ON sp.id = re.data_id
AND re.tenant_id = 11
LEFT JOIN system_role sr
ON sr.id = re.role_id
AND sr.tenant_id = 11
WHERE sr.id = 1
AND sp.tenant_id = 11

评论 (0)
暂无评论 来抢沙发吧~

目录

暂无目录

SpringWonderland

探索 Spring 生态的奇妙世界

2023-06-20 09:43

我的标签

MySQL(31) Linux(20) Java(20) Spring Boot(18) Python(18) Nginx(16) HarmonyOS NEXT(12) Vue(9) 人工智能AI(8) Docker(6) MacOS(5) 多租户(4) POI(4) MyBatis(4) IntelliJ IDEA(4) Hadoop(4) ElementUI(4) 向量数据库(3) Redis(3) Git(3) ESP32(3) Beetl(3) 物联网(2) 小程序(2) Visual Studio Code(2) Spring Cloud(2) Spring AI Alibaba(2) PostgreSQL(2) Node(2) MongoDB(2) JavaScript(2) FastAPI(2) CSS(2) 工具(1) UV(1) Spring(1) Sharding-JDBC(1) Phoenix(1) Oracle(1) Nexus(1) Nacos(1) Maven(1) Markdown(1) Kafka(1) jQuery(1) Hive(1) Hibernate(1) ESP-IDF(1) Electron(1) Django(1) Codex(1) Bootstrap(1) BeetlSQL(1)

随笔分类

物联网 (1)
beetl (3)
beetlSQL (2)
Docker (4)
Git (3)
Hibernate (1)
IntelliJ IDEA (4)
Java (9)
MongoDB (1)
MySQL (4)
Nginx (15)
PostgreSQL (2)
Python (17)
Spring Boot (13)
Visual Studio Code (2)
多租户 (4)
分库分表 (1)
服务器运维 (25)
鸿蒙应用开发 (11)
前端 (5)
人工智能AI (5)
微服务 (2)
我开源的项目 (2)