druid-multi-tenant-starter

目标多租戶插件，開箱即用，支援單個租戶以及多個租戶

位址：https://github.com/osinn/druid-multi-tenant-starter

支援

Mybatis Plus
Mybatis PageHelper 分頁插件

支援忽略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.1</version>
</dependency>

配置

mybatis:
  tenant:
    config:
      # 是否使用MyBatis攔截器方式修改sql,需要禁用druidFilterEnable
      enable: true
      # 資料庫方言
      db-type: mysql
      # 是否忽略表按租戶ID過濾,預設所有表都按租戶ID過濾，指定表名稱(區分大小寫全等判斷)
      ignore-table-name:
#        - user
        - user_role
      ignore-match-table-alias
      # 比對判斷指定表别名稱是否忽略表按租戶ID過濾(區分大小寫比對判斷)
        - temp # tempTableAlias
      # 資料庫中租戶ID的列名
      tenant-id-column: tenant_id
      # 是否使用druid過濾器方式修改sql,依賴druid資料庫連接配接池,需要禁用enable=false
      druid-filter-enable: false

如果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<Integer> getTenantIds() {
        // 查詢系統多租戶id,如果有多個傳回多個值即可
        int tenantId = 1;
        return Lists.newArrayList(tenantId);
    }
}

到此整合完成

多租戶忽略Mapper方法

public interface UserMapper {

    /**
     * 添加@IgnoreTenantIdField注解來忽略設定多租戶字段
     */
    @IgnoreTenantIdField
    void deleteTestIgnoreTenantIdById(Long id);
}

# select語句
```sql
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 `user` u
   JOIN user_role ur
   ON ur.user_id = u.id
      AND ur.tenant_id = 11
WHERE u.tenant_id = 11
------------------------------------- 

SELECT u.*
FROM `user` 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 `user` 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 `user` 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 `user` 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 `user` 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 `user` 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 `user`
WHERE tenant_id = 11
GROUP BY id
HAVING COUNT(*) >= 1
------------------------------------- 

SELECT EXISTS (
      SELECT *
      FROM `user`
      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 `user`
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 `user` 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 `user` 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 `user`
   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語句
```sql
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 `user` 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 `user` 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語句
```sql
INSERT INTO `user` (`id`, `username`, `password`, tenant_id)
VALUES (?, ?, ?, 11),
   (?, ?, ?, 11),
   (?, ?, ?, 11)
```
# delete語句
```sql
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
```

開箱即用多租戶插件druid-multi-tenant-starter釋出1.4.1版本

druid-multi-tenant-starter

支援

支援忽略tenantId政策

快速開始

配置

實作提供擷取多租戶值接口

多租戶忽略Mapper方法

繼續閱讀

durid web監控詳解

搭建通用的SSM架構 (二) 使用druid管理資料源，以及使用Mybatis操作資料庫

SpringBoot--自定義Starter的幾種方式

SpringBoot：自定義Starter

Springboot 整合druid+mybatis+jta分布式事務+多資料源aop注解動态切換（一篇到位）

報錯： com.alibaba.druid.pool.DruidDataSource : abandon connection, owner thread

Springboot整合Mybatis增删查改、連接配接MYSQL資料庫及配置druid連接配接池

spring boot 和 mybatis 整合

Spring Boot + Spring Cloud 內建 Druid 資料源Spring Boot + Spring Cloud 內建 Druid 資料源

SpringBoot 之 Spring Boot 內建 Druid

Druid 之 java.sql.SQLException: sql injection violation

Druid報錯：com.alibaba.druid.parser.ParserException:sysntax error,except RPAREN,actual EXCEPT EXCEPT

解決: com.alibaba.druid.filter.FilterManager : load filter error, filter not found : sl4j問題描述問題分析問題解決

openstack 指令行管理三（keystone） - tenant 管理 (備忘)

射陽高手廣告語：“輔助商家超8000家”。那你可以第一個帶我來上，你能上；你随你打懂不懂？阿姨阿姨阿姨阿姨阿姨阿姨阿姨阿

SpringBoot內建Mybatis,Redis，druid實作多資料源開發