hibernate中SQL包含冒号

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

1
2
3
4
5
6
7
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);

1
QueryException: unexpected char'@' 

原因:HQL不支持这种查询

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

1
Space is not allowed after parameter prefix ':'

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

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

1
2
3
4
5
6
7
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看到有人回复

1
2
3
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.

修改后

1
2
3
4
5
6
7
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  

问题解决

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

posted @   养诚  阅读(2392)  评论(0编辑  收藏  举报
编辑推荐:
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· Linux系列:如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列:如何用 C#调用 C方法造成内存泄露
阅读排行:
· 震惊!C++程序真的从main开始吗?99%的程序员都答错了
· 别再用vector<bool>了!Google高级工程师:这可能是STL最大的设计失误
· 单元测试从入门到精通
· 【硬核科普】Trae如何「偷看」你的代码?零基础破解AI编程运行原理
· 上周热点回顾(3.3-3.9)
点击右上角即可分享
微信分享提示