专注做一家男生最爱的网站,专业网站设计师去哪找,专业做招聘的网站有哪些,wordpress 文章置顶显示最近维护一个旧项目的时候#xff0c;出现了一个BUG#xff0c;经排查后发现是Mybatis-plus分页控件使用的时候需要注意的一个问题#xff0c;故在本地使用MybatisPlus模拟出现了一下这个问题。
首先#xff0c;先说一下MyBatis-Plus的使用#xff1a;
1#xff09;引入…最近维护一个旧项目的时候出现了一个BUG经排查后发现是Mybatis-plus分页控件使用的时候需要注意的一个问题故在本地使用MybatisPlus模拟出现了一下这个问题。
首先先说一下MyBatis-Plus的使用
1引入所需的包
dependencygroupIdcom.baomidou/groupIdartifactIdmybatis-plus-boot-starter/artifactIdversion3.5.10.1/version
/dependency
dependencygroupIdcom.baomidou/groupIdartifactIdmybatis-plus-extension/artifactIdversion3.5.10.1/version !-- 确保版本号正确 --
/dependency
dependencygroupIdcom.baomidou/groupIdartifactIdmybatis-plus-jsqlparser/artifactIdversion3.5.10.1/version !-- 确保版本号正确 --
/dependency2Mapper需要继承BaseMapper
Mapper
public interface ClassifyMapper extends BaseMapperClassify {ListClassify testMybatisPlus(Page? page, Param(artist) String artist);
}3编写对应的Mapper文件下面的左连接是为了模拟项目实际问题编写的artist是music表的字段
?xml version1.0 encodingUTF-8?
!DOCTYPE mapper PUBLIC -//mybatis.org//DTD Mapper 3.0//EN http://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespacecom.zguiz.musicplayer.mapper.ClassifyMapper!-- 通用查询映射结果 --resultMap idBaseResultMap typecom.zguiz.musicplayer.bean.Classifyid columnid propertyid /result columnname propertyname /result columniconurl propertyiconurl //resultMapselect idtestMybatisPlus resultMapBaseResultMapselect * from musclassifyleft join relmusicclass on (musclassify.id relmusicclass.classid)left join music on (relmusicclass.musicid music.id)where artist #{artist}/select
/mapper4让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/*.xml6调用方法
Override
public void testMybatisPlus() {PageClassify 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 clauseat 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);ListObject 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()) {ListJoin 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 idtestMybatisPlus resultMapBaseResultMapselect * from musclassifyleft join relmusicclass on (musclassify.id relmusicclass.classid)left join music on (relmusicclass.musicid music.id)where music.artist #{artist}
/select2. 可以在构建Page对象的时候设置OptimizeCountSql为false该字段是用于设置是否针对查询总行数进行优化
Override
public void testMybatisPlus() {PageClassify page new Page(1,10);page.setOptimizeCountSql(false);classifyMapper.testMybatisPlus(page,张敬轩);
}3.可以自己在Mapper中写一个计算总行数的SQL并将id设置到countId中
Override
public void testMybatisPlus() {PageClassify 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)。
