MyBatis-Plus 3.4.0升级后分页查询增加额外嵌套

当前使用版本(3.4.0)

该问题是如何引起的？(确定最新版也有问题再提!!!)

3.3.2升级到3.4.0后出现问题。

重现步骤(如果有就写完整)

---

1.升级pom对应的依赖

     <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.2</version>
        </dependency>

升级变更为

<dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.4.0</version>
  </dependency>

---

2.按照官方的3.4.0新插件引入方式原MybatisPlusConfig package com.zl.zcloud.data.config;

import com.baomidou.mybatisplus.core.parser.ISqlParser; import com.baomidou.mybatisplus.core.parser.ISqlParserFilter; import com.baomidou.mybatisplus.core.parser.SqlParserHelper; import com.baomidou.mybatisplus.core.toolkit.PluginUtils; import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor; import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize; import com.baomidou.mybatisplus.extension.plugins.tenant.TenantHandler; import com.baomidou.mybatisplus.extension.plugins.tenant.TenantSqlParser;

import com.zl.zcloud.data.config.datascope.DataScopeInterceptor; import com.zl.zcloud.data.config.holder.TenantContextHolder; import net.sf.jsqlparser.expression.Expression; import net.sf.jsqlparser.expression.LongValue; import net.sf.jsqlparser.expression.StringValue; import net.sf.jsqlparser.expression.operators.relational.ExpressionList; import net.sf.jsqlparser.expression.operators.relational.InExpression; import net.sf.jsqlparser.schema.Column; import org.apache.ibatis.mapping.MappedStatement; import org.apache.ibatis.reflection.MetaObject; import org.mybatis.spring.annotation.MapperScan; import org.springframework.boot.autoconfigure.AutoConfigureAfter; import org.springframework.boot.autoconfigure.condition.ConditionalOnBean; import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean; import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource; import java.util.ArrayList; import java.util.List;

@SuppressWarnings("ALL") @Configuration @ConditionalOnBean(DataSource.class) @AutoConfigureAfter(DataSourceAutoConfiguration.class) @MapperScan("com.zl.**.mapper") public class MybatisPlusConfig {

/**
 * 分页插件
 */
@Bean
public PaginationInterceptor paginationInterceptor() {
    PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
    // paginationInterceptor.setLimit(你的最大单页限制数量，默认 500 条，小于 0 如 -1 不受限制);
    paginationInterceptor.setDialectType("mysql");

    /*
     * 【测试多租户】 SQL 解析处理拦截器<br>
     * 这里固定写成住户 1 实际情况你可以从cookie读取，因此数据看不到 【 麻花藤 】 这条记录（ 注意观察 SQL ）<br>
     */
    if(TenantContextHolder.getTenantId()!=null&&!("").equals(TenantContextHolder.getTenantId())){
        List<ISqlParser> sqlParserList = new ArrayList<>();
        TenantSqlParser tenantSqlParser = new MyTenantParser();
        tenantSqlParser.setTenantHandler(new TenantHandler() {

            /**
             * 2019-8-1
             *
             * https://gitee.com/baomidou/mybatis-plus/issues/IZZ3M
             *
             * tenant_id in (1,2)
             *
             * @return
             */
            @Override
            public Expression getTenantId(boolean where) {
                final boolean multipleTenantIds = true;
                if (where &&multipleTenantIds) {
                    return multipleTenantIdCondition();
                } else {
                    return singleTenantIdCondition();
                }
            }

            private Expression singleTenantIdCondition() {
                return new StringValue(TenantContextHolder.getTenantId()) ;
                //   return new LongValue(1);//ID自己想办法获取到
            }

            private Expression multipleTenantIdCondition() {
                final InExpression inExpression = new InExpression();
                inExpression.setLeftExpression(new Column(getTenantIdColumn()));
                final ExpressionList itemsList = new ExpressionList();
                final List<Expression> inValues = new ArrayList<>(2);
                inValues.add(new LongValue(1));//ID自己想办法获取到
                inValues.add(new LongValue(2));
                itemsList.setExpressions(inValues);
                inExpression.setRightItemsList(itemsList);
                return inExpression;
            }

            @Override
            public String getTenantIdColumn() {
                return "tenantId";
            }

            @Override
            public boolean doTableFilter(String tableName) {
                // 这里可以判断是否过滤表
                if ("department_user".equals(tableName)) {
                    return true;
                }else if("user_role".equals(tableName)){
                    return true;
                }else if("tpackage".equals(tableName)){
                    return true;
                }else if("tspecparam".equals(tableName)){
                    return true;
                }else if("turnover".equals(tableName)){
                    return true;
                }else if("tlot".equals(tableName)){
                    return true;
                }else if("tlotdetail".equals(tableName)){
                    return true;
                }else if("tstorer".equals(tableName)){
                    return true;
                }else if("tstorageLocation".equals(tableName)){
                    return true;
                }else if("tstorageArea".equals(tableName)){
                    return true;
                }else if("terminal".equals(tableName)){
                    return true;
                }else if("tbom".equals(tableName)){
                    return true;
                }else if("tbomdetail".equals(tableName)){
                    return true;
                }else if("role_menu".equals(tableName)){
                    return true;
                }

                return false;
            }

        });

        sqlParserList.add(tenantSqlParser);
        paginationInterceptor.setSqlParserList(sqlParserList);
        paginationInterceptor.setSqlParserFilter(new ISqlParserFilter() {
            @Override
            public boolean doFilter(MetaObject metaObject) {
                MappedStatement ms = SqlParserHelper.getMappedStatement(metaObject);
                // 过滤自定义查询此时无租户信息约束【 麻花藤 】出现
                if ("com.zl.wms.base.mapper.OrderMapper.selectOrderByCodeLike".equals(ms.getId())) {
                    return true;
                }else if ("com.zl.wms.base.mapper.OrderMapper.selectOrderByCode".equals(ms.getId())) {
                    return true;
                }
                return false;
            }
        });
    }

    return paginationInterceptor;
}
/**
 * 数据权限插件
 *
 * @param dataSource 数据源
 * @return DataScopeInterceptor
 */
@Bean
@ConditionalOnMissingBean
public DataScopeInterceptor dataScopeInterceptor(DataSource dataSource) {
    return new DataScopeInterceptor(dataSource);
}

}

