问题描述
环境:
mybatis 3.4.6
MySQL数据库
描述:
在学习MyBatis分页过程中,对MySQL数据库分页时出现以下SQLSyntaxErrorException异常:
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 3' at line 1
该异常属于Sql语句发生异常,提示说LIMIT 3附近出现语句格式错误。
java出错部分代码:
public class Test5 {
public static void main(String[] args) throws IOException {
SqlSession sqlSession = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml")).openSession();
PageHelper.startPage(1, 3);
UserDao dao = sqlSession.getMapper(UserDao.class);
List<User> users1 = dao.getAll();
PageInfo<User> userPageInfo = new PageInfo<>(users1);
List<User> list1 = userPageInfo.getList();
list1.forEach(user -> System.out.println(user));
}
}
getAll方法的Mapper配置:
<select id="getAll" resultType="user">
select * from User;
</select>
解决方案
通过使用log4j打印日志:
2021-01-18 09:38:29,839 [main] DEBUG [com.kkb.dao.UserDao.getAll] - ==> Preparing: select * from User; LIMIT ?
2021-01-18 09:38:29,840 [main] DEBUG [com.kkb.dao.UserDao.getAll] - ==> Parameters: 3(Integer)
可以发现,MyBatis分页插件拼接的sql语句中:“select * from User; LIMIT ?”,中间多了个分号,所以将Mapper配置文件中的sql语句句尾处的 ;去掉即可。
将getAll方法的Mapper配置改成:
<select id="getAll" resultType="user">
select * from User
</select>
测试后,成功运行