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

回答1:

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();

回答2:

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.

回答3:

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;