Hibernate exception on encountering mysql := operator

Question

When I execute the following code the exception occurs:

Exception: org.springframework.orm.hibernate3.HibernateQueryException: 
Not all named parameters have been set

Here is my code:

queryString = SET @quot=0,@latest=0,@comp='';
    select B.* from (
    select A.time,A.change,IF(@comp<>A.company,1,0) as LATEST,@comp:=A.company as company from (
    select time,company,quote-@quot as `change`, @quot:=quote curr_quote
    from stocks order by company,time) A
    order by company,time desc) B where B.LATEST=1;

list = getHibernateTemplate().executeFind(new HibernateCallback(){
public Object doInHibernate(Session session)throws     HibernateException,SQLException {
        SQLQuery  query = session.createSQLQuery(queryString);
        query.setParameterList("list", custIds);
        return query.list();
    }

What is the reason for this behavior?

The `:` is interpreted as the start of a named parameter hence `:=` is interpreted as such as well. — M. Deinum, Oct 15 '15 at 11:23
I also doubt that it is a valid query, looks like 2 queries (first the set then the query). I also nowhere see a `:list` in your query so not sure what you are binding to. How to solve the issue I'm not sure instead of using hibernate use a plain JdbcTemplate to execute the query. — M. Deinum, Oct 15 '15 at 11:39
Please see the query execution in http://sqlfiddle.com/#!2/4f8a1/2 This is used to get difference between rows for a particular column — varghese, Oct 15 '15 at 12:43
try escaping the `:` characters by placing a `\\` before them — javaguest, Oct 15 '15 at 12:53
when I try to escape : by placing \ compile error happens as double backslash is to escape Java string. so i tried using \\:= Hibernate sees it as single \ and the same exception happens — varghese, Oct 16 '15 at 05:15
You can escape `:` from Hibernate 4.1.3. http://stackoverflow.com/questions/9460018/how-can-i-use-mysql-assign-operator-in-hibernate-native-query — Sanghyun Lee, Oct 16 '15 at 05:49

Stanislav · Accepted Answer · 2015-10-16T05:43:39.143

4

It's a little bit hard to understand, what is exactly the query you are executing, but if you need to use the colon character in native query, in your case as "assign a value" operator, you should escape all the colon occurances with \\ in your java String with the query, so it could be like:

select B.* from (
  select A.time,A.change,IF(@comp<>A.company,1,0) as LATEST,@comp\\:=A.company as company from (
      select time,company,quote-@quot as `change`, @quot\\:=quote curr_quote
      from stocks order by company,time) A
    order by company,time desc) B where B.LATEST=1;

Update: seems, it is not possible yet to escape the colons in Hibernate native queries, there is an open issue about it. That means, that you are not able to use a colons in Hibernate native queries not for the named parameters. You can try to create a function and call it instead of calling a query.

edited Oct 16 '15 at 05:43

answered Oct 15 '15 at 12:19

Stanislav

27,441
9
87
82

1

when I try to escape : by placing \ compile error happens as double backslash is to escape Java string. so i tried using \\:= Hibernate sees it as single \ and the same exception happens – varghese Oct 16 '15 at 05:29
Yep, if you use hibernate it seems not to be possible to escape the colons in native queries, take a look here https://hibernate.atlassian.net/browse/HHH-1237 The only way you can do it, is to make some sql function and use it. Or try to apply the patch, but you have to build hibernate and it is not a good solution I think – Stanislav Oct 16 '15 at 05:37
Thanks for solving my issue Stanislav – varghese Oct 21 '15 at 07:04
Though this will work on hibernate 5.1.1 and above only – Nikhil Sahu Oct 25 '16 at 09:59

score 0 · Answer 2 · answered Nov 21 '17 at 05:42

0

you can create a named query and then use it in spring jpa repository or hibernate. This link helped me from similar problem.

answered Nov 21 '17 at 05:42

rakesh

4,368
1
19
13

Hibernate exception on encountering mysql := operator

2 Answers2

Linked