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.
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;
}
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
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
My questions are:
- Is this the correct behaviour that based on different ORDER BY clause, two different types of JOIN are generated?
- Is there any way we can make openJPA always generate OUTER JOIN, other than use native SQL? (I tried LEFT OUTER JOIN FETCH, it doesn't help :-().
Thanks in advance,
David Liu