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 |
问题解决
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 基于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)