springboot框架Saas系统构建指南（单数据库分表设计模式）

概述需求场景

在开发过程，需要将一个原来的系统平台改造成一个SaaS系统，用以给多个不同公司的使用。每个公司拥有自己的员工。在各个公司的用户访问系统时，要求只能看到看到自己公司的数据。例如A公司管理员登录系统，只能看到A公司的用户。B公司管理员登录系统，只能看到B公司的用户。基于此我将此系统平台进行了改造。

实现思路：

通过分表，不同企业用户，实现数据隔离，达到A企业用户访问A公司的表，B企业用户用户访问B用户的表的目的。下面自底向上逐个讲解设计过程

数据库表设计

不同企业用户访问不同的表，那么表名应该进行进行有对于公司的特征码。以查询用户表 sys_user 为例

原始表SQL: select name from sys_user
a公司SQL:  select name from a_sys_user （特征码为a）
b公司SQL:  select name from b_sys_user （特征码为b）

用户表设计

有数据库表设计可知，用户在访问数据库时，在原始的SQL语句上进行亿丢丢的小加工，在原始sql的表名前面加上特征码。那么在用户表在设计时，就得附带上这个特征码。用户表用于记录每个公司下面的人员信息，用户表的特征码存放于company_code属性中，方便后续使用后面使用
用户表设计如下(简易版)

CREATE TABLE `sys_user`  (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `dept_id` bigint(20) NULL DEFAULT NULL COMMENT '部门ID',
  `login_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '登录账号',
  `user_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '用户昵称',
  `company_code` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT '' COMMENT '公司编号',
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户信息表' ROW_FORMAT = DYNAMIC;

分表实现方式一（参数传递方式）

a公司用户登录系统后，进行数据库curd时，可以将当前用户的特征码作为参数传入，在mybaties框架下，改写xml文件中的sql。以插入新用户为例，sql如下

我们使用mybaties的$符号获取传递过来的特征码。那么我们只需要在每个sql的表名称前面带上特征码。
此例中通过${companyCode}获得用户特征码
<insert id="insertUserRegister" parameterType="SysUser" useGeneratedKeys="true" keyProperty="userId">
		insert into ${companyCode}_sys_user(
		<if test="userId != null and userId != 0">user_id,</if>
		<if test="deptId != null and deptId != 0">dept_id,</if>
		<if test="loginName != null and loginName != ''">login_name,</if>
		<if test="userName != null and userName != ''">user_name,</if>
	    <if test="companyCode != null">company_code,</if>
		create_time
		)values(
		<if test="userId != null and userId != ''">#{userId},</if>
		<if test="deptId != null and deptId != ''">#{deptId},</if>
		<if test="loginName != null and loginName != ''">#{loginName},</if>
		<if test="userName != null and userName != ''">#{userName},</if>>
		<if test="companyCode != null">#{companyCode},</if>
		sysdate()
		)
	</insert>

分表实现方式二（mybaties全局拦截器）

以下为拦截器代码

import com.cpiot.common.utils.PatternUtils;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Properties;
/**
 * 数据表查询拦截器
 * mybatis插件实现动态表名，可以拦截器新增、编辑、删除、查询等
 */
@Component
@Intercepts({
        @Signature(type = StatementHandler.class, method = "prepare", args = {
                Connection.class, Integer.class})})
public class ReplaceTablePlugin implements Interceptor {

    private static final Logger LOG = LoggerFactory.getLogger(ReplaceTablePlugin.class);

    private final static Map<String, String> TABLE_MAP = new LinkedHashMap<>();

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        StatementHandler statementHandler = realTarget(invocation.getTarget());
        MetaObject metaObject = SystemMetaObject.forObject(statementHandler);
        doTable(statementHandler, metaObject);
        return invocation.proceed();
    }

    private void doTable(StatementHandler handler, MetaObject metaStatementHandler) throws ClassNotFoundException {
        BoundSql boundSql = handler.getBoundSql();
        String originalSql = boundSql.getSql();
        if (originalSql != null && !"".equals(originalSql)) {
            //LOG.info("拦截前的sql:{}", originalSql);
            originalSql=PatternUtils.fromParameter(originalSql);
//            LOG.info("拦截后的sql:{}", originalSql);
            metaStatementHandler.setValue("delegate.boundSql.sql", originalSql);
        }
    }

    private boolean isReplaceTableName(String sql) {
        for (String tableName : TABLE_MAP.keySet()) {
            if (sql.contains(tableName)) {
                return true;
            }
        }
        return false;
    }

    @Override
    public Object plugin(Object target) {
        if (target instanceof StatementHandler) {
            return Plugin.wrap(target, this);
        }
        return target;
    }

    @Override
    public void setProperties(Properties properties) {
    }

    /**
     * Obtain real processing objects, possibly multi-layer agents
     *
     * @param target
     * @param <T>
     * @return
     */
    public static <T> T realTarget(Object target) {
        if (Proxy.isProxyClass(target.getClass())) {
            MetaObject metaObject = SystemMetaObject.forObject(target);
            return realTarget(metaObject.getValue("h.target"));
        }
        return (T) target;
    }
}

