hibernate中SQL包含冒号

最新推荐文章于 2021-02-21 06:41:11 发布

weixin_30252709

最新推荐文章于 2021-02-21 06:41:11 发布

阅读量811

点赞数

CC 4.0 BY-SA版权

文章标签： java 数据库

原文链接：http://www.cnblogs.com/WangBoBlog/p/10384299.html

本文详细介绍了在使用Hibernate进行SQL查询时遇到的@符号错误，并提供了两种解决方案：一是使用转义字符，二是利用注释技巧绕过错误，最终成功解决了查询问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

当前负责的项目使用的是hibernate，而“：”是hibernate的一个占位符，作为预编译使用的，

select tmp.pid from  
(select pid, loginTime, @i := @i + 1 i from gms_record_login_game, (select @i := 0) r order by pid, loginTime) tmp
LEFT JOIN
(select pid, loginTime, @j := @j + 1 k from gms_record_login_game, (select @j := 0) r1 order by pid, loginTime) tmp2 
on tmp.i + 1 = tmp2.k and tmp.pid = tmp2.pid 
WHERE  TIMESTAMPDIFF(DAY,DATE_FORMAT(tmp.loginTime,'%Y-%m-%d'),DATE_FORMAT(tmp2.loginTime,'%Y-%m-%d'))>3 
GROUP BY tmp.pid

使用HQL查询时报错：Query query = session.createQuery(HQL);

QueryException: unexpected char: '@'

原因：HQL不支持这种查询

解决方案：使用原生SQL查询时报错：SQLQuery query = session.createSQLQuery(SQL);

Space is not allowed after parameter prefix ':'

原因：这是hibernate3.X包之下的一个bug,(参照 id=41741)在hibernate4.X中已经修复。

解决方案：需要对双冒号进行转义，在使用双反斜杠进行转义

select tmp.pid from  
(select pid, loginTime, @i \\:= @i + 1 i from gms_record_login_game, (select @i \\:= 0) r order by pid, loginTime) tmp
LEFT JOIN
(select pid, loginTime, @j \\:= @j + 1 k from gms_record_login_game, (select @j \\:= 0) r1 order by pid, loginTime) tmp2 
on tmp.i + 1 = tmp2.k and tmp.pid = tmp2.pid 
WHERE  TIMESTAMPDIFF(DAY,DATE_FORMAT(tmp.loginTime,'%Y-%m-%d'),DATE_FORMAT(tmp2.loginTime,'%Y-%m-%d'))>3 
GROUP BY tmp.pid

重点来了修改后依旧没有解决！！！！

后来在stackoverflow看到有人回复

Another solution for those of us who can't make the jump to Hibernate 4.1.3.
Simply use /*'*/:=/*'*/ inside the query. Hibernate code treats everything between ' as a string (ignores it). MySQL on the other hand will ignore everything inside a blockquote and will evaluate the whole expression to an assignement operator.
I know it's quick and dirty, but it get's the job done without stored procedures, interceptors etc.

修改后

select tmp.pid from  
(select pid, loginTime, @i /*'*/:=/*'*/ @i + 1 i from gms_record_login_game, (select @i /*'*/:=/*'*/ 0) r order by pid, loginTime) tmp
LEFT JOIN
(select pid, loginTime, @j /*'*/:=/*'*/ @j + 1 k from gms_record_login_game, (select @j /*'*/:=/*'*/ 0) r1 order by pid, loginTime) tmp2 
on tmp.i + 1 = tmp2.k and tmp.pid = tmp2.pid 
WHERE  TIMESTAMPDIFF(DAY,DATE_FORMAT(tmp.loginTime,'%Y-%m-%d'),DATE_FORMAT(tmp2.loginTime,'%Y-%m-%d'))>3 
GROUP BY tmp.pid

问题解决