How to Use CASE, WHEN in JPA

Question

I'm new to JPA and have the below JPA query:

String qry = " SELECT e from Event e"
                    + " WHERE e.meeting.meetingDate= :pdate"
                    + " AND e.meeting.feedId IN (1,2)"
                    + " AND e.status <> 'W'"
                    + " AND e.meeting.status ='A'"
                    + " AND e.settleStatus is NULL"
                    + " AND e.offTime is NULL"
                    + " AND e.eventCode >'07:45:00'"
                    + " group by e.id";

And I need to add the ORDER BY clause dynamically. I use MySQL and please anybody tell me how to add below condition to my query using JPA.

part to add:

ORDER BY
 CASE  WHEN m.country_code ='AU' THEN  e.timestamp_updated               
       WHEN m.country_code <> 'AU' THEN  e.event_code  
END DESC

how to add this query segment in to my JPA query?

create a procedure for it and then call this procedure by entityManager.createNativeQuery(storedProcedure); — SpringLearner, Jul 29 '15 at 13:07
its simple create a stored procedure and after that ping me,i will tell you the next step — SpringLearner, Jul 29 '15 at 13:13

score 3 · Accepted Answer · answered Jul 29 '15 at 14:39

Since JPA 2.0 you can use CASE WHEN in JPQL.

So you just have to do something like that:

ORDER BY 
  CASE 
    WHEN (e.property = ?) THEN 1
    WHEN (e.property = ?) THEN 2
    ELSE 3 
END DESC

You can also use Criteria API

Criteria criteria = session.createCriteria(YourEntity.class);
if(clause){
   criteria.addOrder( Order.asc("property_desired_1") );
}else{
  criteria.addOrder( Order.asc("property_desired_2") );
} 
criteria.list();

You can se more about criteria here, about JPQL CASE WHEN here and about Order here

Although JPA 2.x supports `CASE` in JPQL it is not supported in `ORDER BY` clauses. This might go unnoticed, though, as Hibernate's implementation of JPQL, for instance, does support `CASE` in `ORDER BY`. — Christoph Böhme, Aug 28 '19 at 07:44

How to Use CASE, WHEN in JPA

1 Answers1

Linked