java在Hibernate Where子句中如何使用CASE

Question

请举例说明在HQL中使用CASE WHEN

我在代码中使用了以下查询

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";

但当它运行时，会生成以下异常

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: = .....

Answer 1

这一款同样适用于Hibernate 4/JPA和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 

Answer 2

我在一个工作项目中使用了以下查询。可以将其用作模板：）

"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";

Answer 3

请试试这个。希望能成功

            "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";