如何在JPA中创建一个两列左连接？

Question

有没有办法在JPA中进行左连接的查询，连接的条件是两个列，就像普通SQL中的以下语句一样？

select
    combinedin1_.SHIPMENTNUMBER ,
    shipmentin3_.INBOUNDDELIVERYITEM ,
    combinedin1_.PURCHASEORDERNUMBER,
    combinedin1_.PURCHASEORDERITEMNUMBER ,
    purchaseor2_.FACTORY 
from
    REPORT.REPORTLINK this_
left outer join
    REPORT.COMBINEDINFO combinedin1_
        on this_.COMBINEDINFOID=combinedin1_.COMBINEDINFOID
left outer join
    REPORT.PURCHASEORDERINFO purchaseor2_
        on this_.PURCHASEORDERINFOID=purchaseor2_.PURCHASEORDERINFOID and purchaseor2_.DELETED='N'
left outer join
    REPORT.SHIPMENTINFO shipmentin3_
        on this_.SHIPMENTINFOID=shipmentin3_.SHIPMENTINFOID and shipmentin3_.DELETED='N'

更具体地说，这是在普通SQL中编写的左连接语句：

left outer join
    REPORT.SHIPMENTINFO shipmentin3_
        on this_.SHIPMENTINFOID=shipmentin3_.SHIPMENTINFOID and shipmentin3_.DELETED='N'

这是我在 JPA 中的 reportLink 实体：

@Entity
@Table(name = "REPORTLINK")
public class ReportLinkFull implements Serializable {

private static final long serialVersionUID = -2330676941570524461L;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "REPORTLINKID")
private Integer id;

@ManyToOne
@JoinColumn(name = "SALESORDERINFOID")
private SalesOrderInfo salesOrderInfo;

@ManyToOne
@JoinColumn(name = "PURCHASEORDERINFOID")
private PurchaseOrderInfo purchaseOrderInfo;

@ManyToOne
@JoinColumn(name = "SHIPMENTINFOID")
private ShipmentInfo shipmentInfo;

@ManyToOne
@JoinColumn(name = "MATERIALINFOID")
private MaterialInfo materialInfo;

@ManyToOne
@JoinColumn(name = "CUSTOMERINFOID")
private CustomerInfo customerInfo;

@ManyToOne(cascade={CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REMOVE})
@JoinColumn(name = "COMBINEDINFOID")
private CombinedInfo combinedInfo;

@Version
private Integer version;

问候。

- cremersstijn

---

明天你可以并且应该把你的解决方案作为这个问题的答案发布（之后你可以接受它）。 - oers

Answer 1

我找到了答案，可以使用WITH运算符实现：

em.createQuery("SELECT rl FROM ReportLinkFull rl LEFT OUTER JOIN rl.shipmentInfo sh WITH sh.deleted = 'N' LEFT OUTER JOIN rl.purchaseOrderInfo po WITH po.deleted = 'N'").getResultList();

- cremersstijn