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.

Answer 1

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