ORDER BY using the criteria API

Question

When I write a HQL query

Query q = session.createQuery("SELECT cat from Cat as cat ORDER BY cat.mother.kind.value"); return q.list(); 

Everything is fine. However, when I write the criterion

 Criteria c = session.createCriteria(Cat.class); c.addOrder(Order.asc("mother.kind.value")); return c.list(); 

I get an org.hibernate.QueryException: could not resolve property: kind.value of: my.sample.data.entities.Cat

If I want to use criteria and order, how should I express my "order"?

Answer 1

You need to create an alias for mother.kind . You do it like that.

 Criteria c = session.createCriteria(Cat.class); c.createAlias("mother.kind", "motherKind"); c.addOrder(Order.asc("motherKind.value")); return c.list(); 

Answer 2

It's hard to know for sure without seeing a match (see @Juha's comment), but I think you want something like the following:

 Criteria c = session.createCriteria(Cat.class); Criteria c2 = c.createCriteria("mother"); Criteria c3 = c2.createCriteria("kind"); c3.addOrder(Order.asc("value")); return c.list(); 

Answer 3

You can also add a connection type:

 Criteria c2 = c.createCriteria("mother", "mother", CriteriaSpecification.LEFT_JOIN); Criteria c3 = c2.createCriteria("kind", "kind", CriteriaSpecification.LEFT_JOIN); 

Answer 4

This is what you need to do since sess.createCriteria is deprecated:

 CriteriaBuilder builder = getSession().getCriteriaBuilder(); CriteriaQuery<User> q = builder.createQuery(User.class); Root<User> usr = q.from(User.class); ParameterExpression<String> p = builder.parameter(String.class); q.select(usr).where(builder.like(usr.get("name"),p)) .orderBy(builder.asc(usr.get("name"))); TypedQuery<User> query = getSession().createQuery(q); query.setParameter(p, "%" + Main.filterName + "%"); List<User> list = query.getResultList();