CODEWITHSUNDEEP
hibernatejpajpa-2.0spring-data
user3029929
user3029929

Reputation: 491

Case when expression in JPQL

I am having problem when I implement 'CASE WHEN' condition with JPQL

<dependency>
        <groupId>org.hibernate.javax.persistence</groupId>
        <artifactId>hibernate-jpa-2.0-api</artifactId>
</dependency>


select u 
  from user u
  where u.languageId = case
  when :userLanguageId is null then :managerLanguageID
  else :userLanguageId 
   end

I want to get data based on managerLanguageID if data corresponding userLanguageId is null. So the problem occurs when I use parameter with THEN keyword. but it works fine if I set the parameter manually like: 

select u 
  from user u
  where u.languageId = case
  when :userLanguageId is null then 1
  else :userLanguageId 
   end

btw,.Here is the exception it throws:

Caused by: java.lang.IllegalArgumentException: java.lang.ClassCastException: org.hibernate.hql.internal.ast.tree.ParameterNode cannot be cast to org.hibernate.hql.internal.ast.tree.SelectExpression at org.springframework.data.jpa.repository.query.SimpleJpaQuery.(SimpleJpaQuery.java:73) at org.springframework.data.jpa.repository.query.SimpleJpaQuery.fromQueryAnnotation(SimpleJpaQuery.java:132) at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:114)

Upvotes: 1

Views: 1554

Answers (2)

user3029929
user3029929

Reputation: 491

I got it.. I got the solution.The class cast exception that occured because CaseNode.getDataType requires a SelectExpression. So we need to explicitly cast the dynamic parameter(in integer or string whatever the datatype we need) what we send in our query.,........ CAST (:managerLanguageID as integer)

select u 
  from user u
  where u.languageId = case
  when :userLanguageId is null then CAST (:managerLanguageID as integer)
  else :userLanguageId 
   end

Upvotes: 1

NoDataFound
NoDataFound

Reputation: 11979

Your query seems wrong for me:

select u 
from user u
where u.language_id = case
  when (u.languageId = :userLanguageId is null) then 1
  else :userLanguageId 
end

It should be:

select u 
from user u
where u.languageId = case
  when :userLanguageId is null then 1
  else :userLanguageId 
end

Because:

(1) u.languageId =  :userLanguageId is null 
(2) u.languageId = (:userLanguageId is null)
(3) <long> = <boolean>

Expression (1) is equivalent to (2), and type of (2) are those of (3): you are comparing a long to a boolean, and I don't think Hibernate will accept that.

And, since coalesce exists for that:

select u 
from user u
where u.language_id = coalesce(:userLanguageId, 1)

Upvotes: 1

Related Questions