Hibernate version: 3.2.5.ga
I am trying to perform a left join on a one-way one-to-one mapping using the side of the mapping without the reference as the base table. My problem is that I can't seem to figure how to do this in HQL. I am able to do it in SQL, but I would prefer not to.
Here are the relevant mapping annotations:
Code:
@Entity
@Table(schema = "IMMember")
public class MemberTouch extends AutoIdEntity {
@OneToOne(mappedBy = "memberTouch")
public MemberTouchNotification getMemberTouchNotification() {
...
}
@Entity
@Table(schema = "IMMember")
public class MemberTouchNotification extends AutoIdEntity {
@OneToOne(cascade=CascadeType.ALL)
@JoinColumn(name="memberTouchId")
public MemberTouch getMemberTouch() {
...
}
The following code works successfully:
Code:
String sql = "select MT.* "
+ "from IMMember.MemberTouch MT left outer join"
+ " IMMember.MemberTouchNotification MTN"
+ " on MTN.memberTouchId = MT.id "
+ "where MT.groupId = :group"
+ " and MT.memberId = :member"
+ " and MT.type = :type"
+ " and MTN.deleteTimestamp is null "
+ "order by MT.createTimestamp desc";
List<MemberTouch> listOfMemberTouch = this.getSession().createSQLQuery(sql).addEntity(MemberTouch.class)
.setInteger("group", group.getId())
.setInteger("member", member.getId())
.setString("type", type)
.setFirstResult(offset * limit)
.setMaxResults(limit)
.list();
However, I would like to use HQL if at all possible. I tried the following HQL approach:
Code:
String hql = "select MT "
+ "from MemberTouch MT "
+ "where MT.group = :group"
+ " and MT.member = :member"
+ " and MT.type = :type "
+ " and (MT.memberTouchNotification is null"
+ " or MT.memberTouchNotification.deleteTimestamp is null) "
+ "order by MT.createTimestamp desc";
List<MemberTouch> listOfMemberTouch = this.getSession().createQuery(hql).setEntity("group", group)
.setEntity("member", member)
.setString("type", type)
.setFirstResult(offset * limit)
.setMaxResults(limit)
.list();
However, this HQL does not appear to create the correct SQL. Here is what gets created:
Code:
select
membertouc0_.id as id157_,
membertouc0_.classValueId as classVal2_157_,
membertouc0_.classValueName as classVal3_157_,
membertouc0_.createTimestamp as createTi4_157_,
membertouc0_.groupId as group9_157_,
membertouc0_.memberId as memberId157_,
membertouc0_.touchClassValueId as touchCla5_157_,
membertouc0_.touchClassValueName as touchCla6_157_,
membertouc0_.touchMemberId as touchMe10_157_,
membertouc0_.type as type157_
from
IMMember.MemberTouch membertouc0_,
IMMember.MemberTouchNotification membertouc1_
where
membertouc0_.id=membertouc1_.memberTouchId
and membertouc0_.groupId=?
and membertouc0_.memberId=?
and membertouc0_.type=?
and (
membertouc0_.id is null
or membertouc1_.deleteTimestamp is null
)
order by
membertouc0_.createTimestamp desc limit ?
The first member of the created OR clause seems bogus (i.e. membertouc0.id is null).
Any thoughts on how to do left joins on one-to-one mappings that are not bidirectional?
Thanks in advance.
Login
FAQ
Search