mysqlhibernatejpaspring-data-jpajpa-criteria

How to Order By field List names in Jpa repository

I need to order my selected items by status name sequence. (active <- inactive).

I wrote its SQL query correctly as follows. I need to convert it into jpa repository. please help me.

select * from device
 where status in ('active', 'inactive')
order 
    by field(status,'active', 'inactive')

enter image description here

Solution

  • You will have to use a case when expression i.e. case status when 'active' then 0 when 'inactive' then 1 else null end. With JPA Criteria this looks roughly like this:

    CriteriaBuilder.SimpleCase<String> caseExprssion = cb.selectCase(root.get("status"));
caseExprssion.when(cb.literal("active"), cb.literal(0));
caseExprssion.when(cb.literal("inactive"), cb.literal(1));
caseExprssion.otherwise(cb.nullLiteral(Integer.class));
criteriaQuery.orderBy(caseExpression);