Criteria api - retrieve only root entity

dcmoore · **Joined:** Tue Jan 15, 2008 9:31 pm **Posts:** 2

According to Java Persistence with Hibernate, in HQL if you do: "from Item i join i.bids b" it will return all combinations of of bids and items. If you do : "select i from Item i join i.bids b", it will return just the items.

I'm using the criteria API and I'm only interested in the root entities. By default, it's returning the combinations (like items and bids-per first hql example above) and I am only intersted in items, like in the second hql example above. I want to cut down on the traffic coming back from the server, so is there a way to have it only generate sql to pull back the root entity? Right now, if I do a join to reduce my rows, I wind up increasing my columns, dramatically.

Thanks!

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

dcmoore,

You have to create a Criteria and then apply to it a transformation with .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
That returns you an object tree with only one root element per row.

dcmoore · **Joined:** Tue Jan 15, 2008 9:31 pm **Posts:** 2

I'm doing that currently, but I still have all that traffic coming down from the server. I'd like for it to generate the query in such a way as to not bring that all down.

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

it is unclear what your problem is.
You say that you use a join "to reduce the number of rows".
You say that you are already getting a distinct root, so I don't understand what the problem is exactly.

schauder · **Joined:** Thu Jul 05, 2007 9:38 am **Posts:** 287

There are two ways to represent a join in criteria api.

You are probably using

createAlias
http://www.hibernate.org/hib_docs/v3/ap ... ing,%20int)

if you use createCriteria()
http://www.hibernate.org/hib_docs/v3/ap ... ang.String)

it will create a join, but it will not include the columns of the joined table in the result.

gribo · **Joined:** Tue Feb 22, 2005 5:27 am **Posts:** 10

Quote:

if you use createCriteria()
http://www.hibernate.org/hib_docs/v3/ap ... ang.String)

it will create a join, but it will not include the columns of the joined table in the result.

Thank's but it seems not working for me

Here's my example

Code:

Criteria crit = hibernateTemplate.getSessionFactory().getCurrentSession().createCriteria(Candidature.class,"candidature");
crit.setResultTransformer(Criteria.ROOT_ENTITY);
crit.createCriteria("campagne").add(Restrictions.like("reference", "%CG%"));
      
List<Candidature> resultat = crit.list();

OK, my result contains Candidature BUT the generated SQL is

Code:

select this_.CDT_ID, all my columns for Candidature.class [...],
 campagne1_.CMP_ID as CMP1_2_0_, all the colum ofcampagne that i don't want !
from candidatures this_ inner join campagnes campagne1_ on this_.CMP_ID=campagne1_.CMP_ID 
where campagne1_.CMP_REFERENCE like ?

And it's bad because of performance issue, I don't want all the colum of the Campagne table to be return as I don't need them !

What I need is something that works in HQL

select candidature from Candidature candidature join candidature.campagne campagne where campagne.reference like :ref

But I have to use the Criteria API because of dynamic restrictions...

Thanks for you answer

Hibernate version 3.2.6GA

schauder · **Joined:** Thu Jul 05, 2007 9:38 am **Posts:** 287

I don't know where I got the ideas from my previous post from ...

Regarding your problem:

You could try to make a sql restriction using a subselect.

Just an idea
Jens