变更为新的3.4.0MybatisPlusConfig package com.zl.zcloud.data.config;

import com.baomidou.mybatisplus.annotation.DbType; import com.baomidou.mybatisplus.autoconfigure.ConfigurationCustomizer; import com.baomidou.mybatisplus.core.parser.ISqlParser; import com.baomidou.mybatisplus.core.parser.ISqlParserFilter; import com.baomidou.mybatisplus.core.parser.SqlParserHelper; import com.baomidou.mybatisplus.core.toolkit.PluginUtils; import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor; import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor; import com.baomidou.mybatisplus.extension.plugins.handler.TenantLineHandler; import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor; import com.baomidou.mybatisplus.extension.plugins.inner.TenantLineInnerInterceptor; import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize; import com.baomidou.mybatisplus.extension.plugins.tenant.TenantHandler; //import com.baomidou.mybatisplus.extension.plugins.tenant.TenantSqlParser;

import com.zl.zcloud.data.config.datascope.DataScopeInterceptor; import com.zl.zcloud.data.config.holder.TenantContextHolder; import net.sf.jsqlparser.expression.Expression; import net.sf.jsqlparser.expression.LongValue; import net.sf.jsqlparser.expression.StringValue; import net.sf.jsqlparser.expression.operators.relational.ExpressionList; import net.sf.jsqlparser.expression.operators.relational.InExpression; import net.sf.jsqlparser.schema.Column; import org.apache.ibatis.mapping.MappedStatement; import org.apache.ibatis.reflection.MetaObject; import org.mybatis.spring.annotation.MapperScan; import org.springframework.boot.autoconfigure.AutoConfigureAfter; import org.springframework.boot.autoconfigure.condition.ConditionalOnBean; import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean; import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource; import java.util.ArrayList; import java.util.List;

@SuppressWarnings("ALL") @Configuration @ConditionalOnBean(DataSource.class) @AutoConfigureAfter(DataSourceAutoConfiguration.class) @MapperScan("com.zl..mapper") public class MybatisPlusConfig { / * 新的分页插件,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存出现问题(该属性会在旧插件移除后一同移除) */ @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); interceptor.addInnerInterceptor(new TenantLineInnerInterceptor(new TenantLineHandler() { @Override public Expression getTenantId() { return new LongValue(1); }

        // 这是 default 方法,默认返回 false 表示所有表都需要拼多租户条件
        @Override
        public boolean ignoreTable(String tableName) {
            // 这里可以判断是否过滤表
            if (!"department_user".equalsIgnoreCase(tableName)) {
                return true;
            }else if(!"user_role".equalsIgnoreCase(tableName)){
                return true;
            }else if(!"tspecparam".equalsIgnoreCase(tableName)){
                return true;
            }else if(!"turnover".equalsIgnoreCase(tableName)){
                return true;
            }else if(!"tstorer".equalsIgnoreCase(tableName)){
                return true;
            }else if(!"terminal".equalsIgnoreCase(tableName)){
                return true;
            }else if(!"role_menu".equalsIgnoreCase(tableName)){
                return true;
            }else if(!"user".equalsIgnoreCase(tableName)){
                return true;
            }

            return false;
        }
    }));
    // 如果用了分页插件注意先 add TenantLineInnerInterceptor 再 add PaginationInnerInterceptor
    // 用了分页插件必须设置 MybatisConfiguration#useDeprecatedExecutor = false

