分页插件PageHelp 和 解决MySQL server version for the right syntax to use near ‘LIMIT 10‘ at line 1问题

1.导入依赖

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.0.2</version>
</dependency>

2.在Spring-dao中进行文件配置

<!--配置SqlSessionFactory-->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"></property>
        <property name="configLocation" value="classpath:sqlMapConfig.xml"></property>

        <!-- 传入PageHelper的插件 -->
        <property name="plugins">
            <array>
                <!-- 传入插件的对象 -->
                <bean class="com.github.pagehelper.PageInterceptor">
                    <property name="properties">
                        <props>
                            <prop key="helperDialect">mysql</prop>
                            <prop key="reasonable">true</prop>
                        </props>
                    </property>
                </bean>
            </array>
        </property>

    </bean>

3.1在Service层中需要传入 Page 和 limit

public ResultMsg<Product> findAll(Integer page, Integer limit);

public ResultMsg<Product> findAll(Integer page,Integer limit) {

        ResultMsg<Product> resultMsg = new ResultMsg<Product>();

        resultMsg.setCode(0);

        //使用分页插件完成分页
        //page 和limit由前端页面给出
        PageHelper.startPage(page,limit);
        PageInfo<Product> pageInfo = new PageInfo<Product>(productMapper.findAll());
        
        //获取查询到的集合
        resultMsg.setData(pageInfo.getList());

        //获取查询数据的总条数
        resultMsg.setCount(pageInfo.getTotal());

        resultMsg.setMsg("成功响应");

        return resultMsg;
    }

3.2在Controller层

//查询
    @RequestMapping("findAll")
    @ResponseBody
    public ResultMsg<Product> findAll(Integer page,Integer limit){
        return productService.findAll(page,limit);
    }

注意：

在 Mapper.xml文件的sql语句中最后不要添加“ ；” 号   不然会报错；

报错类型：

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 1

原因

利用Log4j打印的结果

 Preparing: select * from t_product; LIMIT 10