How to use Mysql variables with Hibernate ?

Question

Hello,

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

Answer 1

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;

Answer 2

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