mysql 包括冒号_hibernate中SQL包含冒号

当前负责的项目使用的是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

问题解决