CODEWITHSUNDEEP
javajpa
Nomesh DeSilva
Nomesh DeSilva

Reputation: 1649

How to Use CASE, WHEN in JPA

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?

Upvotes: 1

Views: 17755

Answers (2)

Shoniisra
Shoniisra

Reputation: 781

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();
}

Upvotes: 0

xild
xild

Reputation: 187

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

Upvotes: 3

Related Questions