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 musclassifyleft 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 {@Beanpublic 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 causejava.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 没数据不会返回 0Object 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);}}}// 优化 SQLplainSelect.setSelectItems(COUNT_SELECT_ITEM);return select.toString();} catch (JSQLParserException e) {// 无法优化使用原 SQLlogger.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 musclassifyleft 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")。