mysql - 不带 on 的 JPA 查询

Question

我尝试在 jpa 中转换此查询我的 sql native 查询。

SELECT *
FROM  tripinfo ti
inner join car c on ti.carid = c.id
left join customerdetail cd on ti.customerid = cd.id
left join station s on ti.fromstxid = s.id
where ti.starttime is null or ti.stoptime is null

我的jpa查询

SELECT ti
FROM TripInfo ti
join ti.car
left join CustomerDetail cd on ti.customer.customerDetail.id = cd.id
left join Station st on ti.fromstxid = st.id

jpa 2.0 中似乎不存在 on 命令。

TripInfo 类

public class tripInfo{
 Long id

 @OneToOne  
 JoinColumn(name = "customerId") //can be null 
 Customer customer;  

 @OneToOne
 @JoinColumn(name = "carid")  
 Car car;

 @Column(name = "tostxid")  //can be null
 Long toStxId;

 @Column(name = "fromstxid")  //can be null
 Long fromStxId;

 @OneToOne(cascade = { CascadeType.ALL })  
 @JoinColumn(name = "fromdp")  
 DP fromDP;

}

客户类别

public class Customer {
  Long id  
  @OneToOne(cascade = { CascadeType.ALL })  
  @JoinColumn(name = "customerDetail_customerid")  
  CustomerDetail customerDetail;
}

DP类

public class DP {
  @OneToOne(cascade = { CascadeType.ALL })
  @JoinColumn(name = "stationFrom")
  Station stationFrom;
}

有绕过 on 命令的想法吗？

Answer 1

JPA 查询中不允许显式“打开”连接，而是由实体中定义的关系定义它们。在您的示例中，要获取客户详细信息，您可以执行如下所示的操作。

SELECT ti
join ti.car
FROM TripInfo ti
left join ti.customer.customerDetail

但是，我没有看到为站点类别定义任何关系。如果您有 Station Class，那么，

 @Column(name = "fromstxid")  //can be null
 Long fromStxId;

应该是，

@Column(name = "fromstxid")
@JoinColumn(name = "id") //Id in Station
Station station;

JPA 查询将是，

SELECT ti
join ti.car
FROM TripInfo ti
left join ti.customer.customerDetail
left join ti.station

希望这有帮助!