How to use Mysql variables with Hibernate?

I need to use a native sql query in Hibernate with use of variable.

But hibernate throws an error saying: Space is not allowed after parameter prefix

So there is a conflict with the := mysql variable assignment and hibernate variable assignment.

Here is my sql query:

SET @rank:=0; 
UPDATE Rank SET rank_Level=@rank:=@rank+1 ORDER BY Level;

the hibernate code (jpa syntax):

Query query = em.createNativeQuery(theQuery);
query.executeUpdate();

I can't use a stored procedure because my sql query is dynamically generated ('Level' 开发者_如何转开发can be 'int' or 'force'...)

How can I do this ?

thanks

Well, I finally use stored procedure (yes, what I don't want initially) to create dynamic query (I don't think it was possible).

Here is my code: The stored procedure:

DELIMITER |

DROP PROCEDURE IF EXISTS UpdateRank |

CREATE PROCEDURE UpdateRank(IN shortcut varchar(30))
BEGIN
    SET @rank=0;
    SET @query=CONCAT('UPDATE Rank SET ', shortcut, '=@rank:=@rank+1 ORDER BY ', shortcut);     

    PREPARE q1 FROM @query;
    EXECUTE q1;
    DEALLOCATE PREPARE q1;
END;

|
DELIMITER ;

The tip is the use of the CONCAT function to dynamically create a query in the stored procedure.

Then, call the procedure in classic hibernate function:

Query q = em.createNativeQuery("CALL updateRank('lvl')");
q.executeUpdate();

I'll copy paste my answer from https://stackoverflow.com/a/25552002/3987202

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.

Use MySQL Proxy to rewrite the query after Hibernate has sent the query to the database.

For example supply Hibernate with this,

UPDATE Rank SET rank_Level=incr(@rank) ORDER BY Level;

but rewrite it to this,

UPDATE Rank SET rank_Level=@rank:=@rank+1 ORDER BY Level;