JPA Hibernate Postgres precision doesn't work

Question

I am trying to run query below ,Postgres database.

  select new map(avg(cast (speed as double precision)) as avg)
  from table

speed column is of type varchar

I am executing using JPA as below

        em = entityManagerFactory.createEntityManager();        
        Query hqlQuery = em.createQuery(query);
        reportList = hqlQuery.getResultList();

When I run this I get error below

    Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, 
    found 'precision' near line 1

Has anyone come across this situation? Can I not use precision in Hibernate?

That looks like (mostly) SQL, not HQL. If you want to use Hibernate, you need to use the Hibernate query language, which has different type names, etc. — Craig Ringer, Jul 17 '13 at 08:30

score 2 · Answer 1 · edited May 23 '17 at 11:49

If you're trying to execute native SQL in Hibernate via JPA, you need to use em.createNativeQuery.

em.createQuery expects an argument in JPQL, a query language derived from HQL, to the point where Hibernate's JPQL implementation uses the org.hibernate.hql classes.

That doesn't look like valid SQL either, though. new map(...) ? You can't mix JPQL/HQL, and I haven't seen new in an SQL dialect.

score 0 · Answer 2 · answered Jul 17 '13 at 09:14

0

I changed from

  select new map(avg(cast (speed as double precision)) as avg)
  from table

to

      select new map(avg(cast (speed as double)) as avg)
      from table

Hibernate supports that. I am still running as HQL query and it works.

answered Jul 17 '13 at 09:14

Makky

17,117
17
63
86

JPA Hibernate Postgres precision doesn't work

2 Answers2