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

Shoniisra · Answer 2 · 2022-06-22T21:28:46.137

This works protty god for me

My NATIVE Sql query

SELECT  
 another_column1, another_column2
CASE
    WHEN property_column = 'AU' then value_column ELSE 0
END AS LEARN,
CASE
    WHEN property_column = 'EU' then value_column ELSE 0
END AS Contribute 
FROM testing.mytablename;

JpaRepository / JPQL Query: returning a List < tuple >

public interface MyTableRepository extends JpaRepository<MyTable, UUID> {
    //c.columnNames are properties of MyTable class
    // take care of spaces and , caracters
    @Query("SELECT c.anotherColumn1, c.anotherColumn2, " +
            "  CASE c.propertyColumn WHEN 'AU' then c.valueColumn ELSE 0 END as learn," +
            "  CASE c.propertyColumn WHEN 'EU' then c.valueColumn ELSE 0 END as transfer " 
            "FROM MyTable AS c ") 
    List<Tuple> selectByPropertyCase();

}

FINALLY, if you want to MAP your query in a DTO Class

public interface MyTableRepository extends JpaRepository<MyTable, UUID> {
    //make sure to create the constructor for the dto classs
    @Query("SELECT SELECT new com.myprojectpath.model.MyTableClassDTO(c.anotherColumn1, c.anotherColumn2, " +
                "  CASE c.propertyColumn WHEN 'AU' then c.valueColumn ELSE 0 END as learn," +
                "  CASE c.propertyColumn WHEN 'EU' then c.valueColumn ELSE 0 END as transfer) " 
                "FROM MyTable AS c ") 
        List<MyTableClassDTO> selectByPropertyCase();
}

How to Use CASE, WHEN in JPA

2 Answers2

Linked