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.
In both the examples the createAlias() method is very important.If the joined entity is not aliased then the code throws exception:
The createCriteria() method will return a Subcriteria instance (A final inner class).
I tried the same technique for projection based 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)
This is a really awesome blog in shape of tutorial. I like this because this is too much helpful for developers. Thanks for sharing.
ReplyDeleteHey man, can u help me to create a Hibernate Criteria for the SQL query below?
ReplyDeleteSELECT 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