Hibernate - createCriteria question

[TerpInMD]

Alright,

I have a parent - child relationship on my hands. I want to query putting a restriction on the child records. No biggie there. The catch is that I only want to load into the parent records those children that match the criteria. I have been getting no where fast on this. Any ideas?:

right now I have something like this:


DetachedCriteria crit = DetachedCriteria.forClass(Parent.class);
crit = crit.createCriteria("childSet").add(Restrictions.eq("childProperty", childPropertyValue);


The query returns the parent records but unfortunatly it has all the child records loaded into the set of each parent which I do not want.

[neerajn]

Alright,

I have a parent - child relationship on my hands. I want to query putting a restriction on the child records. No biggie there. The catch is that I only want to load into the parent records those children that match the criteria. I have been getting no where fast on this. Any ideas?:

right now I have something like this:


DetachedCriteria crit = DetachedCriteria.forClass(Parent.class);
crit = crit.createCriteria("childSet").add(Restrictions.eq("childProperty", childPropertyValue);


The query returns the parent records but unfortunatly it has all the child records loaded into the set of each parent which I do not want.

You should do a fetch on the child records in the criteria which would solve the issue.


DetachedCriteria crit = DetachedCriteria.forClass(Parent.class);
crit.createAlias("childSet", "aliasName").add(Restrictions.eq("aliasName.childProperty", childPropertyValue));

[TerpInMD]

Thanks for the reply but it looks like you left the fetch part out. Would it be like this?



DetachedCriteria crit = DetachedCriteria.forClass(Parent.class);
crit.createAlias("childSet", "aliasName").add(Restrictions.eq("aliasName.childProperty", childPropertyValue))
.setFetchMode("aliasName.childProperty",FetchMode.JOIN);



Thanks again,

[Jörg Heinicke]

I don't think you need the actual fetch but only the alias as neerajn showed it. The fetch is only for eager initialization of childs.

Joerg

[TerpInMD]

Morning,

I am back at this again and I can not get it to work. When I use the code above I am getting duplicate parent objects, one for each child that should be returned (and I am getting the enterie child set still).


DetachedCriteria crit = DetachedCriteria.forClass(ServicerVO.class);

crit = crit.createAlias("servicerFiles", "foo").add(Restrictions.eq("foo.assignedAnalystId", searchCriteria.getEmployeeId()));


I would expect the result to be one parent object loaded with the three child elements that are restricted by the empId like this:


Servicer
- File 1
- File 2
- File 3

Instead I get three parent objects loaded with all five possible child elements:

Servicer
- File 1
- File 2
- File 3
- File 4
- File 5

Servicer
- File 1
- File 2
- File 3
- File 4
- File 5

Servicer
- File 1
- File 2
- File 3
- File 4
- File 5



Parent mapping

<class name="ServicerVO" table="SERVICER">
<id name="servicerId" column="SERVICER_ID">
<generator class="assigned"/>
</id>
<property name="servicerName" column="SERVICER_NAME" />
<set name="servicerFiles" table="TAPE_INFO" lazy="true">
<key column="SERVICER_ID"/>
<one-to-many class="ServicerFile" />
</set>

</class>


child mapping:


<class name="ServicerFile" table="TAPE_INFO">
<id name="fileName" column="TAPE_NAME">
<generator class="assigned"/>
</id>
<property name="servicerId" column="SERVICER_ID"/>
<property name="assignedAnalystId" column="ASSIGNED_ANALYST_ID"/>
...............
</class>




I have spent too long on this but I feel that its a one time sunk learning cost so I am pushing ahead. Any ideas????

[neerajn]

Morning,

I am back at this again and I can not get it to work. When I use the code above I am getting duplicate parent objects, one for each child that should be returned (and I am getting the enterie child set still).


DetachedCriteria crit = DetachedCriteria.forClass(ServicerVO.class);

crit = crit.createAlias("servicerFiles", "foo").add(Restrictions.eq("foo.assignedAnalystId", searchCriteria.getEmployeeId()));


I would expect the result to be one parent object loaded with the three child elements that are restricted by the empId like this:


Servicer
- File 1
- File 2
- File 3

Instead I get three parent objects loaded with all five possible child elements:

Servicer
- File 1
- File 2
- File 3
- File 4
- File 5

Servicer
- File 1
- File 2
- File 3
- File 4
- File 5

Servicer
- File 1
- File 2
- File 3
- File 4
- File 5



Parent mapping

<class name="ServicerVO" table="SERVICER">
<id name="servicerId" column="SERVICER_ID">
<generator class="assigned"/>
</id>
<property name="servicerName" column="SERVICER_NAME" />
<set name="servicerFiles" table="TAPE_INFO" lazy="true">
<key column="SERVICER_ID"/>
<one-to-many class="ServicerFile" />
</set>

</class>


child mapping:


<class name="ServicerFile" table="TAPE_INFO">
<id name="fileName" column="TAPE_NAME">
<generator class="assigned"/>
</id>
<property name="servicerId" column="SERVICER_ID"/>
<property name="assignedAnalystId" column="ASSIGNED_ANALYST_ID"/>
...............
</class>




I have spent too long on this but I feel that its a one time sunk learning cost so I am pushing ahead. Any ideas????


Sorry, I didnt mention before but you will have to add a result transformer to your criteria. Also, to reiterate, the createAlias creates an inner join and fetches the children set at the same time. If you used a FetchMode.JOIN, its for an outer join but considering that you need an alias to query using the child set column as a restriction, Hibernate overrides that and creates an inner join anyways.

The result transformer is as below.


DetachedCriteria crit = DetachedCriteria.forClass(ServicerVO.class);

crit = crit.createAlias("servicerFiles", "foo").add(Restrictions.eq("foo.assignedAnalystId", searchCriteria.getEmployeeId())).setResultTransfor mer(CriteriaSpecification.DISTINCT_ROOT_ENTITY);


This basically gets you the single parent object and all children inside the set which you can access using a getter.

[TerpInMD]

"This basically gets you the single parent object and all children inside the set which you can access using a getter."




Yes, it is doing that unfortunatly the restriction is still not applied to the Children set. I must not be understanding how this is all supposed to work. Am I incorrect in assuming that the child set should be filtered based on the criteria?

[neerajn]

"This basically gets you the single parent object and all children inside the set which you can access using a getter."




Yes, it is doing that unfortunatly the restriction is still not applied to the Children set. I must not be understanding how this is all supposed to work. Am I incorrect in assuming that the child set should be filtered based on the criteria?

I had this same problem and I figured out that its a problem with the inner join that Hibernate generates so if I use an outer join and be able to create an alias for querying on the child column, it only returns a filtered collection.

For this, you will have to use the actual Criteria object of Hibernate which has an option of passing in the type of join for the createAlias() method and for accessing it, you have to use the HibernateCallback option with Spring. I have an example below.


List results1 = getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException {
Criteria criteria = session.createCriteria(Parent.class);
criteria.createAlias("childSet", "child", CriteriaSpecification.LEFT_JOIN)
.add(Restrictions.eq("child.columnName", objectVal))
.setResultTransformer(CriteriaSpecification.DISTIN CT_ROOT_ENTITY);

return criteria.list();
}
});

