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

hibernate 5 hql query example
hql select query with where clause
hibernate select query example in java
hql vs jpql
hql insert query with parameters
hibernate sql query example
hql update query
update query in hibernate with where clause

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!

The syntax seems to be a SQL, not HQL.

Therefore, we need instead of this

createQuery()

to call that

createSqlQuery()

Hibernate Community • View topic, Board index » Hibernate & Java Persistence » Hibernate Users Post subject: sum(case when then else end) not working. Post select e.name, sum(case e.​gender = 'M' then 1 else 0 end), sum(case when e.address is null then 1 else 0 end) from SQL : Query fired after execution of above HQL query I am trying to use 'case when then' in a sql select, but the parser mistakes it for an alias. I see in the release notes for 3.0.4 (23.5.2005) that this should be possible: * [HHH-467] - HQL: support for case when then else end IN select clause

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

Beginning Hibernate: From Novice to Professional, run a projection query on the products in the database that only returned the array represents one set of properties (in this case, a name and price pair). when <logical expression> then <unary expression> else _<unary expression> end 0  With the exception of names of Java classes and properties, queries are case-insensitive. So SeLeCT is the same as sELEct is the same as SELECT, but org.hibernate.eg.FOO is not org.hibernate.eg.Foo, and foo.barSet is not foo.BARSET. This manual uses lowercase HQL keywords.

Chapter 16. HQL: The Hibernate Query Language, end , and "searched" case, case when then else end. string concatenation || or concat(,). Hibernate Community Forums. Hi, case when is working for me in select clause. please try after removing the '=' symbol in when

Chapter 11. HQL and JPQL, The Hibernate Query Language (HQL) and Java Persistence Query With the exception of names of Java classes and properties, queries are case-insensitive. result in multiple actual SQL statements being executed (for joined-subclass, for example). nickName else '<no first name>' end from Customer c // Again, the  EJB3-QL has been heavily inspired by HQL, the native Hibernate Query Language. Both are therefore very close to SQL, but portable and independent of the database schema. People familiar with HQL shouldn't have any problem using EJB-QL. Actually, you use the same query API for EJB-QL and HQL queries.

Hibernate HQL, In this HQL tutorial, learn what is hibernate query language, hql syntax for various HQL is a superset of the JPQL, the Java Persistence Query Language. A JPQL and must be used when property names in the query would otherwise be ambiguous. You will especially run into problems with native SQL on classes with  Hibernate version: 3.1 "Select case when then else end " statement works fine unless I add another query in the else clause. Like below: select p.category, CASE WHEN p.icrossingTaxonomy.icCategoryId is not null THEN 0 else (select count(*) from Products p1 where p.category = p1.category and p1.icrossingTaxonomy.icCategoryId is null ) end as

Possible bug using CASE WHEN on GROUP BY clause · Issue , Having problem when I create a SELECT query with CASE WHEN and use this same CASE on a If I run this SQL on the database, it runs successfully. then ? when person0_.pes_st<>? then ? else 'O' end 2017-08-29 21:09:10 WARN QueryImpl.list(QueryImpl.java:103) at com.querydsl.jpa.hibernate. java,mysql,hibernate,java-ee,struts2 You can simply create an Entity, that's mapping the database view: @Entity public class CustInfo { private String custMobile; private String profession; private String companyName; private Double annualIncome; } Make sure you include an @Id in your view as well, if that's an updatable view.

Comments
  • 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
  • To get more understanding how to use HQL, do read this docs.jboss.org/hibernate/orm/3.3/reference/en/html/…. And if you would really still need raw SQL, this should help docs.jboss.org/hibernate/orm/3.3/reference/en/html/… (there would be need to use some transformers, converting result columns into entity)
  • 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
  • session.createSQLQuery() deosnot response any way.