JPA Specification sort by specific value first

Question

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

order by colB = 'C' is not valid. Do you want to do where colB = 'C' or what else? — Simon Martinelli, Jul 07 '20 at 09:11
@SimonMartinelli it is a legit query. Refer https://stackoverflow.com/questions/5417980/mysql-sql-specific-item-to-be-first-and-then-to-sort-the-rest-of-the-items — Chan Chun Weng, Jul 07 '20 at 09:16
But it's not a valid SQL statement in terms of standard. So you cannot transform it into JPA query — Simon Martinelli, Jul 07 '20 at 09:18
@SimonMartinelli I think I didn't state clearly, I was using mySQL query. — Chan Chun Weng, Jul 07 '20 at 09:21
I know. And I say you can't turn your query in JPA Criteria because it's not SQL standard — Simon Martinelli, Jul 07 '20 at 09:37
To convert it to a standard query, `ORDER BY CASE ...` is most likely what you want — crizzis, Jul 07 '20 at 13:20

score 1 · Answer 1 · answered Jul 09 '20 at 20:55

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.

score 0 · Answer 2 · answered Jul 13 '20 at 13:49

0

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)

answered Jul 13 '20 at 13:49

Demobilizer

663
9
12

I tried for you, it's not working – Thomas Pires Apr 21 '22 at 19:51

JPA Specification sort by specific value first

2 Answers2