MyBatis-Plus分页控件使用及使用过程发现的一个坑

最近维护一个旧项目的时候，出现了一个BUG，经排查后发现是Mybatis-plus分页控件使用的时候需要注意的一个问题，故在本地使用MybatisPlus模拟出现了一下这个问题。

首先，先说一下MyBatis-Plus的使用：

1）引入所需的包：

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.10.1</version>
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-extension</artifactId>
    <version>3.5.10.1</version> <!-- 确保版本号正确 -->
</dependency>
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-jsqlparser</artifactId>
    <version>3.5.10.1</version> <!-- 确保版本号正确 -->
</dependency>

2）Mapper需要继承BaseMapper

@Mapper
public interface ClassifyMapper extends BaseMapper<Classify> {
    List<Classify> testMybatisPlus(Page<?> page, @Param("artist") String artist);
}

3）编写对应的Mapper文件（下面的左连接是为了模拟项目实际问题编写的，artist是music表的字段）：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zguiz.musicplayer.mapper.ClassifyMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.zguiz.musicplayer.bean.Classify">
       <id column="id" property="id" />
       <result column="name" property="name" />
       <result column="iconurl" property="iconurl" />
    </resultMap>

    <select id="testMybatisPlus" resultMap="BaseResultMap">
       select * from musclassify
       left join relmusicclass on (musclassify.id = relmusicclass.classid)
       left join music on (relmusicclass.musicid = music.id)
       where artist = #{artist}
    </select>
</mapper>

4）让Spring容器托管MybatisPlusInterceptor：

@Configuration
@MapperScan("com.zguiz.musicplayer.mapper")
public class PageConfig {
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor(){
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return interceptor;
    }
}

5）配置Mapper路径：

mybatis-plus:
  mapper-locations: classpath*:com/zguiz/musicplayer/mapper/*.xml

6）调用方法：

@Override
public void testMybatisPlus() {
    Page<Classify> page = new Page<>(1,10);
    classifyMapper.testMybatisPlus(page,"张敬轩");
}

调用后会发现出现了SQL异常：

### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Unknown column 'artist' in 'where clause'
### The error may exist in file [D:\WorkSpace\IdeaProject\MusicPlayer\target\classes\com\zguiz\musicplayer\mapper\ClassifyMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT COUNT(*) AS total FROM musclassify WHERE artist = ?
### Cause: java.sql.SQLSyntaxErrorException: Unknown column 'artist' in 'where clause'
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Unknown column 'artist' in 'where clause'] with root cause

java.sql.SQLSyntaxErrorException: Unknown column 'artist' in 'where clause'
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.20.jar:8.0.20]
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.20.jar:8.0.20]
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.20.jar:8.0.20]
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.20.jar:8.0.20]
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370) ~[mysql-connector-java-8.0.20.jar:8.0.20]
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-3.4.5.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-3.4.5.jar:na]
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:65) ~[mybatis-3.5.19.jar:3.5.19]

从异常信息可以推测是Mybatis-plus分页插件在对总行数统计的时候动态生产SQL时异常，将左联的表全部去除导致的。接下来分析PaginationInnerInterceptor源码的willDoQuery方法，这个方法是在执行查询前查询总行数的方法：

@Override
public boolean willDoQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
    IPage<?> page = ParameterUtils.findPage(parameter).orElse(null);
    if (page == null || page.getSize() < 0 || !page.searchCount() || resultHandler != Executor.NO_RESULT_HANDLER) {
        return true;
    }

    BoundSql countSql;
    MappedStatement countMs = buildCountMappedStatement(ms, page.countId());
    if (countMs != null) {
        countSql = countMs.getBoundSql(parameter);
    } else {
        countMs = buildAutoCountMappedStatement(ms);
        String countSqlStr = autoCountSql(page, boundSql.getSql());
        PluginUtils.MPBoundSql mpBoundSql = PluginUtils.mpBoundSql(boundSql);
        countSql = new BoundSql(countMs.getConfiguration(), countSqlStr, mpBoundSql.parameterMappings(), parameter);
        PluginUtils.setAdditionalParameter(countSql, mpBoundSql.additionalParameters());
    }

    CacheKey cacheKey = executor.createCacheKey(countMs, parameter, rowBounds, countSql);
    List<Object> result = executor.query(countMs, parameter, rowBounds, resultHandler, cacheKey, countSql);
    long total = 0;
    if (CollectionUtils.isNotEmpty(result)) {
        // 个别数据库 count 没数据不会返回 0
        Object o = result.get(0);
        if (o != null) {
            total = Long.parseLong(o.toString());
        }
    }
    page.setTotal(total);
    return continuePage(page);
}

其他代码不关注，我们重点关注下autoCountSql方法，这个方法是自动优化计算总行数SQL的方法：

/**
 * 获取自动优化的 countSql
 *
 * @param page 参数
 * @param sql  sql
 * @return countSql
 */
