java - Hibernate 查询导致大量额外不必要的查询

Question

我正在开发一个拍卖网站。问题在于我使用的 3 个实体:

• 产品(有零个或多个 ProductBid)
• ProductBid(有零个或一个 ProductBidRejection)
• ProductBidRejection

我使用 hibernate 查询来获取出价:

select pb from ProductBid pb left join pb.rejection pbr where pbr is null and pb.product = :product order by pb.amount desc

这会生成此查询(通过控制台):

select
    productbid0_.id as id4_,
    productbid0_.amount as amount4_,
    productbid0_.bid_by as bid4_4_,
    productbid0_.date as date4_,
    productbid0_.product_id as product5_4_ 
from
    product_bids productbid0_ 
left outer join
    product_bid_rejections productbid1_ 
        on productbid0_.id=productbid1_.product_bid_id 
where
(
    productbid1_.id is null
) 
and productbid0_.product_id=?

但对于每个出价，它还会生成:

select
    productbid0_.id as id3_1_,
    productbid0_.date_rejected as date2_3_1_,
    productbid0_.product_bid_id as product4_3_1_,
    productbid0_.reason as reason3_1_,
    productbid0_.rejected_by as rejected5_3_1_,
    productbid1_.id as id4_0_,
    productbid1_.amount as amount4_0_,
    productbid1_.bid_by as bid4_4_0_,
    productbid1_.date as date4_0_,
    productbid1_.product_id as product5_4_0_ 
from
    product_bid_rejections productbid0_ 
inner join
    product_bids productbid1_ 
        on productbid0_.product_bid_id=productbid1_.id 
where
    productbid0_.product_bid_id=?

这些是我的实体:

ProductBid

@Entity
@Table(name = "product_bids")
public class ProductBid
{
    @Column(name = "id", nullable = false)
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;

    @JoinColumn(name = "product_id", nullable = false)
    @Index(name="product")
    @ManyToOne(fetch = FetchType.LAZY)
    private Product product;

    @Column(name = "amount", nullable = false)
    private BigDecimal amount;

    @JoinColumn(name = "bid_by", nullable = false)
    @Index(name="bidBy")
    @ManyToOne(fetch = FetchType.LAZY)
    @Fetch(FetchMode.JOIN)
    private User bidBy;

    @Column(name = "date", nullable = false)
    @Type(type = "org.joda.time.contrib.hibernate.PersistentDateTime")
    private DateTime date;

    @OneToOne(fetch = FetchType.LAZY, mappedBy = "productBid")
    private ProductBidRejection rejection;
}

ProductBidRejection

@Entity
@Table(name = "product_bid_rejections")
public class ProductBidRejection
{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private long id;<p></p>

<pre><code>@Column(name = "reason", nullable = false, columnDefinition = "TEXT")
private String reason;

@Column(name = "date_rejected", nullable = false)
@Type(type = "org.joda.time.contrib.hibernate.PersistentDateTime")
private DateTime dateRejected;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "rejected_by", nullable = false)
private User rejectedBy;

@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "product_bid_id", nullable = false)
@Fetch(FetchMode.JOIN)
private ProductBid productBid;
</code></pre>

}

Answer 1

这是因为您在 ProductBid 上有 @Fetch(FetchMode.JOIN)。因此，对于您检索的每个 ProductBidRejections，它还会加载一个 ProductBid。

更新

试试这个查询。它将获得不同的 pb 并急切地获取 PBR

从 ProductBid pb 中选择不同的 pb left join fetch pb.rejection pbr where pbr is null and pb.product = :product order by pb.amount desc