1, Introduction

Let's explain what multi tenancy is and what scenarios use multi tenancy.

Multi tenancy is a software architecture technology. In a multi-user environment, there is a common system, and attention should be paid to the isolation between data.

Take a practical example: Xiaobian has developed a set of H5 program, which is applied to the APP of different hospitals. When hospital patients download the hospital APP and enter the corresponding H5 page, the APP will transfer user related data to Xiaobian. The hospital identification (tenant ID) shall be carried during transmission, so that the small staff can isolate the data.

When different tenants use the same set of programs, a case of data isolation needs to be considered here.

There are three schemes for data isolation:

1. Independent database: simply speaking, a tenant uses a database. This kind of data isolation level is the highest, the security is the best, but the cost is increased.

2. Shared database and isolated data schema: multiple tenants use the same data schema, 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 shared data has the highest degree and the lowest isolation level.
2, Concrete implementation

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.

Therefore, MP provides a multi tenant solution. The implementation method is based on the paging plug-in. The specific implementation code is as follows:

@Configuration
public class MyBatisPlusConfig {
 
 
    /**
     * Paging plug-in
     *
     * @return
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
 
        // Create SQL parser collection
        List<ISqlParser> sqlParserList = new ArrayList<>();
 
        // Create tenant SQL parser
        TenantSqlParser tenantSqlParser = new TenantSqlParser();
 
        // Set tenant processor
        tenantSqlParser.setTenantHandler(new TenantHandler() {
            @Override
            public Expression getTenantId() {
                // Set the current tenant ID. in fact, you can take it from the cookie or cache
                return new StringValue("jiannan");
            }
 
            @Override
            public String getTenantIdColumn() {
                // Column name corresponding to database tenant ID
                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;
    }
 
}

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

@Test
public void select(){
    List<User> users = userMapper.selectList(Wrappers.<User>lambdaQuery().eq(User::getAge, 18));
    users.forEach(System.out::println);
}

DEBUG==>  Preparing: SELECT id, login_name, name, password, email, salt, sex, age, phone, user_type, status, organization_id, create_time, update_time, version, tenant_id FROM sys_user WHERE sys_user.tenant_id = 'jiannan' AND is_delete = '0' AND age = ? 
DEBUG==> Parameters: 18(Integer)
DEBUG<==      Total: 0

3, 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:

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

paginationInterceptor.setSqlParserFilter(new ISqlParserFilter() {
    @Override
    public boolean doFilter(MetaObject metaObject) {
        MappedStatement ms = SqlParserHelper.getMappedStatement(metaObject);
        // Corresponding methods in Mapper and dao
        if("com.example.demo.mapper.UserMapper.selectList".equals(ms.getId())){
            return true;
        }
        return false;
    }
});

Method 2: in the form of tenant annotation, it can only act on Mapper's method at present.

public interface UserMapper extends BaseMapper<User> {
 
 
    /**
     * Custom Wrapper modification
     *
     * @param userWrapper Conditional constructor
     * @param user        Modified object parameters
     * @return
     */
    @SqlParser(filter = true)
    int updateByMyWrapper(@Param(Constants.WRAPPER) Wrapper<User> userWrapper, @Param("user") User user);
 
}

# 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
mybatis-plus:
  global-config:
    sql-parser-cache: true