CODEWITHSUNDEEP
javamysqlspringhibernate
varghese
varghese

Reputation: 33

Hibernate exception on encountering mysql := operator

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?

Upvotes: 3

Views: 1576

Answers (2)

rakesh
rakesh

Reputation: 4498

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

Upvotes: 0

Stanislav
Stanislav

Reputation: 28146

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.

Upvotes: 4

Related Questions