How to use CASE WHEN in Hibernate Where clause
An example of using Plase for use CASE WHEN
in HQL.
I used the following query in my code.
int receiptNumber = 100;
String hql = "SELECT b FROM OOPExtract as b "
+"WHERE "
+" b.tranStatId =" +receiptNumber+ " AND "
+" b.orderType IN ('EMERGENCY', 'PLENARY', 'PETITION','EXTENSION','MOTION') AND "
+" CASE WHEN b.orderType == 'MOTION' " `enter code here`
+ "THEN "
+" b.status = 'MOTION_SIGNED' "
+" ELSE "
+" b.status LIKE '%%' "
+" END "
+" ORDER BY b.oopExtractId DESC";
But when it runs the following exception is thrown
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: = .....
+3
source to share
3 answers
I used the following query in a working project. Can use it as a template :)
"SELECT "
+ "CASE WHEN smth.status != 'BATMAN' THEN ... "
+ "ELSE (SELECT ... FROM ... WHERE ... ORDER BY ... DESC limit 1) "
+ "END, "
+ "next_field, "
+ "CASE WHEN smth.status == 'BATMAN' THEN ... "
+ "ELSE ... "
+ "END, "
+ "final_field_which_doesent_have_a_case_logic"
+ "FROM ... the_rest_of_the_normal_query";
+1
source to share
Try it. Hope it works.
"SELECT b FROM OOPExtract as b WHERE "
+" b.tranStatId =" +receiptNumber+ " AND "
+" b.orderType IN ('EMERGENCY', 'PLENARY', 'PETITION','EXTENSION','MOTION') AND "
**+" CASE WHEN b.orderType 'MOTION' " `enter code here`
+ "THEN 'MOTION_SIGNED' "
+" ELSE "**
+" b.status LIKE '%%' "
+" END "
+" ORDER BY b.oopExtractId DESC";
0
source to share
This works with Hibernate 4 / JPA and Spring 4!
select
limite
from
Limite limite
join
fetch limite.limiteEnqs enq
join
fetch enq.limiteValors valor
where
limite.id = :limiteId
and :dataReferencia between valor.dtIniVig and valor.dtFimVig
and enq.vrAtributo1 = case limite.atributoId1
when 8 then :produtoId
else enq.vrAtributo1
end
0
source to share