How can I use MySQL assign operator(:=) in hibernate native query?

Question

I'm using Hibernate. I wrote some native query because I need to use sub select statement.

Query looks like this:

SELECT sub.rownum FROM      (SELECT k.`news_master_id` AS id, @row := @row + 1 AS rownum          FROM keyword_news_list k          JOIN (SELECT @row := 0) r          WHERE k.`keyword_news_id` = :kid     ORDER BY k.`news_master_id` ASC) AS sub  WHERE sub.id  = :nid 

When I run this query like this:

sessionFactory.getCurrentSession()     .createSQLQuery(query)     .setParameter("kid", kid)     .setParameter("nid", nid)     .uniqueResult(); 

This exception comes out:

org.hibernate.QueryException: Space is not allowed after parameter prefix ':' .... 

This might because of := operator. I found some Hibernate issue about this. This issue is still open. Isn't there any solution for this problem?

Answer 1

Note that HHH-2697 is now fixed for Hibernate 4.1.3 You can now escape with backslash:

SELECT k.`news_master_id` AS id, @row \:= @row + 1 AS rownum      FROM keyword_news_list k      JOIN (SELECT @row \:= 0) r      WHERE k.`keyword_news_id` = :kid ORDER BY k.`news_master_id` ASC 

Answer 2

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.