SpringBoot+MyBatis-Plus+jsqlparser实现多租户功能

前言

多租户技术（multi-tenancy technology）是一种软件架构技术，它允许在单个系统实例上为多个用户或组织提供服务，同时确保这些用户之间数据的隔离性。在多租户架构中，每个租户（可以是个人用户、企业、组织等）共享相同的应用程序实例、硬件资源和基础设施，但数据和配置是相互隔离的，每个租户只能访问自己的数据和配置，互不干扰。

功能描述

• 基于字段tenant_id数据隔离租户
• 排除特殊表
• 排除特殊查询
• 异步支持

代码实现

依赖引入

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.3.2</version>
</dependency>

<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>3.1</version>
</dependency>

yml配置

# mybatis-plus配置
mybatis-plus:
  # 启动检查MyBatis配置文件
  check-config-location: false
  # MyBatis配置文件位置
  config-location:
  # MyBaits别名包扫描路径
  type-aliases-package: com.qiangesoft.tenantid.entity
  # Mapper所对应的XML文件位置 默认【classpath*:/mapper/**/*.xml】
  mapper-locations: classpath*:/mapper/*Mapper.xml
  # TypeHandler扫描路径
  type-handlers-package:
  configuration:
    # 日志打印
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    # 是否开启自动驼峰命名规则
    map-underscore-to-camel-case: true
    # 开启Mybatis二级缓存，默认为true
    cache-enabled: true
  global-config:
    # 控制台mybatis-plus的logo
    banner: true
    db-config:
      # 全局默认主键类型
      id-type: auto
      # 逻辑删除配置
      logic-delete-field: deleted
      logic-delete-value: 1
      logic-not-delete-value: 0

配置类

package com.qiangesoft.tenantid.config;

import com.baomidou.mybatisplus.core.parser.ISqlParser;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.tenant.TenantHandler;
import com.baomidou.mybatisplus.extension.plugins.tenant.TenantSqlParser;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.StringValue;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpServletRequest;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * mybatis-plus配置
 *
 * @author qiangesoft
 * @date 2024-04-11
 */
@Configuration
public class MybatisPlusConfig {

    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();

        List<ISqlParser> sqlParserList = new ArrayList<>();
        TenantSqlParser tenantSqlParser = new TenantSqlParser();
        tenantSqlParser.setTenantHandler(new TenantHandler() {
            @Override
            public Expression getTenantId(boolean select) {
                ServletRequestAttributes attributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
                HttpServletRequest request = attributes.getRequest();
                String tenantId = request.getHeader("tenantId");
                return new StringValue(tenantId);
            }

            @Override
            public String getTenantIdColumn() {
                return "tenant_id";
            }

            /**
             * 整张表租户过滤排除
             * @param tableName
             * @return
             */
            @Override
            public boolean doTableFilter(String tableName) {
                List<String> ignoreTables = Arrays.asList("sys_demo");
                return ignoreTables.stream().anyMatch(e -> e.equalsIgnoreCase(tableName));
            }
        });
        sqlParserList.add(tenantSqlParser);
        paginationInterceptor.setSqlParserList(sqlParserList);
        return paginationInterceptor;
    }

}

排除特殊查询

• @SqlParser(filter = true)只能作用于mapper层

package com.qiangesoft.tenantid.mapper;

import com.baomidou.mybatisplus.annotation.SqlParser;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.qiangesoft.tenantid.entity.SysDept;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * <p>
 * 部门信息 Mapper 接口
 * </p>
 *
 * @author qiangesoft
 * @since 2024-04-11
 */
public interface SysDeptMapper extends BaseMapper<SysDept> {

    /**
     * 不进行租户过滤
     *
     * @param parentId
     * @return
     */
    @SqlParser(filter = true)
    List<SysDept> listByParam(@Param("parentId") Long parentId);

}

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qiangesoft.tenantid.mapper.SysDeptMapper">

    <select id="listByParam" resultType="sysDept">
        select *
        from sys_dept
        where parent_id = #{parentId}
    </select>

</mapper>

package com.qiangesoft.tenantid.controller;

import com.alibaba.fastjson2.JSON;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.qiangesoft.tenantid.entity.SysDept;
import com.qiangesoft.tenantid.entity.SysUser;
import com.qiangesoft.tenantid.service.ISysDeptService;
import com.qiangesoft.tenantid.service.ISysUserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * <p>
 * 用户信息 前端控制器
 * </p>
 *
 * @author qiangesoft
 * @since 2024-04-11
 */
@Slf4j
@RestController
@RequestMapping("/sys-user")
public class SysUserController {

    @Autowired
    private ISysUserService sysUserService;
    @Autowired
    private ISysDeptService sysDeptService;

    @GetMapping("/list")
    public List<SysUser> list() {
        LambdaQueryWrapper<SysUser> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.orderByDesc(SysUser::getCreateTime);
        List<SysUser> sysUserList = sysUserService.list(queryWrapper);
        log.info(JSON.toJSONString(sysUserList));

        List<SysDept> sysDeptList = sysDeptService.listByParam(1L);
        log.info(JSON.toJSONString(sysDeptList));

        return sysUserList;
    }

}

package com.qiangesoft.tenantid.service;

import com.baomidou.mybatisplus.extension.service.IService;
import com.qiangesoft.tenantid.entity.SysDept;

import java.util.List;

/**
 * <p>
 * 部门信息 服务类
 * </p>
 *
 * @author qiangesoft
 * @since 2024-04-11
 */
public interface ISysDeptService extends IService<SysDept> {

    /**
     * 查询部门
     *
     * @param parentId
     * @return
     */
    List<SysDept> listByParam(Long parentId);

}

异步支持

package com.qiangesoft.tenantid.controller;

import com.alibaba.fastjson2.JSON;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.qiangesoft.tenantid.entity.SysDept;
import com.qiangesoft.tenantid.entity.SysUser;
import com.qiangesoft.tenantid.service.ISysDeptService;
import com.qiangesoft.tenantid.service.ISysUserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import java.util.List;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.FutureTask;

/**
 * <p>
 * 用户信息 前端控制器
 * </p>
 *
 * @author qiangesoft
 * @since 2024-04-11
 */
@Slf4j
@RestController
@RequestMapping("/sys-user")
public class SysUserController {

    @Autowired
    private ISysUserService sysUserService;
    @Autowired
    private ISysDeptService sysDeptService;

    @GetMapping("/sync")
    public List<SysDept> sync() throws ExecutionException, InterruptedException {
        LambdaQueryWrapper<SysUser> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.orderByDesc(SysUser::getCreateTime);
        List<SysUser> sysUserList = sysUserService.list(queryWrapper);
        log.info(JSON.toJSONString(sysUserList));

        // 异步查询部门
        ServletRequestAttributes sra = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
        Callable getUser = () -> {
            RequestContextHolder.setRequestAttributes(sra, true);
            List<SysDept> sysDeptList = sysDeptService.list();
            log.info(JSON.toJSONString(sysDeptList));
            return sysUserList;
        };
        FutureTask<List<SysDept>> future = new FutureTask<>(getUser);
        new Thread(future).start();
        return future.get();
    }

}

请求调用

• 请求头添加tenantId