1、使用PageHelper分页插件时,编写测试方法,运行并测试时出现如下异常:
### 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 5' at line 1
; 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 5' at line 1
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:235)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
Caused by: 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 5' at line 1
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)
2、检查一下SQL语句,感觉没什么问题,最后从打印异常的日志文件中看出端倪
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 5' at line 1
### The error may exist in dao/company/CompanyDao.xml
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT * FROM `company`; LIMIT ?
3、原来每次在SQL映射文件中,写完SQL语句便会在结尾处加一个分号(;
),因为后面需要动态添加LIMIT语句进行分页查询,所以这个分号(;
)出现在LIMIT语句的前面,导致这条查询的SQL语句出错,而分页的功能也无法正常显示
<!-- 获取公司的数据 -->
<select id="getCompanyList" resultType="entity.Company">
SELECT * FROM `company`; <!-- 只需要将分号(;)删掉就可以了 -->
</select>