I am having a bit of trouble trying to get a JPA query to work. I am using the toplink essentials as the entity manager.
Some background:
I have 2 database tables, Member, and ClaimLine, and 2 corresponding classes, with the proper entities mapped. A member can have 0 to x claimLines, each claimLine has a specific date associated with it.
@Entity
@Table(name="Member")
public class Member {
private Collection<ClaimLine> claimLines;
@OneToMany(cascade=CascadeType.ALL, mappedBy="member", fetch=FetchType.EAGER)
public Collection<ClaimLine> getClaimLines(){
return this.claimLines;
}
....
}
@Entity
@Table(name="ClaimLine")
public class ClaimLine {
private Member member;
@ManyToOne()
@JoinColumn(name="memberID", insertable=false, updatable=false)
public Member getMember(){
return member;
}
@Column(name="dateTo")
public Date getDateTo(){
return this.dateTo;
}
...
}
As you can see, a member has a collection of claims. What I am trying to do is get all members and their corresponding claims that are within a certain time period and members that have no claims at all in the time period. With the following query, the member objects that are populated contain all claims for a member if they have at least 1 claim in the time period.
Below is the SQL query that returns what I want, I am not sure what I am doing incorrectly in the JPA query.
SQL Query:
select * from Member m LEFT OUTER JOIN
(select * from ClaimLine c where c.dateTo between '1/1/2001' and '6/30/2001') q
on m.memberID = q.memberID
JPA Query:
Query q = em.createQuery("select distinct m from Member m LEFT JOIN m.claimLines c where (c.dateTo between :startDate and :endDate) or m.claimLines is empty")
Thanks.
Eric
[Message sent by forum member 'epenetar' (epenetar)]
http://forums.java.net/jive/thread.jspa?messageID=252986