Search This Blog

Thursday 21 February 2013

Criteria and Inner joins

Few posts ago we saw HQL's extensive support for joins. I decided to check out Criteria's claims to the same. I decided to start out with Inner joins.
Just to recollect:
The SQL INNER JOIN clause tells the database to only return rows where there is a match found between table1 and table2. An INNER JOIN is most often (but not always) created between the primary key column of one table and the foreign key column of another table.
We saw how querying on associations or selecting data inside associations may result in implicit join queries. I tried the same for Criteria.
public static void testJoinViaAssociationInWhere() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.createAlias("master", "master");
    criteria.add(Restrictions.like("master.data", "master No 1"));
    System.out.println(criteria.list());
}
Here a restriction was applied on association data( other than an id). The result was :
Hibernate: 
    /* criteria query */ 
    select
        this_.ID as ID0_1_,
        this_.NAME as NAME0_1_,
        this_.DATE as DATE0_1_,
        this_.MASTER_ID as MASTER4_0_1_,
        master1_.ID as ID1_0_,
        master1_.DATA as DATA1_0_ 
    from
        ENTITY this_ 
    inner join
        ENTITY_MASTER master1_ 
            on this_.MASTER_ID=master1_.ID 
    where
        master1_.DATA like ?
[[Entity] : ( id 2 , data : entity1 , master.Id : 1 , date : null )], [Entity] :
 ( id 3 , data : entity2 , master.Id : 1 , date : null )]]
Criteria resulted in an explicit inner join. Similarly when we try loading association data in a projection :
public static void testJoinViaAssociationInSelect() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.createAlias("master", "master");
    criteria.add(Restrictions.eq("id", 3));
    criteria.setProjection(
           Projections.projectionList()
               .add(Property.forName("master.data")));
    System.out.println(criteria.list());
}
The generated SQL is :
Hibernate: 
    /* criteria query */ 
    select
        master1_.DATA as y0_ 
    from
        ENTITY this_ 
    inner join
        ENTITY_MASTER master1_ 
            on this_.MASTER_ID=master1_.ID 
    where
        this_.ID=?
[master No 1]
The SQL generated in both cases was an explicit join unlike HQL which used implicit joins for the two cases.
In both the examples the createAlias() method is very important.If the joined entity is not aliased then the code throws exception:
Exception in thread "main" org.hibernate.QueryException: could not resolve prope
rty: master.data of: com.model.Entity
    at org.hibernate.persister.entity.AbstractPropertyMapping.propertyException(Abs
tractPropertyMapping.java:44)
    at org.hibernate.persister.entity.AbstractPropertyMapping.toColumns(AbstractPro
pertyMapping.java:59)
    at org.hibernate.persister.entity.BasicEntityPropertyMapping.toColumns(BasicEnt
ityPropertyMapping.java:31)
    at org.hibernate.persister.entity.AbstractEntityPersister.toColumns(AbstractEnt
ityPersister.java:1350)
The other technique for applying inner joins is to use nested criteria objects.Consider the below method:
public static void testJoinViaAssociationInWhereUsingNestedCriteria() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    Criteria nestedCriteria = criteria.createCriteria("master");
    nestedCriteria.add(Restrictions.like("data", "master No 1"));
    System.out.println(criteria.list());
}
The method is for the same query as testJoinViaAssociationInWhere() method. The generated query is also equivalent. However the technique used here is not alias but to create another criteria instance. The creation of the second criteria for the "master" association results in an inner join between the two tables.
The createCriteria() method will return a Subcriteria instance (A final inner class).
I tried the same technique for projection based inner joins:
public static void testJoinViaAssociationInSelectUsingNestedCriteria() {
    final Session session = sessionFactory.openSession();
    Criteria criteria = session.createCriteria(Entity.class);
    criteria.add(Restrictions.eq("id", 3));

    Criteria nestedCriteria = criteria.createCriteria("master");
    nestedCriteria.setProjection(Projections.projectionList().add(
            Property.forName("data")));
    System.out.println(nestedCriteria.list());
}
This however did not work and resulted in the below exception:
Exception in thread "main" org.hibernate.QueryException: could not resolve prope
rty: data of: com.model.Entity
    at org.hibernate.persister.entity.AbstractPropertyMapping.propertyException(Abs
tractPropertyMapping.java:44)
    at org.hibernate.persister.entity.AbstractPropertyMapping.toType(AbstractProper
tyMapping.java:38)

2 comments:

  1. This is a really awesome blog in shape of tutorial. I like this because this is too much helpful for developers. Thanks for sharing.

    ReplyDelete
  2. Hey man, can u help me to create a Hibernate Criteria for the SQL query below?

    SELECT public.propriedade.nome,
    public.cidade.nome AS cidade_nome,
    public.estado.sigla AS estado_sigla,
    public.talhao.cod_talhao,
    public.porta_enxerto.sigla AS pte_sigla,
    public.variedade.sigla AS var_sigla,
    public.talhao.data_plantio_original,
    public.talhao.esp_linha_original,
    public.talhao.esp_pes_original,
    public.talhao.esp_pes_atual,
    public.talhao.qtd_plantas_original,
    public.talhao.zeroaum,
    public.talhao.umadois,
    public.talhao.doisatres,
    public.talhao.adultas,
    public.talhao.total_plantas,
    public.talhao.replanta,
    public.talhao.area_talhao,
    public.talhao.num_erradicado
    FROM public.talhao
    INNER JOIN public.propriedade ON
    public.talhao.prop_encarregada = public.propriedade.id
    INNER JOIN public.cidade ON
    public.propriedade.id_cidade = public.cidade.id
    INNER JOIN public.estado ON
    public.cidade.estado_id = public.estado.id
    INNER JOIN public.porta_enxerto ON
    public.talhao.ptenxerto_existente = public.porta_enxerto.id
    INNER JOIN public.variedade ON
    public.talhao.variedade_existente = public.variedade.id
    WHERE
    public.propriedade.id = $P{propId}


    thanks

    ReplyDelete