[TerpInMD]

Haha! That worked! Thank you sir.

I has seen that the Criteria class has more join types available and I was wondering about them.

I do have some work left in incorporating this into my criteria builder. I really appreciate your help.

[neerajn]

Not a problem. I have been using Hibernate quite extensively in my project so have worked with it quite a bit.

[TerpInMD]

What is the difference between createCriteria and createAlias?

[neerajn]

Check out the Associations section under Criteria queries in the Hibernate documentation. The createAlias does not create a main criteria to be fetched for an object. I use the create criteria only on the main object that I want to fetch and use a createAlias whenever I want the associations.

The above section mentions on a way to get back the filtered collection when you use createCriteria as in the example you had before.

[gregd]

This is admittedly a confusing issue regarding the way hibernate treats criteria joins. I tried to find out what exactly happens with the help of the debugger and hibernate.format_sql, hibernate.use_sql_comments settings and came to the following conclusion:
The inner join hibernate uses is correct. The culprit for the whole frustration is the different way hibernate marshals the result into the domain model depending on the type of join used.
When using an alias -defaulting to an inner join- the parent entity that the call returns has the joined collection uninitialized. Hibernate obviously discards the columns related to the collection data and when trying to access the collection another select occurs, generated by the mapping metadata, so it fetches the whole collection.
When CriteriaSpecification.LEFT_JOIN used then the collection was initialized with the correct subset of values.
All non-collection associations were in both cases fetched at once.
What I concluded is that hibernate eager fetches collections whenever an outer join is used but with an inner join only when combined with setFetchMode.
The difference between a left join and setFetchMode is that the latter prevents restrictions at the joined part.

I'm very interrested on any further comments or suggestions on this confusing issue

Regards

Gregoris