Using Hibernate query : colon gets treated as parameter / escaping colon

Question

return sessionFactory.getCurrentSession().
            createQuery("FROM Weather WHERE city_id = :id AND date " +
                    "BETWEEN now()::date AND now()::date + (:days - 1)").
                    setInteger("id", city_id).setString("days", days).list();

getting error:

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: :

How can I use this syntax in HQL?

Basically the problem is that I want to use colon(:) in my query, but when hibernate sees colon, it thinks that it is a paramter(:parameterName is syntax for parameters in HQL), as you can see from my 2 uses(:id and :days).

But when I am using now()::date statement, it is specific postgreSQL syntax, hibernate ruins everything.

Solution 1

Since you're on Postgres, I would change the date() completely:

return sessionFactory.getCurrentSession().
        createQuery("FROM Weather WHERE city_id = :id AND date " +
                "BETWEEN current_date AND (current_date + (integer :days - 1))").
                setInteger("id", city_id).setString("days", days).list();

See http://www.postgresql.org/docs/8.2/static/functions-datetime.html

Solution 2

I just had this problem, had to use casts, so I tried some stuff to make it work. Turns out you escape : in hibernate with \

However, in java, to print \ to begin with, you have to escape it with \.
So, if you want to put a : in your SQL hibernate query, you have to write it like: \\:

And if you wanted to cast in PostgreSQL, such as in my case, you would have to, for example: field\\:\\:int if you wanted to cast some field as an integer.

Solution 3

Take a look at http://www.postgresql.org/docs/8.1/static/sql-createcast.html

Try using cast. To me it worked like a charm.

Comments

[stratwine]

what are you trying to achieve ? cast ?

[Jaanus]

No .. just wanna use my SQL query, but Hibernate marks it as invalid.

[NimChimpsky]

dup : stackoverflow.com/q/2794972/106261

[James DW]

Sorry, but you can't use SQL in place of HQL. You have supplied an SQL query. See docs.jboss.org/hibernate/core/3.3/reference/en/html/… and try and write your query using valid HQL. Or use a native query.

[Jaanus]

@James : Isn't createSQLQuery used for native queries? I tried that but now getting a little different error : Not all named parameters have been set: [:date]

[Jaanus]

Nope...this is actually the problem. I am just trying to use colons in my query, but hibernate thinks that they are parameters.

[atrain]

Maybe try using CAST instead of ::. See stackoverflow.com/questions/4791325/…

[beerbajay]

This did not work for me with hibernate 3.6.10. I ended up using cast(field as integer) instead.

[Mickaël Gauvin]

It worked for me on hibernate 4.3.11.Final sb.append(" NULL\\:\\:DATE AS date, ");

[zpontikas]

Looks ok to me but please add some comments as to what was wrong :)

[carlos.romel]

SQL ANSI don't describe casts with double collons. Tô solve this, use cast function, instead.

[catch22]

Worked for me too with version: '5.0.12.Final'