解决Mybatis分页插件PageHelper自动添加limit导致分页失败问题

╰半橙微兮° 2022-10-12 01:24 59阅读 0赞

### 目录 ###

*  1. 问题描述
 *  2. 解决方案
 *   *  2.1 方案一
     *  2.2 方案二
 *  3. 完成效果
 *  4. 一点困惑
 *  5. 参考文献

# 1. 问题描述 #

今天在完善项目的时候，有一个需求就是给我的评论区实现分页显示评论数，但是当自己运行的时候点击查看评论的时候出现下面图示的结果，这就开始了我长达2个小时的改bug艰难征程。  
![在这里插入图片描述][20210702155504580.gif_pic_center]  
在控制台的显示如下：

### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 10' at line 5
    ### The error may exist in file [D:\java-workspace\forums\target\forums\WEB-INF\classes\forum\mapper\ReplyMapper.xml]
    ### The error may involve forum.dao.ReplyMapper.getRepliesOfTopic-Inline
    ### The error occurred while setting parameters
    ### SQL: select reply.*, user.username, user.avatar from reply, user where reply.reply_user_id = user.id and reply.topic_id = ? order by create_time ASC; LIMIT ?
    ### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 10' at line 5
    ; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 10' at line 5
    	com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 10' at line 5
    		at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    		at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    		at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    		at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    		at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)

# 2. 解决方案 #

既然问题出现了，我们就得给他解决，先说说自己在网上看到的几种解决方案吧，我都亲自尝试过了。

## 2.1 方案一 ##

刚开始的时候，看到网上的一段话，如下所示：

使用了Spring的PageHelper插件，发现了一个奇怪的问题，经常会给SQL无缘无故的增加Limit语句，经过调查，发现原因是没有安全的使用PageHelper插件

然后给出的解决方案就是：

要绝对保证PageHelper.startPage和分页查询语句之间不要有任何其他语句，或者在程序结束时增加PageHelper.clearPage();的调用

嗯嗯，我就按照这个大概的意思尝试了下，我的代码如下：

//获取第1页，10条内容，默认查询总数count
            PageHelper.offsetPage(page.getStart(), page.getCount());
            PageHelper.clearPage();
            //获取主题全部评论
            List<Reply> replies = replyService.getRepliesOfTopic(id);
            //分页
            int total = (int) new PageInfo<Reply>(replies).getTotal();
            page.setTotal(total);

再次运行，结果如下所示：  
![在这里插入图片描述][20210702161447365.gif_pic_center]  
嗯嗯，看起来好像实现了分页，但是当我们选择了一个有11条记录的评论，按理说第一页10条，第二页1条，但是我们看见了第二页与第一页一样都是11条，所以这种方法没有作用。

## 2.2 方案二 ##

嗯嗯，不得不说皇天不负有心人，果然还是方案二一语点醒梦中人，看到的这篇文章说先检查下自己的SQL语句有没有问题，嗯嗯，按照这个我测试了下好像没有啥问题，但是控制台报的下面这句错：

SQL: select reply.*, user.username, user.avatar  from reply, user         where reply.reply_user_id = user.id  and reply.topic_id = ?         order by create_time ASC; LIMIT ?

果然还是自己的SQL语句出问题了因为当我们使用PageHelper这个分页插件的时候，会自动给我们的SQL语句加上limit，所以加上limit之后我们的SQL因为有个;号所以出现了语法错误，故解决方案就是将;号去掉，如下所示：

<select id="getRepliesOfTopic" resultMap="replyAndUser" parameterType="int">
            select reply.*, user.username, user.avatar
            from reply, user
            where reply.reply_user_id = user.id
              and reply.topic_id = #{topicId,jdbcType=INTEGER}
            order by create_time ASC
        </select>

然后再次执行完美运行。

# 3. 完成效果 #

![在这里插入图片描述][20210702162148161.gif_pic_center]

# 4. 一点困惑 #

在使用PageHelper这个分页插件的时候，我发现了一个很奇怪的问题，比如下面例子：

//获取第1页，10条内容，默认查询总数count
            PageHelper.offsetPage(page.getStart(), page.getCount());
            //获取主题信息
            Topic topic = topicService.selectById(id);
            //获取主题全部评论
            List<Reply> replies = replyService.getRepliesOfTopic(id);
            //分页
            int total = (int) new PageInfo<Reply>(replies).getTotal();
            page.setTotal(total);

当我们的 PageHelper.offsetPage(page.getStart(), page.getCount());下面的一句代码是Topic topic = topicService.selectById(id);那么我们的PageHelper会对这个函数的SQL语句后面添加limit，导致出现我们刚开始的问题，所以我暂时也不清楚具体原理，有空的时候再来研究下原理，有知道的小伙伴也可以在留言区告诉我。

# 5. 参考文献 #

[1. Mybatis-PageHelper 官方中文文档][1. Mybatis-PageHelper]

[2. PageHelper分页插件出现的异常][2. PageHelper]

[3. 解决Spring的PageHelper插件，无故给SQL增加Limit语句的问题][3. _Spring_PageHelper_SQL_Limit]

[20210702155504580.gif_pic_center]: /images/20221005/70682ab5fcdf46f1acbc407729ac47cf.png
[20210702161447365.gif_pic_center]: /images/20221005/46e0c75833fa4a42803e401926b52fd7.png
[20210702162148161.gif_pic_center]: /images/20221005/9e1dbcbc006d47f19060fe78c107a924.png
[1. Mybatis-PageHelper]: https://github.com/pagehelper/Mybatis-PageHelper/blob/master/wikis/zh/HowToUse.md
[2. PageHelper]: https://blog.csdn.net/weixin_42140851/article/details/101681677
[3. _Spring_PageHelper_SQL_Limit]: https://codeleading.com/article/10332193810/