JPA Criteria Query API and order by two columns

Question

I'm stuck with a simple problem; struggling how to invoke order by on a joined entity. Essentially I am trying to achieve the following with JPA Criteria:

select distinct d from Department d 
left join fetch d.children c 
left join fetch c.appointments a
where d.parent is null 
order by d.name, c.name

I have the following:

CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();
CriteriaQuery<Department> c = cb.createQuery(Department.class);
Root<Department> root = c.from(Department.class);
Fetch<Department, Department> childrenFetch = root.fetch(
    Department_.children, JoinType.LEFT);
childrenFetch.fetch(Department_.appointments, JoinType.LEFT);

c.orderBy(cb.asc(root.get(Department_.name)));
c.distinct(true);
c.select(root);
c.where(cb.isNull(root.get(Department_.parent)));

How to achieve order by d.name, c.name with Criteria API? I tried with Expression, Path but didn't work. Any pointers will be greatly appreciated.

score 99 · Answer 1 · answered Dec 31 '14 at 16:45

If you need to add couple of orders you can make something like (but for your query and different root objects)

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Route> query = criteriaBuilder.createQuery(Route.class);
Root<Route> routeRoot = query.from(Route.class);
query.select(routeRoot);

List<Order> orderList = new ArrayList();
query.where(routeRoot.get("owner").in(user));

orderList.add(criteriaBuilder.desc(routeRoot.get("date")));
orderList.add(criteriaBuilder.desc(routeRoot.get("rating")));

query.orderBy(orderList);

score 20 · Answer 2 · edited Jan 29 '18 at 09:41

20

I have the same problem with order by using Criteria API. I found this solution:

CriteriaQuery<Test> q = cb.createQuery(Test.class);
Root<Test> c = q.from(Test.class);
q.select(c);
q.orderBy(cb.asc(c.get("name")), cb.desc(c.get("prenom")));

edited Jan 29 '18 at 09:41

pirho

11,565
12
43
70

answered Jan 29 '18 at 09:36

Saif

201
2
2

score 9 · Answer 3 · answered Dec 15 '13 at 03:13

9

I was having trouble doing the same, and I have found a solution on this page: http://www.objectdb.com/api/java/jpa/criteria/CriteriaQuery/orderBy_Order_

//javax.persistence.criteria.CriteriaQuery
//CriteriaQuery<T> orderBy(Order... o)

Specify the ordering expressions that are used to order the query results. Replaces the previous ordering expressions, if any. If no ordering expressions are specified, the previous ordering, if any, is simply removed, and results will be returned in no particular order. The left-to-right sequence of the ordering expressions determines the precedence, whereby the leftmost has highest precedence.

Parameters: o - zero or more ordering expressions

Returns: the modified query

Since: JPA 2.0

answered Dec 15 '13 at 03:13

Adamsan

662
1
15
27

1

How is this an answer for problem author described? – ByeBye Jan 13 '17 at 07:47
2

Why do you think it doesn't? He wanted to know, how to order by multiple properties. My answer contains how to do that... Am I missing something? – Adamsan Jan 13 '17 at 21:58
Because it will not work with distinct and sorting by property from join table – ByeBye Jan 15 '17 at 03:17

score 0 · Answer 4 · answered Mar 29 '18 at 05:22

0

categoryRepository.findAll(predicates, new Sort(Direction.ASC, "sortOrder", "name")) .forEach(categoryDtoList::add);

answered Mar 29 '18 at 05:22

pradeep

21
1

this can be used to order a list based on both sortOrder and name if the sortOrder is same based on the name it will sort the elements – pradeep Mar 29 '18 at 05:23
This is only for Spring Data users. – Marcel Overdijk Nov 13 '18 at 08:29

score 0 · Answer 5 · answered Mar 31 '19 at 15:42

The solucion that work for me is the following

session=HibernateUtil.getSessionJavaConfigFactory_report().openSession();
CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<Object[]> criteriaQuery = builder.createQuery(Object[].class);
List<Order> orderList = new ArrayList();
orderList.add(builder.desc(ejeRoot.get("name")));
criteriaQuery.orderBy(orderList);

Note: ejeRoot is the class object

JPA Criteria Query API and order by two columns

5 Answers5