SSM的pagehelper出错(bad ASQL)

出错的问题

HTTP状态 500 - 内部服务器错误

类型 异常报告

消息 Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException:

描述 服务器遇到一个意外的情况，阻止它完成请求。

例外情况

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: 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 6, 6' at line 3
### The error may exist in com/experience/dao/Book_all_dao.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select *         from book;  LIMIT ?, ?
### Cause: java.sql.SQLSyntaxErrorException: 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 6, 6' at line 3
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: 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 6, 6' at line 3
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1014)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:655)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
    org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)

根本原因。

org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: 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 6, 6' at line 3
### The error may exist in com/experience/dao/Book_all_dao.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select *         from book;  LIMIT ?, ?
### Cause: java.sql.SQLSyntaxErrorException: 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 6, 6' at line 3
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: 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 6, 6' at line 3
    org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
    org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
    org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
    org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
    jdk.proxy3/jdk.proxy3.$Proxy18.selectList(Unknown Source)
    org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
    org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:139)
    org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:76)
    org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
    jdk.proxy3/jdk.proxy3.$Proxy19.getBookList(Unknown Source)
    com.experience.service.impl.Book_all_service_impl.getAllBooks(Book_all_service_impl.java:24)
    com.experience.controller.Book_all_Controller.demo(Book_all_Controller.java:19)
    java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
    java.base/java.lang.reflect.Method.invoke(Method.java:578)
    org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:655)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
    org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)

根本原因。

java.sql.SQLSyntaxErrorException: 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 6, 6' at line 3
    com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
    com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
    com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:371)
    com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)
    org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)
    org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
    org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
    org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:326)
    org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
    org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
    com.github.pagehelper.util.ExecutorUtil.pageQuery(ExecutorUtil.java:215)
    com.github.pagehelper.PageInterceptor.intercept(PageInterceptor.java:147)
    org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61)
    jdk.proxy3/jdk.proxy3.$Proxy37.query(Unknown Source)
    org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
    org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
    java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
    java.base/java.lang.reflect.Method.invoke(Method.java:578)
    org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
    jdk.proxy3/jdk.proxy3.$Proxy18.selectList(Unknown Source)
    org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
    org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:139)
    org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:76)
    org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
    jdk.proxy3/jdk.proxy3.$Proxy19.getBookList(Unknown Source)
    com.experience.service.impl.Book_all_service_impl.getAllBooks(Book_all_service_impl.java:24)
    com.experience.controller.Book_all_Controller.demo(Book_all_Controller.java:19)
    java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:104)
    java.base/java.lang.reflect.Method.invoke(Method.java:578)
    org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
    org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:655)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
    org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117)

导入的依赖

 <!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.3.3</version>
        </dependency>

部分application-dao.xml的配置

   <!--    引入属性文件-->
    <context:property-placeholder location="classpath:jdbc.properties" />
    <!--    数据源-->
    <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="${jdbc.driverClassName}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
    </bean>
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <property name="typeAliasesPackage" value="com.experience.entity"/>
        <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>

    <!--    扫描dao包-->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.experience.dao"/>
    </bean>

Mapper文件

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.experience.dao.Book_all_dao">
    <select id="getBookList" resultType="com.experience.entity.Book">
        select *
        from book;
    </select>
</mapper>

dao层接口

package com.experience.dao;

import com.experience.entity.Book;

import java.util.List;

public interface Book_all_dao {
    List<Book> getBookList();
}

service层接口的实现类

package com.experience.service.impl;

import com.experience.dao.Book_all_dao;
import com.experience.entity.Book;
import com.experience.service.Book_all_service;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.Arrays;
import java.util.List;
import java.util.stream.Stream;

@Service
public class    Book_all_service_impl implements Book_all_service {
    @Autowired
    Book_all_dao book_all_dao;

    @Override
    public PageInfo<Book> getAllBooks() {
        PageHelper.startPage(2, 6);
        List<Book> book = book_all_dao.getBookList();
        PageInfo<Book> pageInfo = new PageInfo<>(book);
        System.out.println(pageInfo);
        return pageInfo;
    }
}

把；去掉试试┌|◎o◎|┘

【以下回答由 GPT 生成】

问题原因是在查询语句中使用了LIMIT关键字，但是语法错误导致查询失败。

要解决这个问题，可以按照以下步骤进行操作：

1. 检查数据库版本

首先确认所使用的MySQL数据库版本，因为不同的版本可能对SQL语法有所不同，导致问题出现。

2. 修改查询语句

根据错误信息提供的SQL语句select * from book; LIMIT ?, ?，将LIMIT关键字后面的参数改为具体的数值，如select * from book LIMIT 0, 6，其中0表示起始位置，6表示每页显示的记录数。这里需要根据具体的业务需求来确定参数的值。

3. 修改对应的Mapper配置文件

根据错误信息提供的错误位置com/experience/dao/Book_all_dao.xml，找到该XML文件中对应的查询语句，将查询语句中的LIMIT关键字和参数进行修改。具体的修改如下：

<select id="queryBooks" parameterType="map" resultMap="bookResultMap">
    select * from book
    LIMIT #{pageNum}, #{pageSize}
</select>

4. 重新编译和部署应用程序

修改完成后，重新编译和部署应用程序，确保修改生效。如果使用的是Maven等构建工具，可以运行相关的命令进行编译和部署。

重新运行应用程序后，应该能够解决SSM框架的pagehelper插件出错的问题。如果问题仍然存在，请检查其他可能的错误原因，如数据库配置、数据源配置等。