public String autoCountSql(IPage<?> page, String sql) {
    if (!page.optimizeCountSql()) {
        return lowLevelCountSql(sql);
    }
    try {
        Select select = (Select) JsqlParserGlobal.parse(sql);
        // https://github.com/baomidou/mybatis-plus/issues/3920  分页增加union语法支持
        if (select instanceof SetOperationList) {
            return lowLevelCountSql(sql);
        }
        PlainSelect plainSelect = (PlainSelect) select;

        ………

        // 包含 join 连表,进行判断是否移除 join 连表
        if (optimizeJoin && page.optimizeJoinOfCountSql()) {
            List<Join> joins = plainSelect.getJoins();
            if (CollectionUtils.isNotEmpty(joins)) {
                boolean canRemoveJoin = true;
                String whereS = Optional.ofNullable(plainSelect.getWhere()).map(Expression::toString).orElse(StringPool.EMPTY);
                // 不区分大小写
                whereS = whereS.toLowerCase();
                for (Join join : joins) {
                    if (!join.isLeft()) {
                        canRemoveJoin = false;
                        break;
                    }
                    FromItem rightItem = join.getRightItem();
                    String str = "";
                    if (rightItem instanceof Table) {
                        Table table = (Table) rightItem;
                        str = Optional.ofNullable(table.getAlias()).map(Alias::getName).orElse(table.getName()) + StringPool.DOT;
                    } else if (rightItem instanceof ParenthesedSelect) {
                        ParenthesedSelect subSelect = (ParenthesedSelect) rightItem;
                        /* 如果 left join 是子查询，并且子查询里包含 ?(代表有入参) 或者 where 条件里包含使用 join 的表的字段作条件,就不移除 join */
                        if (subSelect.toString().contains(StringPool.QUESTION_MARK)) {
                            canRemoveJoin = false;
                            break;
                        }
                        str = subSelect.getAlias().getName() + StringPool.DOT;
                    }
                    // 不区分大小写
                    str = str.toLowerCase();

                    if (whereS.contains(str)) {
                        /* 如果 where 条件里包含使用 join 的表的字段作条件,就不移除 join */
                        canRemoveJoin = false;
                        break;
                    }

                    for (Expression expression : join.getOnExpressions()) {
                        if (expression.toString().contains(StringPool.QUESTION_MARK)) {
                            /* 如果 join 里包含 ?(代表有入参) 就不移除 join */
                            canRemoveJoin = false;
                            break;
                        }
                    }
                }

                if (canRemoveJoin) {
                    plainSelect.setJoins(null);
                }
            }
        }

        // 优化 SQL
        plainSelect.setSelectItems(COUNT_SELECT_ITEM);
        return select.toString();
    } catch (JSQLParserException e) {
        // 无法优化使用原 SQL
        logger.warn("optimize this sql to a count sql has exception, sql:\"" + sql + "\", exception:\n" + e.getCause());
    } catch (Exception e) {
        logger.warn("optimize this sql to a count sql has error, sql:\"" + sql + "\", exception:\n" + e);
    }
    return lowLevelCountSql(sql);
}

可以看到存在优化连接、排序操作。在判断左连接中，可以看到以下代码：

str是指表名（或者表别名），当where 存在表别名的时候将canRemoveJoin标志为false，也就是不去除链接，后续代码也能看到对canRemoveJoin的判断，如果为true则去除连接：

至此，问题的原因也能找到了，我们可选以下几种方式解决：

  1. 在where查询字段前加上表名（别名）,即可避免被优化左连接：

<select id="testMybatisPlus" resultMap="BaseResultMap">
    select * from musclassify
    left join relmusicclass on (musclassify.id = relmusicclass.classid)
    left join music on (relmusicclass.musicid = music.id)
    where music.artist = #{artist}
</select>

2. 可以在构建Page对象的时候设置OptimizeCountSql为false，该字段是用于设置是否针对查询总行数进行优化

@Override
public void testMybatisPlus() {
    Page<Classify> page = new Page<>(1,10);
    page.setOptimizeCountSql(false);
    classifyMapper.testMybatisPlus(page,"张敬轩");
}

3.可以自己在Mapper中写一个计算总行数的SQL，并将id设置到countId中：

@Override
public void testMybatisPlus() {
    Page<Classify> page = new Page<>(1,10);
    page.setCountId("com.zguiz.musicplayer.mapper.ClassifyMapper.countClassify");
    classifyMapper.testMybatisPlus(page,"张敬轩");
}

4. 将CountId设置为ID+_mpCount，这个方法是Mybatis-Plus默认的查询总行数的方法，如果指定CountId，会避免进行总行数SQL优化如（"com.zguiz.musicplayer.mapper.ClassifyMapper.testMybatisPlus_mpCount")。