拦截器中doTable方法会拦截所有的curd的sql语句，我们只要把这些元素的sql加工一下就可以实现分表了
原始sql为:

select * from user

拦截后处理的sql 为

select *  from a_user

当a公司的用户访问时，获取当前用户的特征码。a公司用户在进行读写操作时，拦截器把原始的sql拦截后进行加工，将表名修改为a公司的表名。这样就实现了各个公司的数据隔离。要使得拦截器生效，需要在配置文件中加入相关配置

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!-- 全局参数 -->
    <settings>
        <!-- 使全局的映射器启用或禁用缓存 -->
        <setting name="cacheEnabled"             value="true"   />
        <!-- 允许JDBC 支持自动生成主键 -->
        <setting name="useGeneratedKeys"         value="true"   />
        <!-- 配置默认的执行器.SIMPLE就是普通执行器;REUSE执行器会重用预处理语句(prepared statements);BATCH执行器将重用语句并执行批量更新 -->
        <setting name="defaultExecutorType"      value="SIMPLE" />
		<!-- 指定 MyBatis 所用日志的具体实现 -->
        <setting name="logImpl"                  value="SLF4J"  />
        <!-- 使用驼峰命名法转换字段 -->
		<!-- <setting name="mapUnderscoreToCamelCase" value="true"/> -->
	</settings>
	<plugins>
        <plugin interceptor="包路径**************.ReplaceTablePlugin"></plugin>
    </plugins>
</configuration>

sql处理方法如下：

import com.cpiot.common.constant.TableName;
import com.cpiot.common.core.domain.entity.SysUser;

import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import static com.cpiot.common.utils.ShiroUtils.getSysUser;

/**
 * @Author Song
 * @Date 2021/2/26 16:55
 * @Version 1.0
 * @Description
 */
