How to use Mysql variables with Hibernate?

Question

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

You are not alone, forum.hibernate.org/viewtopic.php?f=1&t=992931&start=0 opensource.atlassian.com/projects/hibernate/browse/HHH-2697 Maybe have one stored procedure per order by clause. — Janek Bogucki
– Janek Bogucki, Commented Apr 26, 2010 at 13:02
Yes I found these two links before posting (it is for that I'm saying that I can't use stored procedure) — Jerome Cance
– Jerome Cance, Commented Apr 26, 2010 at 13:18

Jerome Cance · Accepted Answer · 2010-04-26 14:42:50Z

5

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

answered Apr 26, 2010 at 14:42

Jerome Cance

8,20312 gold badges56 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Janek Bogucki Over a year ago

Make sure to prevent sql injection.

Jerome Cance Over a year ago

Yes, you're right, but I've already protect it (my shortcut is only letters)

Sanghyun Lee Over a year ago

I found better approach to this issue with Hibernate Interceptor stackoverflow.com/questions/9460018/…. I think using stored procedure is awful idea if there's alternative.

Jerome Cance Over a year ago

Yes you are right, it seems to be a better approach ! Thanks for notifying that.

Community · Accepted Answer · 2017-05-23 12:02:42Z

3

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.

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered Aug 28, 2014 at 15:04

Mariusz S.

3312 silver badges5 bronze badges

1 Comment

Don Rhummy Over a year ago

This fixed it for me. Thanks!

Janek Bogucki · Accepted Answer · 2010-04-26 13:58:56Z

0

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;

answered Apr 26, 2010 at 13:58

Janek Bogucki

5,1533 gold badges32 silver badges41 bronze badges

1 Comment

Pedro Over a year ago

HOW STUPID IS HIBERNATE HUH?

Collectives™ on Stack Overflow

How to use Mysql variables with Hibernate?

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related