开发者

HQL right outer join returning partial result with InheritanceType.SINGLE_TABLE

I have the following HQL query:

    entityManager.createQuery("Select customer FROM VisitEntry 
visitEntry RIGHT OUTER JOIN visitEntry.customer customer
 GROUP BY customer ORDER BY max(visitEntry.date) desc").getResultList();

These are my objects:

@Inheritance(strategy=InheritanceType.SINGLE_TABLE)
public class LogEntry implements Comparable<LogEntry> {
...    
    @Column(name="LOGENTRY_DATE")
    public Calendar getDate() {
        return date;
    }

    public void setDate(Calendar calendar) {
        this.date = calendar;
    }
    @Column(name="LOGENTRY_TEXT")
    public String getText() {
        return text;
    }
    public void setText(String text) {
        this.text = text;
    }

    @OneToOne
    public Customer getCustomer() {
        return customer;
    }
    public void setCustomer(Customer member) {
        this.customer = member;
    }

...
}

And the Customer:

@Entity
@Table(name="CUSTOMER")
public class Customer {

    private Integer id;
    @Id
    @GeneratedValue
    @Column(name="ID")
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }

}

This translates into following SQL:

   select * from LOGENTRY visitentry0_ right outer join 
CUSTOMER customer1_ on visitentry0_.customer_ID=customer1_.ID 
where visitentry0_.DTYPE in ('VisitEntry', 'TimeVisitEntry', 
'PpvVisitEntry', 'InactiveVisitEntry') group by customer1_.ID
 order by max(visitentry0_.LOGENTRY_DATE) desc

I do get all results when I use this SQL query directly on the database:

SELECT CUSTOMER.MEMBER_FIRSTNAME, MAX(LOGENTRY_DATE) FROM LOGENTRY 
RIGHT JOIN CUSTOMER ON LOGENTRY.CUSTOMER_ID = CUSTOMER.ID 
GROUP BY CUSTOMER.ID ORDER BY MAX(LOGENTRY_DATE) DESC

So even though these queries are nearly identical only the latter gives me back the right result. The first query only returns customers which have an associated LOGENTRY, the second query returns all customers (even if they have no LOGENTRY associated to them).

Result returned by SQL query (correct):

Matthias    2011-09-22 22:31:38
Christophe  2011-09-22 22:24:03
Patrick Leander 2011-09-21 20:47:49
Thomas  2011-09-21 20:19:09
Ricky   (null)
Glenn Gunther   (null)
开发者_如何学JAVA

Result returned by HQL query (incorrect):

Matthias    2011-09-22 22:31:38
Christophe  2011-09-22 22:24:03
Patrick Leander 2011-09-21 20:47:49
Thomas  2011-09-21 20:19:09


The different result our due to the fact that on the sql query you are using a "right join" and on the HSQL one you are using a "right outer join" change appropiately to get the desired result.


Can't believe I missed this but the issue is actually this clause in the SQL query that is generated from the HQL query:

where visitentry0_.DTYPE in ('VisitEntry', 'TimeVisitEntry', 
'PpvVisitEntry', 'InactiveVisitEntry') 

Problem is that I want the result of the query to return customers without any visitentries, but that clause in the query (which is generated by hibernate) doesn't return visitentrys with type NULL.

This is the SQL that I hibernate should generate for me to return all results:

where visitentry0_.DTYPE in ('VisitEntry', 'TimeVisitEntry', 
    'PpvVisitEntry', 'InactiveVisitEntry') OR visitentry0_ IS NULL

For me this seems like a flaw in hibernate because this prevents that a left or right join returns the correct results.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