2

I have a problem when creating a JPQL query that is derived from a native Oracle SQL query. Some folks told me to use the native query, but I want to leverage the ability of persistence. Here is the native Oracle SQL statement: 

SELECT a.taxnumber, b.compid, b.compnum, 
(case 
    when b.score > 80 then 'LOW RISK' 
    when b.score between 65 and 80 then 'MEDIUM RISK'
    when b.score > 65 then 'HIGH RISK' 
    else null end) status 
FROM registers a, company b

Does anyone know how to convert it into a JPQL query? What I am confused by is the case when statement...

Josh
  • 10,961
  • 11
  • 65
  • 108
faisal_w
  • 21
  • 6
  • possible duplicate of [Is there such thing CASE expression in JPQL?](http://stackoverflow.com/questions/427447/is-there-such-thing-case-expression-in-jpql) – skaffman Jan 20 '11 at 13:40

2 Answers2

1

In JPA 2.0 you can use "CASE WHEN"

http://en.wikibooks.org/wiki/Java_Persistence/JPQL_BNF#New_in_JPA_2.0

Else, you could define a method as this in the entity:

public String getRiskByScore(){
   String risk = null;
   if(this.score > 80) risk = "LOW RISK";
   else if ....
   return risk;
}

JPQL Query:

SELECT a.taxnumber, b.compid, b.compnum, b.score 
FROM registers a, company b

Then, you would call getRiskByScore()

Ahmad Hosny
  • 597
  • 1
  • 6
  • 23
pablo pidal
  • 349
  • 4
  • 4
1

What is your object model?

You could just define a Company class the maps to the company table. Instead of doing the case if SQL, just map the score column either using an @Convert in EclipseLink, or just map it directly and have methods in your object model called getRisk() that returns a Enum basd on the value. (i.e. but the logic in your object model, not in SQL).

You could use the native SQL query in JPA if you really wanted to, or even use the CASE function in JPQL, but putting the logic in your object model would be the best design.

James
  • 17,965
  • 11
  • 91
  • 146
  • I've try to generate the Company and Registers entity with Netbeans(auto),but that solution didn't pass on my mind. So it's best that if i using Enum value in entity,isnt it?ok,i'll try it...
    thank you very much,James!     – faisal_w Jan 21 '11 at 02:13