Hibernate JOIN ON生成无效查询 [英] Hibernate JOIN ON generates invalid query

问题描述

我需要列出所有Product记录，无论它们是否具有相应的ForeignProductInfo. Product没有引用ForeignProductInfo.

I need to list all Product records whether or not they have a corresponding ForeignProductInfo. Product has no reference to ForeignProductInfo.

我正在使用此JPA查询:

I'm using this JPA query:

@Query("select distinct fpi from Product pp "
        + "left join fetch ForeignProductInfo fpi on fpi.partner.id = :partnerId "
        + "left join fetch fpi.product p ") 
public List<ForeignProductInfo> loadAllForPartner(@Param("partnerId") Long partnerId);

这一直有效，直到我添加第二个联接(避免在访问ForeignProductInfo.product时选择n + 1个).然后生成此SQL:

This works until I add the second join (to avoid n+1 selects when accessing ForeignProductInfo.product). Then it generates this SQL:

SELECT 
    foreignpro1_.id AS id1_7_0_,
    product2_.id AS id1_12_1_,
    productsub3_.id AS id1_18_2_,
    productgro4_.id AS id1_14_3_,
    foreignpro1_.code AS code2_7_0_,
    foreignpro1_.name AS name3_7_0_,
    foreignpro1_.partner_id AS partner_4_7_0_,
    foreignpro1_.product_id AS product_5_7_0_,
    product2_.a AS a2_12_1_,
    product2_.b AS b3_12_1_,
    product2_.comment AS comment4_12_1_,
    product2_.conversionMethod AS conversi5_12_1_,
    product2_.diaMax AS diaMax6_12_1_,
    product2_.diaMin AS diaMin7_12_1_,
    product2_.len AS len8_12_1_,
    product2_.mu AS mu9_12_1_,
    product2_.name AS name10_12_1_,
    product2_.nameLang1 AS nameLan11_12_1_,
    product2_.nameLang2 AS nameLan12_12_1_,
    product2_.productSubGroup_id AS product15_12_1_,
    product2_.surfaceMax AS surface13_12_1_,
    product2_.surfaceMin AS surface14_12_1_,
    productsub3_.name AS name2_18_2_,
    productsub3_.productGroup_id AS productG3_18_2_,
    productgro4_.name AS name2_14_3_,
    productgro4_.seq AS seq3_14_3_
FROM
    Product product0_
        LEFT OUTER JOIN
    ForeignProductInfo foreignpro1_ ON (foreignpro1_.partner_id = 3);

如您所见，第二个JOIN丢失并导致异常:

As you can see, the second JOIN is missing and causes an exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'product2_.id' in 'field list'

ForeignProductInfo对于每个Product和Partner的组合，具有零个或一个记录.

ForeignProductInfo has zero or one record for the combination of each Product and Partner.

@Entity
public class ForeignProductInfo {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Long id;

    @ManyToOne
    private Partner partner;

    @ManyToOne
    private Product product;

    @Column(length=50)
    @Size(max=50)
    private String code;

    @Column(length=200)
    @Size(max=200)
    private String name;
    ...
}

推荐答案

我在3天内无法解决这个问题.这是SQL中最简单的事情，但在JPA中似乎无法实现.还尝试了标准API，但无法进行条件提取，只能进行条件连接.

I couldn't figure this out in 3 days. This is the simplest thing in SQL but seems impossible to achieve in JPA. Also tried the criteria API, but conditional fetching is not possible, only conditional joining.

我最终浏览了Product的列表，并一个一个地加载它们的ForeignProductInfo. :(然后放弃JPA并为此使用普通的SQL.

I ended up walking a list of Product's and loading their ForeignProductInfo one by one. :( Then ditched JPA and used plain SQL for this.

这篇关于Hibernate JOIN ON生成无效查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！