preface

This example comes from a multi-tenant development case in the business development department. If you have used mybatis- Plus multi-tenant plugin, you may know that the plugin’s tenant ID values are derived from the context, such as cookie, session, threadLocal, etc. According to the business, during an insert, they found that they could not get the tenant ID value, so they made a layer operation at their code level, setting the tenant ID at save time. While saving, the Column ‘tenant_id’ specified twice successfully appeared

Source of problem

Prior to mybatis- Plus version 3.4, mybatis- Plus did not filter existing tenant_ID for multi-tenant inserts, causing the issue of Column ‘tenant_id’ Specified twice. The source code for the multi-tenant SQL parser that handles INSERT statements prior to version 3.4 is shown below

  @Override
    public void processInsert(Insert insert) {
        if (tenantHandler.doTableFilter(insert.getTable().getName())) {
            // The filter exits the execution
            return;
        }
        insert.getColumns().add(new Column(tenantHandler.getTenantIdColumn()));
        if(insert.getSelect() ! =null) {
            processPlainSelect((PlainSelect) insert.getSelect().getSelectBody(), true);
        } else if(insert.getItemsList() ! =null) {
            // fixed github pull/295
            ItemsList itemsList = insert.getItemsList();
            if (itemsList instanceof MultiExpressionList) {
                ((MultiExpressionList) itemsList).getExprList().forEach(el -> el.getExpressions().add(tenantHandler.getTenantId(false)));
            } else {
                ((ExpressionList) insert.getItemsList()).getExpressions().add(tenantHandler.getTenantId(false)); }}else {
            throw ExceptionUtils.mpe("Failed to process multiple-table update, please exclude the tableName or statementId"); }}Copy the code

Problem solution

1. Scheme 1: When business code is inserted, the entity does not set the tenant ID value, and the value is set by the multi-tenant plug-in

2. Scheme 2: Upgrade MyBatis – Plus version to 3.4.1 or later

However, the multi-tenant plugin should not be written in the same way as before. Although the previous version is compatible with 3.4.1, it has been marked with @deprecated indicating that the previous version is no longer recommended. Therefore, the latest tenant plugin blocker is adopted. The sample code is shown below

  /** * Set MybatisConfiguration#useDeprecatedExecutor = false to avoid caching problems */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(a) {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantLineHandler() {
            @Override
            public Expression getTenantId(a) {
                return new LongValue(1);
            }

            // This is the default method, which returns false to indicate that all tables need multi-tenant conditions
            @Override
            public boolean ignoreTable(String tableName) {
                return !"user".equalsIgnoreCase(tableName); }}));/ / if the paging plug-ins add TenantLineInnerInterceptor first, then add PaginationInnerInterceptor attention
        MybatisConfiguration#useDeprecatedExecutor = false if the paging plugin is used
// interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return interceptor;
    }

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

TenantLineInnerInterceptor the interceptor in com. Baomidou. Mybatisplus. The extension. Plugins. Inner under this package

3. Scheme 3: If the version before Mybatis -plus3.4.1 is used, you can customize a TenantSqlParser and rewrite processInsert method, its core code is as follows

* /@Override
    public void processInsert(Insert insert) {
        if (getTenantHandler().doTableFilter(insert.getTable().getName())) {
            // The filter exits the execution
            return;
        }
        if (isAleadyExistTenantColumn(insert)) {
            return;
        }
        insert.getColumns().add(new Column(getTenantHandler().getTenantIdColumn()));
        if(insert.getSelect() ! =null) {
            processPlainSelect((PlainSelect) insert.getSelect().getSelectBody(), true);
        } else if(insert.getItemsList() ! =null) {
            // fixed github pull/295
            ItemsList itemsList = insert.getItemsList();
            if (itemsList instanceof MultiExpressionList) {
                ((MultiExpressionList) itemsList).getExprList().forEach(el -> el.getExpressions().add(getTenantHandler().getTenantId()));
            } else{ ((ExpressionList) insert.getItemsList()).getExpressions().add(getTenantHandler().getTenantId()); }}else {
            throw ExceptionUtils.mpe("Failed to process multiple-table update, please exclude the tableName or statementId"); }}/** * Check whether the tenant ID column field * exists@param insert
     * @returnIf it already exists, the */ is bypassed
    private boolean isAleadyExistTenantColumn(Insert insert) {
        List<Column> columns = insert.getColumns();
        if(CollectionUtils.isEmpty(columns)){
            return false;
        }
        String tenantIdColumn = getTenantHandler().getTenantIdColumn();
        return columns.stream().map(Column::getColumnName).anyMatch(tenantId -> tenantId.equals(tenantIdColumn));
    }

Copy the code

conclusion

How to choose the above three schemes? If the project is in the early stage, it is recommended to use solution 1, that is, do not directly set the tenant ID at the business level and let the tenant plug-in handle it in a unified manner. For new projects, myBatis – Plus recommends using the latest version. If the project has already set tenant IDS in several places at the business level and mybatis- Plus version is earlier than 3.4, it is recommended that Scheme 3 directly extend the tenant plug-in function of MyBatis – Plus, and scheme 1 is not recommended to avoid omission

The demo link

Github.com/lyb-geek/sp…