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

最近维护一个旧项目的时候，出现了一个BUG，经排查后发现是Mybatis- plus分页控件使用的时候需要注意的一个问题，故在本地使用MybatisPlus模拟出现了一下这个问题。 首先，先说一下MyBatis-Plus的使用： 1）引入所需的包： com.baomidou mybatis-plus-boot-starter 3.5.10.1

com.baomidou mybatis-plus-extension 3.5.10.1

com.baomidou mybatis-plus-jsqlparser 3.5.10.1 2）Mapper需要继承BaseMapper @Mapper public interface ClassifyMapper extends BaseMapper { List testMybatisPlus(Page page, @Param(“artist”) String artist); } 3）编写对应的Mapper文件（下面的左连接是为了模拟项目实际问题编写的，artist是music表的字段）： xml version=“1.0” encoding=“UTF-8”?

select * from musclassify left join relmusicclass on (musclassify.id = relmusicclass.classid) left join music on (relmusicclass.musicid = music.id) where artist = #{artist} 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 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 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); // 分页增加union语法支持 if (select instanceof SetOperationList) { return lowLevelCountSql(sql); } PlainSelect plainSelect = (PlainSelect) select; ……… // 包含 join 连表,进行判断是否移除 join 连表 if (optimizeJoin && page.optimizeJoinOfCountSql()) { List 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 * from musclassify left join relmusicclass on (musclassify.id = relmusicclass.classid) left join music on (relmusicclass.musicid = music.id) where music.artist = #{artist} 2 可以在构建Page对象的时候设置OptimizeCountSql为false，该字段是用于设置是否针对查询总行数进行优化 @Override public void testMybatisPlus() { Page page = new Page<>(1,10); page.setOptimizeCountSql(false); classifyMapper.testMybatisPlus(page,“张敬轩”); } 3.可以自己在Mapper中写一个计算总行数的SQL，并将id设置到countId中： @Override public void testMybatisPlus() { Page 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”)。