Multi tenancy of mybatis plus -- SAAS

Author: Messi loves cycling
Link: https://www.jianshu.com/p/742f40eb9937
Source: Jianshu
The copyright belongs to the author. For commercial reprint, please contact the author for authorization. For non-commercial reprint, please indicate the source.

1, What is multi tenancy

Multi tenancy technology or multi tenancy technology, referred to as multi tenancy. It is a software architecture technology, which realizes how to share the same system or program components in a multi-user environment (multi-users here are generally oriented to enterprise users), and can ensure the isolation of data among users.

To put it simply: run a single application instance on one server, which provides services for multiple tenants (customers). From the definition, we can understand that multi tenancy is an architecture, which aims to use the same set of programs in a multi-user environment and ensure data isolation among users. Then the focus is very simple and easy to understand. The focus of multi tenant is to realize the isolation of multi-user data under the same set of procedures. SaaS application is based on this implementation.

2, There are three schemes for data isolation

  1. Independent database: in short, a tenant uses a database. This kind of data isolation has the highest level and the best security, but it increases the cost.
  2. Shared database and isolated data schema: multiple tenants use the same database, but each tenant corresponds to a schema (database user).
  3. Shared database and shared data Schema: the same database and Schema are used, but the field of tenant ID is added in the table. This kind of shared data has the highest degree and the lowest isolation level.

Scheme 3 is adopted here, that is, shared database and shared data architecture, because this scheme has the lowest server cost, but increases the development cost.


3, Mybatis plus multi tenant solution

Why MyBatisPlus?
In addition to some tables shared by the system, we need to add and t.tenant in SQL for other tables related to tenants_ id = ? If you don't pay attention to the query conditions, the data will cross the boundary, and the problem of data security is worrying. Fortunately, with the artifact of MybatisPlus, it is very convenient to implement multi tenant SQL parser.

Mybatis plus provides a multi tenant solution, which is implemented based on the paging plug-in (interceptor).


3.1 step 1:

Add and maintain a sys in the application_ Tenant (tenant management table), add tenant id on the data table that needs to be isolated;

3.2 step 2:

Create table:

CREATE TABLE `orders_1`.`tenant`  (
  `id` int(0) NOT NULL AUTO_INCREMENT COMMENT 'Auto increment primary key',
  `expire_date` datetime(0) COMMENT 'Agreement expiration time',
  `amount` decimal(8, 2) COMMENT 'amount of money',
  `tenant_id` int(0) COMMENT 'tenant ID',
  PRIMARY KEY (`id`)
);

Customize the context of the system, store the tenant ID obtained from cookie s, etc., and use it in the subsequent getTenantId().

package com.erbadagang.mybatis.plus.tenant.config;

import org.springframework.stereotype.Component;

import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

/**
 * @description Context help class of the system. The ConcurrentHashMap sets the tenant ID for subsequent MP getTenantId()
 * @ClassName: ApiContext
 * @author: Guo Xiuzhi jbcode@126.com
 * @date: 2020/7/12 21:50
 * @Copyright:
 */
@Component
public class ApiContext {
    private static final String KEY_CURRENT_TENANT_ID = "KEY_CURRENT_TENANT_ID";
    private static final Map<String, Object> mContext = new ConcurrentHashMap<>();

    public void setCurrentTenantId(Long providerId) {
        mContext.put(KEY_CURRENT_TENANT_ID, providerId);
    }

    public Long getCurrentTenantId() {
        return (Long) mContext.get(KEY_CURRENT_TENANT_ID);
    }
}

The core class - MyBatisPlusConfig configures MP multi tenancy through the paging plug-in.

package com.erbadagang.mybatis.plus.tenant.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.LongValue;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.ArrayList;
import java.util.List;

/**
 * @description MyBatisPlus Configuration class, paging plug-in and multi tenant are also configured using paging plug-in.
 * @ClassName: MyBatisPlusConfig
 * @author: Guo Xiuzhi jbcode@126.com
 * @date: 2020/7/12 21:34
 * @Copyright:
 */
@Configuration
@MapperScan("com.erbadagang.mybatis.plus.tenant.mapper")//Configure scanned mapper package
public class MyBatisPlusConfig {

    @Autowired
    private ApiContext apiContext;

