—— 阿尔伯特·爱因斯坦

程序员の奇妙冒险

多租户JavaMyBatisSpring Boot多租户
2023-06-20 09:43阅读:57评论:0

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

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

views-a5d25dd831.css"> md-250d69367f.min.css">

druid-multi-tenant-starter

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

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

7">完整的YML配置

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

支持

  • Mybatis Plus
  • Mybatis PageHelper 分页插件

31">支持忽略tenantId策略

  • 支持原SQL语句中已有tenantId字段条件跳过
  • 支持忽略指定表名添加tenantId字段条件
  • 支持忽略指定Mapper接口SQL语句添加tenantId字段条件
  • 支持指定表名忽略SQL语句添加tenantId字段条件
  • 支持指定别名忽略SQL语句添加tenantId字段条件

快速开始

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

49">配置

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

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

  • 需要实现ITenantService接口提供获取多租户ID值
/*PROTECTED22_ 演示：提供多租户ID服务接口
 PROTECTED23_ @author wencycai
 _PROTECTED24___PROTECTED25_ 添加@IgnoreTenantIdField注解来忽略设置多租户字段
     PROTECTED26_
FROM user s
WHERE s.name = '333'
	AND s.tenantid = 11
------------------------------------- 
SELECT tenantid
FROM people
WHERE id IN (
		SELECT id
		FROM user s
		WHERE s.tenantid = 11
	)
	AND tenantid = 11
------------------------------------- 

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

SELECT tenantid
FROM people
WHERE id IN (
		SELECT id
		FROM user s
		WHERE s.tenantid = 11
	)
	AND id IN (
		SELECT u.id
		FROM user u
		WHERE u.tenantid = 11
	)
	AND tenantid IN (
		SELECT u.tenantid
		FROM user u
		WHERE u.tenantid = 11
	)
	AND tenantid = 11
------------------------------------- 
SELECT p.tenantid
	, (
		SELECT u.name
		FROM user u
		WHERE u.id = p.userid
			AND u.tenantid = 11
	)
FROM people p
WHERE p.tenantid = 11
------------------------------------- 
SELECT p.tenantid
	, (
		SELECT u.name
		FROM user u
		WHERE u.id = p.userid
			AND u.tenantid = 11
	)
	, (
		SELECT s.name
		FROM user s
		WHERE s.id = p.userid
			AND s.tenantid = 11
	)
FROM people p
WHERE p.tenantid = 11
------------------------------------- 
SELECT p.tenantid
	, (
		SELECT u.name
			, (
				SELECT s.name
				FROM user s
				WHERE s.id = p.userid
					AND s.tenantid = 11
			)
		FROM user u
		WHERE u.id = p.userid
			AND u.tenantid = 11
	)
FROM people p
WHERE p.tenantid = 11
------------------------------------- 
SELECT u._PROTECTED27_
FROM PROTECTED1_ u
	JOIN userrole ur
	ON ur.userid = u.id
		AND u.id = 22
		AND ur.tenantid = 11
WHERE u.tenantid = 11
------------------------------------- 

SELECT u.PROTECTED28_
FROM PROTECTED3_ u
	JOIN userrole ur
	ON ur.userid = u.id
		AND ur.roleid IN (
			SELECT r.id
			FROM role r
			WHERE r.tenantid = 11
		)
		AND ur.tenantid = 11
WHERE u.tenantid = 11
------------------------------------- 

SELECT u.PROTECTED29_
FROM PROTECTED5_ u
	LEFT JOIN userrole ur
	ON ur.userid = u.id
		AND ur.tenantid = 11
WHERE u.tenantid = 11
------------------------------------- 

SELECT u.PROTECTED30_), id
FROM PROTECTED7_
WHERE tenantid = 11
GROUP BY id
HAVING COUNT(_PROTECTED31_
		FROM PROTECTED8_
		WHERE username = ?
			AND tenantid = 11
	) AS d
------------------------------------- 
SELECT EXISTS (
		SELECT tenantid
		FROM people
		WHERE id IN (
				SELECT id
				FROM user s
				WHERE s.tenantid = 11
			)
			AND tenantid = 11
	) AS d
------------------------------------- 

SELECT username
FROM PROTECTED9_
WHERE tenantid = 11
UNION
SELECT username
FROM account
WHERE tenantid = 11
------------------------------------- 

SELECT u.PROTECTED32_, r.id AS rid, r.NAME AS rname
FROM PROTECTED11_ u
	LEFT JOIN userrole ur
	ON ur.userid = u.id
		AND ur.tenantid = 11
	LEFT JOIN role r
	ON r.id = ur.roleid
		AND u.id = 22
		AND r.tenantid = 11
	LEFT JOIN menu m
	ON m.roleid = r.id
		AND m.tenantid = 11
WHERE u.tenantid = 11
------------------------------------- 

SELECT a, b
FROM (
	SELECT PROTECTED33_
FROM (
	SELECT *
	FROM PROTECTED12_
	WHERE tenantid = 11
) temp
	JOIN userrole ur
	ON ur.userid = temp.id
		AND ur.tenantid = 11
WHERE temp.a = 'a'
	AND temp.tenantid = 11;
------------------------------------- 
SELECT id, name, tenantid
FROM role
WHERE tenantid = 1
	AND tenantid = 11

update语句

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

UPDATE user u
SET ds = ?, u.name = ?, id = 'fdf', ddd = ?
WHERE id IN (1, 2, 3)
	AND u.tenantid = 11
------------------------------------- 
UPDATE user u
SET ds = ?, u.name = ?, id = 'fdf', ddd = ?
WHERE id IN (
		SELECT ur.userid
		FROM userrole ur
		WHERE ur.tenantid = 11
	)
	AND u.tenantid = 11
------------------------------------- 
UPDATE user u
SET ds = ?, u.name = ?, id = 'fdf', ddd = ?
WHERE id IN (
		SELECT ur.userid
		FROM userrole ur
		WHERE ur.id = 1
			AND ur.tenantid = 11
	)
	AND u.tenantid = 11
------------------------------------- 
UPDATE _PROTECTED13_ u
	JOIN userrole ur
	ON ur.userid = u.id
		AND u.id = 111
		AND ur.tenantid = 11
SET u.qrcode = '1212'
WHERE u.tenantid = 11
------------------------------------- 
UPDATE _PROTECTED14_ u
	JOIN userrole ur
	ON ur.userid = u.id
		AND u.id IN (
			SELECT urr.userid
			FROM userrole urr
			WHERE urr.tenantid = 11
		)
		AND ur.tenantid = 11
SET u.qrcode = '1212'
WHERE u.tenantid = 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 tenantid = 11;

436">insert语句

INSERT INTO PROTECTED15_PROTECTED19_PROTECTED16_PROTECTED20_PROTECTED17_PROTECTED21_PROTECTED18_, tenantid)
VALUES (?, ?, ?, 11),
	(?, ?, ?, 11),
	(?, ?, ?, 11)

443">delete语句

DELETE FROM user
WHERE id = 1
	AND tenantid = 11
------------------------------------- 
DELETE FROM user
WHERE id IN (
		SELECT id
		FROM user s
		WHERE s.tenantid = 11
	)
	AND tenantid = 11
------------------------------------- 
DELETE FROM systempermissiondata sp
	LEFT JOIN systemroledatarelation re
	ON sp.id = re.dataid
		AND re.tenantid = 11
	LEFT JOIN systemrole sr
	ON sr.id = re.roleid
		AND sr.tenantid = 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(7)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)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)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(4)
微服务(2)
我开源的项目(2)