1, Introduction

MybaitsPlus multi tenant processor is a solution to the problem of multi tenant. The main solution is to use jSqlParser to parse the sql, and then splice the tenant ID to realize the isolation between multiple tenants. The tenant ID will be spliced when deleting, adding, modifying and querying

For example:

SELECT * FROM info

After treatment:

SELECT * FROM info WHERE tenant_id = 'tenant_id'

2, Use

The following official demo is copied, and the official demo address is: https://gitee.com/baomidou/mybatis-plus-samples/tree/master/mybatis-plus-sample-tenant

package com.baomidou.mybatisplus.samples.tenant.config;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import com.baomidou.mybatisplus.autoconfigure.ConfigurationCustomizer;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.handler.TenantLineHandler;
import com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor;

import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;

/**
 * @author miemie
 * @since 2018-08-10
 */
@Configuration
@MapperScan("com.baomidou.mybatisplus.samples.tenant.mapper")
public class MybatisPlusConfig {

    /**
     * The new multi tenant plug-in configuration follows the rules of mybatis. MybatisConfiguration#useDeprecatedExecutor = false should be set to avoid cache problems
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantLineHandler() {
            @Override
            public Expression getTenantId() {
                return new LongValue(1);
            }

            // This is the default method, which returns false by default, indicating that all tables need to spell multi tenant conditions
            @Override
            public boolean ignoreTable(String tableName) {
                return !"user".equalsIgnoreCase(tableName);
            }
        }));
        // If the paging plug-in is used, pay attention to add TenantLineInnerInterceptor first and then paginationinnerinterceptor
        // If paging plug-in is used, MybatisConfiguration#useDeprecatedExecutor = false must be set
//        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return interceptor;
    }

    @Bean
    public ConfigurationCustomizer configurationCustomizer() {
        return configuration -> configuration.setUseDeprecatedExecutor(false);
    }
}

Do you think it's just a key configuration file that you can handle? After using the multi tenant processor, you don't have to splice the tenant ID manually. Next, let's talk about the place to avoid the pit.

3, Pit avoidance

1. Grammar

In the introduction, I told my friends that mybatis plus is implemented by jsqlparser. Mybatis plus uses jsqlparser to parse the syntax and splice the tenant id.

However, jsqlparser parses the sql specification. If there are some special syntax.

For example:

SELECT CAST('1' as SIGNED) 

Friends who often use mysql should be familiar with the fact that this is a string converted into a number, and this jsqlparser will report some errors in sql syntax parsing, so I hope you can use other methods to replace it, such as:

SELECT CONVERT('1', SIGNED)

2. Table name problem

When using multi tenant processor, many tables do not need to judge the tenant id. here, the author's processing method is to put the tables that do not need to judge the tenant id into the configuration file and use the following to judge.

    @Value("${system.tenant.not-tenant-tables:null}")
    private TreeSet<String> notTenantIdTable;

    @Override
    public boolean doTableFilter(String tableName) {
        return notTenantIdTable.contains(tableName.toLowerCase());
    }

There seems to be no problem here, but you need to pay attention to the case of the table name and the escape symbol `

3. insert sql problem

When using the multi tenant processor, the author is in the later stage of the project. At this time, most of the business code has been written, and the inserted sql is obtained and inserted manually. However, the multi tenant processor of mybatis plus has thought of this problem. When the multi tenant processor is enabled, the tenant ID will be automatically inserted. If you insert the tenant ID yourself, and the multi tenant processor also inserts the tenant ID for you, the following sql will appear:

INSERT INTO info(id, tenant_id, tenant_id) VALUES(1, 'tenant_id', 'tenant_id')

At this time, mysql will report errors mercilessly. The solution is actually very simple. Don't insert the tenant ID. of course, you can also use the following methods

1. Inherit TenantSqlParser

/**
 * Multi tenant sql parser extension
 * @author weicong
 * @version 1.0 createTime 2021-2-22 20:05:58
 */
public class TenantSqlParserExt extends TenantSqlParser {

    /**
     * Whether to process the inserted sql
     */
    private boolean isHandlerInsert = true;

    /**
     * Whether to process sql of query
     */
    private boolean isHandlerSelect = true;

    @Override
    public void processInsert(Insert insert) {
        if(isHandlerInsert){
            int index = findTenantIdColumnIndex(insert.getColumns());
            //If there is no tenant ID, add tenant ID information
            if(-1 == index){
                super.processInsert(insert);
            }
        }
    }

    /**
     * Get the location of the tenant ID in the Column collection. If not, return - 1
     * @param columns
     * @return
     */
    public int findTenantIdColumnIndex(List<Column> columns){
        Column column;
        for(int i = 0; i < columns.size(); i++){
            column = columns.get(i);
            if(Global.TENANT_ID_COLUMN.equals(column.getColumnName())){
                return i;
            }
        }
        return -1;
    }

    @Override
    public void processSelectBody(SelectBody selectBody) {
        if(isHandlerSelect){
            super.processSelectBody(selectBody);
        }
    }

    public boolean isHandlerInsert() {
        return isHandlerInsert;
    }

    public void setHandlerInsert(boolean handlerInsert) {
        isHandlerInsert = handlerInsert;
    }

    public boolean isHandlerSelect() {
        return isHandlerSelect;
    }

    public void setHandlerSelect(boolean handlerSelect) {
        isHandlerSelect = handlerSelect;
    }
}

2. Configure TenantSqlParser

/**
     * Initialize multi tenant processor
     * @param paginationInterceptor
     */
    public void initTenantHandler(PaginationInterceptor paginationInterceptor){
        ArrayList<ISqlParser> sqlParsers = new ArrayList<>();
        TenantSqlParser tenantSqlParser = new TenantSqlParserExt();
        tenantSqlParser.setTenantHandler(new TenantHandler() {
            @Override
            public Expression getTenantId(boolean where) {
                return new LongValue(LoginUserUtils.getTenantId());
            }

            @Override
            public String getTenantIdColumn() {
                return Global.TENANT_ID_COLUMN;
            }

            @Override
            public boolean doTableFilter(String tableName) {
                tableName = clearSymbol(tableName);
                boolean is = tenantProperties.getNotTenantIdTable().contains(tableName.toLowerCase());
                return is;
            }
        });
        sqlParsers.add(tenantSqlParser);
        paginationInterceptor.setSqlParserList(sqlParsers);
    }

4, Summary

This article has introduced the use of mybaits plus multi tenant processor and the pit avoidance guide. The author has compiled a complete demo for your reference. If you have any questions, please comment or go to Q group 415777345 to find the author. This article is purely handwritten.

gitee: https://gitee.com/nightowl7/mybatis-plus-tenant-demo