    /**
     * Paging plug-in
     *
     * @return
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();

        // establish SQL Parser collection
        List<ISqlParser> sqlParserList = new ArrayList<>();

        // Tenant creation SQL Parser
        TenantSqlParser tenantSqlParser = new TenantSqlParser();

        // Set up tenant processor
        tenantSqlParser.setTenantHandler(new TenantHandler() {

            // Set current tenant ID,You can learn from the actual situation cookie,Or in the cache
            @Override
            public Expression getTenantId(boolean select) {
                // Retrieve the service provider of the current request from the current system context ID,Injected into by parser SQL Yes.
                Long currentProviderId = apiContext.getCurrentTenantId();
                if (null == currentProviderId) {
                    throw new RuntimeException("Get CurrentProviderId error.");
                }
                return new LongValue(currentProviderId);
            }

            @Override
            public String getTenantIdColumn() {
                // Tenants in the corresponding database ID Column name of
                return "tenant_id";
            }

            @Override
            public boolean doTableFilter(String tableName) {
                // Do you want to filter a table
              /*  List<String> tableNameList = Arrays.asList("sys_user");
                if (tableNameList.contains(tableName)){
                    return true;
                }*/
                return false;
            }
        });

        sqlParserList.add(tenantSqlParser);
        paginationInterceptor.setSqlParserList(sqlParserList);

        return paginationInterceptor;
    }

}

4, Testing

After configuration, the MP will automatically add the tenant ID no matter the query, add, modify or delete methods. The test is as follows:

package com.erbadagang.mybatis.plus.tenant;

import com.erbadagang.mybatis.plus.tenant.config.ApiContext;
import com.erbadagang.mybatis.plus.tenant.entity.Tenant;
import com.erbadagang.mybatis.plus.tenant.mapper.TenantMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;

/**
 * @description Multi tenant test case
 * @ClassName: MultiTanentApplicationTests
 * @author: Guo Xiuzhi jbcode@126.com
 * @date: 2020/7/12 22:06
 * @Copyright:
 */
@SpringBootTest
class MultiTanentApplicationTests {

    @Autowired
    private ApiContext apiContext;

    @Autowired
    private TenantMapper tenantMapper;

    @Test
    public void before() {
        // Set the of the current service provider in the context ID
        apiContext.setCurrentTenantId(1L);
    }

    @Test
    public void select() {
        List<Tenant> tenants = tenantMapper.selectList(null);
        tenants.forEach(System.out::println);
    }
}

The output SQL automatically includes WHERE tenant_id = 1:

==>  Preparing: SELECT id, expire_date, amount, tenant_id FROM t_tenant WHERE tenant_id = 1 
==> Parameters: 
<==      Total: 0

5, Specific SQL filtering

If some SQL in the program does not need to add the representation of tenant ID and needs to filter specific SQL, you can do it in the following two ways:

5.1 mode I:

Configure the ISqlParserFilter parser in the configuration paging plug-in. If there are many SQL configurations, it is troublesome and not recommended.

        //Some SQL No need to add tenants ID Need to filter specific sql. If there are many, it is not recommended to configure here.
        /*paginationInterceptor.setSqlParserFilter(new ISqlParserFilter() {
            @Override
            public boolean doFilter(MetaObject metaObject) {
                MappedStatement ms = SqlParserHelper.getMappedStatement(metaObject);
                // Corresponding to the method in Mapper or dao
                if("com.erbadagang.mybatis.plus.tenant.mapper.UserMapper.selectList".equals(ms.getId())){
                    return true;
                }
                return false;
            }
        });*/

5.2 mode 2:

In the form of tenant annotation, it can only act on Mapper's methods at present. For specific SQL filtering methods, you can also add annotations to the methods that userMapper needs to exclude. SqlParser(filter=true) excludes SQL parsing.

package com.erbadagang.mybatis.plus.tenant.mapper;

import com.baomidou.mybatisplus.annotation.SqlParser;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.erbadagang.mybatis.plus.tenant.entity.Tenant;
import org.apache.ibatis.annotations.Select;

/**
 * <p>
 * Mapper Interface
 * </p>
 *
 * @author Guo Xiuzhi jbcode@126.com
 * @since 2020-07-12
 */
public interface TenantMapper extends BaseMapper<Tenant> {
    /**
     * Custom Wrapper, @SqlParser(filter = true) annotation means that there is no additional condition for tenants without SQL parsing.
     *
     * @return
     */
    @SqlParser(filter = true)
    @Select("SELECT count(5) FROM t_tenant ")
    public Integer myCount();
}

test

    @Test
    public void myCount() {
        Integer count = tenantMapper.myCount();
        System.out.println(count);
    }

SQL output

==>  Preparing: SELECT count(5) FROM t_tenant 
==> Parameters: 
<==    Columns: count(5)
<==        Row: 0
<==      Total: 1

Enable SQL parsing cache annotation to take effect. If your MP version is 3.1.1 or above, it does not need to be configured

# Enable SQL parsing cache annotation to take effect if your MP version is in 3.1.1 And above do not need to be configured
mybatis-plus:
  global-config:
    sql-parser-cache: true

This article uses the source code Apache License 2.0 Open source license agreement, available from Gitee code address Download it locally through git clone command or view the source code through browser.

 

Posted by dicky96 on Sun, 08 May 2022 21:29:14 +0300