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
– M. Deinum, Commented Oct 15, 2015 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
– M. Deinum, Commented Oct 15, 2015 at 11:39
Please see the query execution in sqlfiddle.com/#!2/4f8a1/2 This is used to get difference between rows for a particular column — varghese
– varghese, Commented Oct 15, 2015 at 12:43
try escaping the : characters by placing a `\` before them — javaguest
– javaguest, Commented Oct 15, 2015 at 12:53

Stanislav · Accepted Answer · 2015-10-16 05:43:39Z

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, 2015 at 5:43

answered Oct 15, 2015 at 12:19

Stanislav

28.2k9 gold badges91 silver badges84 bronze badges

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

4 Comments

varghese Over a year ago

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

Stanislav Over a year ago

Yep, if you use hibernate it seems not to be possible to escape the colons in native queries, take a look here 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

varghese Over a year ago

Thanks for solving my issue Stanislav

Nikhil Sahu Over a year ago

Though this will work on hibernate 5.1.1 and above only

rakesh · Accepted Answer · 2017-11-21 05:42:37Z

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, 2017 at 5:42

rakesh

4,5181 gold badge21 silver badges13 bronze badges

Collectives™ on Stack Overflow

Hibernate exception on encountering mysql := operator

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related