Hibernate version: 2.1.6
Java Classes:
Code:
public class ILT {
private Long iltID;
private String title;
private String description;
private TopicDO topic;
....
}
public class ILTEnrollment {
private Long iltEnrollmentID;
private User user;
private ILT ilt;
private Date beginDate;
private Date endDate;
private Integer duration;
private String location;
private String comments;
private boolean completed = false;
...
}
Mapping documents:Code:
<hibernate-mapping>
<class
name="com.cadtrain.coachlms.common.dataobject.ilt.ILT"
table="ilt"
proxy="com.cadtrain.coachlms.common.dataobject.ilt.ILT"
dynamic-update="false"
dynamic-insert="false"
>
<id
name="iltID"
column="ilt_id"
type="java.lang.Long"
unsaved-value="null"
>
<generator class="native">
</generator>
</id>
<property
name="title"
type="java.lang.String"
update="true"
insert="true"
column="title"
not-null="true"
unique="false"
/>
<property
name="description"
type="java.lang.String"
update="true"
insert="true"
>
<column
name="description"
sql-type="blob"
/>
</property>
<many-to-one
name="topic"
class="com.cadtrain.coachlms.common.dataobject.TopicDO"
cascade="none"
outer-join="true"
update="true"
insert="true"
column="topic_id"
/>
</class>
</hibernate-mapping>
Code:
<hibernate-mapping>
<class
name="com.cadtrain.coachlms.common.dataobject.ilt.ILTEnrollment"
table="ilt_enrollment"
proxy="com.cadtrain.coachlms.common.dataobject.ilt.ILTEnrollment"
dynamic-update="false"
dynamic-insert="false"
>
<id
name="iltEnrollmentID"
column="iltenrollment_id"
type="java.lang.Long"
unsaved-value="null"
>
<generator class="native">
</generator>
</id>
<many-to-one
name="user"
class="com.cadtrain.coachlms.common.dataobject.User"
cascade="none"
outer-join="true"
update="true"
insert="true"
column="user_id"
/>
<many-to-one
name="ilt"
class="com.cadtrain.coachlms.common.dataobject.ilt.ILT"
cascade="none"
outer-join="true"
update="true"
insert="true"
column="ilt_id"
/>
<property
name="beginDate"
type="java.util.Date"
update="true"
insert="true"
column="begindate"
not-null="false"
unique="false"
/>
<property
name="endDate"
type="java.util.Date"
update="true"
insert="true"
column="enddate"
not-null="false"
unique="false"
/>
<property
name="location"
type="java.lang.String"
update="true"
insert="true"
column="location"
not-null="false"
unique="false"
/>
<property
name="comments"
type="java.lang.String"
update="true"
insert="true"
>
<column
name="comments"
sql-type="blob"
/>
</property>
<property
name="duration"
type="java.lang.Integer"
update="true"
insert="true"
column="duration"
not-null="false"
unique="false"
/>
<property
name="completed"
type="boolean"
update="true"
insert="true"
column="completed"
not-null="true"
unique="false"
/>
</class>
</hibernate-mapping>
Whenever I do reporting in my system I'm frequently faced with a common problem and so far I've just resorted to custom SQL to solve it, however, I have to believe that what I'm trying to do isn't that unusual and that I'm simply missing a common technique.
The example referenced above involves two objects: an ILT (Instructor Led Training) item and an ILTEnrollment. There is a Many-To-One relationship between the ILTEnrollment and the ILT. One report that I generate lists the ILT items and the number of people enrolled in each item. The SQL that I would use to get that list looks something like this:
Code:
select ilt.ilt_id, ilt.title, ilt.description, count(ilt_enrollment.ilt_enrollment_id)
from ilt
left outer join ilt_enrollment on ilt.ilt_id on ilt_enrollment.ilt_id
group by (ilt.ilt_id)
Nothing strange there. I get a list of all of the ILT items as was as a count of the number of ILTEnrollment items associated with each ILT.
For the life of me, I can't figure out how to do the same thing in Hibernate. I tried simply mimicing the SQL int HSQL like:
Code:
select ilt, count(iltEnrollment.iltEnrollmentID)
from ILT as ilt
left outer join ILTEnrollment as iltEnrollment
group by (ilt)
I was pretty sure that wasn't going to work as I've never been able to do arnitrary joins in HSQL, and sure enough, it didn't.
Any suggestions?
-Matt W
Login
FAQ
Search
