Search code examples
javahibernatecriteria

Complex query using Criteria API in Hibernate 4

I am using hibernate 4 with criteria API and I am facing a complex query.

My relational model is the following:

relational model

What I am trying to do is to get for a given Person all the shipped Articles that belongs to the "n" last ShoppingEvent before a specified date.

How can I achieve that using criteria API?

n.b. I already tried something like:

ProjectionList properties = Projections.projectionList();
properties.add(Projections.property("article.articleId"), "articleId");
properties.add(Projections.property("article.price"), "price");
properties.add(Projections.property("article.type"), "type");

return session.createCriteria(Person.class)//
         .add(Restrictions.idEq(person.getPersonId()))//
         .createAlias("articles", "article")//
         .createAlias("article.shoppingEvent", "se")//
         .add(Restrictions.le("se.date", currentDate))//
         .addOrder(Order.desc("se.date"))//
         .setProjection(properties)//
         .setResultTransformer(Transformers.aliasToBean(Articles.class))//
         .list();

Which returns the articles I want but I don't succeed to use the setMaxResults to limit the max number of ShoppingEvents.

Solution

  • You might want to try a DetachedCriteria, have it return the ids of your "n" last ShoppingEvent and then in your criteria you add a restriction that your article.shoppingEvent must match the list of ids from your detachedCriteria.