java QueryDSL、Hibernate、JPA——使用。fetchJoin（）并在first SELECT中获取数据，那么为什么要在之后进行N+1查询呢？

Question

我试图查询一个实体列表（MyOrder），这些实体映射到几个简单的子实体：每个MyOrder与一个Store、零个或多个Transaction关联，最多一个Tender。生成的SELECT看起来是正确的——它从所有四个连接的表中检索所有列——但之后，对每个MyOrder执行两个选择，一个用于Transaction和一个用于Tender

我使用的是QueryDSL 4.1.3、Spring Data 1.12、JPA 2.1和Hibernate 5.2

在QueryDSL中，我的查询是：

... = new JPAQuery<MyOrder>(entityManager)
    .from(qMyOrder)
    .where(predicates)
    .join(qMyOrder.store).fetchJoin()
    .leftJoin(qMyOrder.transactions).fetchJoin()
    .leftJoin(qMyOrder.tender).fetchJoin()
    .orderBy(qMyOrder.orderId.asc())
    .transform(GroupBy
        .groupBy(qMyOrder.orderId)
        .list(qMyOrder));

其执行方式如下：

SELECT myorder0_.ord_id AS col_0_0_,
    myorder0_.ord_id AS col_1_0_,
    store1_.sto_id AS sto_id1_56_1_, -- store's PK
    transactions3_.trn_no AS trn_no1_61_2_, -- transaction's PK
    tender4_.tender_id AS pos_trn_1_48_3_, -- tender's PK
    myorder0_.ord_id AS ord_id1_39_0_,
    myorder0_.app_name AS app_name3_39_0_, -- {app_name, ord_num} is unique
    myorder0_.ord_num AS ord_num8_39_0_,
    myorder0_.sto_id AS sto_id17_39_0_,
    store1_.division_num AS div_nu2_56_1_,
    store1_.store_num AS store_nu29_56_1_,
    transactions3_.trn_cd AS trn_cd18_61_2_,
    tx2myOrder2_.app_name AS app_name3_7_0__, -- join table
    tx2myOrder2_.ord_no AS ord_no6_7_0__,
    tx2myOrder2_.trn_no AS trn_no1_7_0__,
    tender4_.app_name AS app_name2_48_3_,
    tender4_.ord_num AS ord_num5_48_3_,
    tender4_.tender_cd AS tender_cd_7_48_3_,
FROM data.MY_ORDER myorder0_
INNER JOIN data.STORE store1_ ON myorder0_.sto_id=store1_.sto_id
LEFT OUTER JOIN data.TX_to_MY_ORDER tx2myOrder2_
    ON myorder0_.app_name=tx2myOrder2_.app_name
    AND myorder0_.ord_num=tx2myOrder2_.ord_no
LEFT OUTER JOIN data.TRANSACTION transactions3_ ON tx2myOrder2_.trn_no=transactions3_.trn_no
LEFT OUTER JOIN data.TENDER tender4_
    ON myorder0_.app_name=tender4_.app_name
    AND myorder0_.ord_num=tender4_.ord_num
ORDER BY myorder0_.ord_id ASC

这正是我所期待的。（为了简洁起见，我删掉了大部分数据列，但我需要的所有内容都被选中了。）

当查询内存中的H2数据库（使用Spring的@DataJpaTest注释设置）时，在执行此查询之后，将对Tender表进行第二次查询，而不是Transaction。在查询MS SQL数据库时，初始查询是相同的，但对Tender和Transaction都会进行额外的查询。两者都不进行额外的调用来加载Store

我找到的所有源代码都表明.fetchJoin()应该足够了（比如Opinionated JPA with Query DSL；从锚点向上滚动几行），事实上，如果我删除它们，初始查询只会从我的_顺序中选择列。所以看来.fetchJoin()确实会强制生成一个查询，一次性获取所有副表，但由于某些原因，没有使用额外的信息。真正奇怪的是，我确实看到Transaction数据被附加到我的H2准单元测试中，而没有第二次查询（如果且仅当我使用.fetchJoin（）），但在使用MS SQL时却没有

我尝试过用@Fetch(FetchMode.JOIN)注释实体映射，但二次查询仍然会触发。我怀疑可能有一种解决方案涉及到扩展CrudRepository<>，但我甚至没有成功地在那里获得正确的初始查询

我的主要实体映射，使用Lombok的@Data注释，其他字段为了简洁而被删除。（Store、Transaction和Tender都有@Id一些简单的数字和字符串字段列映射，没有@Formula或@OneToOne或其他任何东西。）

@Data
@NoArgsConstructor
@Entity
@Immutable
@Table(name = "MY_ORDER", schema = "Data")
public class MyOrder implements Serializable {

@Id
@Column(name = "ORD_ID")
private Integer orderId;

@NonNull
@Column(name = "APP_NAME")
private String appName;
@NonNull
@Column(name = "ORD_NUM")
private String orderNumber;

@ManyToOne
@JoinColumn(name = "STO_ID")
private Store store;

@OneToOne
@JoinColumns({
        @JoinColumn(name = "APP_NAME", referencedColumnName = "APP_NAME", insertable = false, updatable = false),
        @JoinColumn(name = "ORD_NUM", referencedColumnName = "ORD_NUM", insertable = false, updatable = false)})
@org.hibernate.annotations.ForeignKey(name = "none")
private Tender tender;

@OneToMany
@JoinTable(
        name = "TX_to_MY_ORDER", schema = "Data",
        joinColumns = { // note X_to_MY_ORDER.ORD_NO vs. ORD_NUM
                @JoinColumn(name = "APP_NAM", referencedColumnName = "APP_NAM", insertable = false, updatable = false),
                @JoinColumn(name = "ORD_NO", referencedColumnName = "ORD_NUM", insertable = false, updatable = false)},
        inverseJoinColumns = {@JoinColumn(name = "TRN_NO", insertable = false, updatable = false)})
@org.hibernate.annotations.ForeignKey(name = "none")
private Set<Transaction> transactions;

/**
 * Because APP_NAM and ORD_NUM are not foreign keys to TX_TO_MY_ORDER (and they shouldn't be),
 * Hibernate 5.x saves this toString() as the 'owner' key of the transactions collection such that
 * it then appears in the transactions collection's own .toString(). Lombok's default generated
 * toString() includes this.getTransactions().toString(), which causes an infinite recursive loop.
 * @return a string that is unique per order
 */
@Override
public String toString() {
    // use appName + orderNumber since, as they are the columns used in the join, they must (?) have
    // already been set when attaching the transactions - primary key sometimes isn't set yet.
    return this.appName + "\00" + this.orderNumber;
}
}

我的问题是：为什么我会得到多余的选择，我怎么能不这样做

Answer 1

我回答得有点晚了，但今天同样的问题发生在我身上。这种反应可能对你没有帮助，但至少它会让人免于我们经历的头痛

问题在于实体之间的关系，而不是查询中的关系。我尝试了QueryDSL、JPQL，甚至原生SQL，但问题总是一样的

解决方案是通过在这些连接的字段上用@Id注释子类，诱使JPA相信这些关系存在

基本上，您需要像这样设置Tender的id，并从MyOrder使用它，就像它是正常关系一样

public class Tender {
    @EmbeddedId
    private TenderId id;
}

@Embeddable
public class TenderId {
    @Column(name = "APP_NAME")
    private String appName;

    @Column(name = "ORD_NUM")
    private String orderNumber;
}

对于Transaction实体也是如此