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

前言

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

功能描述

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

代码实现

依赖引入

com.baomidou

mybatis-plus-boot-starter

3.3.2

com.github.jsqlparser

jsqlparser

3.1

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 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 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;

/**

*

* 部门信息 Mapper 接口

*

*

* @author qiangesoft

* @since 2024-04-11

*/

public interface SysDeptMapper extends BaseMapper {

/**

* 不进行租户过滤

*

* @param parentId

* @return

*/

@SqlParser(filter = true)

List listByParam(@Param("parentId") Long parentId);

}

select *

from sys_dept

where parent_id = #{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 java.util.List;

/**

*

* 用户信息 前端控制器

*

*

* @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 list() {

LambdaQueryWrapper queryWrapper = new LambdaQueryWrapper<>();

queryWrapper.orderByDesc(SysUser::getCreateTime);

List sysUserList = sysUserService.list(queryWrapper);

log.info(JSON.toJSONString(sysUserList));

List 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;

/**

*

* 部门信息 服务类

*

*

* @author qiangesoft

* @since 2024-04-11

*/

public interface ISysDeptService extends IService {

/**

* 查询部门

*

* @param parentId

* @return

*/

List 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;

/**

*

* 用户信息 前端控制器

*

*

* @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 sync() throws ExecutionException, InterruptedException {

LambdaQueryWrapper queryWrapper = new LambdaQueryWrapper<>();

queryWrapper.orderByDesc(SysUser::getCreateTime);

List sysUserList = sysUserService.list(queryWrapper);

log.info(JSON.toJSONString(sysUserList));

// 异步查询部门

ServletRequestAttributes sra = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();

Callable getUser = () -> {

RequestContextHolder.setRequestAttributes(sra, true);

List sysDeptList = sysDeptService.list();

log.info(JSON.toJSONString(sysDeptList));

return sysUserList;

};

FutureTask> future = new FutureTask<>(getUser);

new Thread(future).start();

return future.get();

}

}

请求调用

请求头添加tenantId

推荐链接