Hibernate criteria query that uses primary key of a related entity to filter results

Question

In our system our database looks like this

One agency has many brokerages, one brokerage has many users, one user has many orders.

I am trying to use the hibernate criteria to find all orders for users under a specified brokerage. In plain SQL this would be very easy. Can I do this in the criteria api? I find the criteria api works especially well when having click and filter type uis as you can build a non-ordered list of restrictions and add them to the criteria.

Currently I have this which does not work

final Session session = (Session) entityManager.getDelegate();
    final Brokerage brokerage = userAdmin.loadBrokerage(brokerageId);
    @SuppressWarnings({"UnnecessaryLocalVariable", "unchecked"})
    final List<UserOrder> userOrders = session
            .createCriteria(UserOrder.class)
            .add(Restrictions.eq("user.brokerage", brokerage))
            .list();

I get the message "Caused by: org.hibernate.QueryException: could not resolve property: user.brokerage of: com.printlogix.rp.server.domain.UserOrder:".

Can you pass param names into Restrictions.eq like "object.parentobject.property"?

Answer 1

You can't chain associations like you would do in HQL. Instead, you need to define a sub-criteria or an alias:

Criteria c = session.createCriteria(UserOrder.class);
Criteria userCriteria = c.createCriteria("user");
userCriteria.add(Restrictions.eq("brokerage", brokerage));

or

Criteria c = session.createCriteria(UserOrder.class);
c.createAlias("user", "user");
c.add(Restrictions.eq("user.brokerage", brokerage));

This is documented in Criteria's javadoc and in the Hibernate reference documentation.