当有order by子句时，openJPA外连接在可选的多对一中 [英] openJPA outer join on optional many-to-one when have order by clause

问题描述

我有一种情况，我希望生成的SQL始终对可选的ManyToOne使用外部联接. 我正在使用OpenJPA2.2.1.x.

I have a scenario, that I would like the generated SQL always using Outer Join for optional ManyToOne. I am using OpenJPA 2.2.1.x.

两个实体:事件和规则，事件与规则具有可选的ManyToOne单向关系.以下是两个实体类:

Two entities: Event and Rule, Event have an optional ManyToOne unidirectional relationship to Rule. Following are the two entity classes:

@Entity 
public class Event { 
    @Id 
    private String uuid; 

    private Date eventTime; 

    @ManyToOne(optional=true) 
    private Rule rule; 
} 

@Entity 
public class Rule { 
    @Id 
    private String uuid; 

    private int rowNum; 
} 

如果在JPQL中我对事件属性(例如eventTime)使用ORDER BY，它将在事件和规则表之间生成LEFT OUTER JOIN. JPQL:

If in JPQL I use ORDER BY on Event attribute(for ex, eventTime), it generates LEFT OUTER JOIN between event and rule table. JPQL:

SELECT e from Event e order by e.eventTime desc 

SQL:

SELECT t0.uuid, t0.eventTime, t1.uuid, t1.rowNum FROM Event t0 LEFT OUTER JOIN Rule t1 ON t0.RULE_UUID = t1.uuid ORDER BY t0.eventTime DESC 

如果在JPQL中我在Rule属性(例如，rowNum)上使用ORDER BY，它将在事件和规则表之间生成INNER JOIN. JPQL:

If in JPQL I use ORDER BY on Rule attribute(for ex, rowNum), it generates INNER JOIN between event and rule table. JPQL:

SELECT e from Event e order by order by e.rule.rowNum asc 

SQL:

SELECT t0.uuid, t0.eventTime, t1.uuid, t1.rowNum FROM Event t0 INNER JOIN Rule t1 ON t0.RULE_UUID = t1.uuid ORDER BY t1.rowNum ASC 

我的问题是:

1. 这是基于不同的ORDER BY子句，生成两种不同类型的JOIN的正确行为吗?
2. 除了使用本机SQL之外，有什么方法可以使openJPA始终生成OUTER JOIN? (我尝试过LEFT OUTER JOIN FETCH，它没有帮助:-().

谢谢，

刘大卫(David Liu)

David Liu

推荐答案

使用LEFT JOIN可以实现目标.

Using LEFT JOIN could achieve the goal.

SELECT e FROM  Event e LEFT JOIN e.rule r  ORDER BY r.rowNum ASC

这将生成以下SQL:

SELECT t0.uuid, t0.eventTime, t2.uuid, t2.rowNum, t1.rowNum FROM Event t0 LEFT OUTER JOIN Rule t1 ON t0.RULE_UUID = t1.uuid LEFT OUTER JOIN Rule t2 ON t0.RULE_UUID = t2.uuid ORDER BY t1.rowNum ASC

JPQL中需要注意一些事项:ORDER BY必须应用于r.rowNum而不是e.rule.rowNum，否则，它将生成一个INNER JOIN.

There is something in the JPQL need to pay attention: the ORDER BY must be applied to r.rowNum rather than e.rule.rowNum, otherwise, it will generate an INNER JOIN.

您可能已经注意到，所生成的SQL在RULE表上具有两个OUTER JOIN.

You may have noticed that the generated SQL have two OUTER JOINs on RULE table.

这篇关于当有order by子句时，openJPA外连接在可选的多对一中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！