Apply Order By in JPA Specifications

Question

I'm writing code with the JPA Specifications:

Snippet1:

Specification.where(withTopics())
        .and(withCar())
        .and(withInsurance())
        // how to add order by

Where each condition method has structure like:

public static Specification<Page> withCar(String type) {
    return new Specification<Page>() {
      @Override
      public Predicate toPredicate(Root<Page> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        return // logic return cb
      }
    };
}

I want to add the the order by conditions.

I've written the order by expressions:

public static Specification<Page> orderByConditions(String input1, String input2) {
    return new Specification<Page>() {
      @Override
      public Predicate toPredicate(Root<Page> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        Expression<Object> cases = cb.selectCase()
                .when(cb.equal(cb.lower(root.get("carn_ame")), inputTopic.toLowerCase()), 1)
                .when(cb.like(cb.lower(root.get("insurance_name")), "%" + inputTitle.toLowerCase() + "%"), 2)
                .otherwise(3);
        Order order = cb.desc(cases);
        cb.createQuery().orderBy(order);
        // what to return
      }
    }
}

What should I return from my orderByConditions(), so I can apply it to my specifications in Snippet1?

But how should I add it to my specifications?

Does this answer your question? [JpaSpecificationExecutor JOIN + ORDER BY in Specification](https://stackoverflow.com/questions/37203878/jpaspecificationexecutor-join-order-by-in-specification) — Simon Martinelli, Apr 29 '20 at 09:29
@SimonMartinelli, I looked into it, but not able to figure out what `criteriaBuilder.equal`should I apply with `criteriaQuery.orderBy`. — reiley, Apr 29 '20 at 09:32

score 2 · Answer 1 · answered Apr 29 '20 at 09:56

2

If you don't have a condition/predicate you can return a "no condition" 1 = 1

return cb.equal(cb.literal(1), 1);

answered Apr 29 '20 at 09:56

Simon Martinelli

34,053
5
48
82

1

Seems a bit hacky. – reiley Apr 29 '20 at 10:17
1

Usually the Specification is used to create the Predicate and not only the order by clause. But this no condition is very common. So the database will remove this anyway. – Simon Martinelli Apr 29 '20 at 11:00
Thanks. I ended up using named query, but this insight will really help me in future. – reiley May 05 '20 at 21:31

Apply Order By in JPA Specifications

1 Answers1