Hibernate query causing lots extra unnecassery queries

I am developing a auction site. The problem lies in 3 entities i use:

• Product (has zero or many ProductBid)
• ProductBid (has zero or one ProductBidRejection)
• ProductBidRejection

I use a hibernate query to get the bids:

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

This generates this query (via console):

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=?

But for each bid it gets it also generates:

select
    productbid0_.id as id3_1_,
    productbid0_.date_rejected as date2_3_1_,
    productbid0_.product_bid_id as product4_3_1_,
    productbid0开发者_如何学Go_.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=?

These are my entities:

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;


@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;


}

Its because you have @Fetch(FetchMode.JOIN) on ProductBid. So for each of the ProductBidRejections you retrieve, it also loads a ProductBid.

UPDATE

Try this query. It will get distinct pb and eagerly fetch the PBR

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

Use Criteria instead of HQL your problem will be solve

session.createCriteria(ProductBid.class).add(Restrictions.eq("product",yourproduct)).list();

and in ProductBid Entity Class use annotation to join EAGER ly to ProductBidRejection