ORDER BY using 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 a Criteria

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

I get an exception 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 by"?

What does your Cat class and its mapping look like? – Juha Syrjälä Nov 22 '09 at 21:57 — Juha Syrjälä, Nov 22 '09 at 21:57

score 109 · Accepted Answer · answered Nov 22 '09 at 22:39

109

You need to create an alias for the mother.kind. You do this like so.

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

answered Nov 22 '09 at 22:39

mR_fr0g

7,904
5
33
51

1

session.createCriteria is deprecated – Stepan Yakovenko Apr 15 '17 at 06:40
I'm using Hibernate 5.0.7 and `session.createCriteria` is not deprecated. – carmelolg May 31 '18 at 07:09

Matt Solnit · Answer 2 · 2009-11-22T22:10:23.527

8

It's hard to know for sure without seeing the mappings (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();

edited Nov 22 '09 at 22:10

answered Nov 22 '09 at 22:02

Matt Solnit

28,352
8
50
56

score 7 · Answer 3 · answered Apr 15 '17 at 07:00

This is what you have 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();

Find this appropriate for `Hibernate 5.2`. – Cryptor Oct 05 '17 at 12:06 — Cryptor, Oct 05 '17 at 12:06

score 5 · Answer 4 · answered Sep 17 '12 at 13:54

5

You can add join type as well:

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

answered Sep 17 '12 at 13:54

Dmitri Algazin

3,017
21
25

score 2 · Answer 5 · answered Mar 31 '18 at 15:47

For Hibernate 5.2 and above, use CriteriaBuilder as follows

CriteriaBuilder builder = sessionFactory.getCriteriaBuilder();
CriteriaQuery<Cat> query = builder.createQuery(Cat.class);
Root<Cat> rootCat = query.from(Cat.class);
Join<Cat,Mother> joinMother = rootCat.join("mother");  // <-attribute name
Join<Mother,Kind> joinMotherKind = joinMother.join("kind");
query.select(rootCat).orderBy(builder.asc(joinMotherKind.get("value")));
Query<Cat> q = sessionFactory.getCurrentSession().createQuery(query);
List<Cat> cats = q.getResultList();

ORDER BY using Criteria API

5 Answers5

Linked