// interceptor.addInnerInterceptor(new PaginationInnerInterceptor());

    interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
    return interceptor;
}

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

/**
 * 数据权限插件
 *
 * @param dataSource 数据源
 * @return DataScopeInterceptor
 */
@Bean
@ConditionalOnMissingBean
public DataScopeInterceptor dataScopeInterceptor(DataSource dataSource) {
    return new DataScopeInterceptor(dataSource);
}

}

报错信息

2020-10-15 10:18:38.956 DEBUG 11612 --- [nio-6666-exec-7] c.z.z.u.m.U.selectUsers_mpCount          : ==> Parameters: 
2020-10-15 10:18:38.971 ERROR 11612 --- [nio-6666-exec-7] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Unknown column 'temp_data_scope.deptId' in 'where clause'
 The error may exist in file [C:\Users\Administrator\Downloads\zcloud\zcloud-user-server\target\classes\mapper\UserMapper.xml]
The error may involve com.zl.zcloud.userserver.mapper.UserMapper.selectUsers-Inline
The error occurred while setting parameters
 SQL: select * from (SELECT COUNT(1) FROM (SELECT GROUP_CONCAT(DISTINCT dept.name) AS deptNames, GROUP_CONCAT(DISTINCT role.name) AS roleNames, u.id AS userid, u.name AS name, u.mobile AS mobile, u.englishName AS englishName, u.position AS position, u.email AS email, u.avatar AS avatar, u.telephone AS telephone, u.enable AS enable, u.status AS status, u.isleader AS isleader, GROUP_CONCAT(du.departmentId) AS deptId FROM user u LEFT JOIN department_user du ON u.id = du.userId LEFT JOIN department dept ON dept.id = du.departmentId LEFT JOIN user_role ur ON u.id = ur.userId LEFT JOIN role role ON role.id = ur.roleId GROUP BY u.id) TOTAL) temp_data_scope  where temp_data_scope.deptId    REGEXP '(^|,)(2)(,|$)'
 Cause: java.sql.SQLSyntaxErrorException: Unknown column 'temp_data_scope.deptId' in 'where clause'
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Unknown column 'temp_data_scope.deptId' in 'where clause'] with root cause

---

原3.3.2对应的sql语句 SELECT COUNT(1) FROM (SELECT GROUP_CONCAT(DISTINCT dept.name) AS deptNames, GROUP_CONCAT(DISTINCT role.name) AS roleNames, u.id AS userid, u.name AS name, u.mobile AS mobile, u.englishName AS englishName, u.position AS position, u.email AS email, u.avatar AS avatar, u.telephone AS telephone, u.enable AS enable, u.status AS status, u.isleader AS isleader, GROUP_CONCAT(du.departmentId) AS deptId FROM user u LEFT JOIN department_user du ON u.id = du.userId LEFT JOIN department dept ON dept.id = du.departmentId LEFT JOIN user_role ur ON u.id = ur.userId LEFT JOIN role role ON role.id = ur.roleId GROUP BY u.id) temp_data_scope WHERE temp_data_scope.deptId REGEXP '(^|,)(2)(,|$)'

3.4.0对应的sql语句

SELECT 
    *
FROM
    (SELECT 
        COUNT(1)
    FROM
        (SELECT 
        GROUP_CONCAT(DISTINCT dept.name) AS deptNames,
            GROUP_CONCAT(DISTINCT role.name) AS roleNames,
            u.id AS userid,
            u.name AS name,
            u.mobile AS mobile,
            u.englishName AS englishName,
            u.position AS position,
            u.email AS email,
            u.avatar AS avatar,
            u.telephone AS telephone,
            u.enable AS enable,
            u.status AS status,
            u.isleader AS isleader,
            GROUP_CONCAT(du.departmentId) AS deptId
    FROM
        user u
    LEFT JOIN department_user du ON u.id = du.userId
    LEFT JOIN department dept ON dept.id = du.departmentId
    LEFT JOIN user_role ur ON u.id = ur.userId
    LEFT JOIN role role ON role.id = ur.roleId
    GROUP BY u.id) TOTAL) temp_data_scope
WHERE
    temp_data_scope.deptId REGEXP '(^|,)(2)(,|$)'

---

多了一层嵌套查询。请帮忙看一下谢谢~~

Comment From: miemieYaho

你这 3.4.0对应的sql语句 既不是count语句也不是分页语句,你怎么得出是我们分页插件的问题的?

Comment From: 285773395

      使用PaginationInterceptor方式没有问题了，使用MybatisPlusInterceptor3.4.0的方式还有问题，应该是我对相关的方法不熟悉导致，我在研究一下，该issue暂时先关闭了，谢谢

Comment From: 285773395

之前使用较早版本PaginationInterceptor和目前的使用方式上有些出入，按照官方文档更改了一下，目前没有问题了