Mybatis Plus Multi-tenant scheme trampling record

The old project of the company is to transform multi-tenant, so we entered a pit. This article will write about the pit and the solution. I searched online for a long time every time I met the problem, and recorded it to prevent it from being forgotten in the future.

(I). Programme

There are many options on the web, but this article will only cover the last one, namely: add tenant IDS to tables to achieve data isolation

Plan 1: Add the tenant ID. Add the tenant ID manually in every place where mapper is called

Such as:

LambdaQueryWrapper<Entity> lambdaQueryWrapper = new LambdaQueryWrapper<>();
lambdaQueryWrapper.eq(Entity::getTenantId,"tenantId");
entityMapper.selectList(lambdaQueryWrapper);
Copy the code

This method is complex, heavy workload, and easy to leak. There is no

Scheme 2: Use mp official multi-tenant plug-in, code omitted here, go to the official document to query

(ii). Optimization and pit of the official multi-tenant scheme

After adopting the multi-tenant plug-in with official documentation, the preliminary debugging went smoothly. Crud tested itself and thought there was no problem, so the test environment was sent. However, as the test went further, many problems and changes were found, which are listed here:

1. Analyze which tenants need to be added and which do not

(1) Rewriting of the tenant ID The default rewriting method is:

@Override
public Expression getTenantId() {
    return null;
}
Copy the code

Here you need to define how to get your tenant ID

(2) Definition of tenant field

private static final String TENANT_ID = "tenant_id";

@Override
public String getTenantIdColumn() {
    return TENANT_ID;
}
Copy the code

(3) Tenant interception

@Override
public boolean ignoreTable(String tableName) {
    return TenantLineHandler.super.ignoreTable(tableName);
}
Copy the code

Here I use the scheme is table name interception, the code is as follows:

@override public Boolean ignoreTable(String tableName) {/** * If a match is found, the table is considered to require multi-tenant concatenation. However, some tables do not follow the same rules, so they need to specify whether the annotation * @tablename can do this. We haven't tested it yet. We'll talk about it later. */ List<String> list = new ArrayList<>(); list.add("das_standard_operation"); list.add("t_expert"); list.add("t_nominate_dict"); list.add("t_nominate_dict_history"); list.add("t_order"); list.add("t_standard_sort"); list.add("t_task"); list.add("t_task_confirm"); list.add("das_view"); if (list.contains(tableName)) { return false; } EntityTableCache instance = EntityTableCache.getInstance(); If (null = = instance | | null = = instance. GetCacheData (tableName)) {/ / if failed to initialize, not joining together the tenant id return true; } String entityPath = EntityTableCache.getInstance().getCacheData(tableName).toString(); // This method converts the large hump to an underscore and completes the initialization of return! EntityUtils.isHaveAttr(entityPath, COLUMN_TENANTID); }Copy the code

EntityUtils method code (github)

@param entityPath * @param attrName Attribute name * @return Boolean */ public static Boolean isHaveAttr(String entityPath, String attrName) { Optional<String> epOptional = Optional.ofNullable(entityPath); if (! epOptional.isPresent()) { return false; } try { Class<? > aClass = Thread.currentThread().getContextClassLoader().loadClass(epOptional.get()); Field[] fields = aClass.getDeclaredFields(); for (Field field : fields) { if (attrName.equals(field.getName())) { return true; } } return false; } catch (ClassNotFoundException e) {// log.error("SystemSqlParser->isHaveAttr class loading exception :" + LLDB etMessage()); return false; }}Copy the code

2. The jSQLParser package is of an incorrect version with PageHelper

It was found that the version number of JSQLParser is 1.2 and 1.2 and 2.0 (the JSPParser version of MP3.4.1 is 2.0). The debugging found that the update method of 1.2 was as shown in the figure:

Version 1.2 is getTables() and 2.0 is getTable() in the figure

Solution: The parent POM enforces the version

</dependencyManagement> </dependencies> <dependency> <groupId>com.github.jsqlparser</groupId> < artifactId > jsqlparser < / artifactId > < version > 2.0 < / version > < / dependency > < the dependency > < the groupId > com. Making. Pagehelper < / groupId > < artifactId > pagehelper < / artifactId > < version > 5.1.10 < / version > < / dependency > </dependencies> </dependencyManagement>Copy the code

3. SQL parsing fails

1.regexp

If regexp > 0 is used in a SQL statement, the parser will report an error

2. Replace into statements

This statement is also currently unsupported as I find it

The above two problems haven’t solved, thumbing through some of the materials, and asked some colleagues, on the contact is less, the mp of the latest version using 3.4.3.4 com. Used in the making. Jsqlparser: jsqlparser is version 4.2, Currently, the above two are still not supported (if there is a big god solved, please comment on the guidance!).

The solution

Since you fail, that I need not hello, manual splicing, see the following ignore method ⬇️

4. The multi-tenant command does not take effect

We all know that the mapper statement does not need SQL parsing and does not need multi-tenant modification via the @interceptorignore (tenantLine = “on”) annotation. However, in the actual application scenario, there is a special scenario in which the annotation does not take effect.

Such as:

Page<Expert> page = PageHelper.startPage(param.getPageNumber(), param.getPageSize());
List<Expert> experts = expertMapper.queryExpertList(page);
Copy the code

This problem really wasted my time for a long time. Later, I found that @interceptorignore will not work when there is pagination, but I think, it is only MP stuff, you are conflicting, how can it work?

And then I noticed this PageHelper thing, it looked weird. Then I took it out of the page, and sure enough, it worked! The comment is in effect. Perfect. In the meantime, why bother with com.github when MP already offers paginating stuff?

Then the transformation is as follows:

IPage<Expert> page = new com.baomidou.mybatisplus.extension.plugins.pagination.Page<>(param.getPageNumber(),param.getPageSize())
List<Expert> experts = expertMapper.queryExpertList(expertMapping.dtoToEntity(param));
Copy the code