hibernate hql sum case when then else end execution in java problem

Question

I need to execute a HQL query with sum when then else end clause in Java that run with no problem in postgres :

SELECT r.reviewer_id,
  sum(case when c.service_type = 'مالتی مدیا' AND c.genre_id = '66c92962-324c-11e7-93ae-92361f002671' then 1 else 0 end) image_multimedia,
  sum(case when c.service_type is null AND c.genre_id = '66c92b1a-324c-11e7-93ae-92361f002671' then 1 else 0 end) image_null

 FROM module_samim.content c
  Join module_samim.content_reviewer r on c.id = r.content_id
  Join module_samim.file f on f.id = c.file_id
  Group by r.reviewer_id

I tried to run it with createQuery() but I get :

Sep 11, 2018 8:57:20 AM org.hibernate.hql.internal.ast.ErrorCounter reportError
ERROR: line 1:58: expecting "then", found 'مدیاAND'
line 1:58: expecting "then", found 'مدیاAND'
and

Sep 11, 2018 8:57:20 AM org.hibernate.hql.internal.ast.ErrorCounter reportError
ERROR: line 1:264: unexpected token: image_null
Sep 11, 2018 8:57:20 AM org.hibernate.hql.internal.ast.ErrorCounter reportError
ERROR: line 1:264: unexpected token: image_null
line 1:264: unexpected token: image_null

please help me! thank you!

score 0 · Answer 1 · answered Sep 11 '18 at 05:14

0

The syntax seems to be a SQL, not HQL.

Therefore, we need instead of this

createQuery()

to call that

createSqlQuery()

answered Sep 11 '18 at 05:14

Radim Köhler

122,561
47
239
335

I tried it but it does not return list() and with getSingleResult() return nothing, it even does not show an error! just jump to final – Fatemeh Asadi Sep 11 '18 at 05:32
To get more understanding how to use HQL, do read this https://docs.jboss.org/hibernate/orm/3.3/reference/en/html/queryhql.html. And if you would really still need raw SQL, this should help https://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html (there would be need to use some transformers, converting result columns into entity) – Radim Köhler Sep 11 '18 at 05:35

score 0 · Accepted Answer · answered Sep 12 '18 at 11:22

0

I found the solution :)) I used createNativeQuery() and @SqlResultSetMapping explained in this link to solve my problem completely

answered Sep 12 '18 at 11:22

Fatemeh Asadi

83
1
11

score 0 · Answer 3 · answered Sep 12 '18 at 12:04

0

It seems that you are mixing the usage of native query over HQL/JPQL createQuery API

Just to clarify it for you, there are three different ways we can create a SQL query in Hibernate:

   1) session.createQuery()             //Hibernate APi
   2) entityManager.createQuery()       //JPA Api

   3) session.createSQLQuery()          // **** Hibernate API ****
   4) entityManager.createNativeQuery() //JPA API

So if you are using Hibernate, you should user session.createSQLQuery() and if you are using JPA you should use entityManager.createNativeQuery()

answered Sep 12 '18 at 12:04

MohammadReza Alagheband

2,110
13
19

mapping error not related to this question, it means you succeeded in your problem with the current question and now have issue with your mapping entity configuration – MohammadReza Alagheband Sep 15 '18 at 16:44
session.createSQLQuery() deosnot response any way. – Fatemeh Asadi Sep 17 '18 at 09:31

hibernate hql sum case when then else end execution in java problem

3 Answers3