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.

No .. just wanna use my SQL query, but Hibernate marks it as invalid. — Jaanus, Sep 19 '11 at 19:18
Sorry, but you can't use SQL in place of HQL. You have supplied an SQL query. See http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html and try and write your query using valid HQL. Or use a native query. — James DW, Sep 19 '11 at 20:30
@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, Sep 19 '11 at 20:55

score 41 · Answer 1 · edited Aug 15 '12 at 20:32

41

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.

edited Aug 15 '12 at 20:32

nhahtdh

52,949
15
113
149

answered Aug 15 '12 at 14:59

SwampDev

607
5
9

2

This did not work for me with hibernate `3.6.10`. I ended up using `cast(field as integer)` instead. – beerbajay Oct 10 '14 at 13:13
3

It worked for me on hibernate `4.3.11.Final` `sb.append(" NULL\\:\\:DATE AS date, ");` – Mickaël Gauvin Oct 16 '15 at 07:05
Worked for me too with `version: '5.0.12.Final'` – catch22 Sep 03 '18 at 17:49

atrain · Accepted Answer · 2011-09-19T20:34:32.800

4

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

edited Sep 19 '11 at 20:34

answered Sep 19 '11 at 19:50

atrain

8,835
1
33
39

4

Maybe try using CAST instead of ::. See http://stackoverflow.com/questions/4791325/how-do-i-write-hql-query-with-cast – atrain Sep 19 '11 at 20:11

score 3 · Answer 3 · answered Mar 14 '14 at 18:56

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.

answered Mar 14 '14 at 18:56

Jonathas Pacífico

668
9
17

score 0 · Answer 4 · answered Nov 09 '17 at 17:34

0

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

answered Nov 09 '17 at 17:34

carlos.romel

46
3

Looks ok to me but please add some comments as to what was wrong :) – zpontikas Nov 09 '17 at 23:40
SQL ANSI don't describe casts with double collons. Tô solve this, use cast function, instead. – carlos.romel Nov 12 '17 at 17:58

score -1 · Answer 5 · answered Sep 19 '11 at 18:59

-1

Named parameters take a colon ':' like this Is that what you were looking for ?

answered Sep 19 '11 at 18:59

stratwine

3,525
2
24
32

1

Nope...this is actually the problem. I am just trying to use colons in my query, but hibernate thinks that they are parameters. – Jaanus Sep 19 '11 at 19:20

score -1 · Answer 6 · answered Sep 19 '11 at 19:51

-1

You escape : with ::. I think.

Or try a nativequery

answered Sep 19 '11 at 19:51

NimChimpsky

43,542
55
186
295

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

6 Answers6

Linked

Related