mark_dj mark_dj - 1 year ago 53
Java Question

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 as id4_,
productbid0_.amount as amount4_,
productbid0_.bid_by as bid4_4_, as date4_,
productbid0_.product_id as product5_4_
product_bids productbid0_
left outer join
product_bid_rejections productbid1_
( is null
and productbid0_.product_id=?

But for each bid it gets it also generates:

select 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_, as id4_0_,
productbid1_.amount as amount4_0_,
productbid1_.bid_by as bid4_4_0_, as date4_0_,
productbid1_.product_id as product5_4_0_
product_bid_rejections productbid0_
inner join
product_bids productbid1_

These are my entities:


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

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

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

@JoinColumn(name = "bid_by", nullable = false)
@ManyToOne(fetch = FetchType.LAZY)
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;


@Table(name = "product_bid_rejections")
public class ProductBidRejection
@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)
private ProductBid productBid;


Answer Source

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


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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download