mybatis puls分页出现两个 limit 10 LIMIT ?

 
2020-10-15 12:03:02.909 ERROR 28056 --- [nio-8000-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### 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 20' at line 6
### The error may exist in file [G:\Git\xf\service-xf\target\classes\com\lm\anga\platform\anslip\dao\xml\AnSlip.map.xml]
### The error may involve com.lm.anga.platform.anslip.dao.mapper.AnSlipMapper.getLastData-Inline
### The error occurred while setting parameters
### SQL: SELECT     id_,station_id_,collect_time_,an_slip_      FROM dt_an_slip_data   WHERE   station_id_=? order by collect_time_ desc limit 1 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 20' at line 6
; 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 20' at line 6] with root 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 20' at line 6
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_40]
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_40]
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_40]
	at java.lang.reflect.Constructor.newInstance(Constructor.java:422) ~[na:1.8.0_40]
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) ~[mysql-connector-java-5.1.21.jar:na]

遇到的这个问题是mybatis的插件PageHelperi与limit冲突的问题，我的问题是第二句代码放到了方法的最低端。所以产生了冲突

方案一

PageBean pageBean=queryFilter.getPageBean();
		PageHelper.startPage(pageBean.getCurrentPage(),pageBean.getPageSize());
		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);

方案二

这句错：

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>

在使用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，导致出现我们刚开始的问题，所以我暂时也不清楚具体原理，有空的时候再来研究下原理，有知道的小伙伴也可以在留言区告诉我