1. 问题描述
今天在完善项目的时候,有一个需求就是给我的评论区实现分页显示评论数,但是当自己运行的时候点击查看评论的时候出现下面图示的结果,这就开始了我长达2个小时的改bug艰难征程。
在控制台的显示如下:
### 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);
再次运行,结果如下所示:
嗯嗯,看起来好像实现了分页,但是当我们选择了一个有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. 完成效果
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,导致出现我们刚开始的问题,所以我暂时也不清楚具体原理,有空的时候再来研究下原理,有知道的小伙伴也可以在留言区告诉我。