Wednesday, 26 November 2008

Hibernate's Criteria API

Hibernate has a Criteria API which allows you to dynamic build up filter conditions and set various join options. It is perfectly suited for flexible searches on entities, however there are areas in falls down in and the documentation is scarce.
Criteria instances can be either created from the hibernate Session or a DetachedCriteria can be created for later execution or for passing into Spring's HibernateTemplate.

The first thing that is explicitly mentioned is that references to properties can only be one deep, properties on child entities must be mapped via createAlias / createCriteria call. For example, the following will give a "org.hibernate.QueryException: could not resolve property: child.property of: com.example.model.Parent".
DetachedCriteria criteria = DetachedCriteria.forClass(Parent.class,"parent");
criteria.add(Restrictions.eq("parent.child.property", "somevalue");

Instead the child entity must be explicitly join by calling the following:
DetachedCriteria criteria = DetachedCriteria.forClass(Parent.class,"parent");
criteria.createAlias("parent.child","thechild");
criteria.add(Restrictions.eq("thechild.property", "somevalue");

Creating a join is not nesecary, if just the identifier is being referenced:
DetachedCriteria criteria = DetachedCriteria.forClass(Parent.class,"parent");
criteria.add(Restrictions.eq("parent.child.id", 1);

Add an IN condition.
To add a "where field in subquery", two separate criteria are required. One for the main (root) entity, and the other to retrieve the values for the IN list.
The following retrives the orders for which it has an order item which the product has a stock level of ZERO. The result transformer is used to only retrieve one row per entity, instead of having multiple rows as a result of the join on a one-to-many relationship.
DetachedCriteria ids = DetachedCriteria.forClass(ProductStock.class, "stock");
ids.add(Restrictions.eq("stock.stockLevel", 0));
ids.setProjection(Property.forName("productId"));

DetachedCriteria criteria = DetachedCriteria.forClass(Order.class, "order");
criteria.createAlias("order.orderItems", "items", CriteriaSpecification.LEFT_JOIN);
criteria.add(Subqueries.propertyIn("items.productId", ids));
criteria.setResultTransformer(Criteria.ROOT_ENTITY);

Performing a WHERE EXISTS
An exists is a variation on the above query, sometimes useful when the join could be large, or when it simply results in a faster execution.
DetachedCriteria ids = DetachedCriteria.forClass(ProductStock.class, "stock");
ids.add(Restrictions.eqProperty("stock.productId", "items.productId"));
ids.add(Restrictions.eq("stock.stockLevel", 0));

DetachedCriteria criteria = DetachedCriteria.forClass(Order.class, "order");
criteria.createAlias("order.orderItems", "items", CriteriaSpecification.LEFT_JOIN);
criteria.add(Subqueries.exists(ids));

However you cannot do arbitrary joins between 2 unassociated tables. All associations have to be defined in the mapping files before a criteria can be joined (i.e. a createAlias), the IN and EXISTS are the only other alternative.
There are other limitations which I have had to circumvent by either doing extra set mappings or extending Hibernate's classes.

6 comments:

  1. Very good tutorial!! Thanks!

    ReplyDelete
    Replies
    1. This comment has been removed by a blog administrator.

      Delete
  2. Thank you for sharing your experience. Very useful article.

    ReplyDelete
  3. Indeed very useful. However, I'm having trouble chaining Aliases though, any idea if this possible? ie. I want a query that does x.y.z = 1 (in my example it's find profiles where setProfiles.setProfilesId.set.account.accountId = blah). The interweb has lots of examples of one alias but i find no mention of mapping across mulitple tables.

    ReplyDelete
    Replies
    1. Sorry should have said x.y.z.a ... = 1 across multiple tables.

      Delete
  4. Hello my friend, can´t you help me?, I'm trying of translate this sql query with dispatchedCriteria, but I have not been sucessfull. Please I need your help

    select count(cg.id) from comision_generada cg
    where cg.status in(0,2) and cg.Comision in (1,2)
    and
    (
    exists
    (
    select * from comision_generada cg2, nota n, detalle_metodo_de_pago_orden dmo
    where cg2.orden = n.id and dmo.nota = n.id
    and dmo.Metodo_de_pago = 1 and n.status = 1 and cg2.id = cg.id
    )
    or exists
    (
    select * from comision_generada cg3
    where not exists
    (
    select * from registro_pago_de_comision rpc
    where rpc.detalle_de_comision = cg3.id
    and rpc.pago_credito in
    (select pc.id from pago_de_credito pc where rpc.pago_credito = pc.id)
    )
    )
    );

    ReplyDelete