I have a query

select * from docs
where status_id in ('105', '55', '65', '228016')
order by
case status_id
    when '55' then 1
    when '228016' then 2
    when '65' then 3
    when '105' then 4
end;

How to rewrite it in spring data with specifications?

p2 = repository.findAll(new DocsRepository.DocsSpec(filter, type), 
PageRequest.of(page, size, Sort.by(Sort.Order.desc("status"))));

CodePudding user response：

This answer helped me

1. Created OrderByField class

    import javax.persistence.criteria.Expression;
    import javax.persistence.criteria.Order;
   
    public class OrderByField implements Order
    {
        private Expression<?> expression;
   
        public OrderByField(Expression<?> expression)
        {
            this.expression = expression;
        }
   
        @Override
        public Order reverse()
        {
            return null;
        }
   
        @Override
        public boolean isAscending()
        {
            return true;
        }
   
        @Override
        public Expression<?> getExpression()
        {
            return expression;
        }
    }
   

2. Created expression with criteriaBuilder's selectCase function

    Expression searchedCaseExpression = criteriaBuilder.selectCase()
            .when(criteriaBuilder.equal(root.get("status").get("code"), "55"), "1")
            .when(criteriaBuilder.equal(root.get("status").get("code"), "228016"), "2")
            .when(criteriaBuilder.equal(root.get("status").get("code"), "65"), "3")
            .when(criteriaBuilder.equal(root.get("status").get("code"), "105"), "4");
   
            OrderByField order = new OrderByField(searchedCaseExpression);
            criteriaQuery.orderBy(order);
   

And don't forget to remove Sort.by from findAll(), since we're sorting with criteriaBuilder

    p2 = repository.findAll(new DocsRepository.DocsSpec(filter, type), PageRequest.of(page, size));

And one more thing, 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