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
问题解决