I have a situation where I want to return an object based on a child of a child with certain property(s). My model looks like this:
Container
ContainerVersion
ContentElement
ContentElement can then be subclassed (joined strategy) and I want to query based on the property of a subclass, ie:
Code:
public class TextElement extends ContentElement {
public String getTextValue();
}
public class DateElement extends ContentElement {
public Date getDateValue();
}
The first time I tried this I actually used 'value' as the property name on both sub-classes and got a ClassCastException as it tried to compare Date with String (which makes sense given the query it's generating).
I can get my query to work but if you see the generated SQL below it is joining to all sub-class types even though I only want to join to the TextElement table. Hopefully the optimiser will figure out that this is unnecessary but still doesn't seem right to me.
What I need is a way to specify in my nested createCriteria call that I want to join only to entities of a given class, ie.
createCritieria("elements", ContentElement.class)
I can't find that anywhere though - can I do this?
Any ideas? I thought about turning the query around and going for the element first and using projection to get to the container, but I don't think that will work with two hops like I have.
Hibernate version: 3.1 and annotations 3.1beta7.
Mapping documents:N/A - using annotations.
Code between sessionFactory.openSession() and session.close():Code:
Session s=getSession();
try {
Criteria c1=s.createCriteria(Container.class);
c1.createCriteria("versions")
.add(Restrictions.eq("isDraft", true))
.createCriteria("elements")
.add(Restrictions.eq("textValue", value))
.add(Restrictions.eq("name", name))
.addOrder(Order.asc("textValue"));
c1.createCriteria("inbound")
.add(Restrictions.eq("containerVersion", cv));
List l=c1.list();
return (List<Container>) l;
} finally {
releaseSession(s);
}
Name and version of the database you are using:SQL Server
The generated SQL (show_sql=true):Code:
select this_.id as id0_4_,
this_.liveVersion_id as liveVers2_0_4_,
this_.draftVersion_id as draftVer3_0_4_,
this_.owner_id as owner4_0_4_,
containerv1_.id as id1_0_,
containerv1_.container_id as container4_1_0_,
containerv1_.is_live as is2_1_0_,
containerv1_.is_draft as is3_1_0_,
contentele2_.id as id3_1_,
contentele2_.name as name3_1_,
contentele2_.container_version_id as container3_3_1_,
contentele2_1_.textValue as textValue4_1_,
contentele2_2_.dateValue as dateValue5_1_,
case
when contentele2_1_.element_id is not null then 1
when contentele2_2_.element_id is not null then 2
when contentele2_.id is not null then 0
end as clazz_1_,
containerc3_.id as id2_2_,
containerc3_.name as name2_2_,
containerc3_.container_version_id as container3_2_2_,
containerc3_.child_container_id as child4_2_2_,
container8_.id as id0_3_,
container8_.liveVersion_id as liveVers2_0_3_,
container8_.draftVersion_id as draftVer3_0_3_,
container8_.owner_id as owner4_0_3_
from container this_
inner join container_version containerv1_ on this_.id=containerv1_.container_id
inner join element contentele2_ on containerv1_.id=contentele2_.container_version_id
left outer join text contentele2_1_ on contentele2_.id=contentele2_1_.element_id
left outer join date contentele2_2_ on contentele2_.id=contentele2_2_.element_id
inner join container_child_link containerc3_ on this_.id=containerc3_.child_container_id
left outer join container container8_ on containerc3_.child_container_id=container8_.id
where containerv1_.is_draft=?
and contentele2_1_.textValue=?
and contentele2_.name=?
and containerc3_.container_version_id=?