Hibernate version:
3.2.1
Mapping documents:
Code:
<hibernate-mapping>
<class
name="model.AuditGroup"
table="nm_audit_group">
<id
name="id"
column="id"
type="java.lang.Long">
<generator class="sequence">
<param name="sequence">nm_audit_group_sequence</param>
</generator>
</id>
<property
name="date"
type="java.util.Date"
update="true"
insert="true"
column="date"
not-null="true"
/>
<set
name="audits"
lazy="true"
cascade="none"
sort="unsorted">
<key column="audit_group_id"/>
<one-to-many class="model.Audit"/>
</set>
</class>
</hibernate-mapping>
<hibernate-mapping>
<class
name="model.Audit"
table="nm_audit">
<id
name="id"
column="id"
type="java.lang.Long">
<generator class="sequence">
<param name="sequence">nm_audit_sequence</param>
</generator>
</id>
<many-to-one
name="auditGroup"
class="model.AuditGroup"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="audit_group_id"
/>
<joined-subclass
name="model.Record"
table="nm_record">
<key column="audit_id"/>
<many-to-one
name="name"
class="model.RecordName"
cascade="all"
outer-join="auto"
update="true"
insert="true"
column="record_name_id"
/>
</joined-subclass>
<joined-subclass
name="model.UserAudit"
table="nm_user_audit">
<key column="audit_id"/>
<many-to-one
name="user"
class="model.User"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="user_id"
not-null="true"
/>
</joined-subclass>
<joined-subclass
name="model.RecordAudit"
table="nm_record_audit">
<key column="audit_id"/>
<many-to-one
name="record"
class="model.Record"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="record_id"
not-null="true"
/>
<joined-subclass
name="model.RecordName"
table="nm_record_name">
<key column="record_audit_id"/>
<property
name="name"
type="java.lang.String"
update="true"
insert="true"
column="name"
not-null="true"
/>
</joined-subclass>
</joined-subclass>
</class>
</hibernate-mapping>
What I am trying to do is use the Criteria API to retrieve all the AuditGroup objects that hava a collection of Audit objects which contain at least one RecordAudit object for a given Record id.
In SQL it looks like this:
Code:
select * from nm_audit_group as ag
inner join nm_audit as a on a.audit_group_id=ag.id
inner join nm_record_audit as ra on ra.audit_id=a.id
where ra.record_id=3;
Which very nicely can be done in a single select query.
The closest I was able to come with the Criteria API is:
Code:
DetachedCriteria dc=DetachedCriteria.forClass(AuditGroup.class);
dc=dc.createCriteria("audits");
dc.add(Restrictions.eq("class",RecordAudit.class));
dc.add(Restrictions.eq("record.id",_recordId));
return(getHibernateTemplate().findByCriteria(dc));
Which doesn't actually work.
I would really like to find a way to create the above SQL statement using the Criteria API. It seems like this should be possible.
*** update
I managed get the query I wanted working using HQL. The following two queries will return all the objects I wanted:
Code:
select distinct ag from AuditGroup ag, RecordAudit ra where ra.auditGroup=ag and ra.record.id=3
Code:
select distinct ag from RecordAudit ra inner join ra.auditGroup ag where ra.record.id=3
Login
FAQ
Search