public class PatternUtils {
    /**
     * 利用好其预编译功能，可以有效加快正则匹配速度
     */
    private static final String JOIN ="JOIN";
    private static final String join ="join";
    private static final String FROM ="FROM";
    private static final String from ="from";
    private static final String into ="into";
    private static final String INTO ="INTO";
    private static final String update ="update";
    private static final String UPDATE ="UPDATE";
    private static final ConcurrentHashMap<String,String> excludeList=TableName.getExcludeList();
    public static Pattern tableRegex1=Pattern.compile("(JOIN|join)[\\s]+[\\w]");
    public static Pattern tableRegex2=Pattern.compile("(from|FROM)[\\s]+[\\w]");
    public static Pattern tableRegex3=Pattern.compile("(UPDATE|update)[\\s]+[\\w]");
    public static Pattern tableRegex4=Pattern.compile("(into|INTO)[\\s]+[\\w]");
    public static String fromParameter(String content) {
        if(containExcludeTable(content)){
            return content;
        }
        StringBuffer result=new StringBuffer();
        if(content.contains(join)||content.contains(JOIN)){
            result=macherbuffer(content,tableRegex1);
            content=result.toString();
        }
        if(content.contains(from)||content.contains(FROM)){
            result=macherbuffer(content,tableRegex2);
            content=result.toString();
        }
        if(content.contains(update)||content.contains(UPDATE)){
            result=macherbuffer(content,tableRegex3);
            content=result.toString();
        }
        if(content.contains(into)||content.contains(INTO)){
            result=macherbuffer(content,tableRegex4);
            content=result.toString();
        }
        return content;
    }
    public static StringBuffer macherbuffer(String content,Pattern pattern){
        String companyCode="";
        StringBuffer stringBuffer1 = new StringBuffer();
        try {
            //当前用户已经登录，取当前用户特征值
            companyCode=getSysUser().getCompanyCode();
        }catch (Exception e){
            //如果当前用户没登录
            companyCode=TableName.companycode.toString();
            if(companyCode==null){
                companyCode="";
            }
        }
        Matcher matcher=pattern.matcher(content);
        while (matcher.find()) {
            // 匹配区间
            String substring = content.substring(matcher.start(), matcher.end()-1);
            if(!"".equals(companyCode)&&!companyCode.contains("_")){
                //非管理员
                companyCode+="_";
            }
            matcher.appendReplacement(stringBuffer1, matcher.group().replace(substring,substring+companyCode));
        }
        // 最终结果追加到尾部
        matcher.appendTail(stringBuffer1);
        // 最终完成替换后的结果
        return stringBuffer1;
    }
    //是否包含不过滤的表
    public static boolean containExcludeTable(String content){
        for (Map.Entry<String,String> entry:excludeList.entrySet()) {
           if(content.contains(entry.getKey())) {
               return true;
           }
        }
        return false;
    }
    public static void main(String[] args) {
        //测试代码
        String content ="SELECT count(0) FROM sys_user u LEFT JOIN sys_dept d ON u.dept_id = d.dept_id LEFT JOIN sys_user_post up ON u.user_id = up.user_id LEFT JOIN sys_post p ON up.post_id = p.post_id WHERE u.del_flag = '0'";
        //System.out.println(fromParameter(content));
        System.out.println(fromParameter(content));
//        List<String> keyListByContent = getKeyListByContent(content);
//        System.out.println("内容中的动态参数为:");
//        keyListByContent.forEach(System.out::println);
    }
}

简单得说下原理：
1：getSysUser()这个是基于shiro框架的获取当前操作用户的方法，原理是用户登录后存放一个threadlocal来存用户信息,Spring Security也有类似这个方法。
登录系统后，用户的属性信息将存放到shiro中，通过getCompanyCode()获取用户特征值
2：通过正则匹配，将特征值加在数据表名上
3：修改后的sql进行执行
4：excludeList是不需要拦截的表，有些配置数据表是公用的，可以不拦截。

最后再说下前端的设计：
前端设计为公司号（也就是特征值）、账号、密码。再用户登录之前，使用公司号查找对应的表。

此时用户还没有登录系统，还在权限鉴定阶段，所以我在拦截器中放了这么一段代码

在用户在验证账号密码时，此时还没有验证通过，所以当前用户信息还没有存放到threadLocal里里面。在调用getSysUser()是，将无法找到这个用户的特征值，所以定义一个全局变量来存放正在登录的用户的特征值。在用户在前端输入公司号，账号、密码后。公司号传递到到后端赋值给全局变量companycode，拦截器将companycode通过正则匹配将查询的sql附加到sql上。在登录时，会查询用户表user,经过拦截器加工，就成了companycode_user表,这样在用户登录时，就跑到指定的表上去查询账号密码信息进行验证了。而登录的用户，在调用getSysUser().getCompanyCode()后就能获取到特征值。这样就做到了，用户在登录前、登录后，都有特征值。拦截器就能根据这个特征值进行拦截处理对应的sqL。

public static StringBuffer companycode = new StringBuffer();

完结，如果有疑问我再后续进行补充