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?

Is there any thing that can help you from here? dev.mysql.com/doc/refman/5.0/en/… — ManuPK
– ManuPK, Commented Feb 27, 2012 at 5:11
@ManuPK Thanks, but it didn't help. I think there's no solution for my question. I found similar question with mine stackoverflow.com/questions/2712240/… but there's no good solution. — Sanghyun Lee
– Sanghyun Lee, Commented Feb 27, 2012 at 5:26

rogerdpack · Accepted Answer · 2020-07-28 17:58:17Z

33

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

edited Jul 28, 2020 at 17:58

rogerdpack

67.7k40 gold badges291 silver badges410 bronze badges

answered Jul 29, 2012 at 13:03

Ondra Žižka

47.2k49 gold badges228 silver badges298 bronze badges

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

3 Comments

Andreas Covidiot Over a year ago

using Hibernate 4.3.7 only one backslash is enough, e.g. PG 9.3 query select f( p_tst \:= 1 ) (where it may be two in Java strings to escape them)

Jignesh M. Khatri Over a year ago

This should be marked as correct answer, its effective and much more easy.

RAM237 Over a year ago

Works fine, in spring jpa repository for @Query( value = "<...>" , nativeQuery = true) , I had to use double backslash \\:= inside quotes to avoid compile time error.

Mariusz S. · Accepted Answer · 2014-08-28 14:58:01Z

20

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.

answered Aug 28, 2014 at 14:58

Mariusz S.

3312 silver badges5 bronze badges

Comments

Anantha Sharma · Accepted Answer · 2012-02-27 08:23:29Z

6

you can implement this is a slightly different way.. you need to replace the : operator with something else (say '|' char ) and in your interceptor replace the '|' with the : .

this way hibernate will not try to think the : is a param but will ignore it

For the interceptor logic you can refer to the hibernate manual

This has worked for me using MySQL 5.

remember, this replacing of : must be only done to ':=' and other MySQL specific requirments.. don't try to replace the : for the param-placeholders. (hibernate will not be able to identify the params then)

answered Feb 27, 2012 at 8:23

Anantha Sharma

10.1k4 gold badges35 silver badges35 bronze badges

2 Comments

Pedro Over a year ago

HOW STUPID IS HIBERNATE HUH? I can imagine the coder going. Nah, how could there EVER be a colon in the SQL. No chance! Let's make it so there is no escaping mechanism!

Pedro Over a year ago

Sorry, was a bit pissed of about having to work around. The solution above is only if you only have a hammer. You can also use \: to escape the colons, though in my case, it broke every single named parameter (they just stopped working.)

Adam Lear · Accepted Answer · 2013-07-05 02:28:40Z

0

I prefer to include Spring JDBC and execute the query rather than fight against Hibernate interceptors.

edited Jul 5, 2013 at 2:28

Adam Lear♦

38.9k13 gold badges83 silver badges105 bronze badges

answered Jul 4, 2013 at 16:46

leccionesonline

6081 gold badge10 silver badges24 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:34:54Z

0

in Hibernate exception on encountering mysql := operator Stanislav gave another option other than interceptor to solve this issue

edited May 23, 2017 at 12:34

CommunityBot

11 silver badge

answered Oct 21, 2015 at 7:08

varghese

334 bronze badges

Comments

Zon · Accepted Answer · 2020-12-03 19:21:07Z

0

If you keep your SQL-files away from Java code - try this piece of code. Played a lot to get the right number of escaping slashes:

String sqlPattern = FileUtils.readFile(this.getClass(), /sql/my_query.sql");
sqlPattern = sqlPattern.replaceAll(":=", "\\\\:=");
Query query = entityManager.createNativeQuery(sqlPattern);

edited Dec 3, 2020 at 19:21

answered Dec 3, 2020 at 19:05

Zon

20k8 gold badges99 silver badges104 bronze badges

Comments

Rocky · Accepted Answer · 2012-02-27 04:42:01Z

-6

I guess there should not be a space after = , the operator should be written as =: (without any spaces)

answered Feb 27, 2012 at 4:42

Rocky

9517 silver badges11 bronze badges

Collectives™ on Stack Overflow

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

7 Answers 7

3 Comments

Comments

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

3 Comments

Comments

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related