CODEWITHSUNDEEP
javajpasql-order-bycriteria-api
kmansoor
kmansoor

Reputation: 4345

JPA Criteria Query API and order by two columns

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.

Upvotes: 73

Views: 144291

Answers (5)

Jorge Santos Neill
Jorge Santos Neill

Reputation: 1785

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

Upvotes: 0

pradeep
pradeep

Reputation: 21

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

Upvotes: 0

Saif
Saif

Reputation: 211

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

Upvotes: 21

levo4ka
levo4ka

Reputation: 2298

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

Upvotes: 104

Adamsan
Adamsan

Reputation: 662

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

Upvotes: 9

Related Questions