JPA Criteria Query API and order by null last

Question

My problem is null values must be last order by statement. My code snipshot below. I use javax persistance criteria builder. My query complicated.

import javax.persistence.criteria.CriteriaBuilder;

public Predicate getSomePredicate() {
    Predicate predicate = cb.conjunction();....

    ...predicate.getExpressions().add(cb.and(cb.or(cb.and(v1, v2), cb.and(s1, s2))));

    EOrderByType orderType = EOrderByType.values()[orderBy]
            ;
    switch (orderType) {
    case PRICE: cq.where(predicate).orderBy(cb.asc(root.get("price")));
        break;
    case PRICE_HIGH_TO_LOW: cq.where(predicate).orderBy(cb.desc(root.get("price")));
        break;
    case CONSUPTION: cq.where(predicate).orderBy(cb.desc(root.get("consume")));
        break;
    default:
        break;
    }

    return cq.getRestriction();
}

How to achieve order by price null last with criteria builder ?

Answer 1

Hi I almost search all internet pages and then find a solution, you can write switch case order by part. like below: to order by desc if price is null, price value is 1000000, and to order by asc if price is null, price value is 0. if you want these, you can write expression like below.

                EOrderByType orderType = EOrderByType.values()[orderBy];                    
                Expression<Object> queryCase = cb.selectCase().when(cb.isNull(root.get("price")), 100000000).otherwise(root.get("price"));
                Direction dir = Direction.ASC;

                switch (orderType) {
                    case UCUZDAN_PAHALIYA:
                        queryCase = cb.selectCase().when(cb.isNull(root.get("price")), 100000000).otherwise(root.get("price"));
                        break;
                    case PAHALIDAN_UCUZA:
                        queryCase = cb.selectCase().when(cb.isNull(root.get("price")), 0).otherwise(root.get("price"));
                        dir = Direction.DESC;
                        break;
                }

                  cq.where(predicate).orderBy(direction( cb, queryCase, dir));