JPA Specification sort by specific value first

user_4041592 0
Viewed 1311

I'm using JPA Specification to retrieve data for my Spring boot app.

I have a mySQL query as below to get the list that I wanted:

select *
from TableA
order by colB = 'C' DESC, colB;

Result: -

no    | colB
3     |  C
1     |  A
2     |  B
4     |  D

My question is, is it possible to convert into JPA Specification? I have already looked around but could not find what I wanted.

     Page<Object> page = tableARepository.findAll(
            (Specification<Object>) (root, criteriaQuery, criteriaBuilder) -> {

                List<Predicate> predicates = new ArrayList<>();

                if (sortConfig.getSpecification() != null) {
                    Predicate predicate = sortConfig.getSpecification().toPredicate(root, criteriaQuery, criteriaBuilder);
                    predicates.add(predicate);
                }
                return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
            },
            sortConfig.getPageable()
    );
spring-data spring-data-jpa jpa spring
2 Answers

There is no equivalent in JPA for this query, since it is vendor specific. What you can do, like crizzis said, is create an ORDER BY CASE clause. For example:

ORDER BY 
CASE
    -- First condition with higher priority
    WHEN priority = 1 AND situation_id = 2 THEN 1

    -- Second condition with less priority
    WHEN situation_id = 1 THEN 2

    -- Third condition
    WHEN priority = 0 AND situation_id = 2 THEN 3

    -- Fourth condition
    WHEN situation_id = 3 THEN 4
END, 
-- Second ordering column
creation_date DESC

And then create a JPQL query with it.

user_50429870

I've not tested, but you may try something like below!

tableARepo.findAllByColBByOrderByColBDesc(col_b_type col_b_parameter)

or maybe

tableARepo.findAllByColBByOrderByColBDesc(String colB)
user_32154370
Related