在mybatis中ql语句错误,求大佬指点一下 万分感谢

言午琅 发布于 2018/11/11 16:17
阅读 233
收藏 0
SET @a=CONCAT('SELECT a.accout_,a.name,a.birthday_,b.name_j
FROM users a,role b,users_role c
WHERE a.`id`=c.`user_id`
AND b.`id_j`=c.`role` LIMIT ',(#{page}-1)*#{rows},",",#{rows},'')
 PREPARE texts FROM @a
 EXECUTE texts

#{page}#{rows}为什么没有识别不出来 这是异常信息 求大佬指点一下 万分感谢

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 'PREPARE texts FROM @a
EXECUTE texts' at line 2
### The error may exist in file [D:\mybatisSimple\out\artifacts\mybatisSimple_war_exploded\WEB-INF\classes\com\bypx\mapping\userXM.xml]
### The error may involve com.bypx.dao.UserXMDao.userquery-Inline
### The error occurred while setting parameters
### SQL: SET @a=CONCAT('SELECT a.accout_,a.name,a.birthday_,b.name_j FROM users a,role b,users_role c WHERE a.`id`=c.`user_id` AND b.`id_j`=c.`role` LIMIT ',(?-1)*?,",",?,'')  PREPARE texts FROM @a  EXECUTE texts
### 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 'PREPARE texts FROM @a
EXECUTE texts' at line 2
; 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 'PREPARE texts FROM @a
EXECUTE texts' at line 2

 

加载中
0
红薯官方
红薯官方

你这个做法有点古怪,为什么传递一个字符串给MySQL?然后它是 PREPARE 可执行的SQL?

你用 Mybatis 直接 处理 page 和 rows 参数就可以了啊:

<select ...>
 <bind name="offset" value="page - 1 * rows"/> <!-- 放大招在此!-->
 SELECT a.accout_,a.name,a.birthday_,b.name_j
 FROM users a,role b,users_role c
 WHERE a.`id`=c.`user_id`
 AND b.`id_j`=c.`role` LIMIT #{offset},#{rows}
</select>

何必还搞个复杂难懂的东西呢?

项目里面很多翻页的语句?试试#PageHelper#

从此只需要:

PageHelper.startPage(pageNumber, pageSize, isExecuteCount);  // 黑魔法,无痛分页
List<UserEntity> list = userMapper.select(queryParams); // 魔法转身,普通 SELECT 变身分页查询

要想使用去看看文档吧。

 

不过,框架只是为你提高工作效率的而已,原理还是懂的最好。

言午琅
回复 @蓝水晶飞机 : 非常感谢
红薯官方
红薯官方
文档地址 https://pagehelper.github.io/
0
言午琅

是不是要加$符号而不是#

0
言午琅

参数是识别出来 可问题还是没有得到解决

0
言午琅

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 'PREPARE texts FROM @a
 EXECUTE texts' at line 2
### The error may exist in file [D:\mybatisSimple\out\artifacts\mybatisSimple_war_exploded\WEB-INF\classes\com\bypx\mapping\userXM.xml]
### The error may involve com.bypx.dao.UserXMDao.userquery-Inline
### The error occurred while setting parameters
### SQL: SET @a=CONCAT('SELECT a.accout_,a.name,a.birthday_,b.name_j FROM users a,role b,users_role c WHERE a.`id`=c.`user_id` AND b.`id_j`=c.`role` LIMIT ',(1-1)*3,",",3,'')  PREPARE texts FROM @a  EXECUTE texts
### 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 'PREPARE texts FROM @a
 EXECUTE texts' at line 2
; 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 'PREPARE texts FROM @a
 EXECUTE texts' at line 2

新的异常 说我

PREPARE texts FROM @a

有问题难道是没有加分号的问题(O_O)?

0
返回顶部
顶部