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.